* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > Here's the results of this. I think we're pretty close to having both
> > "Basic roles" and "Extended roles" personally. For 'Basic roles' we
> > need SET ROLE and some information schema tables.
>
> The information schema views already exist, although I suspect the view
> definitions may need more work.
Ok.
> > REVOKE ROLE w/CASCADE drop behavior.
>
> I was just about to quiz you about the lack of any use of the grantor
> column in pg_auth_members. I suppose that revoking a membership that
> was held WITH ADMIN OPTION ought to lead to searching for and destroying
> all memberships granted by that ID (possibly indirectly?). DROP ROLE
> has got the same problem.
Not sure about indirectly, but I think a 'drop role' should check for
existing entries where that role is the 'grantor' and fail if any exist
unless 'cascade' is given. I think 'drop role' at one point (when it
was still seq-scan based) dropped based on the 'grantor' field
(regardless of 'cascade' or not). When I converted it to using an index
apparently I missed that issue, sorry about that. Seems like that'd
mean it'd have to go back to seq-scan based again. :/
> Also, I've been working on converting the CREATEROLE privilege into
> something usable, and am about ready to commit that. The way it works
> is that CREATEROLE lets you do anything that user.c formerly required
> superuser for, *except* that you have to be superuser to mess with
> superuser roles in any way. This all seems fine as far as it goes,
> but should revoking CREATEROLE lead to dropping grants that were made
> by means of that power? Not sure. We ended up with some fairly
> carefully crafted compromises for ACL representation of grants made
> by superusers, and I think we'll likely need to think hard about it
> for role memberships too.
I'd tend to think that revoking CREATEROLE wouldn't drop grants which
were made using it. I do agree that it needs to be thought out more
carefully than I believe it has been so far though.
Thanks,
Stephen
