Re: role self-revocation

On Thu, Mar 10, 2022 at 7:46 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Mar 9, 2022 at 4:31 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > I don't think we need syntax to describe it. As I just said in my
> > other reply, we have a perfectly good precedent for this already
> > in ordinary object permissions. That is: an object owner always,
> > implicitly, has GRANT OPTION for all the object's privileges, even
> > if she revoked the corresponding plain privilege from herself.
> >
> > Yeah, this does mean that we're effectively deciding that the creator
> > of a role is its owner. What's the problem with that?
>
> I don't think that's entirely the wrong concept, but it doesn't make a
> lot of sense in a world where the creator has to be a superuser. If
> alice, bob, and charlie are superusers who take turns creating new
> users, and then we let charlie go due to budget cuts, forcing alice
> and bob to change the owner of all the users he created to some other
> superuser as a condition of dropping his account is a waste of
> everyone's time. They can do exactly the same things to every account
> on the system after we change the role owner as before.
>

Then maybe we should just implement the idea that if a superuser would
become the owner we instead substitute in the bootstrap user. Or give the
DBA the choice whether they want to retain knowledge of specific roles -
and thus are willing to accept the "waste of time".

> But wait, I hear you cry, what about CREATEROLE? Well, CREATEROLE is
> generally agreed to be broken right now, and if you don't agree with
> that, consider that it can grant pg_execute_server_programs to a
> newly-created account and then explain to me how it's functionally
> different from superuser.

CREATEROLE has long been defined as basically having "with admin option" on
every role in the system. The failure to special-case the roles that grant
different aspects of superuser-ness to its members doesn't make CREATEROLE
itself broken, it makes the implementation of pg_execute_server_programs
broken. Only superusers should be considered to have with admin option on
these roles. They can delegate through the usual membership+admin mechanism
to a CREATEROLE role if they desire.

> The whole area needs a rethink. I believe
> everyone involved in the discussion on the other threads agrees that
> some reform of CREATEROLE is necessary, and more generally with the
> idea that it's useful for non-superusers to be able to create roles.
>

As the documentation says, using SUPERUSER for day-to-day administration is
contrary to good security practices. Role management is considered to be a
day-to-day administration activity. I agree with this principle. It was
designed to neither be a superuser nor grant superuser, so removing the
ability to grant the pg_* role memberships remains consistent with its
original intent.

> I want that because I want mini-superusers, where alice can administer
> the users that alice creates just as if she were a superuser,
> including having their permissions implicitly and dropping them when
> she wants them gone, but where alice cannot break out to the operating
> system as a true superuser could do.

CREATEROLE (once the pg_* with admin rules are fixed) + Ownership and rules
restricting interfering with another role's objects (unless superuser)
seems to handle this.

> the bot can stand up
> accounts, can grant them membership in a defined set of groups, but
> cannot exercise the privileges of those accounts (or hack superuser
> either).

The bot should be provided a security definer procedure that encapsulates
all of this rather than us trying to hack the permission system. This
isn't a user permission concern, it is an unauthorized privilege escalation
concern. Anyone with the bot's credentials can trivially overcome the
third restriction by creating a role with the desired membership and then
logging in as that role - and there is nothing the system can do to prevent
that while also allowing the other two permissions.

> And that's why I'm not sure it's really the right idea to say that we
> don't need syntax for this admin-without-member concept.

We already have this syntax in the form of CREATEROLE. But we do need a
fix, just on the group side. We need a way to define a group as having no
ADMINS.

ALTER ROLE pg_superuser WITH [NO] ADMIN;

Then adding a role membership including the WITH ADMIN OPTION can be
rejected, as can the non-superuser situation. Setting WITH NO ADMIN should
fail if any existing members have admin. You must be a superuser to
execute WITH ADMIN (maybe WITH NO ADMIN as well...). And possibly even a
new pg_* role that grants this ability (and maybe some others) for use by a
backup/restore user.

Or just special-case pg_* roles.

The advantage of exposing this to the DBA is that they can then package
pg_* roles into a custom group and still have the benefit of superuser only
administration. In the special-case implementation the presence of a pg_*
role in a group hierarchy would then preclude a non-superuser from having
admin on the entire tree (the pg_* roles are all roots, or in the case of
pg_monitor, directly emanate from a root role).

David J.

David J.