Drop role postgresql

Question

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.

• Colin 't Hart: Have you checked all databases in the cluster? Roles are cluster-level objects in Postgres.
• Daniel Vérité: 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.
• Daniel Huesca: @DanielVérité Amazon does not provide access to a superuser account, REASSIGN requires superuser access
• Daniel Huesca: @Colin'tHart yes, the only database in this instance (other than 'postgre') is the one I'm executing the commands in

Answer 1

You have to revoke the default privileges:

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