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. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
