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: