Ever tried dropping a user in Redshift only to discover that user “user_1” cannot be dropped because the user has a privilege on some object.
That’s not of great help Redshift. Luckily for us, there is a solution. Kamlesh Gallani posted a response about revoking permissions for tables and schemas that I user might still be assigned to, along with changing the ownership of tables that the user might have created. After that, dropping the user is straightforward.
I created a python snippet with his response that might save you a few minutes. You will need the view v_get_obj_priv_by_user from amazon-utils, simply create the view on your Redshift cluster, then, copy and paste this Python script into your favorite editor (e.g. Sublime Text) fill in the connection information and the list of users to drop and execute 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))