Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\))
Subject: Re: role self-revocation
From: Mark Dilger <mark.dilger@enterprisedb.com>
In-Reply-To: <CA+TgmoaCpYSvgvVi5ug__02LOEijsf2yAQOBUoxf1EBtoWEHXw@mail.gmail.com>
Date: Thu, 10 Mar 2022 14:17:08 -0800
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
 Stephen Frost <sfrost@snowman.net>,
 Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
 Joshua Brindle <joshua.brindle@crunchydata.com>,
 Andrew Dunstan <andrew@dunslane.net>,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <0AFBA185-6812-45B1-A1FD-D1C432F47C87@enterprisedb.com>
References: <20220228190923.GJ10577@tamriel.snowman.net>
 <CA+TgmobNfJvG6keNU6YcykJnntDm_-BxN_5JDacakusr5E0hEg@mail.gmail.com>
 <CAKFQuwb3=Zdp2-mEPWbTmktt3NLRNdprGi7=DqfG-pxpTQo43w@mail.gmail.com>
 <CA+TgmoY9SLMEpCcVFw9gs-9_ihK+3T7P0_4zkMDfXk8DfvheYA@mail.gmail.com>
 <CAKFQuwbmx9bSu6iob-k88APFZtzvPVmXFjAjdR0u3MqR3TWzzg@mail.gmail.com>
 <CA+TgmobHD6zVgRnNrz-7=Wm6W-76Q4Qrrif4zmhk522G5ay9Fw@mail.gmail.com>
 <0c095133-7dc7-7a11-b773-0318807380db@enterprisedb.com>
 <CA+Tgmobbnro+Z-uFZCGr_y6wq8gANo8EaYSvcZK2kekDdtFaZA@mail.gmail.com>
 <2e2f9ae2-50fc-1a03-394c-ed4288a8cae2@enterprisedb.com>
 <CA+Tgmoa5+pfDeuAjPtk=pKF2a63X9UoUkWBmbzR56FGoBvD_7g@mail.gmail.com>
 <20220310195849.GH10577@tamriel.snowman.net>
 <CAKFQuwZ0AD+9Yucdt_cvq5qyh_3FuWkXUgT3cX1J5E4aKk5p6Q@mail.gmail.com>
 <CA+TgmoaCpYSvgvVi5ug__02LOEijsf2yAQOBUoxf1EBtoWEHXw@mail.gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/0AFBA185-6812-45B1-A1FD-D1C432F47C87%40enterprisedb.com>
Precedence: bulk


> On Mar 10, 2022, at 2:01 PM, Robert Haas <robertmhaas@gmail.com> =
wrote:
>=20
> It sounds like you prefer a behavior where CREATEROLE gives power over
> all non-superusers, but that seems pretty limiting to me. Why can't
> someone want to create a user with power over some users but not
> others?

I agree with Robert on this.

Over at [1], I introduced a patch series to (a) change CREATEROLE and =
(b) introduce role ownership.  Part (a) wasn't that controversial.  The =
patch series failed to make it for postgres 15 on account of (b).  The =
patch didn't go quite far enough, but with it applied, this is an =
example of a min-superuser "lord" operating within database "fiefdom":

fiefdom=3D# -- mini-superuser who can create roles and write all data
fiefdom=3D# CREATE ROLE lord
fiefdom-#     WITH CREATEROLE
fiefdom-#     IN ROLE pg_write_all_data;
CREATE ROLE
fiefdom=3D#=20
fiefdom=3D# -- group which "lord" belongs to
fiefdom=3D# CREATE GROUP squire
fiefdom-#     ROLE lord;
CREATE ROLE
fiefdom=3D#=20
fiefdom=3D# -- group which "lord" has no connection to
fiefdom=3D# CREATE GROUP paladin;
CREATE ROLE
fiefdom=3D#=20
fiefdom=3D# SET SESSION AUTHORIZATION lord;
SET
fiefdom=3D>=20
fiefdom=3D> -- fail, merely a member of "squire"
fiefdom=3D> CREATE ROLE peon IN ROLE squire;
ERROR:  must have admin option on role "squire"
fiefdom=3D>=20
fiefdom=3D> -- fail, no privilege to grant CREATEDB=20
fiefdom=3D> CREATE ROLE peon CREATEDB;
ERROR:  must have createdb privilege to create createdb users
fiefdom=3D>=20
fiefdom=3D> RESET SESSION AUTHORIZATION;
RESET
fiefdom=3D#=20
fiefdom=3D# -- grant admin over "squire" to "lord"
fiefdom=3D# GRANT squire
fiefdom-#     TO lord
fiefdom-#     WITH ADMIN OPTION;
GRANT ROLE
fiefdom=3D#=20
fiefdom=3D# SET SESSION AUTHORIZATION lord;
SET
fiefdom=3D>=20
fiefdom=3D> -- ok, have both "CREATEROLE" and admin option for "squire"
fiefdom=3D> CREATE ROLE peon IN ROLE squire;
CREATE ROLE
fiefdom=3D>=20
fiefdom=3D> -- fail, no privilege to grant CREATEDB
fiefdom=3D> CREATE ROLE peasant CREATEDB IN ROLE squire;
ERROR:  must have createdb privilege to create createdb users
fiefdom=3D>=20
fiefdom=3D> RESET SESSION AUTHORIZATION;
RESET
fiefdom=3D#=20
fiefdom=3D# -- Give lord the missing privilege
fiefdom=3D# GRANT CREATEDB TO lord;
ERROR:  role "createdb" does not exist
fiefdom=3D#=20
fiefdom=3D# RESET SESSION AUTHORIZATION;
RESET
fiefdom=3D#=20
fiefdom=3D# -- ok, have "CREATEROLE", "CREATEDB", and admin option for =
"squire"
fiefdom=3D# CREATE ROLE peasant CREATEDB IN ROLE squire;
CREATE ROLE

The problem with this is that "lord" needs CREATEDB to grant CREATEDB, =
but really it should need something like grant option on "CREATEDB".  =
But that's hard to do with the existing system, given the way these =
privilege bits are represented.  If we added a few more built-in pg_* =
roles, such as pg_create_db, it would just work.  CREATEROLE itself =
could be reimagined as pg_create_role, and then users could be granted =
into this role with or without admin option, meaning they could/couldn't =
further give it away.  I think that would be a necessary component to =
Joshua's "bot" use-case, since the bot must itself have the privilege to =
create roles, but shouldn't necessarily be trusted with the privilege to =
create additional roles who have it.

[1] =
https://www.postgresql.org/message-id/53C7DF4C-8463-4647-9DFD-779B5E1861C4=
@amazon.com

=E2=80=94
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company