I dropped a user from an Amazon Redshift database, but the user still appears in the pg_class table

3 minute read
0

Why do deleted Amazon Redshift users still appear in the pg_class table but not the pg_user table?

Short description

Deleted users can still appear in the pg_class table when the dropped user owns an object in another database in the cluster. The DROP USER command only checks the current database for objects that are owned by the user who is about to be dropped. If the user owns an object in another database, then no errors are thrown. Instead, the dropped user retains ownership of the object and the usesysid still appears in the pg_class table.

Resolution

Run the following command on each database in your cluster. This command checks for objects that are owned by deleted users.

select distinct schemaname, tablename, tableowner from pg_tables where tableowner like '%unknown%';

If a deleted user still owns objects in a database, you get an output similar to the following. The tableowner is "unknown" because the owner was deleted from the pg_user table, where usenames are stored.

demo_localdb=# select distinct schemaname, tablename, tableowner from pg_tables where tableowner like '%unknown%'; 
 schemaname |    tablename    |    tableowner     
------------+-----------------+-------------------
 demo_local | orders_nocomp_2 | unknown (UID=114)
 demo_local | orders_nocomp_3 | unknown (UID=115)
(2 rows)

Use the ALTER TABLE command to transfer ownership of the objects. In the following example, the new owner is userc.

alter table demo_local.orders_nocomp_2 owner to userc;

Prevention

Before dropping a user, run the following query to find objects that they own. (You can also use the v_find_dropuser_objs view to do this.) In the following example, the user is labuser.

SELECT decode(pgc.relkind,
             'r','Table',
             'v','View'
       ) ,
       pgu.usename,
       pgu.usesysid,
       nc.nspname,
       pgc.relname,
       'alter table ' || QUOTE_IDENT(nc.nspname) || '.' || QUOTE_IDENT(pgc.relname) || ' owner to '
FROM pg_class pgc,
     pg_user pgu,
     pg_namespace nc
WHERE pgc.relnamespace = nc.oid
AND   pgc.relkind IN ('r','v')
AND   pgu.usesysid = pgc.relowner
AND nc.nspname NOT ILIKE 'pg\_temp\_%'
AND pgu.usename = 'labuser'

This query might not find users who own schemas that were created with a command similar to the following. In this example, labuser is the schema owner.

create schema demo_local authorization labuser;

Users with schema-level authorizations can't be dropped unless the schema is transferred to a new owner. Here's an example of how to transfer ownership to userc:

alter table demo_local.orders_nocomp_2 owner to userc;

Related information

How do I resolve the "user cannot be dropped" error in Amazon Redshift?

Querying the catalog tables

View database users

AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago