0

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?

2
  • You seem to be going at it from a bizarre direction. If myuser is meant to be restricted, why do you make it the owner of the database and schema objects in the first place? You should probably use a separate administrative role to create objects, then only grant specific privileges to myuser. – mustaccio Mar 31 '20 at 16:27
  • I wondering what privileges have to be set specifically, as I get permission denied for schema myschema for a distinct role, despite USAGE rights have been granted. – laserbrain Mar 31 '20 at 16:31
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.