Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 7:23 AM Laurenz Albe 
wrote:

> On Wed, 2024-01-24 at 11:08 +0100, gp...@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 table_name
> OWNER TO 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 SET ROLE to the new owning
> role.
> +   All object privileges of the old owner are transferred to the new owner
> +   along with the ownership.
>
>
>
>
>
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;

   
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, privileges must be
-   granted.
+   owner is the role that executed the creation statement
+   unless the statement itself specifies an owner.
   

   
-   There are different kinds of privileges: SELECT,
+   There are different kinds of grantable privileges:
SELECT,
INSERT, UPDATE,
DELETE,
TRUNCATE, REFERENCES,
TRIGGER,
CREATE, CONNECT,
TEMPORARY,
@@ -1877,13 +1874,27 @@ ALTER TABLE products RENAME TO items;
these privileges are used.
   

+  
+   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.
+  
+
   
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 .)
   

+  
+   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.
+  
+
   
An object can be assigned to a new owner with an
ALTER
command of the appropriate kind for the object, for example
@@ -1893,6 +1904,11 @@ ALTER TABLE table_name
OWNER TO 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 SET ROLE 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.
   

   

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.

Re: SQL command : ALTER DATABASE OWNER TO

[gparc]

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  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 ! 

Best regards 
Gilles 


De: "gparc"  
À: "David G. Johnston"  
Cc: "Laurenz Albe" , "Daniel Gustafsson" 
, "pgsql-docs"  
Envoyé: Mercredi 24 Janvier 2024 18:11:30 
Objet: Re: SQL command : ALTER DATABASE OWNER TO 


De: "David G. Johnston"  
À: "gparc"  
Cc: "Laurenz Albe" , "Daniel Gustafsson" 
, "pgsql-docs"  
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:gp...@free.fr | gp...@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 

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 10:13 AM Tom Lane  wrote:

> "David G. Johnston"  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.

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.

Re: SQL command : ALTER DATABASE OWNER TO

[Tom Lane]

"David G. Johnston"  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

Re: SQL command : ALTER DATABASE OWNER TO

[gparc]

De: "David G. Johnston"  
À: "gparc"  
Cc: "Laurenz Albe" , "Daniel Gustafsson" 
, "pgsql-docs"  
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:gp...@free.fr | gp...@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 

Re: SQL command : ALTER DATABASE OWNER TO

[Tom Lane]

"David G. Johnston"  writes:
> On Wed, Jan 24, 2024 at 8:35 AM 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).

> 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

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 9:56 AM Alvaro Herrera 
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.

Re: SQL command : ALTER DATABASE OWNER TO

[Alvaro Herrera]

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

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 9:23 AM  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.

Re: SQL command : ALTER DATABASE OWNER TO

[gparc]

De: "David G. Johnston"  
À: "Laurenz Albe"  
Cc: "gparc" , "Daniel Gustafsson" , 
"pgsql-docs"  
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.a...@cybertec.at | laurenz.a...@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 

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe 
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.

Re: SQL command : ALTER DATABASE OWNER TO

[gparc]

- Mail original -
De: "Laurenz Albe" 
À: "gparc" , "Daniel Gustafsson" 
Cc: "pgsql-docs" 
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, gp...@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

Re: SQL command : ALTER DATABASE OWNER TO

[Laurenz Albe]

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

Re: SQL command : ALTER DATABASE OWNER TO

[David G. Johnston]

On Wed, Jan 24, 2024 at 8:35 AM Laurenz Albe 
wrote:

> On Wed, 2024-01-24 at 15:40 +0100, gp...@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.

Re: Spam complaint

[Alvaro Herrera]

On 2024-Jan-24, Laurenz Albe wrote:

> For months now, I have received this automated reply whenever I post to
> this list:

Yeah, me too -- the oldest I can find is 27th November last year, but
it's quite possible that I deleted older ones without leaving traces.
I unsubscribed this address now, thanks.

-- 
Álvaro HerreraBreisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Las navajas y los monos deben estar siempre distantes"   (Germán Poo)

Re: SQL command : ALTER DATABASE OWNER TO

[Laurenz Albe]

On Wed, 2024-01-24 at 15:40 +0100, gp...@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

Incorrect grammar on ALTER EVENT TRIGGER

[Daniel Gustafsson]

The ALTER EVENT TRIGGER ..  page has an extra TRIGGER in the parameters section
for ENABLE/DISABLE parameters which isn't supported in the grammar:

https://www.postgresql.org/docs/devel/sql-altereventtrigger.html

postgres=# alter event trigger on_login_trigger disable trigger;
ERROR:  syntax error at or near "trigger"
LINE 1: alter event trigger on_login_trigger disable trigger;
 ^
postgres=# alter event trigger on_login_trigger enable trigger;
ERROR:  syntax error at or near "trigger"
LINE 1: alter event trigger on_login_trigger enable trigger;
^
postgres=# alter event trigger on_login_trigger enable always trigger;
ERROR:  syntax error at or near "trigger"
LINE 1: alter event trigger on_login_trigger enable always trigger;
   ^
The attached trivial patch removes the extra keyword, which needs to be
backpatched all the way down.

--
Daniel Gustafsson



alter_evt.diff
Description: Binary data

Re: SQL command : ALTER DATABASE OWNER TO

[gparc]

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" 
À: "Laurenz Albe" 
Cc: "gparc" , "pgsql-docs" 
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  wrote:
> 
> On Wed, 2024-01-24 at 11:08 +0100, gp...@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 table_name OWNER 
> TO 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 SET ROLE to the new owning 
> role.
> +   All object privileges of the old owner are transferred to the new owner
> +   along with the ownership.
>   

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

Spam complaint

[Laurenz Albe]

For months now, I have received this automated reply whenever I post to
this list:

 Forwarded Message 
From: 471266196 <471266...@qq.com>
To: Laurenz Albe 
Subject: 自动回复: Re: SQL command : ALTER DATABASE OWNER TO
Date: 01/24/2024 03:23:31 PM

邮件已收到，我会尽快处理。谢谢！
---

Seems like that person will never return to the office.
Could someone please unsubscribe that address?

Yours,
Laurenz Albe

Re: SQL command : ALTER DATABASE OWNER TO

[Daniel Gustafsson]

> On 24 Jan 2024, at 15:23, Laurenz Albe  wrote:
> 
> On Wed, 2024-01-24 at 11:08 +0100, gp...@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 table_name OWNER 
> TO 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 SET ROLE to the new owning 
> role.
> +   All object privileges of the old owner are transferred to the new owner
> +   along with the ownership.
>   

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

Re: SQL command : ALTER DATABASE OWNER TO

[Laurenz Albe]

On Wed, 2024-01-24 at 11:08 +0100, gp...@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 table_name OWNER 
TO 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 SET ROLE to the new owning role.
+   All object privileges of the old owner are transferred to the new owner
+   along with the ownership.
   
 
   

Yours,
Laurenz Albe

Re: SQL command : ALTER DATABASE OWNER TO

[gparc]

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" 
À: "pgsql-docs" 
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

Re: Missing information on '-X' in section 26.3.6.1.

[Daniel Gustafsson]

> On 23 Jan 2024, at 21:43, David G. Johnston  
> wrote:
> 
> On Tue, Jan 23, 2024 at 1:30 PM PG Doc comments form  > wrote:
> The following documentation comment has been logged on the website:
> 
> Page: https://www.postgresql.org/docs/16/continuous-archiving.html 
> 
> Description:
> 
> I noticed, that in section 26.3.6.1. it's not specified, what the -X
> parameter should be set to (stream or fetch, or whether it even matters). I
> could continue with trial and error, but it confused me a bit.
> 
> The -X parameter is documented to have a default; but since both fetch and 
> stream are documented to give you the same end result it doesn't matter.  Of 
> course you cannot specify the none method.

Agreed.  Still, it doesn't hurt to spell out what we take for granted but a
newcomer have to figure out in order to make the documentation easy to follow
for new users. Something like the attached would be enough I think.
--
Daniel Gustafsson



pg_basebackup_X.diff
Description: Binary data