> Any security definer function should be designed with security in mind. That
> is the responsibility of the dba. You can't limit the dba in what he can do
> just in case he doesn't know what he is doing. You can suggest, but if the
> dba thinks he knows what he is doing, give him all the tools to do it.
> If the function can cause privilege escalation when not in a security
> definer function, then I would say there is a serious problem with the
> security system of the engine. Can you think of any possibility where a
> function would allow privilege escalation when it is not in a security
> definer function?
No I can't. But you can actually prevent this problem by making the
function security definer. Something like:
CREATE SCHEMA evaljail;
CREATE USER evaljail;
GRANT USAGE ON SCHEMA evaljail TO evaljail;
REVOKE CREATE ON SCHEMA evaljail FROM evaljail;
REVOKE USAGE ON SCHEMA public FROM evaljail;
CREATE FUNCTION evaljail.eval......
ALTER FUNCTION evaljail.eval OWNER TO evaljail;
ALTER FUNCTION evaljail.eval SECURITY DEFINER;
Now the function has no table access at all.
postgres=# select evaltest.eval('select * from public.test');
ERROR: permission denied for schema public
LINE 1: select (select * from public.test)::text as res1
^
QUERY: select (select * from public.test)::text as res1
CONTEXT: PL/pgSQL function "eval" line 8 at EXECUTE statement
postgres=# select evaltest.eval('1 - 2');
eval
------
-1
(1 row)
Best Wishes,
Chris Travers