无法删除被授予连接数据库的角色 [英] 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

2B000dependent_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;

两者都会引发相关错误:

Both raise related errors:

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 被授予时返回单行:

Returns a single row when CONNECT is granted:

"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(访问控制列表，即权限列表)目的.(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 "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;

瞧！

注意:这里还有另一个被广泛引用且有效的答案:https://sysadmintips.com/services/databases/postgresql-error-permission-denied-to-reassign-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屋！