我从 Amazon Redshift 数据库中删除了一个用户,但该用户仍出现在 pg_class 表中

上次更新时间:2020 年 1 月 9 日

为什么已删除的 Amazon Redshift 用户仍显示在 pg_class 表中,而不是 pg_user 表中?

简短描述

当被删除的用户在集群中的另一个数据库中拥有对象时,已删除的用户仍可以显示在 pg_class 表中。DROP USER 命令只检查当前数据库中是否存在对象,这些对象被将要删除的用户所拥有。如果用户在另一个数据库中拥有对象,则不会显示任何错误信息。相反,删除的用户保留对象的所有权,usesysid 仍显示在 pg_class 表中。

解决方法

对集群中的每个数据库运行以下命令。此命令检查是否存在对象,这些对象被已删除的用户所拥有。

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

如果已删除的用户仍拥有数据库中的对象,您将获得与以下内容类似的输出。tableowner 为“未知”,因为拥有者已从 pg_user 表删除,表格中存储了 usenames

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)

使用 ALTER TABLE 命令转移对象的所有权。在以下示例中,新拥有者为 userc

alter table demo_local.orders_nocomp_2 owner to userc;

预防措施

删除用户之前,请运行以下查询来查找其拥有的对象。(您也可以使用 v_find_dropuser_objs 视图执行此操作。) 在以下示例中,用户为 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'

此查询可能找不到拥有使用以下类似命令创建的架构的用户。在本示例中,labuser 是架构拥有者。

create schema demo_local authorization labuser;

除非将架构传输到新的拥有者,否则不能删除具有架构级授权的用户。以下是如何将所有权转移到 userc 的用户:

alter table demo_local.orders_nocomp_2 owner to userc;