Thread: 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
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 Tue, Mar 30, 2010 at 10:46 AM, Andy Colson <andy@squeakycode.net> wrote:
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.
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
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 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