Postgres Pro
Downloads
Home   >   mailing lists

Re: role self-revocation - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: role self-revocation
Date
Msg-id CAKFQuwZ0AD+9Yucdt_cvq5qyh_3FuWkXUgT3cX1J5E4aKk5p6Q@mail.gmail.com
Whole thread Raw
In response to Re: role self-revocation  (Stephen Frost <sfrost@snowman.net>)
Responses Re: role self-revocation  (Robert Haas <robertmhaas@gmail.com>)
List pgsql-hackers
Tree view
On Thu, Mar 10, 2022 at 12:58 PM Stephen Frost <sfrost@snowman.net> wrote:
I don't think we're that far from having all of these though.  To start
with, we remove from CREATEROLE the random things that it does which go
beyond what folks tend to expect- remove the whole 'grant any role to
any other' stuff, remove the 'drop role' exception, remove the
'alter role' stuff.  Do make it so that when you create a role, however,
the above GRANT is effectively done.  Now, for the items above where we
removed the checks against have_createrole_privilege() we go back and
add in checks using is_admin_of_role().  Of course, also remove the role
self-administration bug.

That's step #1, but it gets us more-or-less what you're looking for, I
think, and brings us a lot closer to what the spec has.

That still leaves attribute specification in place: e.g., REPLICATION, CREATEROLE, CREATEDB, etc... (I see BYPASSRLS already is SUPERUSER only)

I dislike changing the documented behavior of CREATEROLE to the degree suggested here.  However, there are three choices here, only one of which can be chosen:

1. Leave CREATEROLE alone entirely
2. Make it so CREATEROLE cannot assign membership to the predefined roles or superuser (inheritance included), but leave the rest alone.  This would be the hard-coded version, not the role attribute one.
3. Make it so CREATEROLE can only assign membership to roles for which it has been made an admin; as well as the other things mentioned

Moving forward I'd prefer options 1 or 2, leaving the ability to create/alter/drop a role to be vested via predefined roles.

The rest seems fine at an initial glance.

David J.

pgsql-hackers by date:

Previous
From: Justin Pryzby
Date:
Subject: Re: Adding CI to our tree
Next
From: Chapman Flack
Date:
Subject: Re: [PATCH] pg_permissions