User action accounting

On 3/30/2010 10:03 AM, 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?
2. Should I stay with using the same role for the application, but
somehow store a per session variable that would have the user's login
name and be accessible by the triggers?

Anyhow, the goal is to be able to note which of the 40 users
created/modified records in the backend. I'm sure that this has been
solved by each person and has been asked a million times... I'm just not
sure where to begin with Google/postgresql.net <http://postgresql.net&gt;
queries! Please feel free to reply with a helpful search query or URL.

Kind Regards,
-Joshua

Joshua Berry

When your app/users connect to the db, do they connect as the same user,
or each with a different username?

Do you have your own "users" table?

-Andy

On Tue, Mar 30, 2010 at 10:46 AM, Andy Colson <andy@squeakycode.net> wrote:

When your app/users connect to the db, do they connect as the same user, or
each with a different username?

The application instances each connect to the database with the same
username. The application currently uses an ODBC connection which has hard
coded username values. If each user has their own workstation, this would be
easy, but I want to be able to specify the username when the application
begins. I'm not worried about the security aspect; I just want to present
users with an easy way to specify who they are to aid in tracking.

Do you have your own "users" table?

There is a "users" table currently used for another purpose, but it could be
reused/extended.

If I go the route of keeping the same role for each application instance, it
would be great if I could avoid having to pass the username into each query
and instead have a per-session or per-connection variable that the trigger
could access. Sounds easy, but I've never tried it before and things not
usually as easy as they seem.

Regards,

-Joshua

On 3/30/2010 11:13 AM, Joshua Berry wrote:

On Tue, Mar 30, 2010 at 10:46 AM, Andy Colson <andy@squeakycode.net
<mailto:andy@squeakycode.net>> wrote:

When your app/users connect to the db, do they connect as the same
user, or each with a different username?

The application instances each connect to the database with the same
username. The application currently uses an ODBC connection which has
hard coded username values. If each user has their own workstation, this
would be easy, but I want to be able to specify the username when the
application begins. I'm not worried about the security aspect; I just
want to present users with an easy way to specify who they are to aid in
tracking.

Do you have your own "users" table?

There is a "users" table currently used for another purpose, but it
could be reused/extended.

If I go the route of keeping the same role for each application
instance, it would be great if I could avoid having to pass the username
into each query and instead have a per-session or per-connection
variable that the trigger could access. Sounds easy, but I've never
tried it before and things not usually as easy as they seem.

Regards,

-Joshua

I ask because there is a CURRENT_UESR you can use in a trigger. It is
who you connect to the db as. Which in your case all users would have
the same name. But.. there is also a set role:

http://www.postgresql.org/docs/8.4/static/sql-set-role.html

So after you connect you could fire off a "set role bob", and the
triggers would use 'bob' as current_user.

Or something like that. You'd also have to create all the users on the
pg side (create role...). I have not done this, its just "in theory it
should work".

-Andy

On Mar 30, 2010, at 8:03 AM, 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?
2. Should I stay with using the same role for the application, but somehow store a per session variable that would have the user's login name and be accessible by the triggers?

Anyhow, the goal is to be able to note which of the 40 users created/modified records in the backend. I'm sure that this has been solved by each person and has been asked a million times... I'm just not sure where to begin with Google/postgresql.net queries! Please feel free to reply with a helpful search query or URL.

I create a one-row temporary table with information about the current user in it at the beginning of each connection and audit triggers that need to know the current application user use that table. (There's also an underlying non-temporary table so that stuff doesn't break during ad-hoc updates).

I'm not sure whether that's a good approach, but it seems to work well and means the database doesn't need to be aware of the users accessing it (which is more than just authentication, but also creating and revoking users).

The main downside is that you can't use it with any sort of connection pooling.

Cheers,
Steve

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

Le 31/03/2010 07:11, Craig Ringer a écrit :

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.

RADIUS authentication will be available in 9.0. See
https://commitfest.postgresql.org/action/patch_view?id=260 .

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com