group privileges not picked up by `v_generate_user_grant_revoke_ddl`

[sworisbreathing]

I'm trying to drop a group from my redshift cluster. Using the latest version of v_generate_user_grant_revoke_ddl, I'm running the following query to get the DDL statements to run for the group privileges:

select ddl from admin.v_generate_user_grant_revoke_ddl where ddltype='revoke' and username='<groupname>' order by grantseq;

This is the statement documented at https://aws.amazon.com/premiumsupport/knowledge-center/redshift-user-cannot-be-dropped/ except that the sequence number column has been renamed since the article was written (NB: I've also provided feedback on the page saying it's out of date and needs to be updated to reflect the column name changes).

The query returns an empty result set, which leads me to believe I'm clear to drop the group, however when I do so, I get the following error:

SQL Error [500310] [55006]: [Amazon](500310) Invalid operation: group "<groupname>" cannot be dropped because the group has a privilege on some object;

I'm gonna have to dig into the view definition and see if I can figure out where the missing privileges are. I will add comments to this ticket with my findings.

[sworisbreathing]

I've found the group mentioned in aclstring:

SELECT b.relowner,
        pg_get_userbyid(b.relowner)::text AS objowner,
		trim(c.nspname)::text AS schemaname,  
		b.relname::text AS objname,
		CASE WHEN relkind='r' THEN 'table' ELSE 'view' END::text AS objtype, 
		TRIM(SPLIT_PART(array_to_string(b.relacl,','), ',', NS.n))::text AS aclstring, 
		NS.n as grantseq,
		null::text as colname
		FROM 
		(SELECT oid,generate_series(1,array_upper(relacl,1))  AS n FROM pg_catalog.pg_class) NS
		INNER JOIN pg_catalog.pg_class B ON b.oid = ns.oid AND  NS.n <= array_upper(b.relacl,1)
		INNER JOIN pg_catalog.pg_namespace c on b.relnamespace = c.oid
		where relkind in ('r','v')
    AND aclstring LIKE '%<groupname>%'

none of the objowners returned by this query are in the group I am trying to drop.

[sworisbreathing]

I can use the following to generate schema-level revoke statements:

WITH objprivs AS (
----table and view privileges
SELECT pg_get_userbyid(b.relowner)::text AS objowner, 
		trim(c.nspname)::text AS schemaname,  
		b.relname::text AS objname,
		CASE WHEN relkind='r' THEN 'table' ELSE 'view' END::text AS objtype, 
		TRIM(SPLIT_PART(array_to_string(b.relacl,','), ',', NS.n))::text AS aclstring, 
		NS.n as grantseq,
		null::text as colname
		FROM 
		(SELECT oid,generate_series(1,array_upper(relacl,1))  AS n FROM pg_catalog.pg_class) NS
		INNER JOIN pg_catalog.pg_class B ON b.oid = ns.oid AND  NS.n <= array_upper(b.relacl,1)
		INNER JOIN pg_catalog.pg_namespace c on b.relnamespace = c.oid
		where relkind in ('r','v')
UNION ALL
---- table and view column privileges
SELECT pg_get_userbyid(c.relowner)::text AS objowner, 
		trim(d.nspname)::text AS schemaname,  
		c.relname::text AS objname,
		'column'::text AS objtype, 
		TRIM(SPLIT_PART(array_to_string(b.attacl,','), ',', NS.n))::text AS aclstring, 
		NS.n as grantseq,
		b.attname::text as colname
		FROM 
		(SELECT attrelid,generate_series(1,array_upper(attacl,1))  AS n FROM pg_catalog.pg_attribute_info) NS
		INNER JOIN pg_catalog.pg_attribute_info B ON b.attrelid = ns.attrelid AND  NS.n <= array_upper(b.attacl,1)
		INNER JOIN pg_catalog.pg_class c on b.attrelid = c.oid
		INNER JOIN pg_catalog.pg_namespace d on c.relnamespace = d.oid
		where relkind in ('r','v')
UNION ALL
SELECT pg_get_userbyid(b.nspowner)::text AS objowner,
		null::text AS schemaname,
		b.nspname::text AS objname,
		'schema'::text AS objtype,
		TRIM(SPLIT_PART(array_to_string(b.nspacl,','), ',', NS.n))::text AS aclstring,
		NS.n as grantseq,
		null::text as colname
		FROM 
		(SELECT oid,generate_series(1,array_upper(nspacl,1)) AS n FROM pg_catalog.pg_namespace) NS
		INNER JOIN pg_catalog.pg_namespace B ON b.oid = ns.oid AND NS.n <= array_upper(b.nspacl,1)
)

SELECT 'REVOKE USAGE ON SCHEMA ' || objname || ' FROM GROUP <groupname>;' AS ddl
FROM objprivs
WHERE objtype='schema'
AND aclstring like '%<groupname>%'
GROUP BY ddl

[sworisbreathing]

Similarly I can generate revoke statements for table-level access:

WITH objprivs AS (
----table and view privileges
SELECT pg_get_userbyid(b.relowner)::text AS objowner, 
		trim(c.nspname)::text AS schemaname,  
		b.relname::text AS objname,
		CASE WHEN relkind='r' THEN 'table' ELSE 'view' END::text AS objtype, 
		TRIM(SPLIT_PART(array_to_string(b.relacl,','), ',', NS.n))::text AS aclstring, 
		NS.n as grantseq,
		null::text as colname
		FROM 
		(SELECT oid,generate_series(1,array_upper(relacl,1))  AS n FROM pg_catalog.pg_class) NS
		INNER JOIN pg_catalog.pg_class B ON b.oid = ns.oid AND  NS.n <= array_upper(b.relacl,1)
		INNER JOIN pg_catalog.pg_namespace c on b.relnamespace = c.oid
		where relkind in ('r','v')
UNION ALL
---- table and view column privileges
SELECT pg_get_userbyid(c.relowner)::text AS objowner, 
		trim(d.nspname)::text AS schemaname,  
		c.relname::text AS objname,
		'column'::text AS objtype, 
		TRIM(SPLIT_PART(array_to_string(b.attacl,','), ',', NS.n))::text AS aclstring, 
		NS.n as grantseq,
		b.attname::text as colname
		FROM 
		(SELECT attrelid,generate_series(1,array_upper(attacl,1))  AS n FROM pg_catalog.pg_attribute_info) NS
		INNER JOIN pg_catalog.pg_attribute_info B ON b.attrelid = ns.attrelid AND  NS.n <= array_upper(b.attacl,1)
		INNER JOIN pg_catalog.pg_class c on b.attrelid = c.oid
		INNER JOIN pg_catalog.pg_namespace d on c.relnamespace = d.oid
		where relkind in ('r','v')
UNION ALL
SELECT pg_get_userbyid(b.nspowner)::text AS objowner,
		null::text AS schemaname,
		b.nspname::text AS objname,
		'schema'::text AS objtype,
		TRIM(SPLIT_PART(array_to_string(b.nspacl,','), ',', NS.n))::text AS aclstring,
		NS.n as grantseq,
		null::text as colname
		FROM 
		(SELECT oid,generate_series(1,array_upper(nspacl,1)) AS n FROM pg_catalog.pg_namespace) NS
		INNER JOIN pg_catalog.pg_namespace B ON b.oid = ns.oid AND NS.n <= array_upper(b.nspacl,1)
)

SELECT 'REVOKE ALL ON ALL TABLES IN SCHEMA ' || schemaname || ' FROM GROUP <groupname>;' AS ddl
FROM objprivs
WHERE objtype='table'
AND aclstring like '%<groupname>%'
GROUP BY ddl

[sworisbreathing]

After running the above two SQL blocks and removing all users from the group I was able to drop the group.

[Venkat-Castlery]

I am not able to delete it still getting the error like SQL Error [55006]: ERROR: group "buying_alias" cannot be dropped because permission dependency is found

[wahlrkr]

Getting this same exact problem for a group we have. I have tried to do all these steps above and also

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema>
    REVOKE ALL ON TABLES FROM GROUP <group_i_want_to_drop>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema>
    REVOKE ALL ON FUNCTIONS FROM group <group_i_want_to_drop>;

All of the admin views return empty and SQLs @sworisbreathing posted. Still getting

SQL Error [55006]: ERROR: group "<group_i_want_to_drop>" cannot be dropped because permission dependency is found

EDIT. Ok, so our team found out, with the help of AWS Support, that this was due to the default dev-database which is created by AWS when you first setup the cluster. There were some privileges granted accidentally by us. After removing them, I could easily drop the group. So if encountering the same problem here, check also the dev-database especially if you have done your own besides that, and run the queries again against admin.v_generate_user_grant_revoke_ddl there 👍