无法删除授予连接数据库的角色 [英] Cannot drop a role that is granted to connect database

问题描述

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

I am using PostgreSQL 10.4 and I found a strange behavior.

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

If we create an role and grant it to CONNECT the database:

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

然后，即使该命令根本不拥有任何对象，我们也不能删除该角色：

Then we cannot drop this role, even if it owns no object at all, this command:

DROP ROLE dummy;

涨幅：

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

文档有点误导：

2B类-依存特权描述符仍然存在

Class 2B — Dependent Privilege Descriptors Still Exist

2B000 relative_privilege_descriptors_still_exist

2B000 dependent_privilege_descriptors_still_exist

2BP01dependent_objects_still_exist

2BP01 dependent_objects_still_exist

依赖对象仍然存在，但是似乎没有对象依赖于此特定角色，它在数据库中不拥有任何内容。

It says dependent objects still exist, but it seems there are no objects dependent to this specific role, it owns nothing on the database.

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

Anyway, if we revoke the CONNECT privilege, then role can be dropped:

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

我刚刚检查了PostgreSQL 9.5上的行为。我觉得有些奇怪，我不明白为什么这种特殊的特权使放弃角色失败。

I just checked the behavior also exists on PostgreSQL 9.5. I feel it a bit strange and I cannot understand why this specific privilege makes dropping role fails.

其他观察

这实际上是阻塞的，因为我们不能重新分配该对象：

This is really blocking, because we can neither reassign this object:

REASSIGN OWNED BY dummy TO postgres;

也不要放下对象：

DROP OWNED BY dummy;

两次加薪相关的错误：

ERROR: permission denied to reassign objects
SQL state: 42501

ERROR: permission denied to drop objects
SQL state: 42501

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

As @RaymondNijland pointed out, this must be because the CONNECT privileges is viewed as a role dependent object. The following query:

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"

撤销特权后，根本没有任何行。

And no row at all when the privilege is revoked. This at least explain why we cannot reassign the object.

关于依赖类型，文档指出：

SHARED_DEPENDENCY_ACL （a）

ACL（访问权限）中提到了引用的对象（必须是角色）从属
对象的控制列表，即特权列表）。 （对于
对象的所有者，不会创建<< c $ c> SHARED_DEPENDENCY_ACL 条目，因为所有者将具有 SHARED_DEPENDENCY_OWNER 输入
。）

The referenced object (which must be a role) is mentioned in the ACL (access control list, i.e., privileges list) of the dependent object. (A SHARED_DEPENDENCY_ACL entry is not made for the owner of the object, since the owner will have a SHARED_DEPENDENCY_OWNER entry anyway.)

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

But I have not enough insight to understand it clearly.

我的问题是：

• Postgres在删除角色之前是否总是要求撤销特权？


• 如果不是，为什么此特定特权的行为如此？

推荐答案

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

There are some very unintuitive permission requirements when using REASSIGN.

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

I have found that when a superuser account is not available (as in the case of RDS or Cloud SQL) I have to grant the target role to my current role in order to reassign or drop owned objects from the target role. For instance, if my active user is postsgres, and I'm trying to remove 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 ...

Now, it becomes a little trickier if user_a happens to be a member of postgres, especially if it happens to inherit that membership through some other role, let's call it 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 "metabase" 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/数据库/ postgresql-error-permission-densed-ressign-objects / 效果很好，只要您能够创建并以新的临时用户身份登录。但是，在某些情况下，这本身就是一个问题（然后，您还需要进行额外的清理工作，以在完成后删除该临时角色），因此我在这里尝试避免这种情况。

Note: There is another widely-referenced and effective answer here: https://sysadmintips.com/services/databases/postgresql-error-permission-denied-to-reassign-objects/ which works great, as long as you are able to create and log in as a new temporary user. However, in some contexts, that is a problem in itself (and then you also have the extra cleanup to handle of removing that temporary role when you're done), so I tried to avoid that here.

这篇关于无法删除授予连接数据库的角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！