0

I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and sequences inside have been created by users foo_migration and bar_migration respectively.

First I log in as the postgres user to the foo DB. When I try to drop the user, I get:

ERROR: role "asd" cannot be dropped because some objects depend on it with details about privileges from multiple sequences, tables, the bar DB and a few objects in the postgres DB.

So, I try to clear things one by one (while still logged in as postgres user in the foo DB). I first try to REASSIGN OWNED BY asd TO foo_migration;. This returns ERROR: permission denied, which is weird because I'm logged in as the postgres user. Anyway, then I try

REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM asd;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM asd;

and I get a bunch of WARNING: no privileges could be revoked for all the sequences and tables that the user has rights on. I also try DROP OWNED BY asd; which also ends in ERROR: permission denied.

This was all unsuccessful, so I try logging in the postgres DB as the postgres user and perform the same steps. The REVOKE commands execute successfully without warnings, but no permissions actually get changed/affected. The REASSIGN and DROP OWNED BY still result in ERROR: permission denied.

The error messages are bare. There's no hints or details provided alongside with them.

I do not have the password for the asd user, so I cannot try to log in with it and perform any actions.

Do you have any ideas of how to approach this? Do I need to look into some tables that show ownerships and go from there? Isn't the postgres user the admin user that is "almighty" ?

  • Strangely postgres is listed as FALSE for rolsuper. The only TRUE one is rdsadmin. – Milkncookiez Jan 10 at 14:57
1

I was running into similar issue on Postgres RDS. This was the workaround I used to reassign objects from one user to another and then drop user. For you it would go like:

-- logged in as the rds_superuser role you defined when you created your RDS instance
CREATE ROLE change_owner LOGIN;
GRANT asd TO change_owner;
GRANT foo_migration TO change_owner;

-- log out of current user and log in as `change_owner`
REASSIGN OWNED BY asd TO foo_migration;

-- log out of current user and log in as rds_superuser role
DROP USER asd;
-1

I'm going thru this issue myself, so one easy thing you can do is change the password of asd. That can be done by running

ALTER USER asd WITH PASSWORD 'new_password';

After that, I tried the following, which was provided by AWS Support and even took me a while to realize which commands should be executed by which role.

-- logged in as postgres, grant the unwanted role to the master user 
GRANT asd TO postgres;

-- and then reassign the objects
REASSIGN OWNED BY asd TO postgres;
DROP ROLE IF EXISTS asd;

In case there are still objects that asd has privileges on, you would need to log into each user that is the owner of the objects mentioned in the error message, and then revoke all the privileges for the concerned object from the user which needs to be dropped.

-- logged in as the owner of {table} 
REVOKE ALL PRIVILEGES ON TABLE {table} FROM asd;

Run the above for each table, logged in as the owner of the table in question. Once done, perform the DROP ROLE again as postgres.

PS: My case has about 100 items and I still couldn't script my way around these steps nor perform it all manually, but the first tests have worked. I'm confident that this will do the trick but cannot confirm just yet. Good luck.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.