Re: Advice needed on application/database authentication/authorization/auditing model

I have a web application with a Postgres backend.  In my initial
prototype, I decided not to have a Postgres database user created for
each application user, opting instead to use my own users table.

Authentication of application users is done via PAM, so no password is
necessary in my users table -- I just let PAM do its thing, and if the
user is authenticated, I check for a record in my application's users
table to see if they're authorized to use the app, along with what
privileges they have, e.g.:

CREATE TYPE USER_ROLE AS ENUM ('User', 'Auditor', 'Administrator');

CREATE TABLE users (
   id SERIAL PRIMARY KEY,
   username TEXT UNIQUE NOT NULL,
   displayname TEXT NOT NULL,
   role USER_ROLE NOT NULL DEFAULT 'User'
);

Now that this is moving beyond a prototype stage, I need to tighten up
the authentication/authorization/access control model.  In particular, I
need to add some basic audit trail functionality.  I found a couple of
projects that help make auditing easy (tablelog and EMaj being the most
promising) but they both rely on the database users mapping 1:1 to
application users, which is currently not the case -- right now I've
only got one database user that's used for all of the pooled
connections, so the audit logs are showing that user instead of my
application user.

So, I'm wondering what others have done in similar situations.  It seems
to me like the database connection pooling means I probably need the
connections to be made with a privileged "database superuser" account
that has permission to "SET ROLE" to each and every application user,
then "RESET ROLE" when it's done.  That's a bit of a pain, but doable

Then there's the issue of application roles vs. database roles.  I
wanted to have three roles in this application:  regular users, auditors
(who can do everything regular users can, plus access audit tables to
view audit log tables and potentially restore data from them) and
administrators (who can do everything in the application, but shouldn't
be Postgres superusers.)  Unfortunately, I can't figure out a clever way
to do this mapping, especially because Postgres doesn't allow users to
refer to system tables like pg_authid to do an explicit mapping of app
roles to database roles. 

So, does anyone have any suggestions here?  I feel like there's got to
be a way to do this, but I can't find anything relevant in the list
archives.

Thanks.
-Tony

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general