Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSiZ6-00FTrA-8B for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 19:05:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rSiZ4-007Yiw-GO for pgsql-docs@arkaria.postgresql.org; Wed, 24 Jan 2024 19:05:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rSiZ4-007Yio-7N for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 19:05:34 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rSiYx-003WIb-EV for pgsql-docs@lists.postgresql.org; Wed, 24 Jan 2024 19:05:33 +0000 Received: by mail-ot1-x32a.google.com with SMTP id 46e09a7af769-6de424cef01so2934473a34.2 for ; Wed, 24 Jan 2024 11:05:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1706123125; x=1706727925; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=86Q4Ohql+uH2qrUX6UwseoxSp7Q9RkS7CWmmE2QtamI=; b=JVbNXnzqlmQcGNH+hX+UjKSszKc6/K0LIU6/QyLCJ8XqEYIuuBgOSsuWV//JW561zF AEiNh3+svj3RjqDTxLUi2xRcHFVUjNnEMm5jnrAzEnUaKDWw1TM7j/v37HWW9OFSuc/z 5iB4V+btBLLfLnM/9JsYmVSy2wejJv0pp63lzrtn5zm9MCxY+Nq4Q1htrWuYl3MAus+y dqOi6SS2lXM4z82ntMX/iZrRJ2lN1bMDD5L1/+hvUXS81d960fwGJxA/pYrwMoYg7JpY PI1NVH0iIEllYDz8eG6SmrSRY2anCBWIC3C6ltGiJrsCwF4LO7K8e7hJzNBCLSWog+4+ Lzkw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1706123125; x=1706727925; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=86Q4Ohql+uH2qrUX6UwseoxSp7Q9RkS7CWmmE2QtamI=; b=hf4ym6YIADCdxhX1MGaw4LP/u5ZFE6Fw0jsLgJ1Pe8nN2KUxAAf7fMiHHCORenbVTY Qmnp+bgFNu8k1ZY30XDZrv0IHLiscuzX5yQc3yvLSZgXFPYc0jzhomN7F7+nglXDWjdR 24LSmTjXZEzoavwyq9hAsBlLR+s95SMa0kPpRpLBn/dXh+XCx7jy9pbY6cWiTLQjUFLd LavmUjGmuS9nvk4I5S3ex8luq1FBW7NDK/f8eCgFSv3JoXaCSefGOm5iKN0sX8T+ZWNV 0F9sj31FaxZZohB9bTGyMi+6R/UdfyghiEl/eC1Fb8Eb7Zf8M+1ofl/pDjfsda4PnsAz unVA== X-Gm-Message-State: AOJu0Yz3+XfrhdvbGQwotUwe2n3+jMIBAapP2kqK95u/OoIIw/EUsTw+ wSUeKIf1F0SlytHq3OQf4SWQRwaH+wuPBPQSs8Z9HFWtkH42TMfm+SIeN946qTsSSh31tXx6ins 9SVcllB4ZIKsvP5O5zC80xk9rig0= X-Google-Smtp-Source: AGHT+IG7mkBwoOCe2ona7qYXTHCRB3qFoUL3VVRyd73mB3MgYhxE7XMr9Zmg4hd+oP7VdHu1Cjyp2awQcVC9DCjNLUQ= X-Received: by 2002:a05:6870:2152:b0:214:8d2f:cb0e with SMTP id g18-20020a056870215200b002148d2fcb0emr2436237oae.113.1706123125221; Wed, 24 Jan 2024 11:05:25 -0800 (PST) MIME-Version: 1.0 References: <202401241656.hbks4btwvjnf@alvherre.pgsql> <167867.1706116393@sss.pgh.pa.us> In-Reply-To: <167867.1706116393@sss.pgh.pa.us> From: "David G. Johnston" Date: Wed, 24 Jan 2024 12:04:48 -0700 Message-ID: Subject: Re: SQL command : ALTER DATABASE OWNER TO To: Tom Lane Cc: Alvaro Herrera , Laurenz Albe , gparc@free.fr, Daniel Gustafsson , pgsql-docs Content-Type: multipart/alternative; boundary="000000000000d26bdb060fb5be3a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d26bdb060fb5be3a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Wed, Jan 24, 2024 at 10:13=E2=80=AFAM Tom Lane wrote= : > "David G. Johnston" writes: > > postgres=3D# 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 th= e > > 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=3DCTc/testowner+ | to3=3DC*T*c*/testowner + | to4=3DCTc/to3 + | testowner=3DCTc/to3 postgres=3D> reset role; RESET postgres=3D# alter database newdb2 owner to davidj; ALTER DATABASE postgres=3D# \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=3DCTc/davidj+ | to3=3DC*T*c*/davidj+ | to4=3DCTc/to3 + | davidj=3DCTc/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=3D# alter database newdb2 owner to to3; ALTER DATABASE postgres=3D# \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=3DC*T*c*/to3+ | to4=3DCTc/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. --000000000000d26bdb060fb5be3a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Wed, Jan 24, 2024 at 10:13=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> wr= ites:
> postgres=3D# 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 t= he
> grantor

Yes.=C2=A0 The FM points out somewhere that if a superuser does a GRANT, it's executed as though by the object owner.=C2=A0 That provision preda= tes
when we supported explicit GRANTED BY clauses in GRANT.=C2=A0 I'm not s= ure
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 docum= ented on the GRANT page.=C2=A0 Also noted is I can "inherit ownership&= quot; if I exercise that inherited ability the resultant grant still comes = from the owner.=C2=A0 This unifies two of three ways for these grants to be= established.

If I give out the ability via a grant op= tion only then does the grantor become the grant optioned role.=C2=A0 This = is the expected behavior and doesn't require documentation explicitly.<= /div>

The following testing of this behavior surprises me th= ough:

List of databases
-[ RECORD 1 ]-----+--------= ----------------
Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| = newdb2
Owner =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | testowner
En= coding =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| UTF8
Locale Provider =C2=A0 |= libc
Collate =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | en_US.UTF-8
Ctype = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | en_US.UTF-8
ICU Locale =C2= =A0 =C2=A0 =C2=A0 =C2=A0|
ICU Rules =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
Acc= ess privileges | testowner=3DCTc/testowner+
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | to3=3DC*T*c*/testowner =C2=A0 +
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | to4=3DCTc/to3= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0+
=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | testowner=3DCTc/to3

postgres=3D= > reset role;
RESET
postgres=3D# alter database newdb2 owner to da= vidj;
ALTER DATABASE
postgres=3D# \l newdb2
List of databases
-= [ RECORD 1 ]-----+------------------
Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| newdb2
Owner =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | davidj
Encoding =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| UTF8
Loc= ale Provider =C2=A0 | libc
Collate =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = en_US.UTF-8
Ctype =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | en_US.UTF-= 8
ICU Locale =C2=A0 =C2=A0 =C2=A0 =C2=A0|
ICU Rules =C2=A0 =C2=A0 =C2= =A0 =C2=A0 |
Access privileges | davidj=3DCTc/davidj+
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | to3=3DC*T*c*/davidj+
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | to4=3DCTc/= to3 =C2=A0 =C2=A0 =C2=A0+
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | davidj=3DCTc/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 someth= ing we are willing to change at this point.=C2=A0 So, in short, it seems im= possible for an owner of an object to be left with any direct permissions o= n said object after having their ownership reassigned.=C2=A0 The role which= gets the new assignment assumes all of the explicit grants that exist for = the old role.

postgres=3D# alter database newdb2 owner= to to3;
ALTER DATABASE
postgres=3D# \l newdb2
List of databases-[ RECORD 1 ]-----+---------------
Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0| newdb2
Owner =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | to3
Encoding =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| UTF8
Locale= Provider =C2=A0 | libc
Collate =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | en_= US.UTF-8
Ctype =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | en_US.UTF-8ICU Locale =C2=A0 =C2=A0 =C2=A0 =C2=A0|
ICU Rules =C2=A0 =C2=A0 =C2=A0= =C2=A0 |
Access privileges | to3=3DC*T*c*/to3+
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | to4=3DCTc/to3

<= /div>
This makes sense since the three grants that to3 would have after m= erging are consolidated into a single one - in an additive sense and the gr= ant options being retained if present.

David J.
<= /div>
--000000000000d26bdb060fb5be3a--