Drop role postgresql

0 votes
1 answer
1835 views
                          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.
                        
Asked by Daniel Huesca (3 rep)
May 28, 2021, 09:32 AM
Last activity: May 28, 2021, 10:34 AM
Drop role postgresql

Related Questions
