2015-02-14 14:07 GMT+01:00 Berend Tober <bto...@broadstripe.net>: > 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.
-- Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com
