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

2015-02-14 14:07 GMT+01:00 Berend Tober <btober(at)broadstripe(dot)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