Обсуждение: How to hide stored procedure's bodies from specific user

On Thu, Feb 12, 2015 at 2:53 AM, Saimon Lim <aimon.slim@gmail.com> wrote:
> For clarification - I run the commands using psql program.
>
> 2015-02-11 12:54 GMT+03:00 Saimon Lim <aimon.slim@gmail.com>:
>>
>> Hi
>> I want to hide my own stored procedures' bodies from the specific user.
>> As far as I know, procedure's body is visible in the pg_catalog.pg_proc
>> table.
>>
>> So, I did the following:
>> REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC;
>>
>> And after it, when user tries:
>> SELECT * from pg_proc;
>>
>> The following error occurs:
>> ERROR:  permission denied for relation pg_proc
>>
>> It turns out that user don't have access to the body of the procedure.
>> But I still can get stored procedure's body using
>> \sf function_name
>> or with
>> \ef function_name
>>
>> So, how can I completely hide my own stored procedure's bodies from this
>> user?

oddly, psql does not echo (via -E switch) \sf and \ef which is small bug IMO.

however, it's pretty easy to see what's going on via tailing the
server log.   What's happening here is psql is calling the function
pg_catalog.pg_get_functiondef...you can revoke execute on that in a
similar way.

merlin

On 2/11/2015 1:54 AM, Saimon Lim wrote:
> I want to hide my own stored procedures' bodies from the specific user.
> As far as I know, procedure's body is visible in the
> pg_catalog.pg_proc table.

only good way I know of to do that is to write the procedures in C so
they are binary .so/.dll files.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Hello, Saimon,

I propose the following (ugly) solution.

------
/*as some privileged user: */

begin;

create table hidden_function_foo as select $code$
     create function pg_temp.foo(p_input text) returns text as $$
         select /*nodoby knows we are using
md5*/md5('the_salt_nobody_can_see' || p_input ||
'one_more_salt_nobody_can_see');
     $$ language sql;
$code$::text code;

revoke all on table hidden_function_foo from ro;

create function foo(p_input text) returns text as $$
declare
     l_res text;
begin
     drop function if exists pg_temp.foo(text);
     execute (select code from hidden_function_foo);
     l_res := (select pg_temp.foo(p_input));
     drop function pg_temp.foo(text);
     return l_res;
end;
$$ language plpgsql security definer set search_path to pg_catalog,
public, pg_temp;

grant execute on function foo(text) to ro;

end;

------

/*as unprivileged ro user*/
select foo('bar');
select * from hidden_function_foo; -- fails

------

Maybe the solution is still unsafe, it is sufficient to make the inner
function produce some error to get a part of its body as a stacktrace.

BTW Do you believe hiding procedure bodies greatly improves security?
isn't it easier to hide salts, keys etc only?

Regards, Alexey

On 11.02.2015 12:54, Saimon Lim wrote:
> Hi
> I want to hide my own stored procedures' bodies from the specific user.
> As far as I know, procedure's body is visible in the
> pg_catalog.pg_proc table.
>
> So, I did the following:
> REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC;
>
> And after it, when user tries:
> SELECT * from pg_proc;
>
> The following error occurs:
> ERROR:  permission denied for relation pg_proc
>
> It turns out that user don't have access to the body of the procedure.
> But I still can get stored procedure's body using
> \sf function_name
> or with
> \ef function_name
>
> So, how can I completely hide my own stored procedure's bodies from
> this user?
>
> Thanks in advance
> Saimon

On Fri, Feb 13, 2015 at 5:17 AM, Saimon Lim <aimon.slim@gmail.com> 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.
>
> If I block access using:
>
> REVOKE ALL ON pg_catalog.pg_proc FROM PUBLIC;
> REVOKE ALL ON FUNCTION pg_catalog.pg_get_functiondef(oid) FROM PUBLIC;
>
> the user will fail to select procedure's body from pg_catalog.pg_proc and
> using psql \sf of \ef.
> Is this method correct?

[FYI -- please try to avoid top-posting]

That is correct. However, I'm not guaranteeing that it's a 100% clean
solution...there may be other loopholes you have to close also.  For
example, if you get an unhandled error inside a function the database
will send the error context back to the client.  Basically you're
playing 'whack-a-mole' -- however, it's not too difficult to hide
stored procedure bodies from the *casual observer* if you take certain
precautions.

A stronger approach would be write a wrapper to pl/pgsql that
encrypted the function bodies (this is not trivial), or to write them
in C as John noted.

merlin

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;

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

Agreed, but he already knows about that and how to mitigate.