Re: PostgreSQL Authentication and Pooling in JBoss : static 'postgres' user VS dynamic jboss user

On Tue, Dec 28, 2010 at 7:13 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)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.