On Tue, Dec 28, 2010 at 7:13 AM, Achilleas Mantzios
<achill@matrix.gatewaynet.com> wrote:
You most probably are referring to a low-complexity system.
Lets see this scenario. Imagine you want to indentify a cause for a variety of problems. Examples
a) High load, you want to find what/who causes a very high load on the server
Unfortunately the most likely culprit in your system is going to be the huge number of connections that postgres is having to deal with. You should read up on postgresql performance tuning and learn about how having large numbers of connections to the server can impact postgresql performance.
If you are looking for systemic problems that are related to authorization, you are only going to create more of those kinds of problems by having such fine-grained authorization configuration within your database - forget to grant an authority to user y on table x when some app state changes and you suddenly have authorization errors.
If you are looking for data issues like 'who was responsible for setting column x to value y', there are likely much better mechanisms for logging user behaviour than via the postgres logs. Depending upon what frameworks you are using in your app, it isn't usually very difficult to put code in place which can call a stored proc in the db with the current web user's id/username at the start of any web request or db transaction. From there, it is relatively simple to implement audit logging in the db - triggers on update/insert/delete which can grab the web user id associated with the current backend process id and write relevant info into audit logging tables or some other mechanism.
Basic mechanism I use is as follows:
- when connection gets assigned to request, call stored proc with unique identifier for web user. Stored proc associates user id with backend process id in a table. I handle this via an aspect that gets applied to my service layer, but you could easily handle it via a wrapper around your connection pool/session factory just as easily. The only trick is having a mechanism for resolving the user's identity from within your aspect/wrapper. I use a static ContextHolder pattern which ensures that the relevant info is accessible from thread local storage as soon as the request starts processing inside the security layer. If you're using spring-security, the SecurityContextHolder likely has everything you need already available.
- Add triggers to tables on update/insert/delete, as appropriate, which look up current user (via another stored proc that does lookup via backend process id), then inserts user id, type of operation, and NEW.* or OLD.* (for delete) into an audit log table that has the same schema, without unique constraints and with extra columns for holding audit info (timestamp, operation type, user_id, app name, anything else you choose to make available)
If you've got a huge transaction rate, this may not be your best solution, but a well configured postgres install uses pooling to keep active connections to a small number, so the user/backend association table will always be small and in cache, and insert into a table with no constraints is also quick unless you are already io-bound. It does mean every insert/update/delete results in that operation plus another insert. If you are read-mostly, this has effectively no real impact on performance. If you have high load and are write-mostly, then it could easily be an unacceptable increase (doubling) in load.
