Re: 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 just delete the role and do a
blanket grant removal at the same time?

https://www.postgresql.org/docs/12/sql-drop-owned.html <https://www.postgresql.org/docs/12/sql-drop-owned.html>

> On Apr 2, 2020, at 20:34 , AC Gomez <antklc(at)gmail(dot)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?

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 <guyren(at)gmail(dot)com> wrote:
>
> https://www.postgresql.org/docs/12/sql-drop-owned.html <https://www.postgresql.org/docs/12/sql-drop-owned.html>
>
>> On Apr 2, 2020, at 20:34 , AC Gomez <antklc(at)gmail(dot)com <mailto:antklc(at)gmail(dot)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?
>>

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(at)gmail(dot)com> wrote:

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

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(at)gmail(dot)com> wrote:
>
> > https://www.postgresql.org/docs/12/sql-drop-owned.html
> >
> > On Apr 2, 2020, at 20:34 , AC Gomez <antklc(at)gmail(dot)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?

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 <raf(at)raf(dot)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(at)gmail(dot)com> wrote:
> >
> > > https://www.postgresql.org/docs/12/sql-drop-owned.html
> > >
> > > On Apr 2, 2020, at 20:34 , AC Gomez <antklc(at)gmail(dot)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?
>
>
>

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.cybertec-postgresql.com

> On Apr 3, 2020, at 2:50 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> 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 help you along.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
Perhaps there is a script which has all the grants for a new hire?
I trust login has been disabled?

>

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(at)raf(dot)org <mailto:raf(at)raf(dot)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(at)gmail(dot)com
> <mailto:guyren(at)gmail(dot)com>> wrote:
> >
> > > https://www.postgresql.org/docs/12/sql-drop-owned.html
> > >
> > > On Apr 2, 2020, at 20:34 , AC Gomez <antklc(at)gmail(dot)com
> <mailto:antklc(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com

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,
>     acl.*
> FROM
>     pg_class,
>     aclexplode(relacl) AS acl
>     JOIN pg_roles ON acl.grantee = pg_roles.oid
> WHERE
>     pg_roles.oid = 'some_role'::regrole;
>

Possible flaw in plan, the above only works with v12+.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

yeah I'm on 9.5, but thanks for the info.

On Fri, Apr 3, 2020 at 1:24 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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 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;
> >
>
> Possible flaw in plan, the above only works with v12+.
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>