Redshift Force Drop User

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 straight forward.

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.

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[0])
        cursor.execute(r[0])
    # Revoke schema permissions
    cursor.execute(revoke_schema_permissions % (user, user))
    for r in cursor.fetchall():
        print("Executing: %s" % r[0])
        cursor.execute(r[0])
    # Revoke table permissions
    cursor.execute(revoke_table_permissions % (user, user))
    for r in cursor.fetchall():
        print("Executing: %s" % r[0])
        cursor.execute(r[0])
    # Drop user
    cursor.execute(drop_user % (user))

Leave a Reply

Your email address will not be published. Required fields are marked *