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