Auditing Postgres activity from HTTP users

The goal of auditing who is updating what data in a postgres database is coming now to a need.

The idea is having an audit of all changes into any of the tables in a postgres database. The solution I need should be independent of wether the frontend is a web application or the command-line or a remote ssh connection. The extra trick is where to grab the user identification when updates come through the web application.

There is a gret Lorenzo has come with a very nice approach I still have to try. It is based on triggers applied to each of the tables you want to audit, and a global function which gets called on every update, insert or delete.

When the frontend is the web application, the design must allow for a user authentication, then the database connection needs to be done on behalf of that user. That way, the postgres procedures will already know who the user is.