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>
Show quoted text
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?
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;
Show quoted text
On Apr 2, 2020, at 20:37 , Guyren Howe <guyren@gmail.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@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?
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:
Show quoted text
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?
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:
Show quoted text
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?
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:
Show quoted text
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,
rafAC 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 trialand
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.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?
Show quoted text
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,
rafAC 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
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
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:
Show quoted text
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