文档

RDS PostgreSQL删除账号失败

更新时间:

问题描述

在RDS PostgreSQL控制台删除账号时,出现错误提示。其中:

  • 报错信息:数据库对象依赖该操作的账号,请先解除依赖后,再操作。

  • 错误码:AccountActionForbidden

问题原因

在RDS PostgreSQL中,如果存在数据库对象依赖于即将被删除的账号,将导致删除操作失败。例如,存在user_to_be_dropped账号,并使用该账号创建了对象。示例SQL如下:

  1. 使用高权限账号,创建测试数据库testdb01

    CREATE DATABASE testdb01;
  2. 使用高权限账号,登录testdb01,创建测试账号user_to_be_dropped,并赋予测试账号对应的权限。

    CREATE USER user_to_be_dropped WITH PASSWORD 'your_password' CREATEROLE CREATEDB;
    GRANT ALL ON SCHEMA public TO user_to_be_dropped;
    GRANT ALL ON DATABASE testdb01 TO user_to_be_dropped;
  3. 使用账号user_to_be_dropped登录testdb01,创建对象和测试数据库testdb02

    CREATE SCHEMA test_nsp;
    CREATE TABLE test_nsp.test_tbl(a1 int);
    CREATE TABLE test_tbl(a2 int);
    CREATE DATABASE testdb02;
  4. 使用账号user_to_be_dropped登录testdb02,创建对象和新测试账号user_to_be_dropped_2,并给其授予高权限账号(本文以testdbuser为例)的权限。

    CREATE SCHEMA testnsp;
    CREATE TABLE testnsp.tbl(a3 int);
    CREATE USER user_to_be_dropped_2 WITH PASSWORD 'your_password';
    GRANT user_to_be_dropped_2 TO testdbuser;

删除账号user_to_be_dropped时,由于存在依赖对象,所以删除操作会出现报错。

  • RDS PostgreSQL控制台:

    image

  • SQL命令:

    DROP USER user_to_be_dropped;
    ERROR:  role "user_to_be_dropped" cannot be dropped because some objects depend on it
    DETAIL:  privileges for schema public
    privileges for database testdb01
    owner of schema test_nsp
    owner of table test_nsp.test_tbl
    owner of table test_tbl
    owner of database testdb02
    privileges for membership of role testdbuser in role user_to_be_dropped_2
    2 objects in database testdb02
    

解决方案

查找依赖目标账号的对象,并将其删除。

查找依赖对象

系统表pg_shdepend中记录了单个数据库内部的对象对全局对象的依赖,可以用于查询哪些对象依赖了目标账号。例如,查询哪些对象依赖账号 user_to_be_dropped

  1. 使用高权限账号登录数据库实例,查询与账号 user_to_be_dropped相关联的对象。

    WITH role as (SELECT oid FROM pg_roles WHERE rolname = 'user_to_be_dropped')
    SELECT
        db.datname AS database,
        pg_class.relname AS classname,
        shp.objid AS oid,
        CASE
            WHEN shp.deptype = 'o' THEN 'Object Owner'
            WHEN shp.deptype = 'a' THEN 'In Access Control List'
            WHEN shp.deptype = 'r' THEN 'Policy Object'
        ELSE
            'CANNOT HAPPEN'  
        END
    FROM
        pg_shdepend shp
            LEFT JOIN pg_database db ON shp.dbid = db.oid
            JOIN pg_class ON shp.classid = pg_class.oid
    WHERE
        shp.refclassid = 1260 AND shp.refobjid IN (SELECT oid FROM role);

    查询结果为:

     database |    classname    |  oid  |          case
    ----------+-----------------+-------+------------------------
     testdb01 | pg_namespace    |  2200 | In Access Control List
              | pg_database     | 16399 | In Access Control List
     testdb01 | pg_namespace    | 16402 | Object Owner
     testdb01 | pg_class        | 16403 | Object Owner
     testdb01 | pg_class        | 16406 | Object Owner
              | pg_database     | 16409 | Object Owner
     testdb02 | pg_namespace    | 16410 | Object Owner
     testdb02 | pg_class        | 16411 | Object Owner
              | pg_auth_members | 16416 | In Access Control List

    其中各个字段的含义如下。

    字段

    含义

    database

    指明依赖对象所在的库,为空时表示全局对象的依赖。

    classname

    指明系统表名称。

    oid

    指明依赖对象的oid。

    case

    指明依赖类型,常见的类型包括:

    • Owner:被删除账号是该对象的所有者。

    • ACL(Access Control List):被删除账号在Access Control List中。

  2. 使用高权限账号登录到对应数据库中,在对应的系统表中查询上一步获得的oid所对应的对象名称,例如。

    • 全局对象的依赖

      database字段为空,表示全局对象的依赖。

      • oid=16399testdb01的ACL中有user_to_be_dropped的相关条目。

        SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16399;
         datname  |   datdba   |                                    datacl
        ----------+------------+------------------------------------------------------------------------------
         testdb01 | testdbuser | {=Tc/testdbuser,testdbuser=CTc/testdbuser,user_to_be_dropped=CTc/testdbuser}
        (1 row)
      • oid=16409testdb02的所有者为user_to_be_dropped

        SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16409;
         datname  |       datdba       | datacl
        ----------+--------------------+--------
         testdb02 | user_to_be_dropped |
        (1 row)
      • oid=16416pg_auth_members中的角色从属关系,记录了授予user_to_be_dropped账号的相应权限。

        SELECT oid, roleid::regrole, member::regrole, grantor::regrole FROM pg_auth_members WHERE oid = 16416;
          oid  |        roleid        |   member   |      grantor
        -------+----------------------+------------+--------------------
         16416 | user_to_be_dropped_2 | testdbuser | user_to_be_dropped
        (1 row)
    • 单库内对象的依赖

      database字段不为空,表示该库下存在依赖对象。

      • oid=2200testdb01public schema的ACL中有user_to_be_dropped的相关条目。

        SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 2200;
         nspname |  nspowner  |                                  nspacl
        ---------+------------+---------------------------------------------------------------------------
         public  | testdbuser | {testdbuser=UC/testdbuser,=U/testdbuser,user_to_be_dropped=UC/testdbuser}
        (1 row)
      • oid=16403testdb01下的表test_nsp.test_tbl的所有者为user_to_be_dropped

        SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16403;
         relname  | relnamespace |      relowner      | relacl
        ----------+--------------+--------------------+--------
         test_tbl | test_nsp     | user_to_be_dropped |
        (1 row)

处理依赖对象

您可以根据实际需求:

  • 如果是ACL类型的依赖,可以收回对应的权限。

  • 如果是Owner类型的依赖,可以将其所有权转移给其他账号,也可以删除对应的依赖。

例如:

  • ACL类型的依赖

    • oid=2200,收回testdb01public schema的权限。

      SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 2200;
       nspname |  nspowner  |                                  nspacl
      ---------+------------+---------------------------------------------------------------------------
       public  | testdbuser | {testdbuser=UC/testdbuser,=U/testdbuser,user_to_be_dropped=UC/testdbuser}
      (1 row)
      
      REVOKE ALL ON SCHEMA public FROM user_to_be_dropped;
    • oid=16399,收回testdb01的权限。

      SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16399;
       datname  |   datdba   |                                    datacl
      ----------+------------+------------------------------------------------------------------------------
       testdb01 | testdbuser | {=Tc/testdbuser,testdbuser=CTc/testdbuser,user_to_be_dropped=CTc/testdbuser}
      (1 row)
      
      REVOKE ALL ON DATABASE testdb01 from user_to_be_dropped;
    • oid=16416,对于包含角色从属关系的情况,RDS PostgreSQL 16 及以上版本中,需要切换到user_to_be_dropped账号,删除 user_to_be_dropped_2的高权限账号(本文以testdbuser为例)的权限。

      \c testdb01 user_to_be_dropped;
      You are now connected to database "testdb01" as user "user_to_be_dropped".
      
      REVOKE user_to_be_dropped_2 FROM testdbuser cascade;
  • Owner类型依赖所有权转移给其他账号。

    使用高权限账单登录到对应数据库。

    • 将对象的所有权转移给其他账号。

      oid=16403,将testdb01下的表test_nsp.test_tbl的所有者权限转移给其他账号(本文以testdbuser为例)。

      SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16403;
       relname  | relnamespace |      relowner      | relacl
      ----------+--------------+--------------------+--------
       test_tbl | test_nsp     | user_to_be_dropped |
      (1 row)
      
      ALTER TABLE test_nsp.test_tbl OWNER TO testdbuser;
    • 删除依赖对象。

      • 删除测试数据库testdb02。

        DROP DATABASE testdb02;
      • 删除oid=16402的对象。

        SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 16402;
         nspname  |      nspowner      | nspacl
        ----------+--------------------+--------
         test_nsp | user_to_be_dropped |
        (1 row)
        
        DROP SCHEMA test_nsp cascade;
      • 删除oid=16406的对象。

        SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16406;
         relname  | relnamespace |      relowner      | relacl
        ----------+--------------+--------------------+--------
         test_tbl | public       | user_to_be_dropped |
        (1 row)
        DROP TABLE public.test_tbl;

使用高权限账号登录数据库实例,查询与账号 user_to_be_dropped相关联的对象。

返回结果:

 database | classname | oid | case
----------+-----------+-----+------
(0 rows)

删除目标账号

在RDS PostgreSQL控制台或通过SQL命令删除目标账号。SQL示例如下。

DROP USER user_to_be_dropped;