Redshift - user "xyz" cannot be dropped because the user owns some object

redshift user permissions
redshift user groups
redshift add user to group
redshift delete user
redshift change user password
rename redshift user
redshift user connection limit
redshift view user permissions

When trying to drop a user "xyz" in a Redshift database, I get the error message:

user 'xyz' cannot be dropped because the user owns some object.

According to the documentation:

If a user owns an object, first drop the object or change its ownership to another user before dropping the original user

How do I know which objects (schemas, tables, views, UDFs?, ...) are owned by the user?

you can see the tables and views owned by a specific user in pg_tables and pg_views system tables. (there is also pg_udf, but it doesn't hold any reference to specific user)

select 
    case when schemaname='public' then '' 
        else schemaname+'.' end+tablename 
    from pg_tables where tableowner = 'xyz'
union 
    select 
    case when schemaname='public' then ''
        else schemaname+'.' end+viewname 
    from pg_views where viewowner = 'xyz'

;

CREATE USER - Amazon Redshift, WITH is ignored by Amazon Redshift. PASSWORD { 'password' | 'md5hash' | DISABLE }. Sets the user's password. By default, users can change their own  Improve Analytics Insights and Learn Data Management Best Practices-Get Started!

In my case, the user didn't own any tables or views or anything else that I could find. I guessed correctly that having been granted a privilege meant the user "owned" something, because I fixed the problem with this:

revoke all privileges on database <database> from '<username>'

after which the drop user command worked.

Step 2: Create a database user, Create user accounts to grant other users access to your Amazon Redshift database. Users are authenticated when they login to Amazon Redshift. They can own databases and database objects (for example, tables) and can grant privileges on those objects to users, groups, and schemas to control who has access to which object.

Since the following resolved the problem in so spectacularly easy fashion for me, I duplicate this response into this thread as well:

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.

Step 5: Query the system tables, You can query the PG_USER catalog to view a list of all database users, along with the user ID (USESYSID) and user privileges. select * from pg_user; usename |  A clause that specifies the level of access the user has to the Amazon Redshift system tables and views. If RESTRICTED is specified, the user can see only the rows generated by that user in user-visible system tables and views. The default is RESTRICTED.

List users in Redshift, Columns. user_id - id of the user; username - user name; db_create - flag indicating if user can create new databases; is_superuser - flag if  Lists the columns in the USERS table of the TICKIT sample database. AWS Documentation Amazon Redshift Database Developer Guide The user's 14-character phone

ALTER USER, This tutorial will show you an easy way to see what permissions have been granted to users in your database. Access Types. Amazon Redshift allows many types  Step 2: Create a database user By default, only the master user that you created when you launched the cluster has access to the initial database in the cluster. To grant other users access, you must create one or more user accounts.

How to View Permissions in Amazon Redshift, IAM users with the proper permissions can use the AWS Management Console, AWS Command Line Interface (CLI), or Amazon Redshift Application  Amazon Redshift supports creating user defined functions. You can create custom user-defined functions (UDF) using either SQL SELECT statements or Python program. You can create custom user-defined functions (UDF) using either SQL SELECT statements or Python program.

Comments
  • The username cannot be in single quotes (double quotes or no quotes is ok).
  • Weird.. I get ERROR: syntax error at or near "'someusername'" with LINE 1: revoke all privileges on database mydatabase from 'someusername'; with a caret pointing to the opening single quote. Without the single quotes or with a double quotes it works ok. Logged in to Redshift CLI with psql on macOS High Sierra (if that makes any difference in this context).