Re: Backing out of privilege grants rabbit hole

yeah I'm on 9.5, but thanks for the info. On Fri, Apr 3, 2020 at 1:24 PM Adrian Klaver wrote: > On 4/3/20 10:18 AM, Adrian Klaver wrote: > > 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 co

Re: Backing out of privilege grants rabbit hole

On 4/3/20 10:18 AM, Adrian Klaver wrote: 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,

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(r

Re: Backing out of privilege grants rabbit hole

> On Apr 3, 2020, at 2:50 AM, Laurenz Albe wrote: > > On Fri, 2020-04-03 at 00:59 -0400, AC Gomez wrote: >> Granted. But we are where we are, so I'm assuming this is going to be hand >> to hand combat. > > Yes, unfortunately there is no better way. > But DROP ROLE will produce messages that

Re: Backing out of privilege grants rabbit hole

On Fri, 2020-04-03 at 00:59 -0400, AC Gomez wrote: > Granted. But we are where we are, so I'm assuming this is going to be hand to > hand combat. Yes, unfortunately there is no better way. But DROP ROLE will produce messages that help you along. Yours, Laurenz Albe -- Cybertec | https://www.cyb

Re: Backing out of privilege grants rabbit hole

Granted. But we are where we are, so I'm assuming this is going to be hand to hand combat. On Fri, Apr 3, 2020, 12:57 AM raf 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

Re: Backing out of privilege grants rabbit hole

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 q

Re: Backing out of privilege grants rabbit hole

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

Re: Backing out of privilege grants rabbit hole

More fully: REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role; -- repeat the above commands in each database of the cluster DROP ROLE doomed_role; > On Apr 2, 2020, at 20:37 , Guyren Howe wrote: > > https://www.postgresql.org/docs/12/sql-drop-owned.html >

Re: Backing out of privilege grants rabbit hole

https://www.postgresql.org/docs/12/sql-drop-owned.html > On Apr 2, 2020, at 20:34 , AC Gomez 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

Backing out of privilege grants rabbit hole

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 ju