Re: SQL command : ALTER DATABASE OWNER TO
| От | David G. Johnston |
|---|---|
| Тема | Re: SQL command : ALTER DATABASE OWNER TO |
| Дата | |
| Msg-id | CAKFQuwbFrbdOWCNDgE49RRh07sTAuRODPR0BV1=-nVA01rFFrg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: SQL command : ALTER DATABASE OWNER TO (Alvaro Herrera <alvherre@alvh.no-ip.org>) |
| Ответы |
Re: SQL command : ALTER DATABASE OWNER TO
|
| Список | pgsql-docs |
On Wed, Jan 24, 2024 at 9:56 AM Alvaro Herrera <alvherre@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
-[ 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;
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
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.
В списке pgsql-docs по дате отправления: