Thread: 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 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)
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
À: "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 <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)
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
-[ 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.
"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
À: "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 <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.
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.
(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
> 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.
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.
-[ 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
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.
David,
reading again your last reply, it reminded me that as soon as we create a database
we revoke default PUBLIC grants (i.e revoke all on <new_database> from public) to grant
only databases privileges to specific roles/users.
we revoke default PUBLIC grants (i.e revoke all on <new_database> from public) to grant
only databases privileges to specific roles/users.
That's why after changing database ownership, we have to (re)grant privileges (ie. grant all on database)
to the old owner...and that's what i forgot to do.
So no problem at all and documentation is OK.
Sorry for the noise !
Sorry for the noise !
Best regards
Gilles
De: "gparc" <gparc@free.fr>
À: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "Laurenz Albe" <laurenz.albe@cybertec.at>, "Daniel Gustafsson" <daniel@yesql.se>, "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoyé: Mercredi 24 Janvier 2024 18:11:30
Objet: Re: SQL command : ALTER DATABASE OWNER TO
À: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "Laurenz Albe" <laurenz.albe@cybertec.at>, "Daniel Gustafsson" <daniel@yesql.se>, "pgsql-docs" <pgsql-docs@lists.postgresql.org>
Envoyé: Mercredi 24 Janvier 2024 18:11:30
Objet: Re: SQL command : ALTER DATABASE OWNER TO
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
À: "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 <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.
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.
(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
On Wed, Jan 24, 2024 at 7:23 AM 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>
<para>
Here's a slightly more detailed patch to consider to cover both the transference of ownership as well as documenting precisely what ownership means.
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index fc03a349f0..c8866ee9c7 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1856,15 +1856,12 @@ ALTER TABLE products RENAME TO items;
<para>
When an object is created, it is assigned an owner. The
- owner is normally the role that executed the creation statement.
- For most kinds of objects, the initial state is that only the owner
- (or a superuser) can do anything with the object. To allow
- other roles to use it, <firstterm>privileges</firstterm> must be
- granted.
+ owner is the role that executed the creation statement
+ unless the statement itself specifies an owner.
</para>
<para>
- There are different kinds of privileges: <literal>SELECT</literal>,
+ There are different kinds of grantable privileges: <literal>SELECT</literal>,
<literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>,
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>,
@@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items;
these privileges are used.
</para>
+ <para>
+ Upon object creation the owner is granted all grantable privileges
+ on the object. Additionally, the built-in PUBLIC privileges of
+ the associated object type are granted. Lastly, if any have been defined,
+ the system grants the default privileges for the object type to the defined roles.
+ All of these privileges can be revoked.
+ </para>
+
<para>
The right to modify or destroy an object is inherent in being the
object's owner, and cannot be granted or revoked in itself.
- (However, like all privileges, that right can be inherited by
+ (However, like the grantable privileges, that right can be inherited by
members of the owning role; see <xref linkend="role-membership"/>.)
</para>
+ <para>
+ Another inherent right the owner of an object has is to grant all
+ grantable privileges on that object to any database role, including
+ their own.
+ </para>
+
<para>
An object can be assigned to a new owner with an <command>ALTER</command>
command of the appropriate kind for the object, for example
@@ -1893,6 +1904,11 @@ 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.
+ The reassignment process involves changing the recorded owner of the object in
+ the appropriate system catalog, as well as changing all references
+ (grantor and grantee) to the old role in the Access Control List (ACL, see below)
+ column to the new role; leaving the old role without any direct privileges on the object.
+ Multiple privilege entries with the same grantor and grantee are consolidated into a single entry.
</para>
<para>
index fc03a349f0..c8866ee9c7 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1856,15 +1856,12 @@ ALTER TABLE products RENAME TO items;
<para>
When an object is created, it is assigned an owner. The
- owner is normally the role that executed the creation statement.
- For most kinds of objects, the initial state is that only the owner
- (or a superuser) can do anything with the object. To allow
- other roles to use it, <firstterm>privileges</firstterm> must be
- granted.
+ owner is the role that executed the creation statement
+ unless the statement itself specifies an owner.
</para>
<para>
- There are different kinds of privileges: <literal>SELECT</literal>,
+ There are different kinds of grantable privileges: <literal>SELECT</literal>,
<literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>,
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>,
@@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items;
these privileges are used.
</para>
+ <para>
+ Upon object creation the owner is granted all grantable privileges
+ on the object. Additionally, the built-in PUBLIC privileges of
+ the associated object type are granted. Lastly, if any have been defined,
+ the system grants the default privileges for the object type to the defined roles.
+ All of these privileges can be revoked.
+ </para>
+
<para>
The right to modify or destroy an object is inherent in being the
object's owner, and cannot be granted or revoked in itself.
- (However, like all privileges, that right can be inherited by
+ (However, like the grantable privileges, that right can be inherited by
members of the owning role; see <xref linkend="role-membership"/>.)
</para>
+ <para>
+ Another inherent right the owner of an object has is to grant all
+ grantable privileges on that object to any database role, including
+ their own.
+ </para>
+
<para>
An object can be assigned to a new owner with an <command>ALTER</command>
command of the appropriate kind for the object, for example
@@ -1893,6 +1904,11 @@ 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.
+ The reassignment process involves changing the recorded owner of the object in
+ the appropriate system catalog, as well as changing all references
+ (grantor and grantee) to the old role in the Access Control List (ACL, see below)
+ column to the new role; leaving the old role without any direct privileges on the object.
+ Multiple privilege entries with the same grantor and grantee are consolidated into a single entry.
</para>
<para>
Laurenz has already commented to my accidentally off-list initial post that this seems to be too much detail for this section. But it is the language specification Chapter, not the Tutorial, and I disagree. It doesn't seem like an internals topic and there just isn't anywhere else to define this stuff. The man pages I suppose work, and this does have some repetition of the material there, but personally this feels like the more correct spot. Some of the "where" language probably can be removed without loss of clarity but I haven't added anything here that isn't already described in even more detail at the end of this section. I just used that material in context.
David J.
On Wed, 2024-01-24 at 16:04 -0700, David G. Johnston wrote: > Here's a slightly more detailed patch to consider to cover both the transference of ownership as well as documenting preciselywhat ownership means. > > diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml > index fc03a349f0..c8866ee9c7 100644 > --- a/doc/src/sgml/ddl.sgml > +++ b/doc/src/sgml/ddl.sgml > @@ -1856,15 +1856,12 @@ ALTER TABLE products RENAME TO items; > > <para> > When an object is created, it is assigned an owner. The > - owner is normally the role that executed the creation statement. > - For most kinds of objects, the initial state is that only the owner > - (or a superuser) can do anything with the object. To allow > - other roles to use it, <firstterm>privileges</firstterm> must be > - granted. > + owner is the role that executed the creation statement > + unless the statement itself specifies an owner. > </para> Ok, you want to describe that in more detail. But you should preserve the <firstterm> when the term is used for the first time. > <para> > - There are different kinds of privileges: <literal>SELECT</literal>, > + There are different kinds of grantable privileges: <literal>SELECT</literal>, > <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, > <literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>, > <literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>, We use "privilege" for the rights you can grant everywhere else, so you'd have to change that all over the documentation. I don't think we should randomly change our terminology. I see that you didn't change the <indexterm>. I understand that you want to disambiguate between "grantable privileges" and "inherent privileges" like the right to drop an object. Note that the documentation is careful to avoid the term "privilege" when speaking about the latter: below, it says "The *right* to modify or destroy an object is inherent in being the object's owner". We should leave that as it is. > @@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items; > these privileges are used. > </para> > > + <para> > + Upon object creation the owner is granted all grantable privileges > + on the object. Additionally, the built-in PUBLIC privileges of > + the associated object type are granted. Lastly, if any have been defined, > + the system grants the default privileges for the object type to the defined roles. > + All of these privileges can be revoked. > + </para> > + Perhaps it would be better to say "has the privileges" than "is granted the privileges", because there is nothing specifically granted (the ACL is NULL). Also, I don't think we should burden the user with ALTER DEFAULT PRIVILEGES right here, where we speak about ownership. Default privileges are discribed in some detail further down, which I believe is sufficient. In the same vein, the page describes further down that "An object's owner can choose to revoke their own ordinary privileges, for example ...". Again, no need to mention it another time (before it is discussed). > <para> > The right to modify or destroy an object is inherent in being the > object's owner, and cannot be granted or revoked in itself. > - (However, like all privileges, that right can be inherited by > + (However, like the grantable privileges, that right can be inherited by > members of the owning role; see <xref linkend="role-membership"/>.) > </para> See above. > + <para> > + Another inherent right the owner of an object has is to grant all > + grantable privileges on that object to any database role, including > + their own. > + </para> > + Again, that's mostly a repetition: "Ordinarily, only the object's owner (or a superuser) can grant or revoke privileges on an object." > @@ -1893,6 +1904,11 @@ 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. > + The reassignment process involves changing the recorded owner of the object in > + the appropriate system catalog, as well as changing all references > + (grantor and grantee) to the old role in the Access Control List (ACL, see below) > + column to the new role; leaving the old role without any direct privileges on the object. > + Multiple privilege entries with the same grantor and grantee are consolidated into a single entry. > </para> This change is fundamentally OK, although I doubt that we need to get as detailed as to how multiple access control items get consolidated. I think we should say "owner" instead of "recorded owner". Also, is it necessary to detail to the level of system catalog columns? Yours, Laurenz Albe
Thank you for the feedback. I've taken it into account and come up with a partial rewrite of my first pass, and posted it to -hackers so we can close this thread out.
Some replies below.
On Thu, Jan 25, 2024 at 7:35 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2024-01-24 at 16:04 -0700, David G. Johnston wrote:
> Here's a slightly more detailed patch to consider to cover both the transference of ownership as well as documenting precisely what ownership means.
Ok, you want to describe that in more detail. But you should preserve
the <firstterm> when the term is used for the first time.
Yeah, I need to get better at looking for and applying semantic markup.
Note that the documentation is careful to avoid the term "privilege"
when speaking about the latter: below, it says "The *right* to modify or
destroy an object is inherent in being the object's owner".
We should leave that as it is.
Agreed. I was going for "first encounter clarification" only, not intending to define the term fully. I've taken a different approach with the new patch - not redefining the main term but adding appropriate qualifications for limited use in learning how things work.
I think we should say "owner" instead of "recorded owner". Also, is it necessary
to detail to the level of system catalog columns?
Agreed, with the overall flow of the content and context improved that specific sub-paragraph can be made much simpler without loss of clarity.
David J.
On Wed, 2024-01-24 at 15:26 +0100, Daniel Gustafsson wrote: > > 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. Here is a patch for this change. Yours, Laurenz Albe