6

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

squeekwull
  • 61
  • 1
  • 3

4 Answers4

4

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>;
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
rsuser
  • 41
  • 2
1

alter default privileges worked for me in a similar scenario

below query should help identify the dependency

SELECT nspname, defaclobjtype, array_to_string(defaclacl, ',')
FROM   pg_default_acl a
         JOIN pg_namespace b ON a.defaclnamespace=b.oid;

the alter default privileges command also takes the grantor information as a parameter.

on running the above query you should be able to identify the dependency and also the user that granted that priviledge, use that user name as part of alter default privileges, as seen below. NOTE you must have superuser privileges to do this

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

Hope it helps. :-)

Susheel
  • 11
  • 4
1

The above options almost worked for me, but missed a couple objects and functions, and took work to manually re-configure for each group. Here is a Python script to do it all for you, using the suggested AWS views: https://github.com/grantdexter/redshift-tools/blob/master/scripts/drop_redshift_group.py

DextersLab
  • 11
  • 1
0

A few years later than the OP, but Redshift has updated guidance on how best to do this:

https://aws.amazon.com/premiumsupport/knowledge-center/redshift-user-cannot-be-dropped/

To summarize, you can use one of the admin views from github to generate the appropriate DDL that releases group permissions. Once you run this script:

https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_user_grant_revoke_ddl.sql

You can execute what it generates, and thereafter drop the group

Will Ayd
  • 101
  • 2