主页 / user-230770

Daniel Huesca's questions

Martin Hope
Daniel Huesca
Asked: 2021-05-29 01:32:05 +0800 CST
  • 0

我刚刚继承了一个 AWS RDS PostgreSQL 12.5 实例（因此，没有超级用户访问权限），我需要进行一些清理并删除一堆组/角色/用户。我正在使用 AWS 提供的用户“master”在唯一可用的数据库上执行以下命令（除了postgres db）

尝试时：

DROP ROLE xxx

我得到：

错误：无法删除角色“xxx”，因为某些对象依赖于它
详细信息：数据库 xxx 中有 N 个对象

经过大量谷歌搜索后，我最终得到：

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON SCHEMA some_schema FROM "xxx";
REVOKE USAGE ON SCHEMA some_schema FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON SEQUENCES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON TABLES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON FUNCTIONS FROM "xxx";
REVOKE ALL PRIVILEGES ON DATABASE xxx FROM "xxx";

但是， DROP 仍然失败：

错误：无法删除角色“xxx”，因为某些对象依赖于它详细信息：在架构 some_schema SQL 状态
中属于角色 master 的新类型的默认权限的权限：2BP01

尝试使用（取自一些stackoverflow问题）检查依赖关系，但查询一无所获：

SELECT *
FROM
    --r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
    (SELECT N.NSPNAME AS SCHEMA_NAME,
            C.RELNAME AS REL_NAME,
            C.RELKIND AS REL_KIND,
            PG_GET_USERBYID(C.RELOWNER) AS OWNER_NAME
        FROM PG_CLASS C
        JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
    UNION ALL
    -- functions (or procedures)
    SELECT N.NSPNAME AS SCHEMA_NAME,
            P.PRONAME,
            'p',
            PG_GET_USERBYID(P.PROOWNER)
        FROM PG_PROC P
        JOIN PG_NAMESPACE N ON N.OID = P.PRONAMESPACE) AS FOO
WHERE OWNER_NAME = 'xxx'

然后尝试使用（再次出现空）检查权限：

SELECT
    grantee AS user,
    CONCAT(table_schema, '.', table_name) AS table, 
    CASE 
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee
HAVING grantee = 'xxx'

这是我第一次使用 PostgreSQL，来自 SQL Server。

postgresql users