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

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