如何在Hologres中删除账号及报错如何排查并处理_实时数仓 Hologres(Hologres)-阿里云帮助中心

本文将为您介绍在Hologres中如何删除账号（drop user），以及删除账号时报错如何排查并处理。

背景信息

在日常业务使用中，会有需要删除某个账号的场景。在Hologres中当需要删除的账号是DB对象（数据库、Schema、表、视图等）的所有者时，通常执行删除操作时会报错，常见报错如下。

账号有所属的对象，无法删除报错如下。

ERROR:  role "<uid>" cannot be dropped because some objects depend on it
DETAIL:  owner of table xxx
owner of schema yyy

账号有对象依赖，无法删除报错如下。

ERROR:  role "<uid>" cannot be dropped because some objects depend on it
DETAIL:  1 object in database xxx

账号上有被赋予的权限，无法删除报错如下。

ERROR: role "<uid>" cannot be dropped because some objects depend on it
Detail: privileges for table xxx
privileges for table yyy

删除账号报错，都是因为被删除的账号在实例内还有对象（DB、Schema、View、Table）的依赖或者权限，所以不能直接删除。

明确删除账号的原因

在删除账号之前，需要明确为什么需要删除账号，常见原因如下。

原因一：账号授权有误，想要删除账号，重新授权。
原因二：该账号的所有者离职了或者账号不再使用等业务种种原因确实需要删除。

建议解决方法。

删除账号原因为原因一。
此情形不需要删除账号，建议将账号当前的权限取消，再重新授权。
- 专家权限模型撤销权限并重新授权，详情请参见专家权限模型。
- 简单权限模型撤销授权并重新授权，详情请参见简单权限模型的使用和基于Schema级别的简单权限模型的使用。
- 当前账号被误设置成了Normal，想要改成Superuser，执行以下语句进行修改。
```
-- 若是子账号，需要将uid改成p4_id
alter user "<uid>" superuser;
```
  uid为账号ID，详情请参见账号ID。
- 当前账号被误设置成了Superuser，想要改成普通用户，执行以下语句进行修改。
  说明
  将Superuser改成普通用户后，用户将会没有任何权限，需要重新给用户授权。
```
-- 若是子账号，需要将uid改成p4_id
alter user "<uid>" nosuperuser;
```
  uid为账号ID，详情请参见账号ID
删除账号原因为原因二。
保留账号拥有的对象，将对象的所有者转移给其他用户，再删除该账号，详情请参见删除账号但是保留账号拥有的对象。

删除账号但是保留账号拥有的对象

当确定要删除账号，又要保留账号拥有的对象（包括表、View、函数）等时，可以将账号拥有的对象转移给另一个用户，再将账号删除，命令语法如下。

将对象的所有者转移给另一用户。
```
REASSIGN OWNED BY "<uid>" TO "<Another_uid>" ;
```
uid为账号ID，详情请参见账号ID。
删除账号。
```
DROP USER "<uid>"; 
```

查看账号依赖

通过以下语句可以查看账号的依赖对象。

select 'select * from ' || s.classid::regclass || ' where oid = ' || s.objid || '; (在 '
 || d.datname || ' DB中执行)' as "查询依赖的对象", case when deptype = 'a' then '权限依赖' 
when deptype = 'o' then 'Owner依赖' else deptype::text end as "依赖类型"from pg_shdepend s 
join pg_database d on (s.dbid = d.oid) join pg_roles r on (r.oid = s.refobjid) where 
datname = current_database() and refclassid = 1260 and r.rolname = '<用户名>';

查看所有表、视图、外表的Owner。

SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 't' THEN
        'TOAST table'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner",
    pg_catalog.obj_description(c.oid,'pg_class') AS "Description"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r', 'p', 't', 'v', 'm', 'S', 's', 'f', '')
    AND pg_catalog.pg_table_is_visible(c.oid);

查看某用户为Owner的表、视图、外表。

SELECT
    n.nspname AS "Schema",
    c.relname AS "Name",
    CASE c.relkind
    WHEN 'r' THEN
        'table'
    WHEN 'v' THEN
        'view'
    WHEN 'm' THEN
        'materialized view'
    WHEN 'i' THEN
        'index'
    WHEN 'S' THEN
        'sequence'
    WHEN 's' THEN
        'special'
    WHEN 't' THEN
        'TOAST table'
    WHEN 'f' THEN
        'foreign table'
    WHEN 'p' THEN
        'partitioned table'
    WHEN 'I' THEN
        'partitioned index'
    END AS "Type",
    pg_catalog.pg_get_userbyid(c.relowner) AS "Owner",
    pg_catalog.obj_description(c.oid, 'pg_class') AS "Description"
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relkind IN ('r', 'p', 't', 'v', 'm', 'S', 's', 'f', '')
    AND pg_catalog.pg_table_is_visible(c.oid)
    AND pg_catalog.pg_get_userbyid(c.relowner) ='<user_name>';

更改Owner。

-- 更改表的Owner
ALTER TABLE schema_name.table_name OWNER TO new_owner;

-- 更改外表的Owner
ALTER FOREIGN TABLE schema_name.foreign_table_name OWNER TO new_owner;

-- 更改视图的Owner
ALTER VIEW schema_name.view_name OWNER TO new_owner;

查看SCHEMA的Owner

查看所有SCHEMA的Owner。

SELECT
    n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
    pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
    pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM
    pg_catalog.pg_namespace n
ORDER BY
    1;

查看某用户为Owner的SCHEMA。

SELECT
    n.nspname AS "Name",
    pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
    pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
    pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM
    pg_catalog.pg_namespace n
WHERE pg_catalog.pg_get_userbyid(n.nspowner) ='<user_name>';

更改Owner。

-- 更改schema的Owner
ALTER SCHEMA schema_name OWNER TO new_owner;

查看Server的Owner。

查看所有SERVER的Owner。

SELECT
    s.srvname AS "Name",
    pg_catalog.pg_get_userbyid(s.srvowner) AS "Owner",
    f.fdwname AS "Foreign-data wrapper",
    pg_catalog.array_to_string(s.srvacl, E'\n') AS "Access privileges",
    s.srvtype AS "Type",
    s.srvversion AS "Version",
    CASE WHEN srvoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')'
    END AS "FDW options",
    d.description AS "Description"
FROM
    pg_catalog.pg_foreign_server s
    JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid = s.srvfdw
    LEFT JOIN pg_catalog.pg_description d ON d.classoid = s.tableoid
        AND d.objoid = s.oid
        AND d.objsubid = 0;

查看某用户为Owner的SERVER。

SELECT
    s.srvname AS "Name",
    pg_catalog.pg_get_userbyid(s.srvowner) AS "Owner",
    f.fdwname AS "Foreign-data wrapper",
    pg_catalog.array_to_string(s.srvacl, E'\n') AS "Access privileges",
    s.srvtype AS "Type",
    s.srvversion AS "Version",
    CASE WHEN srvoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(srvoptions)), ', ') || ')'
    END AS "FDW options",
    d.description AS "Description"
FROM
    pg_catalog.pg_foreign_server s
    JOIN pg_catalog.pg_foreign_data_wrapper f ON f.oid = s.srvfdw
    LEFT JOIN pg_catalog.pg_description d ON d.classoid = s.tableoid
        AND d.objoid = s.oid
        AND d.objsubid = 0
WHERE pg_catalog.pg_get_userbyid(s.srvowner) = '<user_name>';

更改Owner。

-- 更改server的Owner
ALTER SERVER server_name OWNER TO new_owner;

查看USER MAPPING的Owner。

查看所有USER MAPPING的Owner。

SELECT
    um.srvname AS "Server",
    um.usename AS "User name",
    CASE WHEN umoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(umoptions)), ', ') || ')'
    END AS "FDW options"
FROM
    pg_catalog.pg_user_mappings um;

查看某用户为Owner的USER MAPPING。

SELECT
    um.srvname AS "Server",
    um.usename AS "User name",
    CASE WHEN umoptions IS NULL THEN
        ''
    ELSE
        '(' || pg_catalog.array_to_string(ARRAY (
                SELECT
                    pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value)
                FROM pg_catalog.pg_options_to_table(umoptions)), ', ') || ')'
    END AS "FDW options"
FROM
    pg_catalog.pg_user_mappings um
WHERE um.usename = '<user_name>';

删除User Mapping。

-- 删除User Mapping
DROP USER MAPPING FOR user_name SERVER server_name;