Re: How to hide stored procedure's bodies from specific user

Saimon Lim wrote:

Thanks for your help

I want to restrict some postgres users as much as possible and allow
them to execute a few my own stored procedures only.

Create the function that you want restrict access to in a separate 'private' schema to which usage is not granted.

Create the functions you wish to allow access to in a schema to which the role is granted access to.

You original question was different, i.e., you were asking about hiding your clever algorithms from inquisitive inspection. For that, similarly use as 'private' schema where you keep you super-secret stuff, and then provide a sanitized interface in the 'public' schema:


CREATE OR REPLACE FUNCTION private.average(a float, b float)
 RETURNS float
 LANGUAGE sql
AS $$
        SELECT ($1 + $2)/2.;
$$;


CREATE OR REPLACE FUNCTION public.average(a float, b float)
RETURNS float
 LANGUAGE sql
as $$
        select private.average(a,b)
$$
security definer;