Cannot revoke permissions or drop user in PgSQL AWS RDS

Question

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

Answer 1

I'll tell first that the master user you defined for your AWS RDS is able to drop the user.

I am on a Postgres 13.3 engine.

You'll have to make an important reading exercise to go through this problem and be extremely clear on how you created the user and to what database are you connected to.

I dropped a user created in this way:

CREATE USER "my_user" WITH PASSWORD 'my_difficult_pass' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA PUBLIC TO  "my_user";
GRANT CONNECT ON DATABASE my_database TO "my_user";
GRANT USAGE ON SCHEMA public TO "my_user";
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO  "my_user";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "my_user";

Next, I'll go through the successful commands that let me DROP the user.

my_database=> DROP USER "my_user";
ERROR:  role "my_user" cannot be dropped because some objects depend on it
DETAIL:  privileges for database my_database
2 objects in database postgres

my_database=> REVOKE ALL PRIVILEGES ON SCHEMA public FROM "my_user"; 

REVOKE

my_database=> REVOKE ALL PRIVILEGES ON DATABASE my_database FROM "my_user";  

REVOKE

After a ton of errors, I realized I needed to change my connection to postgres db with \c postgres in order to continue:

postgres=> REVOKE ALL PRIVILEGES ON SCHEMA public FROM "my_user";
REVOKE

postgres=> REVOKE ALL PRIVILEGES ON DATABASE postgres FROM "my_user";
REVOKE

postgres=> ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM "my_user";
ALTER DEFAULT PRIVILEGES

postgres=> DROP USER "my_user";
DROP ROLE

So, each time DROP USER "my_user" fails you must understand in the error message where the next object -or whatever- is located so you REVOKE or ALTER.

Answer 2

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;

Answer 3

I solved this by first REVOKING everything using a script.
I wrote it in python (sqlalchemy) and keeping the postgresql explicit; it should be clear enough to migrate to another language.

all_schemas = db_conn.execute(
    "SELECT schema_name FROM information_schema.schemata "
    "WHERE schema_name != 'information_schema' "
    "AND schema_name != 'pg_toast' "
    "AND schema_name != 'pg_catalog';"
).fetchall()
for schem in all_schemas:
    if any([s in schem[0] for s in ["pg_temp_", "pg_toast_temp_"]]):
        continue  # skip over these.  This is kinda what '\dn' does in psql.
    db_conn.execute(f"REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA {schem[0]} FROM {user}")
    db_conn.execute(f"REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA {schem[0]} FROM {user}")
    db_conn.execute(f"REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA {schem[0]} FROM {user}")
    db_conn.execute(f"REVOKE USAGE ON SCHEMA {schem[0]} FROM {user}")

# this is response from \d
all_resources = db_conn.execute("""
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind 
  WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' 
  WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' 
  END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
""").fetchall()
for resource in all_resources:
    resource_name = resource[1]
    db_conn.execute(f"REVOKE ALL ON {resource_name} FROM {user}")

all_databases = db_conn.execute(
    "SELECT datname FROM pg_database "
    "WHERE NOT datacl ~ 'rdsadmin=CTc/rdsadmin';"
).fetchall()
for db_tup in all_databases:
    db_conn.execute(f"REVOKE ALL PRIVILEGES ON DATABASE {db_tup[0]} FROM {user}")

# And finally (good god this shouldn't be this hard)
db_conn.execute(f"DROP USER IF EXISTS {user}")

Answer 4

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.