Skip site navigation (1) Skip section navigation (2)

User action accounting

From: Joshua Berry <yoberi(at)gmail(dot)com>
To: PostgreSQL - General <pgsql-general(at)postgresql(dot)org>
Subject: User action accounting
Date: 2010-03-30 15:03:11
Message-ID: 5ccd53c11003300803o119f3e83t6ac2b2e16593bc44@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: Stefan KellerDate: 2010-03-30 15:27:17
Subject: Re: hstore equality-index performance question
Previous:From: moataz ElmasryDate: 2010-03-30 15:00:52
Subject: Re: createuser: could not connect to database postgres: FATAL: password authentication failed for user "postgres"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group