Re: Role Self-Administration

Greetings,

* Vik Fearing (vik(at)postgresfriends(dot)org) wrote:
> On 10/6/21 8:48 PM, Stephen Frost wrote:
> > Consider that with what you're proposing, a user could execute the
> > following series of entirely SQL-spec compliant statements, and get
> > very different results depending on if we have this 'ownership' concept
> > or not:
> >
> > SET ROLE postgres;
> > CREATE ROLE r1;
> >
> > SET ROLE r1;
> > CREATE ROLE r2;
> >
> > SET ROLE postgres;
> > DROP ROLE r1 CASCADE;
> >
> > With what you're suggesting, the end result would be that r2 no longer
> > exists, whereas with the spec-defined behvaior, r2 *would* still exist.
>
> The way I read the spec, r2 would be destroyed along with its objects.
>
> 12.7 GR 30.b.i says to destroy all abandoned role authorization
> descriptors, and r2 matches that according to my reading of 12.7 GR 7.

12.7 refers to the "revoke statement", just so folks are able to follow.

I concur that 30.b.1 says that.

What I disagree with, however, is that a 'role authorization descriptor'
equates to a 'role'.

12.6 is 'drop role statement' and it's "Function" is "Destroy a role"

12.7 is 'revoke statement' and it's "Function" is "Destroy privileges
and role authorizations".

In other words, my reading is that a "role authorization descriptor" is
the equivilant of a row in pg_auth_members, not one in pg_authid. This
is further substantiated in Framework, 4.4.6 Roles, which makes a clear
distinction between "role" and "role authorization".

I certainly don't think that "REVOKE R FROM A;" should be going around
dropping roles, yet your reading would imply that it should be.

Thanks,

Stephen