I have a pretty standard problem that I can’t resolve. I want to remove a user in redshift
DROP USER u_A; which returns me:
user "u_A" cannot be dropped because the user has a privilege on some object.
The problem is that I have no idea what kind of privilege is this and on what object.
In PostgreSQL I would just
REASSIGN OWNED BY u_A TO u_B where a u_B is some other user. The problem is that redshift does not support REASSIGN. The amount of tables and schemas I have is too big (so I can’t randomly try everything hoping that at some point I will remove the needed privilege.
So how can I remove that user?
There are some queries that give you the privileges currently granted to users on any objects:
select nspname as schemaname , array_to_string(nspacl, ',') as acls from pg_namespace where nspacl is not null and nspowner != 1 and array_to_string(nspacl, ',') like '%u_A=%' -- REPLACE USERNAME ;
select pg_namespace.nspname as schemaname , pg_class.relname as tablename , array_to_string(pg_class.relacl, ',') as acls from pg_class left join pg_namespace on pg_class.relnamespace = pg_namespace.oid where pg_class.relacl is not null and pg_namespace.nspname not in ( 'pg_catalog' , 'pg_toast' , 'information_schema' ) and array_to_string(pg_class.relacl, ',') like '%u_A=%' -- REPLACE USERNAME order by pg_namespace.nspname , pg_class.relname ;
r -- SELECT ("read") w -- UPDATE ("write") a -- INSERT ("append") d -- DELETE D -- TRUNCATE x -- REFERENCES t -- TRIGGER X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) * -- grant option for preceding privilege /yyyy -- role that granted this privilege
After trying suggestions from countless posts and threads, awslabs’ aws-redshift-utils provided relief in the form of admin.v_find_dropuser_objs view. It instantly identified the remaining dependencies making it possible to drop the user in question.
Before you drop a user, you must revoke any privileges that the user has and then transfer ownership of any database objects that the user owns.
Download and install the v_generate_user_grant_revoke_ddl.sql script from the AWS Labs GitHub repository. This script creates a view in Amazon Redshift that is useful for the next two steps.
Find all privileges granted to the user and then grant those privileges to the user again, as a superuser or another user, as shown in the following example.
WHERE grantor=” and ddltype=’grant’ AND
objtype <>’default acl’ order by objseq,grantseq;
Find all privileges granted to the user and then revoke those privileges, as shown in the following example.
WHERE ddltype=’revoke’ and (grantee=” OR
grantor=”) order by objseq, grantseq desc;
Download and install the v_find_dropuser_objs.sql script from the AWS Labs GitHub repository. This script creates a view in Amazon Redshift that is useful for the next step.
Find all objects owned by the user and then transfer ownership to a different user. In the example below, is the current owner and is the new owner.
Repeat steps 2-5 in each database on the cluster.
Drop the user.
EDIT: In case none of the above works it might be because of a bug in Role Based Access Control (RBAC) system on the Redshift side.
They are planning to apply a permanent fix but in case reach out to AWS Support and they’ll be able to apply a workaround on their side (might require a Cluster restart).
As of today, Redshift does not have a REASSIGN functionality and you cannot drop a user if the user –
- is the owner of an object; and/or
- has some privilege on any object.
Quoting from DROP USER :
DROP USER doesn’t return an error if the user owns database objects or has any privileges on objects in another database. If you drop a user that owns objects in another database, the owner for those objects is changed to ‘unknown’.
You can follow below steps to to revoke all the privileges assigned to that user and later drop user from the database –
- Change owner of all tables owned by user x(to be dropped) to someone else (y) –
select 'alter table '+schemaname+'.'+tablename+' owner to y;' from pg_tables where tableowner like 'x'
Redirect output of this SQL to a file, say alterowner.sql.
- Revoke all schema level privileges from that user
select distinct 'revoke all on schema '+schemaname+' from x;' from admin.v_get_obj_priv_by_user where usename like 'x'
Save the output to another file, revokeschemapriv.sql
- Revoke table level privileges –
select distinct 'revoke all on all tables in schema '+schemaname+' from x;' from admin.v_get_obj_priv_by_user where usename like 'x'
Save this to a file revoketablepriv.sql
Now run all the 3 SQL files against your database. It will solve your purpose.
Drop the user
DROP USER x;
You can also put all this together in a script which will make things a lot easier.
NOTE – admin.v_get_obj_priv_by_user is a view provided as an amazon utility. You can see the view definition here
and create it in your database.
Kamlesh Gallani’s response works great. The
v_get_obj_priv_by_user function is defined in https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_get_obj_priv_by_user.sql. I created a Python script for it:
import psycopg2 # Connect to Redshift conn = psycopg2.connect(host="", dbname="", user="", password="", port="5439") conn.autocommit = True cursor = conn.cursor() # List of users to drop users = ['user_to_drop_1', 'user_to_drop_2'] # New owner, used when changing ownership new_owner = 'new_user' # Templates to change ownership, revoke permissions, and drop users change_ownership = "select 'alter table '+schemaname+'.'+tablename+' owner to %s;' from pg_tables where tableowner like '%s'" revoke_schema_permissions = "select distinct 'revoke all on schema '+schemaname+' from %s;' from admin.v_get_obj_priv_by_user where usename like '%s'" revoke_table_permissions = "select distinct 'revoke all on all tables in schema '+schemaname+' from %s;' from admin.v_get_obj_priv_by_user where usename like '%s'" drop_user = "drop user %s;" for user in users: # Change ownership cursor.execute(change_ownership % (new_owner, user)) for r in cursor.fetchall(): print("Executing: %s" % r) cursor.execute(r) # Revoke schema permissions cursor.execute(revoke_schema_permissions % (user, user)) for r in cursor.fetchall(): print("Executing: %s" % r) cursor.execute(r) # Revoke table permissions cursor.execute(revoke_table_permissions % (user, user)) for r in cursor.fetchall(): print("Executing: %s" % r) cursor.execute(r) # Drop user cursor.execute(drop_user % (user))