How to use default schema privileges on functions

Question

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) 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) Why did not it granted execute to PUBLIC?

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;

Let's verify it:

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>

Correct. And now:

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

Damn, it does not!

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.

Now questions:

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

2. 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?

3. 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?

Answer 1

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

1. The documentation says:

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

   target_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.

2. 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.

3. Any granted privileges are added to the existing privileges.

   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.

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