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
