No title

Started by AC Gomezover 4 years ago2 messagesgeneral
Jump to latest
#1AC Gomez
antklc@gmail.com

Postgres 9.6. We're attempting to delete some old users from a DB.

Log into DB as masteruser.

Run this block of commands (the_schema=public, and it's the only schema in
this particular DB):

REVOKE ALL PRIVILEGES ON DATABASE the_database FROM old_role;
REVOKE USAGE ON SCHEMA the_schema FROM old_role;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA the_schema FROM old_role;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA the_schema FROM old_role;

Here we actually had to create a loop to revoke one function at a time
while avoiding system functions on which this single command fails:
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA the_schema FROM old_role;

REVOKE ALL PRIVILEGES ON SCHEMA the_schema FROM old_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON SEQUENCES FROM
old_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON TABLES FROM
old_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA the_schema REVOKE ALL ON FUNCTIONS FROM
old_role;

REASSIGN OWNED BY old_role TO masteruser;
DROP OWNED BY old_role;
DROP USER old_role;

The consensus online is that these are the steps to be taken to
successfully remove roles, yet we get this error below:

ERROR: role "old_role" cannot be dropped because some objects depend on it
DETAIL: privileges for database the_database 96 objects in database
the_database SQL state: 2BP01

What is it that we could be missing here?

Thanks for any help!

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: AC Gomez (#1)
Re:

AC Gomez <antklc@gmail.com> writes:

REASSIGN OWNED BY old_role TO masteruser;
DROP OWNED BY old_role;
DROP USER old_role;

The consensus online is that these are the steps to be taken to
successfully remove roles, yet we get this error below:
ERROR: role "old_role" cannot be dropped because some objects depend on it
DETAIL: privileges for database the_database 96 objects in database
the_database SQL state: 2BP01

What is it that we could be missing here?

You need to do the REASSIGN OWNED and DROP OWNED steps in each
database of the cluster that old_role has any objects/privileges in.

You might need a manual revoke on "the_database" too; I'm not sure
if DROP OWNED does anything about DB-level privileges.

regards, tom lane