PostgreSQL 11: How to restrict privileges to stored functions? 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=&gt; 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?
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.