Re: Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)

Alvaro Herrera wrote:
> Stephen Frost wrote:
>
> > I don't have time right at the moment (leaving shortly and will be gone
> > all weekend) but what I would do is check the SQL standard, especially
> > the information schema, for any requirement to track the grantor. Much
> > of what I did was based on the standard so that may have been the
> > instigation for tracking grantor.
>
> Hmm. I had forgotten the information schema. I just checked: the only
> view using pg_auth_members is APPLICABLE_ROLES, and that one doesn't
> display the grantor column.

This section of the standard is relevant:
4.34.3 Roles

Each grant is represented and identified by a role authorization descriptor. A
role authorization descriptor includes:

— The role name of the role.
— The <authorization identifier> of the grantor.
— The <authorization identifier> of the grantee.
— An indication of whether or not the role was granted with the WITH ADMIN
OPTION and hence is grantable.

... continues reading the spec ...

Ah, here it is, 12.7 <revoke statement>. It says that if role revokes
another role from a third role, it will only remove the privileges that
were granted by him, not someone else.

That is, if roles A and B grant a role Z to C, and then role A revokes Z
from C, then role C continues to have the role Z because of the grant B
gave.

So we have a problem here, because this

alvherre=# create role a;
CREATE ROLE
alvherre=# create role b;
CREATE ROLE
alvherre=# create role z admin a, b;
CREATE ROLE
alvherre=# create role c;
CREATE ROLE
alvherre=# set session authorization a;
SET
alvherre=> grant z to c;
GRANT ROLE
alvherre=> set session authorization b;
SET
alvherre=> grant z to c;
NOTICE: role "c" is already a member of role "z"

should not emit any noise, but instead add another grant of Z to C with
grantor B.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.