Re: drop role with privileges - Mailing list pgsql-general

From Stephen Frost
Subject Re: drop role with privileges
Date
Msg-id 20061210000744.GH24675@kenobi.snowman.net
Whole thread Raw
In response to drop role with privileges  ("Tom Darci" <tom@nuws.com>)
List pgsql-general
* Tom Darci (tom@nuws.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

Attachment

pgsql-general by date:

Previous
From: "Tom Darci"
Date:
Subject: drop role with privileges
Next
From: "Raymond O'Donnell"
Date:
Subject: Re: ALTER TABLE RENAME and primary key