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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
