[Pljava-dev] PL Java Security

Hi Thomas, hi everybody.

I put some effort to solve the PL Java Security problem.
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:

DEPLOYER SECURITY
-----------------------------------
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.

SQLJ Schema security:
-----------------------------------
The SQLJ schema is authorized to user postgres.
By default, the USAGE privilege is granted to PUBLIC and it can be changed (restricted) at any time.
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.
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.

JAVA LANGUAGE SECURITY
---------------------------------------------
The Java language is marked as TRUSTED so that any user could use it.

DEPLOYMENT DESCRIPTOR SECURITY
--------------------------------------------------------------
This is probably the toughest problem. What we need is
- to connect as a normal user
- to invoke e.g. install_jar in the superuser context (through SECURITY DEFINER)
- to execute commands from deployment descriptor AS A NORMAL USER AGAIN !
- to be able to return to superuser context if needed (e.g. in case of remove or reinstall/redeploy)

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).
It seemed to me that there is no solution, but I have found one:

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.
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.
To avoid a wait lock on function's name (in case of concurrent transactions) I generate a function name based on the backend PID.
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.

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.

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.

There are not so many changes, you can use diff to find what has changed.

Thanks.

Best regards
Filip Hrbek
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20050201/046fb5f4/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pljava-1.0.0.security-path.tar
Type: application/octet-stream
Size: 51200 bytes
Desc: not available
URL: <http://lists.pgfoundry.org/pipermail/pljava-dev/attachments/20050201/046fb5f4/attachment.obj>