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" ?

  • @jjanes , I updated. – Milkncookiez Jan 10 at 14:30
  • Are you sure "postgres" is a superuser? It usually is by default, but that can be changed. For example, some people might consider it more secure if the superuser name is not easily guessable. Another example, if hackers get into your system they will often revoke the superuserness of "postgres". You can check with '\du' or 'select rolname, rolsuper from pg_catalog.pg_roles'. Also, there are some things even a superuser can not do directly, as it risks corrupting the database or being a security hazard (although I don't know how that applies to this case, and can't reproduce it.) – jjanes Jan 10 at 14:42
  • @jjanes, strangely postgres is listed as FALSE for rolsuper. The only TRUE one is rdsadmin. How can I approach this issue then? – Milkncookiez Jan 10 at 14:57
  • 1
    Is this an amazon RDS system? If so, you should tag it that way and make it clear up front. And you should probably consult their documentation and/or tech support. – jjanes Jan 10 at 15:00
  • 2
    It doesn't make much difference for writing SELECT queries. But it makes big difference when it comes to administrative tasks like this one. Unfortunately I probably don't know enough about RDS to help you. – jjanes Jan 10 at 15:09
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;

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.