如何在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;