PostgreSQL 11: How to restrict privileges to stored functions?

Question

How can I restrict the privileges of a PostgreSQL role to access only functions already present in a schema, so no individual SELECT or INSERT queries are allowed?

I've created a new user:

$ createuser --no-superuser --createdb --no-createrole --pwprompt myuser
$ createdb --encoding UTF8 --owner myuser mydatabase

Then, I added my schema with all tables and functions for table access:

$ psql -h localhost -U myuser -d mydatabase -a -f myschema.sql

Is it sufficient do drop all privileges for role myuser and them just:

myuser=> GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema TO myuser;

Or should I create a distinct role? Can this role access a schema whom it is not the owner of?

Answer 1

If you want to use a function to give a user access to otherwise inaccessible data, you need to define it as SECURITY DEFINER and make the owner a user with the required privileges on the data.

Don't forget to

• REVOKE EXECUTE ON FUNCTION myfunc FROM PUBLIC;
  

  because by default, everybody can execute a function

• ALTER FUNCTION myfunc SET search_path = ..., pg_temp;
  

  so that the function is safe from privilege escalation attacks.