Drop user in redshift which has privilege on some object

Question

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?

Answer 1

There are some queries that give you the privileges currently granted to users on any objects:

Schema ACL

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
;

Table ACL

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
;

Privilege Explanation

      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

Answer 2

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.

Answer 3

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.

1. 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.

2. 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.

   SELECT regexp_replace(ddl,grantor,'<superuser>')
   FROM admin.v_generate_user_grant_revoke_ddl 
   WHERE grantor='<username>' and ddltype='grant' AND 
         objtype <>'default acl' order by objseq,grantseq;
   

3. Find all privileges granted to the user and then revoke those privileges, as shown in the following example.

   SELECT ddl 
   FROM admin.v_generate_user_grant_revoke_ddl 
   WHERE ddltype='revoke' and (grantee='<username>' OR
         grantor='<username>') order by objseq, grantseq desc;
   

4. 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.

5. 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.

6. Repeat steps 2-5 in each database on the cluster.

7. Drop the user.

   DROP USER <username>
   

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

Answer 4

As of today, Redshift does not have a REASSIGN functionality and you cannot drop a user if the user -

1. is the owner of an object; and/or
2. 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 -

1. 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.

2. 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

3. 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

4. Now run all the 3 SQL files against your database. It will solve your purpose.

5. 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.

Answer 5

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[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))