Re: User action accounting

Joshua Berry wrote:
> Hello All,
>
> I have a few PHP/Clarion based applications that don't currently track
> who created and modified records. I'd like to be able to track all user
> and timestamp pairs for INSERT/UPDATEs by way of triggers.
>
> The problem is that I currently use the same role name for each instance
> of the application, so "current_user" is not particularly helpful. So I
> have a few ideas that I wanted to bounce off the experts here:
> 1. Should I use seperate PG roles for each user? Is there a way of
> permitting user names queried against a RADIUS server to inherit a role
> allowing the needed permissions (trusting that the RADIUS server is
> secured) and allowing the requested name to be used without having to
> maintain two lists of accounts?

I'm not sure about RADIUS, but Pg can auth users against Kerberos and
against LDAP, or against anything that'll talk to PAM. You should be
able to use RADIUS via PAM if nothing else.

( Side note: it looks like LDAP auth doesn't support storage of role
memberships or mapping of Pg roles to unix user group memberships. It'd
be really rather handy... )

Anyway, one way or the other I'd personally strongly suggest option (1).
It allows you to vary the rights granted to users using the database's
priv logic instead of having to roll your own whenever you want to limit
user rights. Especially now that Pg supports column privs, this is a big
bonus.

You can maintain the created-by/when and modified by/when columns using
triggers, and deny anybody the right to insert/update these columns so
nothing except the trigger may affect them.

By the way, if your trigger-maintained `last mod user' and `last mod
time' tables confuse an app that likes to use "SELECT *" and doesn't
ignore appended columns, there's a workaround. Rename the original table
the app uses, and make a view with that name that selects only the
columns the app expects to see in the table. Add appropriate UPDATE and
DELETE rules to the view so the app doesn't realise it's a view. Now
you've got that last-user/time information, but the app that uses the
table can't see it to be confused by it.

This can be particularly important if the app isn't aware of column
privs and tries to update all columns, but you have 'originally created
by' cols or the like that the app doesn't have the rights to update.

--
Craig Ringer