0

I just inherited an AWS RDS PostgreSQL 12.5 instance (so, no superuser access) and I'm required to do some cleanup and delete a bunch of groups/roles/users. I'm executing the following commands with user 'master' provided by AWS on the only available database (apart from postgres db)

When trying a:

DROP ROLE xxx

I get:

ERROR: role "xxx" cannot be dropped because some objects depend on it
DETAIL: N objects in database xxx

After a LOT of googling I ended up with:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON SCHEMA some_schema FROM "xxx";
REVOKE USAGE ON SCHEMA some_schema FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON SEQUENCES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON TABLES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON FUNCTIONS FROM "xxx";
REVOKE ALL PRIVILEGES ON DATABASE xxx FROM "xxx";

But still, the DROP fails:

ERROR: role "xxx" cannot be dropped because some objects depend on it
DETAIL: privileges for default privileges on new types belonging to role master in schema some_schema
SQL state: 2BP01

Tried to check dependencies using (taken from some stackoverflow question) but query comes up with nothing:

SELECT *
FROM
    --r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
    (SELECT N.NSPNAME AS SCHEMA_NAME,
            C.RELNAME AS REL_NAME,
            C.RELKIND AS REL_KIND,
            PG_GET_USERBYID(C.RELOWNER) AS OWNER_NAME
        FROM PG_CLASS C
        JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
    UNION ALL
    -- functions (or procedures)
    SELECT N.NSPNAME AS SCHEMA_NAME,
            P.PRONAME,
            'p',
            PG_GET_USERBYID(P.PROOWNER)
        FROM PG_PROC P
        JOIN PG_NAMESPACE N ON N.OID = P.PRONAMESPACE) AS FOO
WHERE OWNER_NAME = 'xxx'

Then tried to check privileges using (again, came up empty):

SELECT
    grantee AS user,
    CONCAT(table_schema, '.', table_name) AS table, 
    CASE 
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee
HAVING grantee = 'xxx'

This is my first time with PostgreSQL, coming from SQL Server.

4
  • 1
    Have you checked all databases in the cluster? Roles are cluster-level objects in Postgres. May 28 at 10:33
  • You may start from the doc: postgresql.org/docs/current/role-removal.html and tell us why REASSIGNED OWNED and/or DROP OWNED wouldn't work in your case. May 28 at 11:05
  • @DanielVérité Amazon does not provide access to a superuser account, REASSIGN requires superuser access May 31 at 7:43
  • @Colin'tHart yes, the only database in this instance (other than 'postgre') is the one I'm executing the commands in May 31 at 8:00
1

You have to revoke the default privileges:

ALTER DEFAULT PRIVILEGES FOR ROLE master IN SCHEMA some_schema
   REVOKE ALL ON TYPES FROM xxx;

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.