public inbox for [email protected]
help / color / mirror / Atom feedFrom: David G. Johnston <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Cc: Daniel Gustafsson <[email protected]>
Cc: pgsql-docs <[email protected]>
Subject: Re: SQL command : ALTER DATABASE OWNER TO
Date: Wed, 24 Jan 2024 09:36:43 -0700
Message-ID: <CAKFQuwYqhMfzzNeqx1HzYYH_bsu1O0H6Sdaux6wgG=_H-h-G8A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <2023185982.281851219.1646733038464.JavaMail.root@zimbra15-e2.priv.proxad.net>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAKFQuwZjb=umdSBrW5diWDbXU25ygFzTKUON2TnfRdXmt9pjTg@mail.gmail.com>
<[email protected]>
On Wed, Jan 24, 2024 at 9:13 AM Laurenz Albe <[email protected]>
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.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL command : ALTER DATABASE OWNER TO
In-Reply-To: <CAKFQuwYqhMfzzNeqx1HzYYH_bsu1O0H6Sdaux6wgG=_H-h-G8A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox