2

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.

0

2 Answers 2

2

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:

2
  • Like I mentioned, the solutions in that other question do not work for me. Specifically, RDS does not support "REASSIGN" and gives me the error ERROR: permission denied to reassign objects even for my RDS superuser.
    – Cerin
    Sep 15, 2023 at 2:45
  • Like I said, REASSIGN OWNED is optional for you. DROP OWNED does the trick. Sep 15, 2023 at 2:49
1

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;
2
  • 1
    Even after revoking those privileges, running DROP USER myuser; still results in: ERROR: role "myuser" cannot be dropped because some objects depend on it DETAIL: privileges for database mydb privileges for schema myschema
    – Cerin
    Sep 15, 2023 at 2:42
  • If you tried DROP OWNED (which should take care of permissions) and it gave you an error , I'd suggest trying revoking everything you granted explicitly in addition to revoking default privileges. E.g. REVOKE USAGE ,REVOKE CONNECT , REVOKE SELECT and then try to drop user again.
    – a1ex07
    Sep 15, 2023 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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