Re: pg_auth_members.grantor is bunk

On Wed, Sep 7, 2022 at 10:56 AM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> OK. I suppose the best path forward is to just try to improve the
> ability to administer the system without relying as much on superusers,
> which will allow us to safely ignore some of the weirdness caused by
> superusers issuing grants.

Yeah, and I think we might not even be that far away from making that
happen. There are still a few thorny design issues to work out, I
believe, and there's also some complexity that is introduced by the
fact that different people want different things. For example, last
release cycle, I believed that the NOINHERIT behavior was a weird wart
that probably nobody cared about. That turned out to be false, really
false.

What I *personally* want most as an alternative to superuser is an
account that inherits all the privileges of the other accounts that it
manages, which might not be all the accounts on the system, and which
can also SET ROLE to those accounts. If you're logged into such an
account, you can do many of the things a superuser can do and in the
same ways that a superuser can do them. For example, if you've got
some pg_dump output, you could probably restore the dump using such an
account and privilege restoration would work, provided that the
required accounts exist and that they're among the accounts managed by
your account.

However, I think that other people want different things. For example,
I think that Joshua Brindle mentioned wanting to have a user-creation
bot that should be able to make new accounts but not access them in
any way, and I think Stephen Frost was interested in semantics where
you could make accounts and be able to SET ROLE into them but not
inherit their privileges. Or maybe they were both proposing the same
thing: not quite sure. Anyway, it will perhaps turn out to be
impossible to give everybody 100% of everything they would like, but
I'm thinking about a few ideas that might enable us to cater to a few
different scenarios - and I'm hopeful that it will be possible to
propose something in time for inclusion in v16, but my ideas aren't
quite well enough formulated yet to make a concrete proposal just yet,
and when I do make such a proposal I want to do it on a new thread for
better visibility.

In the meantime, I think that what has already been committed is
clearly a step in the right direction. The patch which is the subject
of this thread has basically brought the role grant code up to the
level of other object types. I don't think it's an overstatement to
say that the previous state of affairs was that this feature just
didn't work properly and no one had cared enough to bother fixing it.
That always makes discussions about future enhancements harder. The
patch to add grant-level control to the INHERIT option also seems to
me to be a step in the right direction, since, at least IMHO, it is
really hard to reason about behavior when the heritability of a
particular grant is a property of the grantee rather than something
which can be controlled by the grantor, or the system. If we can reach
agreement on some of the other things that I have proposed,
specifically sorting out the issues under discussion on the
"has_privs_of_role vs. is_member_of_role, redux" thread and adding the
new capability discussed on the "allowing for control over SET ROLE"
thread, I think will be a further, useful step.

--
Robert Haas
EDB: http://www.enterprisedb.com