Невозможно удалить роль, предоставленную для подключения к базе данных.

Question

Я использую PostgreSQL 10.4 и обнаружил странное поведение.

Если мы создадим роль и предоставим ее CONNECT база данных:

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

Тогда мы не можем отбросить эту роль, даже если она вообще не имеет объекта, этой командой:

DROP ROLE dummy;

поднимает:

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

Документация немного вводит в заблуждение:

Класс 2B - зависимые дескрипторы привилегий все еще существуют

2B000 independent_privilege_descriptors_still_exist

2BP01pendent_objects_still_exist

Он говорит, что зависимые объекты все еще существуют, но кажется, что нет объектов, зависящих от этой конкретной роли, ему ничего не принадлежит в базе данных.

Во всяком случае, если мы отзовем CONNECT привилегия, тогда роль может быть отброшена:

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

Я только что проверил поведение, также существует в PostgreSQL 9.5. Мне это немного странно, и я не могу понять, почему эта конкретная привилегия делает сбрасывание роли неудачной.

Дополнительные наблюдения

Это действительно блокирует, потому что мы не можем переназначить этот объект:

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 привилегии рассматриваются как объект, зависящий от роли. Следующий запрос:

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"

И никакой строки вообще, когда привилегия отозвана. Это, по крайней мере, объясняет, почему мы не можем переназначить объект.

О типе зависимости в документации говорится:

SHARED_DEPENDENCY_ACL (А)

Указанный объект (который должен быть ролью) упоминается в ACL (список управления доступом, то есть список привилегий) зависимого объекта. (A SHARED_DEPENDENCY_ACL запись не сделана для владельца объекта, так как у владельца будет SHARED_DEPENDENCY_OWNER вход в любом случае.)

Но у меня недостаточно проницательности, чтобы понять это ясно.

Мой вопрос:

• Всегда ли Postgres требует отменить привилегии, прежде чем отказаться от роли?
• Если нет, то почему эта конкретная привилегия ведет себя так?

Answer 1

Есть некоторые очень неинтуитивные требования к разрешениям при использовании 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 "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;

Вуаля!

Примечание. Здесь есть еще один часто упоминаемый и эффективный ответ: https://sysadmintips.com/services/databases/postgresql-error-permission-denied-to-reassign-objects/, который отлично работает, если вы можете создать и войти в систему как новый временный пользователь. Однако в некоторых случаях это проблема сама по себе (и тогда у вас также есть дополнительная очистка для удаления этой временной роли, когда вы закончите), поэтому я попытался избежать этого здесь.