Re: drop role with privileges

* Tom Darci (tom(at)nuws(dot)com) wrote:
> I've been looking into using the function aclcontains() in conjunction
> with the table pg_class, in order to determine which objects a role has
> been granted privilges to. And while this seems promising, I'm still not
> having any luck formulating the sql to get me the answer to: "What
> objects has role x been granted specific rights to?"

There really should be a better solution to this, I agree. Some
functions that might help are the 'has_*_privilege' ones. ie:

select * from pg_class where
has_table_privilege('sfrost',oid,'select');

You could also pull the information from pg_depend since that's what
'drop role' actually uses to figure out if there are still things which
depend on the role, iirc.

As I recall the trouble with doing this automagically is that a given
backend is associated with a particular database while roles can have
privileges in multiple databases. Giving an admin the ability to 'drop
all privileges for role X in this database' might be possible though...

Thanks,

Stephen