无法删除被授予连接数据库的角色

我正在使用 PostgreSQL 10.4，我发现了一个奇怪的行为。

如果我们创建一个角色并将其授予CONNECT数据库：

CREATE ROLE dummy;
GRANT CONNECT ON DATABASE test TO dummy;

那么我们就不能删除这个角色，即使它根本不拥有任何对象，这个命令：

DROP ROLE dummy;

Raises:

ERROR: role "dummy" cannot be dropped because some objects depend on it
SQL state: 2BP01
Detail: privileges for database test

文档有点误导：

2B 类 — 依赖权限描述符仍然存在

2B000 dependent_privilege_descriptors_still_exist

2BP01 dependent_objects_still_exist

It says 依赖对象仍然存在，但似乎没有任何对象依赖于这个特定角色，它在数据库上不拥有任何东西。

无论如何，如果我们撤销CONNECT特权，然后可以删除角色：

REVOKE CONNECT ON DATABASE test FROM dummy;
DROP ROLE dummy;

我刚刚检查了 PostgreSQL 9.5 上也存在这种行为。我觉得有点奇怪，我不明白为什么这个特定的特权会导致删除角色失败。

额外的观察结果

这确实是阻塞的，因为我们无法重新分配这个对象：

REASSIGN OWNED BY dummy TO postgres;

也不丢弃对象：

DROP OWNED BY dummy;

两者都会引发相关错误：

错误：重新分配对象的权限被拒绝
SQL状态：42501

错误：删除对象的权限被拒绝
SQL状态：42501

正如@RaymondNijland 指出的，这一定是因为CONNECT特权被视为角色相关的对象。以下查询：

WITH
R AS (SELECT * FROM pg_roles WHERE rolname = 'dummy')
SELECT
    D.*
FROM
    R, pg_shdepend AS D
WHERE
    refobjid = R.oid;

当以下情况时返回单行CONNECT被授予：

"dbid";"classid";"objid";"objsubid";"refclassid";"refobjid";"deptype"
0;1262;27961;0;1260;27966;"a"

当特权被撤销时，根本没有任何行。这至少解释了为什么我们不能重新分配对象。

关于依赖类型，文档 states:

SHARED_DEPENDENCY_ACL (a)

引用的对象（必须是角色）在 依赖的ACL（访问控制列表，即权限列表） 目的。 (A SHARED_DEPENDENCY_ACL条目不是为所有者创建的 对象，因为所有者将拥有一个SHARED_DEPENDENCY_OWNER入口 反正。）

但我没有足够的洞察力来清楚地理解它。

我的问题是：

• Postgres 是否总是要求在删除角色之前撤销权限？
• 如果不是，为什么这个特定特权会这样？

使用时有一些非常不直观的权限要求REASSIGN.

我发现，当超级用户帐户不可用时（如 RDS 或 Cloud SQL 的情况），我必须将目标角色授予我的当前角色，以便从目标角色重新分配或删除拥有的对象。例如，如果我的活跃用户是postsgres，我正在尝试删除user_a:

> DROP OWNED BY user_a
ERROR:  permission denied to drop objects
> GRANT user_a TO postgres;
GRANT ROLE
> DROP OWNED BY user_a;
DROP OWNED

现在，如果user_a恰好是以下组织的成员postgres，特别是如果它碰巧通过其他角色继承了该成员资格，我们称之为schema_admin...

> DROP OWNED BY user_a
ERROR:  permission denied to drop objects
> GRANT user_a TO postgres;
ERROR:  role "user_a" is a member of role "postgres"

-- Alright, let's try to revoke it...
> REVOKE postgres FROM user_a;
REVOKE ROLE
> GRANT user_a TO postgres;
ERROR:  role "user_a" is a member of role "postgres"

-- It's still a member through the inherited grant - trying to revoke again doesn't work:
> REVOKE postgres FROM user_a;
WARNING:  role "user_a" is not a member of role "postgres"
REVOKE ROLE

-- So you have to identify the role it's inheriting from, and revoke that:
> REVOKE schema_admin FROM user_a;
REVOKE ROLE
> GRANT user_a TO postgres;
GRANT ROLE

-- Now just to be safe, I'll reassign owned objects before actually dropping everything:
> REASSIGN OWNED BY user_a TO postgres;
REASSIGN OWNED
> DROP OWNED BY user_a;
DROP OWNED
> DROP ROLE user_a;
DROP ROLE;

Voila!

注意：这里还有另一个被广泛引用且有效的答案：https://sysadmintips.com/services/databases/postgresql-error-permission-denied-to-reassign-objects/只要您能够创建并以新的临时用户身份登录，这效果就很好。但是，在某些情况下，这本身就是一个问题（然后您还需要进行额外的清理工作，以便在完成后删除该临时角色），所以我在这里尝试避免这种情况。