Обсуждение: Backing out of privilege grants rabbit hole

Поиск
Список
Период
Сортировка

Backing out of privilege grants rabbit hole

От
AC Gomez
Дата:
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?

Re: Backing out of privilege grants rabbit hole

От
Guyren Howe
Дата:
https://www.postgresql.org/docs/12/sql-drop-owned.html

On Apr 2, 2020, at 20:34 , AC Gomez <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?
ul[class*='mb-extra__public-links'], ul[class*='mb-note__public-links'], ul[class*='mb-task__public-links'] { display: none !important; }

Re: Backing out of privilege grants rabbit hole

От
Guyren Howe
Дата:
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@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> 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?

ul[class*='mb-extra__public-links'], ul[class*='mb-note__public-links'], ul[class*='mb-task__public-links'] { display: none !important; }

Re: Backing out of privilege grants rabbit hole

От
AC Gomez
Дата:
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> wrote:
https://www.postgresql.org/docs/12/sql-drop-owned.html

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

Re: Backing out of privilege grants rabbit hole

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



Re: Backing out of privilege grants rabbit hole

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


Re: Backing out of privilege grants rabbit hole

От
Laurenz Albe
Дата:
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




Re: Backing out of privilege grants rabbit hole

От
Rob Sargent
Дата:

> On Apr 3, 2020, at 2:50 AM, Laurenz Albe <laurenz.albe@cybertec.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?

>



Re: Backing out of privilege grants rabbit hole

От
Adrian Klaver
Дата:
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



Re: Backing out of privilege grants rabbit hole

От
Adrian Klaver
Дата:
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.klaver@aklaver.com



Re: Backing out of privilege grants rabbit hole

От
AC Gomez
Дата:
yeah I'm on 9.5, but thanks for the info.

On Fri, Apr 3, 2020 at 1:24 PM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com