Re: Backing out of privilege grants rabbit hole

On 4/2/20 9:59 PM, AC Gomez wrote:
> Granted. But we are where we are, so I'm assuming this is going to be 
> hand to hand combat.

Well you could even the odds somewhat by using the below as a starting 
point:


SELECT
     relname,
     pg_roles.rolname,
     acl.*
FROM
     pg_class,
     aclexplode(relacl) AS acl
     JOIN pg_roles ON acl.grantee = pg_roles.oid
WHERE
     pg_roles.oid = 'some_role'::regrole;




> 
> On Fri, Apr 3, 2020, 12:57 AM raf <raf@raf.org <mailto:raf@raf.org>> wrote:
> 
>     It's probably more sensible to grant permissions to roles that
>     represent groups, and have roles for individual users that
>     inherit the permissions of the group roles. Then you don't
>     need to revoke the permissions just because an individiual
>     has left.
> 
>     cheers,
>     raf
> 
>     AC Gomez wrote:
> 
>      > Thanks for the quick response. The problem is, in most cases the
>     owner is
>      > not the grantee. So if a role, let's say a temp employee, gets
>     grants, then
>      > leaves, I can't do a drop owned because that temp never owned those
>      > objects, he just was granted access. Is there a "drop granted"
>     kind of
>      > thing?
>      >
>      > On Thu, Apr 2, 2020, 11:37 PM Guyren Howe <guyren@gmail.com
>     <mailto:guyren@gmail.com>> wrote:
>      >
>      > > https://www.postgresql.org/docs/12/sql-drop-owned.html
>      > >
>      > > On Apr 2, 2020, at 20:34 , AC Gomez <antklc@gmail.com
>     <mailto:antklc@gmail.com>> wrote:
>      > >
>      > > Do I understand correctly that if a role was assigned countless
>     object
>      > > privileges and you want to delete that role you have to sift
>     through a
>      > > myriad of privilege grants in what amounts to a time consuming
>     trial and
>      > > error exercise until you've got them all?
>      > >
>      > > Or is there a single command that with just delete the role and
>     do a
>      > > blanket grant removal at the same time?
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com