Drop user in redshift which has privilege on some object

permissionsredshift

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?

Best Answer

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
Related Question