Thread: [JDBC] Could pgsql jdbc support pool reauthentication?
Hello, I was at pgconfeu and attended a talk by Joe Conway about STIG and the implementation of set_user. It took me back when Iwas trying to find better ways to do connection pooling with jboss/widlfly. Basically in jboss/wildfly you can have a single app user for all connections, which takes from you all the benefits of havingindividual postgresql users, or specify a security-domain where you say jboss to use another login module such as CallerIdentity which makes the jboss pooled connection use the same user/credentialas the one of the logged in jboss user, which is great, as we can apply all the security mechanisms of postgersql, have correct logging/stats per user which is very useful, row-level security,apply advanced multitenancy schemes, etc... but suffers that every user has his own pool. So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time,the total number of connections would have to be raised to 1000. So, one solution would be to keep one common pool with connections originally authenticated with a dedicated app user whichhas the privilege to run set_user, and then when getConnection() is called, to run set_user with the calling user, and have the app/db behave as per the user's authorization, then when the connectionis closed, to call reset_user so that the effective user of the inactive connection to be again the dedicated app user. This way we could have one single pool, and have all the benefits of the postgresql's security system. Any thoughts on this? Thanks -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-jdbc
Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That is, you could configure 200 pools with minCapacity=0, maxCapacity=5.
Of course you would need thread-pool limits as well to avoid sending more than 5 concurrent requests on behalf of a given user id.
Technically speaking, there's JDBC standard way of calling "set_user" via java.sql.Connection#setClientInfo("ClientUser", java.lang.String), however I'm not sure how well it is supported/used.
PS Are you aware of ApplicationName? That is java.sql.Connection#setClientInfo("ApplicationName", java.lang.String)
Vladimir
Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:
> Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak
time,the total number of connections would have to be raised to 1000.
>
> Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000
concurrentconnections.
I know, but we still risk having our max_connections exceeded. And this is not scaleable.
>
> That is, you could configure 200 pools with minCapacity=0, maxCapacity=5.
> Of course you would need thread-pool limits as well to avoid sending more than 5 concurrent requests on behalf of a
givenuser id.
>
That's what he have done.
> Technically speaking, there's JDBC standard way of calling "set_user" via
java.sql.Connection#setClientInfo("ClientUser",java.lang.String), however I'm not sure how well it is supported/used.
>
>
> PS Are you aware of ApplicationName? That is java.sql.Connection#setClientInfo("ApplicationName", java.lang.String)
Yes, we make heavy use of ApplicationName in each of our apps/tenants. Pretty handy with logging, pgbadger etc.
>
> Vladimir
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Achilleas>I know, but we still risk having our max_connections exceeded.
You should limit the work at thread pool level, not at connection pool level.
That is you should not accept new tasks for execution if they might block on a "getConnection" call.
Imagine a case:
1) Thread A starts a transaction, it calls the DB
2) Thread B starts processing another request, it locks some Java lock and calls .getConnection. Unfortunately, there's not enough connections, sot it just waits.
3) Thread A completes the DB call, it continues and tries to take the same Java lock.
Now we have a deadlock: A is waiting for the Java lock and it holds a DB connection (with uncommitted transaction), while B is holding a Java lock and it is trying to get a connection.
That is, it is not connection pool's task to limit the number of concurrent requests.
Achilleas>And this is not scaleable.
Can you elaborate?
Vladimir
On 31/10/2017 11:20, Vladimir Sitnikov wrote: > Achilleas>I know, but we still risk having our max_connections exceeded. > > You should limit the work at thread pool level, not at connection pool level. > That is you should not accept new tasks for execution if they might block on a "getConnection" call. > Problem is one single java thread might use all 5 connections, depending on the app. And as each connection is closed, itis returned to the private user's pool, where it will stay until idle-timeout expires and kills the backend . Till that happens, no one else can use this, which is clearly a poor management of resources. > Imagine a case: > 1) Thread A starts a transaction, it calls the DB > 2) Thread B starts processing another request, it locks some Java lock and calls .getConnection. Unfortunately, there'snot enough connections, sot it just waits. > 3) Thread A completes the DB call, it continues and tries to take the same Java lock. > > Now we have a deadlock: A is waiting for the Java lock and it holds a DB connection (with uncommitted transaction), whileB is holding a Java lock and it is trying to get a connection. A buggy application will manifest its problems sooner or later > > That is, it is not connection pool's task to limit the number of concurrent requests. > > Achilleas>And this is not scaleable. > > Can you elaborate? As described in the first sentence above, this design is not flexible. One should lower idle-timeout to effectively zero,solving the problem of poor management, but effectively loosing the benefits of having a connection pool in the first place. That's why reauth plugins was introduced in JCA spec. > > Vladimir -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-jdbc
On 31/10/17 10:08, Achilleas Mantzios wrote: > Hello Vladimir, thanx > > On 31/10/2017 10:30, Vladimir Sitnikov wrote: >> Achilleas>So if say we need 5 connections max for the most complex >> app to work, and we have 200 users, then at peak time, the total >> number of connections would have to be raised to 1000. >> >> Pools can shrink, so you do not have to raise total number of >> connections to 1000 unless you truly expect 1000 concurrent connections. > I know, but we still risk having our max_connections exceeded. And > this is not scaleable. That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem. Álvaro -- Álvaro Hernández Tortosa ----------- <8K>data -- Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-jdbc
On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote: > > > On 31/10/17 10:08, Achilleas Mantzios wrote: >> Hello Vladimir, thanx >> >> On 31/10/2017 10:30, Vladimir Sitnikov wrote: >>> Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time,the total number of connections would have to be raised to 1000. >>> >>> Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrentconnections. >> I know, but we still risk having our max_connections exceeded. And this is not scaleable. > > That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e.they are returned as soon as they finish the job, they don't sit idle) this > is no longer a problem. That would be a blessing if pgbouncer supported LDAP . > > > Álvaro > -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-jdbc
Pivotal has a branch of pgbouncer that supports LDAP.
On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 31/10/2017 13:08, Dave Cramer wrote:
Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-db/pgbouncerPivotal has a branch of pgbouncer that supports LDAP.
On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
yes, and with a bit of work you should be able to port the changes over to the current pgbouncer
On 31 October 2017 at 07:52, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 31/10/2017 13:08, Dave Cramer wrote:Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-Pivotal has a branch of pgbouncer that supports LDAP.db/pgbouncer On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
In WildFly 11 the connection pool have a property "Allow multiple users" that basically "Specifies if multiple users will access the datasource through the getConnection(user, password) method and hence if the internal pool type should account for that".
Probably you should look if that works for you.
Jorge Solórzano
On Wed, Nov 1, 2017 at 6:55 AM, Dave Cramer <pg@fastcrypt.com> wrote:
yes, and with a bit of work you should be able to port the changes over to the current pgbouncerOn 31 October 2017 at 07:52, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 13:08, Dave Cramer wrote:Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-dPivotal has a branch of pgbouncer that supports LDAP.b/pgbouncer On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Hello again,
After looking at pgbouncer master branch, which supports PAM authentication and indirectly LDAP, I decided to give it a go. Also I had to do some minor patches to both the backend's GUC code, and pgbouncer in order to respect search_path and make it usable with transaction_mode (which IMHO is the main benefit of using pgbouncer vs the competition) - + it is the best bet closest to re-authentication, which postgresql does not support out of the box (and is fairly complicated to do so).
After all those (not so trivial) changes I tested pgbouncer with my app, and I got sort of promising results, but also had a few unexpected failures as well.
Sad thing is that neither pgbouncer's mailing list seems very active , despite having subscribed successfully it still fails to accept my emails, nor do I see the project at github very active, or any announcement for a new release soon .
So I don't really know how to contact the pgbouncer community.
On 01/11/2017 14:55, Dave Cramer wrote:
After looking at pgbouncer master branch, which supports PAM authentication and indirectly LDAP, I decided to give it a go. Also I had to do some minor patches to both the backend's GUC code, and pgbouncer in order to respect search_path and make it usable with transaction_mode (which IMHO is the main benefit of using pgbouncer vs the competition) - + it is the best bet closest to re-authentication, which postgresql does not support out of the box (and is fairly complicated to do so).
After all those (not so trivial) changes I tested pgbouncer with my app, and I got sort of promising results, but also had a few unexpected failures as well.
Sad thing is that neither pgbouncer's mailing list seems very active , despite having subscribed successfully it still fails to accept my emails, nor do I see the project at github very active, or any announcement for a new release soon .
So I don't really know how to contact the pgbouncer community.
On 01/11/2017 14:55, Dave Cramer wrote:
yes, and with a bit of work you should be able to port the changes over to the current pgbouncerOn 31 October 2017 at 07:52, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 13:08, Dave Cramer wrote:Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-Pivotal has a branch of pgbouncer that supports LDAP.db/pgbouncer On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
just provide a PR for pgbouncer here https://github.com/pgbouncer/pgbouncer
On 16 November 2017 at 03:30, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
Hello again,
After looking at pgbouncer master branch, which supports PAM authentication and indirectly LDAP, I decided to give it a go. Also I had to do some minor patches to both the backend's GUC code, and pgbouncer in order to respect search_path and make it usable with transaction_mode (which IMHO is the main benefit of using pgbouncer vs the competition) - + it is the best bet closest to re-authentication, which postgresql does not support out of the box (and is fairly complicated to do so).
After all those (not so trivial) changes I tested pgbouncer with my app, and I got sort of promising results, but also had a few unexpected failures as well.
Sad thing is that neither pgbouncer's mailing list seems very active , despite having subscribed successfully it still fails to accept my emails, nor do I see the project at github very active, or any announcement for a new release soon .
So I don't really know how to contact the pgbouncer community.
On 01/11/2017 14:55, Dave Cramer wrote:yes, and with a bit of work you should be able to port the changes over to the current pgbouncerOn 31 October 2017 at 07:52, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 13:08, Dave Cramer wrote:Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-dPivotal has a branch of pgbouncer that supports LDAP.b/pgbouncer On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
On 16/11/2017 12:15, Dave Cramer wrote:
Had tried that as well. No reaction. IMHO for a project that is supposed to be so vital (like in "but if you have a pgbouncer in front of PostgreSQL (you should) and..." there should be more activity. I mean, not having pgbouncer is supposed to be bad, but OTOH making pgbouncer work in a (semi-)enterprise environment (LDAP/search_path based multi-tenancy) looks like a journey in the mid ocean.just provide a PR for pgbouncer here https://github.com/pgbouncer/pgbouncer
On 16 November 2017 at 03:30, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:Hello again,
After looking at pgbouncer master branch, which supports PAM authentication and indirectly LDAP, I decided to give it a go. Also I had to do some minor patches to both the backend's GUC code, and pgbouncer in order to respect search_path and make it usable with transaction_mode (which IMHO is the main benefit of using pgbouncer vs the competition) - + it is the best bet closest to re-authentication, which postgresql does not support out of the box (and is fairly complicated to do so).
After all those (not so trivial) changes I tested pgbouncer with my app, and I got sort of promising results, but also had a few unexpected failures as well.
Sad thing is that neither pgbouncer's mailing list seems very active , despite having subscribed successfully it still fails to accept my emails, nor do I see the project at github very active, or any announcement for a new release soon .
So I don't really know how to contact the pgbouncer community.
On 01/11/2017 14:55, Dave Cramer wrote:yes, and with a bit of work you should be able to port the changes over to the current pgbouncerOn 31 October 2017 at 07:52, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 13:08, Dave Cramer wrote:Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-dPivotal has a branch of pgbouncer that supports LDAP.b/pgbouncer On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Ok let me see if I can nudge someone
On Nov 16, 2017 7:22 AM, "Achilleas Mantzios" <achill@matrix.gatewaynet.com> wrote:
Had tried that as well. No reaction. IMHO for a project that is supposed to be so vital (like in "but if you have a pgbouncer in front of PostgreSQL (you should) and..." there should be more activity. I mean, not having pgbouncer is supposed to be bad, but OTOH making pgbouncer work in a (semi-)enterprise environment (LDAP/search_path based multi-tenancy) looks like a journey in the mid ocean.On 16/11/2017 12:15, Dave Cramer wrote:just provide a PR for pgbouncer here https://github.com/pgbouncer/pgbouncer On 16 November 2017 at 03:30, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:Hello again,
After looking at pgbouncer master branch, which supports PAM authentication and indirectly LDAP, I decided to give it a go. Also I had to do some minor patches to both the backend's GUC code, and pgbouncer in order to respect search_path and make it usable with transaction_mode (which IMHO is the main benefit of using pgbouncer vs the competition) - + it is the best bet closest to re-authentication, which postgresql does not support out of the box (and is fairly complicated to do so).
After all those (not so trivial) changes I tested pgbouncer with my app, and I got sort of promising results, but also had a few unexpected failures as well.
Sad thing is that neither pgbouncer's mailing list seems very active , despite having subscribed successfully it still fails to accept my emails, nor do I see the project at github very active, or any announcement for a new release soon .
So I don't really know how to contact the pgbouncer community.
On 01/11/2017 14:55, Dave Cramer wrote:yes, and with a bit of work you should be able to port the changes over to the current pgbouncerOn 31 October 2017 at 07:52, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 13:08, Dave Cramer wrote:Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-dPivotal has a branch of pgbouncer that supports LDAP.b/pgbouncer On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Did you actually submit a PR ? I can't see one with your name on it ?
On 16 November 2017 at 07:31, Dave Cramer <pg@fastcrypt.com> wrote:
Ok let me see if I can nudge someoneOn Nov 16, 2017 7:22 AM, "Achilleas Mantzios" <achill@matrix.gatewaynet.com> wrote:Had tried that as well. No reaction. IMHO for a project that is supposed to be so vital (like in "but if you have a pgbouncer in front of PostgreSQL (you should) and..." there should be more activity. I mean, not having pgbouncer is supposed to be bad, but OTOH making pgbouncer work in a (semi-)enterprise environment (LDAP/search_path based multi-tenancy) looks like a journey in the mid ocean.On 16/11/2017 12:15, Dave Cramer wrote:just provide a PR for pgbouncer here https://github.com/pgbouncer/pgbouncer On 16 November 2017 at 03:30, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:Hello again,
After looking at pgbouncer master branch, which supports PAM authentication and indirectly LDAP, I decided to give it a go. Also I had to do some minor patches to both the backend's GUC code, and pgbouncer in order to respect search_path and make it usable with transaction_mode (which IMHO is the main benefit of using pgbouncer vs the competition) - + it is the best bet closest to re-authentication, which postgresql does not support out of the box (and is fairly complicated to do so).
After all those (not so trivial) changes I tested pgbouncer with my app, and I got sort of promising results, but also had a few unexpected failures as well.
Sad thing is that neither pgbouncer's mailing list seems very active , despite having subscribed successfully it still fails to accept my emails, nor do I see the project at github very active, or any announcement for a new release soon .
So I don't really know how to contact the pgbouncer community.
On 01/11/2017 14:55, Dave Cramer wrote:yes, and with a bit of work you should be able to port the changes over to the current pgbouncerOn 31 October 2017 at 07:52, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 13:08, Dave Cramer wrote:Great news Dave, thanx, you mean this repo right ? : https://github.com/greenplum-dPivotal has a branch of pgbouncer that supports LDAP.b/pgbouncer On 31 October 2017 at 07:00, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:On 31/10/2017 12:56, Álvaro Hernández Tortosa wrote:That would be a blessing if pgbouncer supported LDAP .
On 31/10/17 10:08, Achilleas Mantzios wrote:Hello Vladimir, thanx
On 31/10/2017 10:30, Vladimir Sitnikov wrote:Achilleas>So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.I know, but we still risk having our max_connections exceeded. And this is not scaleable.
Pools can shrink, so you do not have to raise total number of connections to 1000 unless you truly expect 1000 concurrent connections.
That's true, but if you have a pgbouncer in front of PostgreSQL (you should) and the connections are used wisely (i.e. they are returned as soon as they finish the job, they don't sit idle) this is no longer a problem.
Álvaro
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt