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
– Bergi, Commented Feb 26, 2021 at 0:40

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

3

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.

answered Feb 26, 2021 at 2:52

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jeb50 Over a year ago

Excellent, I'm able to resolve it now without pg_temp in the path. What's the benefit of having it?

Jeb50 Over a year ago

This is a good article. Wouldn't it be great to change default pg behavior every new funct/sp is granted EXE to public to be explicit permission grant like MS SQL or Oracle? Also REVOKE CREATE ON SCHEMA public FROM PUBLIC; is the default? Last question, any impact if I drop public schema?

Laurenz Albe Over a year ago

That is just the standard order. You can set search_path empty with the same effect. The default settings are still there for compatiblity reasons, although efforts for public are underway. You can drop the public schema, but then you might need another place for extension objects.

Collectives™ on Stack Overflow

Execute Permission on Function not Enough in PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related