CODEWITHSUNDEEP
postgresqldatabase-administrationsql-grantpostgresql-10
greatvovan
greatvovan

Reputation: 3177

Why Postgres does not grant EXECUTE on function to PUBLIC as documented?

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.

Upvotes: 1

Views: 4262

Answers (1)

greatvovan
greatvovan

Reputation: 3177

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.

Upvotes: 1

Related Questions