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.