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

Поиск
Список
Период
Сортировка
От Samuel Gendler
Тема Re: PostgreSQL Authentication and Pooling in JBoss : static 'postgres' user VS dynamic jboss user
Дата
Msg-id AANLkTi=wDULjQ6Gvy8Mpr4XUyGN_wz5m=1VJuw66xkYW@mail.gmail.com
обсуждение исходный текст
Ответ на Re: PostgreSQL Authentication and Pooling in JBoss : static 'postgres' user VS dynamic jboss user  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
Список pgsql-jdbc


On Wed, Dec 29, 2010 at 3:25 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
Στις Tuesday 28 December 2010 20:08:59 γράψατε:
> 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.
On the practical side, increasing the max_connections number to 2000 and having created 1000 connections
on  my test FreeBSD system did not produce any visible degradation in performance.
Now on the theoretical side, It is true that connections eat up (some small mem) from shared mem.
It is true that memory usage will increase by the use of work_mem when simultaneous users perform sorting, etc
also it is true that after several 100s of connections, the task of opening a new connections becomes
a rather expensive and resource intensive operation, but the end result is nowhere as bad as you seem to believe.

1000 idle connections may not be much of a problem, but 1000 connections all performing work simultaneously can be very problematic as you compete for both CPU and I/O resources.  But if that's not the nature of your load, then it wouldn't be a problem, certainly.

 would you prefer "forgetting" to grant a user the right  to perform a certain task
or giving anyone the power to exploit any SQL holes in the application run by the one and only postgres super user? ;D

Well, I wouldn't generally endorse having an application connect as a postgres super user, or even as a user with anything more than the bare minimum required for the application to function. You can potentially isolate one customer's data from another, depending upon your schema, but there's no reason to think that a single user will have any more authorization to do malicious things to the database than a user in your multi-user scenario.

 
Not only that, but i think that this is a userland solution. I prefer things to be done system-wise.
As an Example, if i deploy the setup as i present it here, and if indeed it turns up to be a disaster
it will cost me about 10 seconds to go into ../server/default/deploy/postgres-xa-ds.xml
comment the <security-domain>somesecdomain</security-domain> tag, uncomment user and password
and we are back to the same good old OnePool (JBossManagedConnectionPool)

Granted, though it wouldn't take any longer than that to disable my mechanism by eliminating the aspect which causes the user to be set in the db.  If coded such that no stored user means no work in the trigger, then the triggers become very low overhead, and it isn't much more difficult to disable the triggers entirely.  Yes, it is a userland solution, but it also gives you visibility into userland state that is difficult to get in any other way (we track remote address of the user as well as user identity, for example, and we also get a full audit log of every change made to important tables including the ability to roll back an individual customer's state to an earlier point in time without playing complex games with backups).  But it is certainly not a solution for everyone.  I was just throwing it out there as an option that might solve the problem you are trying to solve. 

 

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Achilleas Mantzios
Дата:
Сообщение: Re: PostgreSQL Authentication and Pooling in JBoss : static 'postgres' user VS dynamic jboss user
Следующее
От: Craig Ringer
Дата:
Сообщение: Re: Client-Side triggers possible?