The following seems likely to be a fairly common situation, but I haven't seen any solutions.
I have a PostgreSQL database containing views that are specific to the
$user, in order to restrict data depending upon some database-specific ACL logic. The database is typically accessed through JDBC, and in particular in JSP for a web interface. Users log in to the website with password authentication using LDAP.
What I would like is to make use of the views from within the JDBC connection to the database. But typically this mid-tier connection is created using a generic user, rather that specific to the person logged in to the website. I believe that in Oracle the "correct" approach is Proxy Authentication, but AFAIAA there is no equivalent for PostgreSQL. So what to do instead?
Some ideas come to mind:
- Give all users' database accounts the same password, and then create user-specific JDBC connection using this generic password (this just feels so wrong...)
- Store the user's login password somewhere in the website, to re-use when creating a database connection (again, this feels very wrong)
Any other (hopefully better) suggestions?