Thread: drop role with privileges

Hey All-

  It seems like there should be an easily-found answer to this, but if
there is, I can't find it.

  I am simply trying to drop a role.

  So, before dropping it I need to reassign all its owned objects
(REASSIGN OWNED, very handy) and then revoke all privilges it has had
assigned to it. The revoking privileges is the hard part. (And why this
requirement?) I can (and have been) explicitly revoking all privileges
on each database object first, but that's a lot of unneccessary work
(and time... it's done programatically though, of course).

  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?"

  We are using 8.2.

  Any help is much appreciated.

Regards,
-Tom D

* 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

Tom Darci wrote:
> Hey All-
>
>   It seems like there should be an easily-found answer to this, but if
> there is, I can't find it.
>
>   I am simply trying to drop a role.
>
>   So, before dropping it I need to reassign all its owned objects
> (REASSIGN OWNED, very handy) and then revoke all privilges it has had
> assigned to it. The revoking privileges is the hard part. (And why this
> requirement?) I can (and have been) explicitly revoking all privileges
> on each database object first, but that's a lot of unneccessary work
> (and time... it's done programatically though, of course).

Did you try DROP OWNED BY?  It revokes privileges (as well as dropping
objects owned by said role).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

>>
>> That sounds very promising. I'll take a look there.
>
>I may be wrong about the table name but certainly drop role uses some
>set of system tables to do it's work. :)

THANKS for your help, Stephen. Once I've reassigned ownership
I can then easily find out privilege dependencies using
pg_shdepend. Here's the preliminary query I've worked up for
coming up with the listing, in case it's of use to others...

------------------------------------------------------
select
    rol.rolname as thisrole,
    db.datname as dbname,
    sch.nspname as dependencyschema,
    c.relname as dependency
from
    pg_shdepend as d
    inner join pg_database as db on d.dbid = db.oid
    inner join pg_authid as rol on d.refobjid = rol.oid
    left join (pg_class as c inner join pg_namespace as sch on
c.relnamespace = sch.oid) on d.objid = c.oid where
    rol.rolname = '<WhateverRoleNameYouLike>'
    and d.deptype in ('o', 'a')
order by
    rol.rolname,
    db.datname,
    sch.nspname,
    c.relname
------------------------------------------------------

I've included a left join in there for the case where there
are items outside the current database or that are not
otherwise in pg_class... not sure if I need that, but it's in
there for now... will probably remove to optimize (and handle
the case elsewise)

-Tom D

>Did you try DROP OWNED BY?  It revokes privileges (as well as
>dropping objects owned by said role).

Thank you. That was exactly the *easy* soluton I'd been missing. I swear
I read the page in the manual for "DROP OWNED" several times, hoping to
see something like "Any privileges granted to the given roles on objects
in the current database will also be revoked.", which is right there.
I'll give that a spin (first using REASSIGN OWNED so that objects don't
get dropped). Thank you, Alvaro.

-Tom D