1

Documentation says:

For other types of objects, the default privileges granted to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables) privileges for databases; EXECUTE privilege for functions; and USAGE privilege for languages and data types (including domains).

I execute:

create user test_user password 'test_user';
grant create on database "NLP" to test_user;

Then I connect under this user and do:

create schema s;
create function s.f() returns void as $$begin null; end;$$ language plpgsql;

I expect that EXECUTE will be granted to PUBLIC on the function but this does not happen. Why?

Additionally I discovered a curious thing. If I alter default function privileges for the schema the mechanism starts to work.

create role test_role;

Under test_user:

alter default privileges in schema s grant execute on functions to test_role;
create function s.x() returns void as $$begin null; end;$$ language plpgsql;

Voila! In addition to EXECUTE on x() for test_role I got EXECUTE for PUBLIC!

My DB report version():

PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg14.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit

Is something wrong with my database? I tested it on another database (same version()) and got the same results.

greatvovan
  • 2,439
  • 23
  • 43
  • I cannot reproduce that. Please tell how you determine that `PUBLIC` does not have `EXECUTE` privileges on `s.f()`. – Laurenz Albe Aug 17 '18 at 10:26
  • @LaurenzAlbe originally I viewed it in reverse object definition generated by pgAdmin, and additionally I verified this using query like `pg_functions_grants` from this question: https://stackoverflow.com/questions/51850833/how-to-use-default-schema-privileges-on-functions-in-postgres-in-right-way Both methods show the same: `f()` does not have any privileges apart from owner's, and `x()` has X granted to `test_role` and PUBLIC. – greatvovan Aug 17 '18 at 10:37
  • 1
    `SELECT has_function_privilege('laurenz', 's.f()', 'EXECUTE');` gives me `TRUE`. What do you get for `SELECT dp.defaclrole::regrole, dp.defaclnamespace::regnamespace, dp.defaclobjtype, acl.grantor::regrole, acl.grantee::regrole, acl.privilege_type FROM pg_default_acl AS dp CROSS JOIN LATERAL aclexplode(dp.defaclacl) AS acl;`? – Laurenz Albe Aug 17 '18 at 10:48
  • Also, can you show the *exact* statement you used to determine that `PUBLIC` cannot `EXECUTE` and its result? – Laurenz Albe Aug 17 '18 at 10:49
  • @LaurenzAlbe `has_function_privilege('anyuser')` gives TRUE as well and actually they work under any users (tested some). Your query returns `test_user,s,f,test_user,test_role,EXECUTE`. – greatvovan Aug 17 '18 at 11:23
  • `select proacl from pg_proc where proname in ('f', 'x')` gives NULL for `f()` and `{=X/test_user,test_user=X/test_user,test_role=X/test_user}` for `x()`. Finally I got it. NULL means default for object's type, which in turn assumes EXECUTE for function. The fact that pgAdming generated explicit grant for PUBLIC in one case and nothing in another, was misleading. Closing question, thank you! – greatvovan Aug 17 '18 at 11:27
  • 1
    So it was a misunderstanding. Good. – Laurenz Albe Aug 17 '18 at 11:36

1 Answers1

1

Answering my own question. Everything works as expected, but I misinterpreted pgAdmin's indications.

select proacl from pg_proc where proname = 'f'

Gives NULL, which according to documentation:

If the “Access privileges” column is empty for a given object, it means the object has default privileges (that is, its privileges column is null). Default privileges always include all privileges for the owner, and can include some privileges for PUBLIC depending on the object type, as explained above.

And for such object type as function, it assumes EXECUTE for PUBLIC (as I posted in initial question).

Effective permissions for a user can also be conveniently tested by (thanks Laurenz):

select has_function_privilege('username', 's.f()', 'EXECUTE');

Which gave TRUE for any user.

The fact that pgAdming generated explicit grant for PUBLIC in one case and nothing in another, was misleading. Absence of grant to PUBLIC does not necessarily mean that PUBLIC does not have permissions.

greatvovan
  • 2,439
  • 23
  • 43