Proxy authentication for PostgeSQL

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?

The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username.

In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will automatically reset the authorization. Make sure your connection pool runs DISCARD ALL on returned connections.

These are a lot like the first of the three "proxy authentication" cases given in the linked Oracle doc.

PostgreSQL doesn't support the other two forms, where the client auth is passed through to the DB. It'd be relatively simple to support but nobody's wanted it enough to implement it or fund the work required for someone else to implement it.

SET ROLE

To use SET ROLE you must connect to the DB as a user that has membership of all the user roles, but the user can be otherwise unprivileged. The DB user should have the NOINHERIT option set with ALTER ROLE or at CREATE ROLE time. So you typically create webapp users as db roles that the webapp user is a member of, e.g.

CREATE ROLE newusername 
  NOLOGIN
  ROLE mywebappuser;

(The ROLE mywebapp is equivant to running a subsequent GRANT newusername TO mywebappuser;).

SET SESSION AUTHORIZATION

To use SET SESSION AUTHORIZATION you must connect as a superuser, which is generally undesirable as it gives total control to the webapp user.

Unlike SET ROLE there's no requirement for group memberships.

StackExchange Database Administrators Q#77704, answer score: 5