<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-2">
<META content="MSHTML 6.00.2800.1264" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi Thomas, hi everybody.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I put some effort to solve the PL Java
Security problem.</FONT></DIV>
<DIV><FONT face=Arial size=2>My first question is if someone has already solved
this problem. If so, then I would like to see the results. If not, here is my
solution:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>DEPLOYER SECURITY</FONT></DIV>
<DIV><FONT face=Arial size=2>
<DIV><FONT face=Arial size=2>-----------------------------------</FONT></DIV>
<DIV>The installation of pl/java (using deploy.jar) MUST be executed by a
database superuser so that correct privileges could be set on pl/java repository
objects.</DIV>
<DIV> </DIV></FONT><FONT face=Arial size=2>SQLJ Schema
security:</FONT></DIV>
<DIV><FONT face=Arial size=2>-----------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2>The SQLJ schema is authorized to user
postgres.</FONT></DIV>
<DIV><FONT face=Arial size=2>By default, the USAGE privilege is granted to
PUBLIC and it can be changed (restricted) at any time.</FONT></DIV>
<DIV><FONT face=Arial size=2>The repository tables are owned by postgres and
only the SELECT privilege is granted to public. There should be no need to
change these privileges.</FONT></DIV>
<DIV><FONT face=Arial size=2>All the functions in SQLJ schema (except
java_call_handler) are marked as SECURITY DEFINER so that any user could install
and uninstall java packages. If needed, one can revoke EXECUTE privilege on
those function for some users.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>JAVA LANGUAGE SECURITY</FONT></DIV>
<DIV><FONT face=Arial
size=2>---------------------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2>The Java language is marked as TRUSTED so that any
user could use it.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>DEPLOYMENT DESCRIPTOR SECURITY</FONT></DIV>
<DIV><FONT face=Arial
size=2>--------------------------------------------------------------</FONT></DIV>
<DIV><FONT face=Arial size=2>This is probably the toughest problem. What we
need is</FONT></DIV>
<DIV><FONT face=Arial size=2>- to connect as a normal user</FONT></DIV>
<DIV><FONT face=Arial size=2>- to invoke e.g. install_jar in the superuser
context (through SECURITY DEFINER)</FONT></DIV>
<DIV><FONT face=Arial size=2>- to execute commands from deployment descriptor AS
A NORMAL USER AGAIN !</FONT></DIV>
<DIV><FONT face=Arial size=2>- to be able to return to superuser context if
needed (e.g. in case of remove or reinstall/redeploy)</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>The problem is that once I give up the superuser
context (using SET SESSION AUTHORIZATION), I have no chance to get it back (SET
SESSION AUTHORIZATION is forbidden if I have already lost the superuser
context).</FONT></DIV>
<DIV><FONT face=Arial size=2>It seemed to me that there is no solution, but I
have found one:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>While I am in superuser context, I create an
auxiliary function which belongs to the normal user (i.e. session_user, not
current_user in superuser context) and this function is also marked as SECURITY
DEFINER. When I need to execute a SQL script (deployment descriptor commands), I
use this "temporary" function to execute them in the context of the original
user.</FONT></DIV>
<DIV><FONT face=Arial size=2>After successful completion of the installation I
drop this function. Since all the installation runs within one transaction,
nobody else can see or use the temporary function. If the installation crashes,
the CREATE FUNCTION command is rolled back. The operation is safe since there is
no situation which could cause that the function would not be dropped or rolled
back.</FONT></DIV>
<DIV><FONT face=Arial size=2>To avoid a wait lock on function's name (in case of
concurrent transactions) I generate a function name based on the backend
PID.</FONT></DIV>
<DIV><FONT face=Arial size=2>The only problem I found is that sometimes
PostgreSQL thinks that the already dropped function still exists (perhaps a
PostgreSQL bug?) and I got an error "Cache lookup failed for function...". To
avoid this, I generate absolutely unique function name for each use, based on
the combination of backend PID, current time and a random number.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>I have tested this and I think it works fine. I
don't like the solution with the temporary function, but I haven't find anything
better. Though I think that it could be a good solution.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Please, if you find some time, take a look at the
source or try to compile and test it. If you like this solution, you can involve
it into your developement. If you like me to join the developement team, just
tell me what I should do for it.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>There are not so many changes, you can use diff to
find what has changed.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Thanks.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Best regards</FONT></DIV>
<DIV><FONT face=Arial size=2> Filip Hrbek</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV></BODY></HTML>