>>Hi,
>>
>>I am considering using a PostgreSQL database as a backend for a system currently using DBFs. Can anyone recommend an Audit Trail that will allow me to track who made what changes to certain tables and when the changes were made? I am anticipating that my application will connect to the database using a single user, then will have its own user table which will contain the username/password hash to verify if the person has access to the system. It's this second user name that I will need to track as part of the audit trail.
>
>If you haven't already, I'd consider using multiple PostgreSQL database roles, with different database logon/user names, rather than just a single PostgreSQL user/logon. My understanding is, these days, most RDBMSs have fairly fine-grained control of privileges so you can set up roles to do pretty much anything you need. You would then only need to add or remove new DB user names from one or more role(s).
>
>- If you want to be able to do all functions (including admin) using a single PG user, that user would have to be an admin. So, even restricted users would be using admin access (although you'd restrict them yourself later). That's not ideal from a security POV
>
>- I briefly Googled [PostgreSQL audit trail], couldn't find any obvious solutions. I suspect any generic solutions will expect to use the DB user, rather than an unknown custom security implementation (which is what you're contemplating).
>
>In a nutshell, I'd look hard at using PostgreSQL DB roles before trying to roll your own access control system.

Thanks Al,

part of my issue is that this is an adaptation of an already working system that has complex security implemented within the application already (Users, Groups, Access Rights, Access Right Exceptions) that I need to use. So maybe I need to ensure that each user in my User table has been defined as a user at the database level so that I can map one on to the other and then the audit trail should work.