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

[Dmitriy Igrishin <dmitigr@xxxxxxxxx>]

Hey Tony,

2010/10/21 Tony Cebzanov <tonyceb@xxxxxxxxxxxxxx>

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

-- 
// Dmitriy.