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

Hey Tony,

2010/10/21 Tony Cebzanov <tonyceb(at)andrew(dot)cmu(dot)edu>

> 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.
>
IMO, you are trying to reinvent the wheel. Although, you may do it just for
fun. :-)

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'
> );
>
Why not just create "groups" via CREATE ROLE User ... and grants this
roles to the "users" (created via CREATE USER or CREATE ROLE ... LOGIN) ?

> 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.
>
Consider to use connection pool for "anonymous" users, i.e. users, which
are not logged in and persistent connections for logged in users.
To implement audit consider to use triggers. See, for example,
http://www.postgresql.org/docs/9.0/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.