Re: Role Self-Administration

Greetings,

On Tue, Oct 5, 2021 at 13:09 Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Oct 5, 2021 at 12:38 PM Mark Dilger
> <mark(dot)dilger(at)enterprisedb(dot)com> wrote:
> > Additionally, role "alice" might not exist anymore, which would leave
> the privilege irrevocable.
>
> I thought that surely this couldn't be right, but apparently we have
> absolutely no problem with leaving the "grantor" column in pg_authid
> as a dangling reference to a pg_authid role that no longer exists:

> rhaas=# select * from pg_auth_members where grantor not in (select oid
> from pg_authid);
> roleid | member | grantor | admin_option
> --------+--------+---------+--------------
> 3373 | 16412 | 16410 | f
> (1 row)
>
> Yikes. We'd certainly have to do something about that if we want to
> use the grantor field for anything security-sensitive, since otherwise
> hilarity would ensue if that OID got recycled for a new role at any
> later point in time.

Yeah, ew. We should just fix this.

This seems weirdly inconsistent with what we do in other cases:
>
> rhaas=# create table foo (a int, b text);
> CREATE TABLE
> rhaas=# grant select on table foo to alice with grant option;
> GRANT
> rhaas=# \c rhaas alice
> You are now connected to database "rhaas" as user "alice".
> rhaas=> grant select on table foo to bob;
> GRANT
> rhaas=> \c - rhaas
> You are now connected to database "rhaas" as user "rhaas".
> rhaas=# drop role alice;
> ERROR: role "alice" cannot be dropped because some objects depend on it
> DETAIL: privileges for table foo
> rhaas=#
>
> Here, because the ACL on table foo records alice as a grantor, alice
> cannot be dropped. But when alice is the grantor of a role, the same
> rule doesn't apply. I think the behavior shown in this example, where
> alice can't be dropped, is the right behavior, and the behavior for
> roles is just plain broken.

Agreed.

Thanks,

Stephen

>