Postgres Pro
Downloads
Home   >   mailing lists

Re: SQL command : ALTER DATABASE OWNER TO - Mailing list pgsql-docs

From gparc@free.fr
Subject Re: SQL command : ALTER DATABASE OWNER TO
Date
Msg-id 1318833190.214290217.1706114489204.JavaMail.zimbra@free.fr
Whole thread Raw
In response to Re: SQL command : ALTER DATABASE OWNER TO  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-docs
Tree view
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 <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

pgsql-docs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: SQL command : ALTER DATABASE OWNER TO
Next
From: "David G. Johnston"
Date:
Subject: Re: SQL command : ALTER DATABASE OWNER TO