PostgreSQL Role Management – How to Drop a Role

managementpostgresqlroleusers

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.

Best Answer

You have to revoke the default privileges:

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