Execute Permission on Function not Enough in PostgreSQL?

Question

New to Pg from MS SQL side where to restrict access simply grant EXE permission to Functions and SPs. So created a user/role, set its search_path to a dedicated schema of a database, grant EXECUTE ON ALL FUNCTIONS IN SCHEMA myschema. Tried execute a function got

permission denied for schema myschema

Ok, grant usage on schema myschema to role. The function does a select ... from mytable so now

permission denied for table mytable

To grant SELECT on my table? Wait, purpose of this function is to restrict the role from exploring tables.

Do you expect the code inside the function to run with different permissions than those of the caller? Then you'll have to use SECURITY DEFINER. — Bergi

Laurenz Albe Laurenz Albe · Accepted Answer · 2021-02-26T02:52:09

Your situation is: User a owns a table mytable in a schema myschema. User b initially has no permissions on either. Now you want to allow b limited access to mytable. Granting SELECT on the table would be too much — you want to grant access only through a special function myfunction.

Then you need a function that does not run with the permissions of the caller (SECURITY INVOKER), which would be the default, but with the permissions of the function owner (SECURITY DEFINER). Then user a should run:

CREATE FUNCTION public.read_mytable(...) RETURNS ...
   LANGUAGE ...
   /* runs with the privileges of the owner */
   SECURITY DEFINER
   /* important: force "search_path" to a fixed order */
   SET search_path = pg_catalog,pg_temp
AS $$...$$;

/* by default, everybody can execute a function */
REVOKE EXECUTE ON FUNCTION public.read_mytable FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.read_mytable TO b;

Note that I created the function in schema public, to which b has access (don't forget to REVOKE CREATE ON SCHEMA public FROM PUBLIC;!).

Setting a search_path for user b is not enough, since this can always be changed dynamically with the SET command. You don't want b to run a privilege escalation attack.

Execute Permission on Function not Enough in PostgreSQL?

1 Answers