I have been using Apache Derby as data base backend for an application over the past years and am looking into adding PostgreSQL as an additional backend the application could be run against. Obviously all stored procedure code I have written is in Java. All procedures/functions are defined with "Security definer" so they are executed with the privileges of the user that defined them. So in a first step I installed the latest version of plJava on PostgreSQL 8.4 running on a Debian 6 Squeeze server and uploaded the jar files into the server.
In one of my procedures I am however noticing that the Apache Derby implementation and the PostgreSQL implementation are leading to different results. When logging in as "Thomas" and then using jdbc function getMetaData().getUserName() *within* a stored procedure and returning the result to the calling program - Apache Derby returns the session user, i.e. Thomas (and not the name of the data base or procedure owner [which is what CURRENT_USER when being called within the proc would return / but which is not what I need) - PostgreSQL returns "postgres" whereas I would have expected it to also return "Thomas".
I am wondering if someone can explain why this is the case? and how I can get around this "compatibility" issue? If the driver implementations are different, any suggestion how to modify the java code so it would actually for both data base systems return the session_user?
