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;