Can’t DROP GROUP, cannot be dropped because some objects depend on it

redshift

RDS Version: PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1161

I've been wrestling with this issue for a few days now, and all solutions I've found here and the AWS Forums aren't working for me.

Basically I'm trying to drop a group. This group has no users in it (though per the AWS docs that shouldn't matter).

I've revoked all privileges on database, schema, tables, and functions. They are not an owner nor part of an ACL in pg_tables, pg_views, pg_namespace, or pg_database.

They are also not a part of the ACL in pg_default_acl.

When I run DROP GROUP my_group; I receive the following error:

[Amazon](500310) Invalid operation: group "my_group" cannot be dropped because some objects depend on it Details: privileges for default privileges on new relations belonging to user dwowner in schema changehistory privileges for default privileges on new relations belonging to user dwowner in schema stage privileges for default privileges on new relations belonging to user dwowner in schema public 5 object in database prod;

Running:

ALTER DEFAULT PRIVILEGES
IN SCHEMA stage, public, changehistory, admin
REVOKE ALL ON TABLEs FROM GROUP my_group;

Doesn't result in changes to the pg_default_acl table (because they weren't there in the first place) and the above error continues when trying to drop the group.

A few questions:

  • dwowner is in fact the owner of those schemas, but what does that have to do with my_group?
  • the end of the error references the prod database. I ran this query in dev. I've run the same revoke statements on prod as well. Running the DROP GROUP my_group; on prod results in a similar error, this time referencing the dev database.

There's obviously some object permission relationship that I'm missing somewhere but I have absolutely no clue at this point

Best Answer

What I found is that in order to drop a group you have to revoke all privileges that the group might have been assigned. A group can have default privileges, usage on schema, and grants on tables. I used following 5 statements and then was able to drop the group. 

--Drop All users from the group
alter group <groupname> drop user <username>;
--REVOKE usage on the group
REVOKE ALL ON SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    FROM group <groupname>;
--REVOKE grants on tables
REVOKE ALL ON ALL TABLES IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    FROM group <groupname>;
--REVOKE Default privileges on TABLES
ALTER DEFAULT PRIVILEGES for USER <username> IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    REVOKE ALL ON TABLES FROM group <groupname>;
--REVOKE Default privileges on FUNCTIONS if any
ALTER DEFAULT PRIVILEGES for USER <username> IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    REVOKE ALL ON FUNCTIONS FROM group <groupname>;
--drop the group finally
drop group <groupname>;