2015-02-14 14:07 GMT+01:00 Berend Tober <btober@xxxxxxxxxxxxxxx>:
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;
Unless I misunderstood something, this doesn't protect at all the function source code. You can still get it by reading pg_proc.
--
