pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Drop role cascade ?
8+ messages / 5 participants
[nested] [flat]

* Drop role cascade ?
@ 2022-11-17 23:07 David Gauthier <dfgpostgres@gmail.com>
  2022-11-17 23:16 ` Re: Drop role cascade ? Adrian Klaver <adrian.klaver@aklaver.com>
  2022-11-17 23:16 ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-18 06:13 ` Re: Drop role cascade ? Ron <ronljohnsonjr@gmail.com>
  0 siblings, 3 replies; 8+ messages in thread

From: David Gauthier @ 2022-11-17 23:07 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

psql (11.5, server 11.3) on linux

Someone who had privileges in a lot of tables/schemas/DBs left the
company.  I want to drop that role but "rop role xxx" won't let me
because...  "xxx cannot be dropped because some objects depend on it".

Is there a way to just get rid of this role without revoking xxx in all
the individual DB objects he has privs too?  Something like "drop role xxx
cascade" ?

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Drop role cascade ?
  2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
@ 2022-11-17 23:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2022-11-17 23:16 UTC (permalink / raw)
  To: David Gauthier <dfgpostgres@gmail.com>; pgsql-general@lists.postgresql.org

On 11/17/22 15:07, David Gauthier wrote:
> psql (11.5, server 11.3) on linux
> 
> Someone who had privileges in a lot of tables/schemas/DBs left the 
> company.  I want to drop that role but "rop role xxx" won't let me 
> because...  "xxx cannot be dropped because some objects depend on it".
> 
> Is there a way to just get rid of this role without revoking xxx in all 
> the individual DB objects he has privs too?  Something like "drop role 
> xxx cascade" ?

1) You might want to confirm that removing that role is not going to 
leave you a bind where the objects do not have the necessary privileges 
to complete their tasks.

2) From here :

https://www.postgresql.org/docs/current/sql-droprole.html

"A role cannot be removed if it is still referenced in any database of 
the cluster; an error will be raised if so. Before dropping the role, 
you must drop all the objects it owns (or reassign their ownership) and 
revoke any privileges the role has been granted on other objects. The 
REASSIGN OWNED and DROP OWNED commands can be useful for this purpose; 
see Section 22.4 for more discussion."

So you should take a look at:

https://www.postgresql.org/docs/current/sql-reassign-owned.html



-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Drop role cascade ?
  2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
@ 2022-11-17 23:16 ` Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-17 23:44   ` Re: Drop role cascade ? Ron <ronljohnsonjr@gmail.com>
  2 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2022-11-17 23:16 UTC (permalink / raw)
  To: David Gauthier <dfgpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

David Gauthier <dfgpostgres@gmail.com> writes:
> Someone who had privileges in a lot of tables/schemas/DBs left the
> company.  I want to drop that role but "rop role xxx" won't let me
> because...  "xxx cannot be dropped because some objects depend on it".

> Is there a way to just get rid of this role without revoking xxx in all
> the individual DB objects he has privs too?  Something like "drop role xxx
> cascade" ?

Not directly, but see REASSIGN OWNED and DROP OWNED.

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Drop role cascade ?
  2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
  2022-11-17 23:16 ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-17 23:44   ` Ron <ronljohnsonjr@gmail.com>
  2022-11-17 23:57     ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Ron @ 2022-11-17 23:44 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/17/22 17:16, Tom Lane wrote:
> David Gauthier <dfgpostgres@gmail.com> writes:
>> Someone who had privileges in a lot of tables/schemas/DBs left the
>> company.  I want to drop that role but "rop role xxx" won't let me
>> because...  "xxx cannot be dropped because some objects depend on it".
>> Is there a way to just get rid of this role without revoking xxx in all
>> the individual DB objects he has privs too?  Something like "drop role xxx
>> cascade" ?
> Not directly, but see REASSIGN OWNED and DROP OWNED.

SIMULATE and VERBOSE options would be handy, to see what those commands 
would actually do.

-- 
Angular momentum makes the world go 'round.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Drop role cascade ?
  2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
  2022-11-17 23:16 ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-17 23:44   ` Re: Drop role cascade ? Ron <ronljohnsonjr@gmail.com>
@ 2022-11-17 23:57     ` Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-18 00:14       ` Re: Drop role cascade ? Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2022-11-17 23:57 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org, Alvaro Herrera <alvherre@alvh.no-ip.org>

Ron <ronljohnsonjr@gmail.com> writes:
> On 11/17/22 17:16, Tom Lane wrote:
>> Not directly, but see REASSIGN OWNED and DROP OWNED.

> SIMULATE and VERBOSE options would be handy, to see what those commands 
> would actually do.

Hmph.  I'm surprised to realize that those commands don't produce
trace output comparable to DROP CASCADE.  If they did, this need
would be met by the traditional hack of "BEGIN; DROP ...; ROLLBACK".

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Drop role cascade ?
  2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
  2022-11-17 23:16 ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-17 23:44   ` Re: Drop role cascade ? Ron <ronljohnsonjr@gmail.com>
  2022-11-17 23:57     ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-18 00:14       ` Ron <ronljohnsonjr@gmail.com>
  2022-11-18 05:34         ` Re: Drop role cascade ? Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 1 reply; 8+ messages in thread

From: Ron @ 2022-11-18 00:14 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/17/22 17:57, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> writes:
>> On 11/17/22 17:16, Tom Lane wrote:
>>> Not directly, but see REASSIGN OWNED and DROP OWNED.
>> SIMULATE and VERBOSE options would be handy, to see what those commands
>> would actually do.
> Hmph.  I'm surprised to realize that those commands don't produce
> trace output comparable to DROP CASCADE.  If they did, this need
> would be met by the traditional hack of "BEGIN; DROP ...; ROLLBACK".

So... you'll add it to v16????  :D

-- 
Angular momentum makes the world go 'round.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Drop role cascade ?
  2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
  2022-11-17 23:16 ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-17 23:44   ` Re: Drop role cascade ? Ron <ronljohnsonjr@gmail.com>
  2022-11-17 23:57     ` Re: Drop role cascade ? Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-18 00:14       ` Re: Drop role cascade ? Ron <ronljohnsonjr@gmail.com>
@ 2022-11-18 05:34         ` Andreas Kretschmer <andreas@a-kretschmer.de>
  0 siblings, 0 replies; 8+ messages in thread

From: Andreas Kretschmer @ 2022-11-18 05:34 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>

On 18 November 2022 01:14:37 CET, Ron <ronljohnsonjr@gmail.com> wrote:
>On 11/17/22 17:57, Tom Lane wrote:
>> Ron <ronljohnsonjr@gmail.com> writes:
>>> On 11/17/22 17:16, Tom Lane wrote:
>>>> Not directly, but see REASSIGN OWNED and DROP OWNED.
>>> SIMULATE and VERBOSE options would be handy, to see what those commands
>>> would actually do.
>> Hmph.  I'm surprised to realize that those commands don't produce
>> trace output comparable to DROP CASCADE.  If they did, this need
>> would be met by the traditional hack of "BEGIN; DROP ...; ROLLBACK".
>
>So... you'll add it to v16????  :D
>

+1 as feature request




-- 
Andreas Kretschmer, EDB





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Drop role cascade ?
  2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
@ 2022-11-18 06:13 ` Ron <ronljohnsonjr@gmail.com>
  2 siblings, 0 replies; 8+ messages in thread

From: Ron @ 2022-11-18 06:13 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/17/22 17:07, David Gauthier wrote:
> psql (11.5, server 11.3) on linux
>
> Someone who had privileges in a lot of tables/schemas/DBs left the 
> company.  I want to drop that role but "rop role xxx" won't let me 
> because...  "xxx cannot be dropped because some objects depend on it".
>
> Is there a way to just get rid of this role without revoking xxx in all 
> the individual DB objects he has privs too?  Something like "drop role xxx 
> cascade" ?

Had this idea tonight about how to find what is owned by a specific role.  
Haven't tested it, so the syntax might be a bit wrong, and it might not work:

$ pg_dump --schema-only $DATABASE | grep OWNER | grep $OLDEMPLOYEE


-- 
Angular momentum makes the world go 'round.





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2022-11-18 06:13 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-17 23:07 Drop role cascade ? David Gauthier <dfgpostgres@gmail.com>
2022-11-17 23:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2022-11-17 23:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-17 23:44   ` Ron <ronljohnsonjr@gmail.com>
2022-11-17 23:57     ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-18 00:14       ` Ron <ronljohnsonjr@gmail.com>
2022-11-18 05:34         ` Andreas Kretschmer <andreas@a-kretschmer.de>
2022-11-18 06:13 ` Ron <ronljohnsonjr@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox