Re: Role Self-Administration

On 10/7/21 4:21 PM, Stephen Frost wrote:
> 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'.

Okay.

> 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 was looking for this distinction in Foundation and didn't think to
look in Framework (I wish this thing would be just one huge document),
so thanks for pointing me to that.

I think I got confused by 12.4 <role definition> putting in the General
Rules that a role authorization descriptor is created, but putting that
a role descriptor is created in the *Syntax Rules*. And that is in fact
the *only* place "role descriptor" appears in Foundation.

> 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.

I can agree with you now, but it's certainly not the easiest thing to
interpret.
--
Vik Fearing