Unable to drop user due to assigned privileges?

Question

How do you drop a user with read-only permission to PostgreSQL?

I created a user, with read-only access to all tables, on an RDS Postgres database with:

CREATE ROLE myuser WITH LOGIN PASSWORD 'supersecretpassword';
ALTER USER myuser SET search_path=myschema;
GRANT CONNECT ON DATABASE mydb TO myuser;
GRANT USAGE ON SCHEMA myschema TO myuser;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO myuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO myuser;

Now I'd like to drop this user's access, so I tried the obvious:

DROP USER myuser;

But this gives me the error:

Could not drop the role.
ERROR: privileges for database mydb
privileges for schema myschema
privileges for default privileges on new relations belonging to role mainrole in schema myschema role "myuser" cannot be dropped because some objects depend on it

If I try:

DROP OWNED BY myuser;

That also gives me the error:

ERROR: permission denied to drop objects

My role is the root login for my RDS instance, so not sure why I can't drop objects. Must be some RDS limitation. So maybe if I get a list of all the objects owned by the user I want to drop as outlined in this post, I can get a sense of what I need to reassign?

However, if I run:

select 
    nsp.nspname as SchemaName
    ,cls.relname as ObjectName 
    ,rol.rolname as ObjectOwner
    ,case cls.relkind
        when 'r' then 'TABLE'
        when 'm' then 'MATERIALIZED_VIEW'
        when 'i' then 'INDEX'
        when 'S' then 'SEQUENCE'
        when 'v' then 'VIEW'
        when 'c' then 'TYPE'
        else cls.relkind::text
    end as ObjectType
from pg_class cls
join pg_roles rol 
    on rol.oid = cls.relowner
join pg_namespace nsp 
    on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
    and nsp.nspname not like 'pg_toast%'
    and rol.rolname = 'myuser'  
order by nsp.nspname, cls.relname

it returns nothing.

So I can't drop the user because it owns stuff. But also it doesn't own anything? What am I missing?

This is similar to this question but none of its solution work, presumably because this is on RDS.

Answer 1

This is the safe sequence of commands:

Repeat in every database of the same DB cluster, where the role may own anything or may have any privileges:

REASSIGN OWNED BY myuser TO rds_superuser;  -- optional; see below
DROP OWNED BY myuser;

Then, once, in any database of the same DB cluster:

DROP ROLE myuser;

The manual:

DROP USER is simply an alternate spelling of DROP ROLE.

DROP OWNED also removes all privileges including DEFAULT PRIVILEGES, which is your problem in particular.

REASSIGN OWNED is optional in your case, since the role myuser does not own anything (but privileges). But it's the safe way if there can be objects that you might not want to destroy. I chose rds_superuser as target role sionce that seems to be the role you are operating with. Adjust to your needs. The manual:

REASSIGN OWNED requires membership on both the source role(s) and the target role.

See:

• Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`
• Find objects linked to a PostgreSQL role

Answer 2

It seems you first need to drop default privileges you granted:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLES FROM myuser;

You can get default privileges using the query from https://stackoverflow.com/a/14555063

 SELECT 
  nspname,         -- schema name
  defaclobjtype,   -- object type
  defaclacl        -- default access privileges
FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;