如何以正确的方式在Postgres中的函数上使用默认模式特权? [英] How to use default schema privileges on functions in Postgres in right way?

问题描述

我正在努力理解Postgres中默认模式特权的工作方式.对我来说，它们应该通过自动发布权限来减轻管理负担，但是我发现它们有点用处不大.我从文档中发现了一些根本不明显的东西.

I am struggling to comprehend how default schema privileges work in Postgres. To me, they are something that supposed to ease administration load by issuing permissions automatically, but I found them bit unusable. I discovered several things that are not at all obvious from documentation.

我希望多个用户能够在架构中创建和修改对象.我创建了一个将成为所有者的角色，并将此角色授予多个(通常)用户:

I want several users to be able to create and modify objects in schema. I create a role who gonna be the owner and grant this role to multiple (in general) users:

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;

请注意，我是使用我自己的(管理)帐户进行的.

Please note that I do this under my own (administration) account.

接下来，我检查我得到了什么.我使用此视图:

Next, I check what I got. I use this view:

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

并请求创建对象的权限:

and request permissions for created objects:

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>

a)我们看到它已在func1上授予PUBLIC执行.可以，文档说它是默认设置. b)它向我授予了执行许可.可以，但是由于我已经是所有者，所以似乎很多余. c)按照我的要求，将执行授予my_role.完美.

a) We see it granted execute on func1 to PUBLIC. It's OK, documentation says it's by default. b) It granted execute permission to me. It's OK, but it seems redundant since I am already the owner. c) It granted execute to my_role as I asked. Perfect.

现在我假装我是被授予所有权的用户:

Now I pretend that I am a user to whom ownership was granted:

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

d)为什么不授予PUBLIC执行权限?

d) Why did not it granted execute to PUBLIC?

e)为什么没有应用默认特权?

e) Why the hell it did not apply default privileges?

我试图弄清楚发生了什么事

I try to figure out what's going on:

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>

嗯...我看到这里提到的授予者...这可能很重要吗?让我们在用户下设置默认值:

Hmmm... I see the grantor mentioned here... May be this is important? Let's set up defaults under my user:

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

现在它可以按预期工作了.

Now it worked as expected.

好吧，可能是它通过授予的角色继承了默认特权吗?

OK, may be it inherits default privileges through granted roles?

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

该死，不是！

得出结论:默认特权仅在创建用户(明确)设置了默认用户的用户时起作用，不在授予了角色且具有以下权限的用户下不起作用设置默认权限.

To conclude: default privileges work only when creating objects under the user (explicit) who set the default privileges and does not work under users that were granted with role who set default privileges.

现在的问题:

1. 在我找不到的文档中某个地方提到了以上事实吗?

1. Is the fact above is mentioned in some place in documentation which I failed to find?

为什么这么不方便?我可能会滥用它吗?有没有办法在架构中设置默认特权，该特权将对具有某些授予角色的每个用户都起作用?对于所有(现有的和将来的)用户?

Why is it so inconvenient? May be I misuse it? Is there way to set default privileges in schema that would work for every user with some granted role? For all (existing and future) users?

PUBLIC的情况完全不清楚.为什么没有在d)中将EXECUTE授予PUBLIC?我进行了几次实验，发现如果用户为架构设置了任何个默认授权，则EXECUTE for PUBLIC会为他们增强.但是，如果没有默认特权，则不会向函数的PUBLIC授予任何EXECUTE.在我看来，这完全是不合逻辑的.对此有解释吗?

It is completely unclear situation with PUBLIC. Why did not it grant EXECUTE to PUBLIC in d)? I conducted few more experiments and discovered that if a user have any default grants set for a schema, they get augmented by EXECUTE for PUBLIC. But if there are no default privileges no EXECUTEs granted to PUBLIC on functions. It looks completely illogical to me. Is there an explanation for this?

推荐答案

我将尝试回答最后提出的问题:

I'll try to answer the questions raised towards the end:

1. 文档说:

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

target_role

当前角色为其成员的现有角色的名称.如果省略FOR ROLE，则假定为当前角色.

The name of an existing role of which the current role is a member. If FOR ROLE is omitted, the current role is assumed.

您始终为某个角色定义默认权限 ，即，特权仅在该角色创建对象时适用.

You always define default privileges for a certain role, that is, the privileges only apply when that role creates an object.

就是这样.最好的办法是只允许一个角色在架构中创建对象.

That's the way it is. The best thing is to have only a single role that is allowed to create objects in a schema.

任何授予的特权都会添加到现有特权中.

Any granted privileges are added to the existing privileges.

所有功能都是使用PUBLIC的EXECUTE特权创建的，我不相信d)中的结果.为此，您必须提出一个简单的可复制测试用例.

All functions are created with EXECUTE privileges for PUBLIC, and I don't believe your result in d). You'll have to come up with a simple reproducible test case for that.

更改的唯一方法是拥有撤销 EXECUTE特权的默认特权(不限于架构！).

The only way to change that is to have a default privilege (not restricted to a schema!) that revokes the EXECUTE privilege.

这篇关于如何以正确的方式在Postgres中的函数上使用默认模式特权?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！