Re: pg_auth_members.grantor is bunk

On Tue, 2022-09-06 at 13:15 -0400, Robert Haas wrote:

> Like, the logic to infer the grantor in check_role_grantor() and
> select_best_admin() is intended to be, and as far as I know actually
> is, an exact clone of the logic in select_best_grantor(). It is
> different only in that we regard the bootstrap superuser as the
> object
> owner because there is no other owner stored in the catalogs; and in
> that we check CREATEROLE permission rather than SUPERUSER permission.

There's at least one other difference: if you specify "GRANTED BY su1"
for a table grant, it still selects the table owner as the grantor;
whereas if you specify "GRANTED BY su1" for a role grant, it selects
"su1".

grant all privileges on schema public to public;
create user su1 superuser;
create user u1;
create user u2;
create user aa;
grant u2 to su1 with admin option;
\c - aa
create table t_aa(i int);
grant all privileges on t_aa to su1 with grant option;
\c - su1
grant select on t_aa to u1 granted by su1;
-- grantor aa
select relname, relacl from pg_class where relname='t_aa';
grant u2 to u1 granted by su1; -- grantor su1
-- grantor su1
select grantor::regrole from pg_auth_members
where member='u1'::regrole;

[ If you run the same example but where su1 is not a superuser, then
both select "su1" as the grantor because that's the only valid grantor
that can be inferred. ]

Now that I understand the underlying philosophy better, and I've
experimented with more cases, I propose the following grantor inference
behavior which I believe is in the spirit of your changes:

* Let the granting user be the one specified in the GRANTED BY clause
if it exists; otherwise the current user. In other words, omitting
GRANTED BY is the same as specifying "GRANTED BY current_user".
* If the granting user has privileges to be the grantor (ADMIN OPTION
for roles, GRANT OPTION for other objects) then the granting user is
the grantor.
* Else if the granting user inherits from a user with the privileges
to be the grantor, then it selects a role with the fewest inheritance
hops as the grantor.
* Else if the current user is any superuser:
- If the grant is a role grant, it selects the bootstrap superuser
as the grantor.
- Else the object owner is the grantor.
* Else error (or if an error would break important backwards
compatibility, silently make it work like before or perhaps issue a
WARNING).

In other words, try to issue the grant normally if at all possible, and
play the superuser card as a last resort. I believe that will lead to
the fewest surprising cases, and make them easiest to explain, because
superuser-ness doesn't influence the outcome in as many cases.

It cements the idea that the bootstrap superuser is the "real"
superuser, and must always remain so, and that all other superusers are
temporary stand-ins (kind of but not quite the same as inheritance).
And it leaves the ugliness that we lose the information about the
"real" grantor when we play the superuser card, but, as I say above,
that would be a last resort.

The proposal would be a slight behavior change from v15 in the
following case:

grant all privileges on schema public to public;
create user su1 superuser;
create user u1;
create user aa;
\c - aa
create table t_aa(i int);
grant all privileges on t_aa to su1 with grant option;
\c - su1
grant select on t_aa to u1 granted by su1;
-- grantor "aa" in v15, grantor "su1" after my proposal
select relname, relacl from pg_class where relname='t_aa';

Another change in behavior would be that the bootstrap superuser could
be the grantor for table privileges, if the bootstrap superuser has
WITH GRANT OPTION privileges.

But those seems minor to me.

Regards,
Jeff Davis