Обсуждение: User action accounting

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.

Kind Regards,
-Joshua

Joshua Berry

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>
> 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 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
beable 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
particularlyhelpful.  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
serverto inherit a role allowing the needed permissions (trusting that the RADIUS server is secured) and allowing the
requestedname 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
havethe 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
thishas been solved by each person and has been asked a million times... I'm just not sure where to begin with
Google/postgresql.netqueries! 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
andaudit triggers that need to know the current application user use that table. (There's also an underlying
non-temporarytable 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
ofthe 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