Как правильно использовать привилегии схемы по умолчанию для функций в Postgres?

Question

Я пытаюсь понять, как привилегии схемы по умолчанию работают в Postgres. Для меня это то, что должно облегчить административную нагрузку, автоматически выдавая разрешения, но я нашел их немного непригодными. Я обнаружил несколько вещей, которые совсем не очевидны из документации.

Я хочу, чтобы несколько пользователей могли создавать и изменять объекты в схеме. Я создаю роль, которая будет владельцем, и предоставляю эту роль нескольким (в общем) пользователям:

create schema my_schema;
create role my_schema_owner;
alter schema my_schema owner to my_schema_owner;
create user my_user password 'xxx';
grant my_schema_owner to my_user;
create role my_role;
alter default privileges in schema my_schema grant execute on functions to my_role;
create function my_schema.my_func1() returns int as
  $$ begin return 3; end; $$ language plpgsql;

Обратите внимание, что я делаю это под своей учетной записью (администрацией).

Далее я проверяю, что у меня есть. Я использую эту точку зрения:

create or replace view pg_functions_grants as
select proname, n.nspname, coalesce(nullif(s[1], ''), 'public') as grantee,
       s[2] as privileges, s[3] as grantor
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
join pg_roles r on r.oid = p.proowner
join pg_type rt on p.prorettype = rt.oid,
unnest(coalesce(p.proacl::text[], format('{%s=arwdDxt/%s}', r.rolname, r.rolname)::text[])) acl,
regexp_split_to_array(acl, '=|/') s

и запросить разрешения для созданных объектов:

select * from pg_functions_grants where proname = 'my_func1' order by 1;

my_func1    my_schema   public      X       <me>
my_func1    my_schema   <me>        X       <me>
my_func1    my_schema   my_role     X       <me>

а) Мы видим, что исполнено на func1 для PUBLIC. Все в порядке, документация говорит, что это по умолчанию. б) Это дало мне разрешение на исполнение. Это нормально, но кажется излишним, так как я уже владелец. c) Он предоставил выполнение my_role, как я и просил. Отлично.

Теперь я притворяюсь, что являюсь пользователем, которому было предоставлено право собственности:

set role my_user;
create function my_schema.my_func2() returns int as
  $$ begin return 3; end; $$ language plpgsql;

select * from pg_functions_grants where proname = 'my_func2' order by 1;

my_func2    my_schema   my_user     arwdDxt my_user

г) Почему он не предоставил исполнение для общественности?

д) Какого черта он не применял привилегии по умолчанию?

Я пытаюсь выяснить, что происходит:

create or replace view pg_namespaces_default_grants as
select n.nspname, r.rolname, d.defaclobjtype, coalesce(nullif(s[1], ''), 'public') as grantee,
       s[2] as privileges, s[3] as grantor
from pg_default_acl d
join pg_namespace n on d.defaclnamespace = n.oid
join pg_roles r on r.oid = n.nspowner,
unnest(coalesce(d.defaclacl::text[], format('{%s=arwdDxt/%s}', r.rolname, r.rolname)::text[])) acl,
regexp_split_to_array(acl, '=|/') s;

select * from pg_namespaces_default_grants where nspname = 'my_schema';

my_schema   my_schema_owner f   my_role X   <me>

Хммм... Я вижу упомянутое здесь лицо, предоставляющее право... Может быть, это важно? Давайте установим значения по умолчанию под моим пользователем:

set role my_user;
alter default privileges in schema my_schema grant execute on functions to my_role;
create function my_schema.my_func3() returns int as
  $$ begin return 3; end; $$ language plpgsql;

select * from pg_functions_grants where proname = 'my_func3' order by 1;

my_func3    my_schema   public  X   my_user
my_func3    my_schema   my_user X   my_user
my_func3    my_schema   my_role X   my_user

Теперь все заработало как положено.

Хорошо, может быть он наследует привилегии по умолчанию через предоставленные роли?

set role my_schema_owner;
alter default privileges in schema my_schema grant execute on functions to my_role;
set role my_user;
alter default privileges in schema my_schema revoke execute on functions from my_role;

Давайте проверим это:

select * from pg_namespaces_default_grants where nspname = 'my_schema';

my_schema   my_schema_owner f   my_role X   my_schema_owner
my_schema   my_schema_owner f   my_role X   <me>

Правильный. И сейчас:

set role my_user;
create function my_schema.my_func7() returns int as
  $$ begin return 3; end; $$ language plpgsql;

select * from pg_functions_grants where proname = 'my_func7' order by 1;

my_func7    my_schema   my_user arwdDxt my_user

Блин, это не так!

В заключение: права по умолчанию работают только при создании объектов под пользователем (явным), который устанавливает права по умолчанию, и не работают с пользователями, которым предоставлена ​​роль, которые устанавливают права по умолчанию.

Теперь вопросы:

1. Упоминается ли вышеупомянутый факт в какой-то части документации, которую мне не удалось найти?

2. Почему это так неудобно? Может быть, я неправильно это использую? Есть ли способ установить привилегии по умолчанию в схеме, которые будут работать для каждого пользователя с определенной ролью? Для всех (существующих и будущих) пользователей?

3. Совершенно непонятная ситуация с PUBLIC. Почему он не предоставил EXECUTE PUBLIC в d)? Я провел еще несколько экспериментов и обнаружил, что, если для пользователя установлены какие-либо права по умолчанию для схемы, они дополняются EXECUTE для PUBLIC. Но если нет никаких привилегий по умолчанию, то функции EXECUTE не предоставляются PUBLIC для функций. Это выглядит совершенно нелогичным для меня. Есть ли объяснение этому?

Answer 1

Я постараюсь ответить на вопросы, поднятые к концу:

1. В документации сказано:

   ALTER DEFAULT PRIVILEGES
       [ FOR { ROLE | USER } target_role [, ...] ]
       [ IN SCHEMA schema_name [, ...] ]
       abbreviated_grant_or_revoke
   

   target_role

   Имя существующей роли, членом которой является текущая роль. Если FOR ROLE опущен, текущая роль предполагается.

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

2. Так оно и есть. Лучше всего иметь только одну роль, которая позволяет создавать объекты в схеме.

3. Любые предоставленные привилегии добавляются к существующим привилегиям.

   Все функции созданы с EXECUTE привилегии для PUBLIC и я не верю вашему результату в г). Для этого вам понадобится простой воспроизводимый контрольный пример.

   Единственный способ изменить это - иметь привилегию по умолчанию (не ограниченную схемой!), Которая аннулирует EXECUTE привилегия.