SQL command : ALTER DATABASE OWNER TO
Hello,
for this "ALTER DATABASE" form, it should be mentioned that after execution of the command,
the old database owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it.
Thanks in advance to add this important precision.
Regards
Gilles
Import Notes
Reply to msg id not found: 1216810578.281812820.1646732222284.JavaMail.root@zimbra15-e2.priv.proxad.net
On Tue, Mar 8, 2022 at 10:50:38AM +0100, gparc@free.fr wrote:
Hello,
for this "ALTER DATABASE" form, it should be mentioned that after execution of the command,
the old database owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it.Thanks in advance to add this important precision.
Uh, the original owner is not the owner anymore, so why would they
assume they can reconnect, unless there is some other permission
specified for them.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
On Tue, Mar 8, 2022 at 7:39 AM Bruce Momjian <bruce@momjian.us> wrote:
On Tue, Mar 8, 2022 at 10:50:38AM +0100, gparc@free.fr wrote:
Hello,
for this "ALTER DATABASE" form, it should be mentioned that after
execution of the command,
the old database owner loses all his privileges on it (even connection)
although it might
still owns schemas or objects (tables, index,...) inside it.
Thanks in advance to add this important precision.
Uh, the original owner is not the owner anymore, so why would they
assume they can reconnect, unless there is some other permission
specified for them.
Agreed. The proposed solution simply addresses a single symptom of what
may be a misunderstanding about how the system works (i.e., that an object
can only have a single owner, and, each privilege is specific to an object
and does not confer any implied privileges on container objects - schemas
and databases namely).
If there is a suggestion to improve the core misunderstandings that is
something to consider. Ideally in a central place about permissions in
general and not in the specific ALTER DATABASE command.
Given that the default behavior of PostgreSQL is to grant CONNECT via
PUBLIC, removing ownership of a database from a role does not, by default,
remove their connect privilege.
David J.
Hello,
as one of my colleagues fell into the same trap
I repeat my request for correction of the documentation on this subject.
Many thanks in advance
Regards
Gilles
----- Mail original -----
De: "gparc" <gparc@free.fr>
À: "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoyé: Mardi 8 Mars 2022 10:50:38
Objet: SQL command : ALTER DATABASE OWNER TO
Hello,
for this "ALTER DATABASE" form, it should be mentioned that after execution of the command,
the old database owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it.
Thanks in advance to add this important precision.
Regards
Gilles
On Wed, 2024-01-24 at 11:08 +0100, gparc@free.fr wrote:
for this "ALTER DATABASE" form, it should be mentioned that after execution of the command,
the old database owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it.Thanks in advance to add this important precision.
How about this:
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 4044f0908f..44042f863c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1891,6 +1891,8 @@ ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owne
Superusers can always do this; ordinary roles can only do it if they are
both the current owner of the object (or inherit the privileges of the
owning role) and able to <literal>SET ROLE</literal> to the new owning role.
+ All object privileges of the old owner are transferred to the new owner
+ along with the ownership.
</para><para>
Yours,
Laurenz Albe
On 24 Jan 2024, at 15:23, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-01-24 at 11:08 +0100, gparc@free.fr wrote:
for this "ALTER DATABASE" form, it should be mentioned that after execution of the command,
the old database owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it.Thanks in advance to add this important precision.
How about this:
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 4044f0908f..44042f863c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1891,6 +1891,8 @@ ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owne Superusers can always do this; ordinary roles can only do it if they are both the current owner of the object (or inherit the privileges of the owning role) and able to <literal>SET ROLE</literal> to the new owning role. + All object privileges of the old owner are transferred to the new owner + along with the ownership. </para>
Doesn't seem unreasonable to me, it won't make the docs harder to read and use
for experienced users while it may make them easier to follow for new users.
--
Daniel Gustafsson
Hello,
maybe a misunderstanding of my part, but your proposed modification doesn't matched
with the current behaviour of the command as precisely the object privileges of the old owner are **NOT** transferred
to the new owner along with the ownership
Regards
Gilles
----- Mail original -----
De: "Daniel Gustafsson" <daniel@yesql.se>
À: "Laurenz Albe" <laurenz.albe@cybertec.at>
Cc: "gparc" <gparc@free.fr>, "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoyé: Mercredi 24 Janvier 2024 15:26:22
Objet: Re: SQL command : ALTER DATABASE OWNER TO
On 24 Jan 2024, at 15:23, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-01-24 at 11:08 +0100, gparc@free.fr wrote:
for this "ALTER DATABASE" form, it should be mentioned that after execution of the command,
the old database owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it.Thanks in advance to add this important precision.
How about this:
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 4044f0908f..44042f863c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1891,6 +1891,8 @@ ALTER TABLE <replaceable>table_name</replaceable> OWNER TO <replaceable>new_owne Superusers can always do this; ordinary roles can only do it if they are both the current owner of the object (or inherit the privileges of the owning role) and able to <literal>SET ROLE</literal> to the new owning role. + All object privileges of the old owner are transferred to the new owner + along with the ownership. </para>
Doesn't seem unreasonable to me, it won't make the docs harder to read and use
for experienced users while it may make them easier to follow for new users.
--
Daniel Gustafsson
On Wed, 2024-01-24 at 15:40 +0100, gparc@free.fr wrote:
maybe a misunderstanding of my part, but your proposed modification doesn't matched
with the current behaviour of the command as precisely the object privileges of the old owner are **NOT** transferred
to the new owner along with the ownership
But that is what happens.
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).
Yours,
Laurenz Albe
On Wed, Jan 24, 2024 at 8:35 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
On Wed, 2024-01-24 at 15:40 +0100, gparc@free.fr wrote:
maybe a misunderstanding of my part, but your proposed modification
doesn't matched
with the current behaviour of the command as precisely the object
privileges of the old owner are **NOT** transferred
to the new owner along with the ownership
But that is what happens.
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).
I dislike this change, ownership of an object is completely independent of
the grant system of privileges. The granted privileges of the old row do
not transfer to the new owner when alter ... owner to is executed. The
separate object attribute "owner" is the only thing that changes. If the
old owner doesn't have any granted privileges on the modified object then
they will be left with no ability to interact with that object. In the
case of Database the applicable interactions are Create and Connect. The
permissions the old owner may have on any other objects in the database are
also left unaffected - such as those on a schema. But if they have lost
the ability to Connect then actually exercising schema privileges becomes
impossible. It really isn't any different than removing their login
attribute.
Note that since PUBLIC gets connect privileges on all databases by
default...
David J.
On Wed, 2024-01-24 at 08:47 -0700, David G. Johnston wrote:
I dislike this change, ownership of an object is completely independent of
the grant system of privileges. The granted privileges of the old row do
not transfer to the new owner when alter ... owner to is executed.
CREATE TABLE mytab ();
REVOKE ALL ON mytab FROM PUBLIC;
\z mytab
Access privileges
Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies
════════╪═══════╪═══════╪═══════════════════════════╪═══════════════════╪══════════
public │ mytab │ table │ postgres=arwdDxt/postgres │ │
(1 row)
ALTER TABLE mytab OWNER TO laurenz;
\z mytab
Access privileges
Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies
════════╪═══════╪═══════╪═════════════════════════╪═══════════════════╪══════════
public │ mytab │ table │ laurenz=arwdDxt/laurenz │ │
(1 row)
Yours,
Laurenz Albe
----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
À: "gparc" <gparc@free.fr>, "Daniel Gustafsson" <daniel@yesql.se>
Cc: "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoyé: Mercredi 24 Janvier 2024 16:35:10
Objet: Re: SQL command : ALTER DATABASE OWNER TO
On Wed, 2024-01-24 at 15:40 +0100, gparc@free.fr wrote:
maybe a misunderstanding of my part, but your proposed modification doesn't matched
with the current behaviour of the command as precisely the object privileges of the old owner are **NOT** transferred
to the new owner along with the ownership
But that is what happens.
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).
Yours,
Laurenz Albe
Laurenz,
may be better with an example to explain what I mean with "the old database owner loses all his privileges on it (even connection) although it might
still owns schemas or objects (tables, index,...) inside it"
[postgres] $ psql
psql (14.10)
[postgres@PGDEV14] postgres=# create user tst password 'tst';
CREATE ROLE
[postgres@PGDEV14] postgres=# create database tst owner = tst;
CREATE DATABASE
[postgres@PGDEV14] postgres=# grant all on database tst to tst;
GRANT
[postgres@PGDEV14] postgres=# \l+ tst
Liste des bases de données
Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description
-----+--------------+----------+-----------------+--------------+----------------+---------+------------+-------------
tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/tst +| 9809 kB | pg_default |
| | | | | tst=CTc/tst | | |
(1 ligne)
[postgres@PGDEV14] postgres=# \c tst tst
Mot de passe pour l'utilisateur tst :
Vous êtes maintenant connecté à la base de données « tst » en tant qu'utilisateur « tst ».
[tst@PGDEV14] tst=> create schema tst;
CREATE SCHEMA
[tst@PGDEV14] tst=> create table t1 (x int);
CREATE TABLE
[tst@PGDEV14] tst=> \dn+ tst
Liste des schémas
Nom | Propriétaire | Droits d'accès | Description
-----+--------------+----------------+-------------
tst | tst | |
(1 ligne)
[tst@PGDEV14] tst=> \dt+ t1
Liste des relations
Schéma | Nom | Type | Propriétaire | Persistence | Méthode d'accès | Taille | Description
--------+-----+-------+--------------+-------------+-----------------+---------+-------------
tst | t1 | table | tst | permanent | heap | 0 bytes |
(1 ligne)
[tst@PGDEV14] tst=> \c - postgres
Vous êtes maintenant connecté à la base de données « tst » en tant qu'utilisateur « postgres ».
[postgres@PGDEV14] tst=# alter database tst owner to postgres;
ALTER DATABASE
[postgres@PGDEV14] tst=# \l+ tst
Liste des bases de données
Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description
-----+--------------+----------+-----------------+--------------+-----------------------+---------+------------+-------------
tst | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/postgres +| 9809 kB | pg_default |
| | | | | postgres=CTc/postgres | | |
(1 ligne)
[postgres@PGDEV14] tst=# \dn+ tst
Liste des schémas
Nom | Propriétaire | Droits d'accès | Description
-----+--------------+----------------+-------------
tst | tst | |
(1 ligne)
[postgres@PGDEV14] tst=# \dt tst.t1
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+-----+-------+--------------
tst | t1 | table | tst
(1 ligne)
Regards
Gilles
On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
On Wed, 2024-01-24 at 08:47 -0700, David G. Johnston wrote:
I dislike this change, ownership of an object is completely independent
of
the grant system of privileges. The granted privileges of the old row do
not transfer to the new owner when alter ... owner to is executed.CREATE TABLE mytab ();
REVOKE ALL ON mytab FROM PUBLIC;
\z mytab
Access privileges
Schema │ Name │ Type │ Access privileges │ Column privileges │
Policies════════╪═══════╪═══════╪═══════════════════════════╪═══════════════════╪══════════
public │ mytab │ table │ postgres=arwdDxt/postgres │ │
(1 row)ALTER TABLE mytab OWNER TO laurenz;
\z mytab
Access privileges
Schema │ Name │ Type │ Access privileges │ Column privileges │
Policies════════╪═══════╪═══════╪═════════════════════════╪═══════════════════╪══════════
public │ mytab │ table │ laurenz=arwdDxt/laurenz │ │
(1 row)
You need to actually revoke something to make the point stand out.
postgres=# \z tt1
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
--------+------+-------+-----------------------+-------------------+----------
public | tt1 | table | davidj=arwdDxt/davidj | |
(1 row)
postgres=# revoke update on tt1 from davidj;
REVOKE
postgres=# \z tt1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+----------------------+-------------------+----------
public | tt1 | table | davidj=ardDxt/davidj | |
(1 row)
postgres=# alter table tt1 owner to testowner;
ALTER TABLE
postgres=# \z tt1
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
--------+------+-------+----------------------------+-------------------+----------
public | tt1 | table | testowner=ardDxt/testowner | |
(1 row)
The new owner, testowner, is missing the same update privilege that davidj
removed from himself. In short, setting owner does indeed cause explicit
grants to appear in the system, grants that can be revoked. And so, yes,
transferring ownership transfers the set of grants currently in effect for
the existing owner.
I can see making this detail more clear in the DDL chapter. It is
unrelated to the confusion behind the topic of this thread though.
David J.
De: "David G. Johnston" <david.g.johnston@gmail.com>
À: "Laurenz Albe" <laurenz.albe@cybertec.at>
Cc: "gparc" <gparc@free.fr>, "Daniel Gustafsson" <daniel@yesql.se>, "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoyé: Mercredi 24 Janvier 2024 17:36:43
Objet: Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe < [ mailto:laurenz.albe@cybertec.at | laurenz.albe@cybertec.at ] > wrote:
On Wed, 2024-01-24 at 08:47 -0700, David G. Johnston wrote:
I dislike this change, ownership of an object is completely independent of
the grant system of privileges. The granted privileges of the old row do
not transfer to the new owner when alter ... owner to is executed.
CREATE TABLE mytab ();
REVOKE ALL ON mytab FROM PUBLIC;
\z mytab
Access privileges
Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies
════════╪═══════╪═══════╪═══════════════════════════╪═══════════════════╪══════════
public │ mytab │ table │ postgres=arwdDxt/postgres │ │
(1 row)
ALTER TABLE mytab OWNER TO laurenz;
\z mytab
Access privileges
Schema │ Name │ Type │ Access privileges │ Column privileges │ Policies
════════╪═══════╪═══════╪═════════════════════════╪═══════════════════╪══════════
public │ mytab │ table │ laurenz=arwdDxt/laurenz │ │
(1 row)
You need to actually revoke something to make the point stand out.
postgres=# \z tt1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-----------------------+-------------------+----------
public | tt1 | table | davidj=arwdDxt/davidj | |
(1 row)
postgres=# revoke update on tt1 from davidj;
REVOKE
postgres=# \z tt1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+----------------------+-------------------+----------
public | tt1 | table | davidj=ardDxt/davidj | |
(1 row)
postgres=# alter table tt1 owner to testowner;
ALTER TABLE
postgres=# \z tt1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+----------------------------+-------------------+----------
public | tt1 | table | testowner=ardDxt/testowner | |
(1 row)
The new owner, testowner, is missing the same update privilege that davidj removed from himself. In short, setting owner does indeed cause explicit grants to appear in the system, grants that can be revoked. And so, yes, transferring ownership transfers the set of grants currently in effect for the existing owner.
I can see making this detail more clear in the DDL chapter. It is unrelated to the confusion behind the topic of this thread though.
David J.
Hello again,
note that my point concerns "alter database" not "alter table".
See my last reply for an example
Regards
On Wed, Jan 24, 2024 at 9:23 AM <gparc@free.fr> wrote:-
[postgres] $ psql
psql (14.10)
You really should add commentary, especially since you never demonstrated
the tst role (I advise picking different names for all of the objects in
the future) being unable to login. Which they should be able to since
public is shown to have "c" connect privileges (=Tc/tst)
[postgres@PGDEV14] postgres=# create user tst password 'tst';
CREATE ROLE
[postgres@PGDEV14] postgres=# create database tst owner = tst;
CREATE DATABASE
This next command is pointless, it is a no-op, as soon as you made them
owner of the tst database they already had all privileges to it, granted by
the same user that created the database. And only it, that command is not
recursing through the database into schemas and tables and adding more
permissions. That isn't how this all works, a database is an object.
While it is also a concept that encompasses the entire schema within it the
permissions system only cares about the first definition.
[postgres@PGDEV14] postgres=# grant all on database tst to tst;
GRANT
[postgres@PGDEV14] postgres=# \l+ tst
Liste des bases de données
Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits
d'accès | Taille | Tablespace | Description-----+--------------+----------+-----------------+--------------+----------------+---------+------------+------------- tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/tst +| 9809 kB | pg_default | | | | | | tst=CTc/tst | | | (1 ligne)
What are you trying to demonstrate here?
[postgres@PGDEV14] tst=# \dn+ tst
Liste des schémas
Nom | Propriétaire | Droits d'accès | Description
-----+--------------+----------------+-------------
tst | tst | |
(1 ligne)
David J.
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.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"You don't solve a bad join with SELECT DISTINCT" #CupsOfFail
https://twitter.com/connor_mc_d/status/1431240081726115845
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
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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
On Wed, Jan 24, 2024 at 8:35 AM Laurenz Albe <laurenz.albe@cybertec.at>
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).
I dislike this change, ownership of an object is completely independent of
the grant system of privileges. The granted privileges of the old row do
not transfer to the new owner when alter ... owner to is executed. The
separate object attribute "owner" is the only thing that changes.
Laurenz is correct, as you can easily find out by testing. For
example,
regression=# create user joe;
CREATE ROLE
regression=# create database joe owner joe;
CREATE DATABASE
regression=# grant connect on database joe to joe;
GRANT
regression=# select datacl from pg_database where datname = 'joe';
datacl
-----------------------
{=Tc/joe,joe=CTc/joe}
(1 row)
regression=# create user bob;
CREATE ROLE
regression=# alter database joe owner to bob;
ALTER DATABASE
regression=# select datacl from pg_database where datname = 'joe';
datacl
-----------------------
{=Tc/bob,bob=CTc/bob}
(1 row)
If no explicit GRANTs have ever been done, so that the ACL column
is null, then it stays null --- but that has the same effect,
because the default privileges implied by the null entry now attach
to the new owner.
For myself, I thought Laurenz's proposed patch is an improvement.
regards, tom lane
De: "David G. Johnston" <david.g.johnston@gmail.com>
À: "gparc" <gparc@free.fr>
Cc: "Laurenz Albe" <laurenz.albe@cybertec.at>, "Daniel Gustafsson" <daniel@yesql.se>, "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoyé: Mercredi 24 Janvier 2024 17:50:17
Objet: Re: SQL command : ALTER DATABASE OWNER TO
On Wed, Jan 24, 2024 at 9:23 AM < [ mailto:gparc@free.fr | gparc@free.fr ] > wrote: -
[postgres] $ psql
psql (14.10)
You really should add commentary, especially since you never demonstrated the tst role (I advise picking different names for all of the objects in the future) being unable to login. Which they should be able to since public is shown to have "c" connect privileges (=Tc/tst)
BQ_BEGIN
[postgres@PGDEV14] postgres=# create user tst password 'tst';
CREATE ROLE
[postgres@PGDEV14] postgres=# create database tst owner = tst;
CREATE DATABASE
BQ_END
This next command is pointless, it is a no-op, as soon as you made them owner of the tst database they already had all privileges to it, granted by the same user that created the database. And only it, that command is not recursing through the database into schemas and tables and adding more permissions. That isn't how this all works, a database is an object. While it is also a concept that encompasses the entire schema within it the permissions system only cares about the first definition.
BQ_BEGIN
[postgres@PGDEV14] postgres=# grant all on database tst to tst;
GRANT
[postgres@PGDEV14] postgres=# \l+ tst
Liste des bases de données
Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès | Taille | Tablespace | Description
-----+--------------+----------+-----------------+--------------+----------------+---------+------------+-------------
tst | tst | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =Tc/tst +| 9809 kB | pg_default |
| | | | | tst=CTc/tst | | |
(1 ligne)
BQ_END
What are you trying to demonstrate here?
BQ_BEGIN
[postgres@PGDEV14] tst=# \dn+ tst
Liste des schémas
Nom | Propriétaire | Droits d'accès | Description
-----+--------------+----------------+-------------
tst | tst | |
(1 ligne)
BQ_END
David J.
David,
what I wanted to demonstrate/convey is that when I alter the ownership of a **database**, the old owner loses all his privileges on it
(even CONNECT) although he still owns schema and objects (table, index,..) inside it.
As such, he can't use his own schema anymore.
That's why I propose to update the documentation as it's weird, at least for me, when you get caught by this behaviour.
Regards
Gilles
"David G. Johnston" <david.g.johnston@gmail.com> writes:
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
Yes. The FM points out somewhere that if a superuser does a GRANT,
it's executed as though by the object owner. That provision predates
when we supported explicit GRANTED BY clauses in GRANT. I'm not sure
we'd have made it work like that if we had GRANTED BY already, but
I'm afraid of the compatibility implications if we change it now.
regards, tom lane
On Wed, Jan 24, 2024 at 10:13 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
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
grantorYes. The FM points out somewhere that if a superuser does a GRANT,
it's executed as though by the object owner. That provision predates
when we supported explicit GRANTED BY clauses in GRANT. I'm not sure
we'd have made it work like that if we had GRANTED BY already, but
I'm afraid of the compatibility implications if we change it now.
Agreed, and I do recall that - it is documented on the GRANT page. Also
noted is I can "inherit ownership" if I exercise that inherited ability the
resultant grant still comes from the owner. This unifies two of three ways
for these grants to be established.
If I give out the ability via a grant option only then does the grantor
become the grant optioned role. This is the expected behavior and doesn't
require documentation explicitly.
The following testing of this behavior surprises me though:
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+
| to3=C*T*c*/testowner +
| to4=CTc/to3 +
| testowner=CTc/to3
postgres=> reset role;
RESET
postgres=# alter database newdb2 owner to davidj;
ALTER DATABASE
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-----+------------------
Name | newdb2
Owner | davidj
Encoding | UTF8
Locale Provider | libc
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale |
ICU Rules |
Access privileges | davidj=CTc/davidj+
| to3=C*T*c*/davidj+
| to4=CTc/to3 +
| davidj=CTc/to3
I was expecting the privileges given to me by to3 to remain in place even
after I lost my ownership grants.
As you've noted it seems unlikely this is something we are willing to
change at this point. So, in short, it seems impossible for an owner of an
object to be left with any direct permissions on said object after having
their ownership reassigned. The role which gets the new assignment assumes
all of the explicit grants that exist for the old role.
postgres=# alter database newdb2 owner to to3;
ALTER DATABASE
postgres=# \l newdb2
List of databases
-[ RECORD 1 ]-----+---------------
Name | newdb2
Owner | to3
Encoding | UTF8
Locale Provider | libc
Collate | en_US.UTF-8
Ctype | en_US.UTF-8
ICU Locale |
ICU Rules |
Access privileges | to3=C*T*c*/to3+
| to4=CTc/to3
This makes sense since the three grants that to3 would have after merging
are consolidated into a single one - in an additive sense and the grant
options being retained if present.
David J.
