On Wed, Jan 24, 2024 at 9:56 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote:
> On 2024-Jan-24, Laurenz Albe wrote: > > > The permissions are transferred to the new owner, so the old owner > doesn't > > have any privileges on the object (and, in your case, cannot connect to > > the database any more). > > However, if the old owner had a pg_hba.conf line that allowed them in, > and the new owner doesn't, then they're now both locked out of the > database with no recourse. > > The OP doesn't actually care about inherited permissions, just the stated ones. That said, I do think there is a problem here: postgres=# select current_user; -[ RECORD 1 ]+------- current_user | davidj postgres=# revoke all on database newdb2 from public; REVOKE postgres=# \l newdb2 List of databases -[ RECORD 1 ]-----+------------------------ Name | newdb2 Owner | testowner Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale | ICU Rules | Access privileges | testowner=CTc/testowner postgres=# grant all on database newdb2 to testowner; -- as I am logged in as davidj this grant should actually happen, with davidj as the grantor -- the grants that materialize from ownership has the owning role as the grantor -- it is only those that should be removed upon reassigning ownership GRANT postgres=# \l newdb2 List of databases -[ RECORD 1 ]-----+------------------------ Name | newdb2 Owner | testowner Encoding | UTF8 Locale Provider | libc Collate | en_US.UTF-8 Ctype | en_US.UTF-8 ICU Locale | ICU Rules | Access privileges | testowner=CTc/testowner -- I expect to see "testowner=CTc/davidj" here as well David J.
