Thread: Drop role cascade ?
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" ?
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
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
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.
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
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.
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
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.