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.