Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mxQaa-0002S9-Fd for pgsql-admin@arkaria.postgresql.org; Wed, 15 Dec 2021 09:28:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mxQaY-0003UY-5L for pgsql-admin@arkaria.postgresql.org; Wed, 15 Dec 2021 09:28:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mxQaX-0003UO-MY for pgsql-admin@lists.postgresql.org; Wed, 15 Dec 2021 09:28:41 +0000 Received: from hermes.gatewaynet.com ([193.92.121.98]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mxQaU-00088z-1y for pgsql-admin@lists.postgresql.org; Wed, 15 Dec 2021 09:28:40 +0000 Received: from smadev.internal.net (pc131.internal.net [10.9.200.131]) by hermes.gatewaynet.com (8.15.2/8.15.2/Debian-8+deb9u1) with ESMTPS id 1BF9ST12001854 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Wed, 15 Dec 2021 11:28:34 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id 1BF9SSRI098153 for ; Wed, 15 Dec 2021 11:28:29 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: [EXTERNAL] Re: Limits user connection in pgbouncer To: pgsql-admin@lists.postgresql.org References: <5B05AAB3-8FAB-45E2-ABE5-277E4C0902E8@sqlexec.com> From: Achilleas Mantzios Message-ID: Date: Wed, 15 Dec 2021 11:28:28 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------57FB77AC160F93453D8FE1C6" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.102.4 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-26.9 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_NAUTICAL,DYN_HAM_SIGNAT1,HTML_MESSAGE,TW_PG autolearn=unavailable autolearn_force=no version=3.4.2 X-Spam-Checker-Version: SpamAssassin 3.4.2 (2018-09-13) on hermes.gatewaynet.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------57FB77AC160F93453D8FE1C6 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 14/12/21 6:25 μ.μ., Daulat wrote: > still i am able to login more than 2 connections after making changes under the database section . > [databases] > > db1 = host=localhost port=5432 dbname=db1 pool_mode = transaction pool_size=2 max_db_connections=2 user=user1 > > Thanks The point with pgbouncer is to map N user connections to n DB connections where N >> n. So pgbouncer will allow up to max_client_conn in total, but every user (user1) will have max 2 real DB connections to this pgbouncer db (db1). Makes sense? > > On Tue, Dec 14, 2021 at 7:16 PM Michaeldba@sqlexec.com > wrote: > > Do it under the databases section > > Sent from my iPad > >> On Dec 14, 2021, at 2:28 AM, Daulat > wrote: >> >>  >> Hi Michael , >> >> I have tried by setting the below entry under the users section but still I am able to make more than 2 connections. Please suggest what I am missing here ? >> >> [users] >> user1 = pool_mode=transaction max_user_connections=2 >> >> Here are my other details: >> >> [databases] >> db1 = host=localhost dbname=db1 >> >> logfile = /var/log/pgbouncer/pgbouncer.log >> pidfile = /var/run/pgbouncer/pgbouncer.pid >> listen_addr = * >> listen_port = 6432 >> auth_type = md5 >> auth_file = /etc/pgbouncer/userlist.txt >> admin_users = postgres >> pool_mode = session >> max_client_conn = 300 >> max_db_connections = 2 >> max_user_connections = 2 >> >> On Mon, Dec 13, 2021 at 6:38 PM MichaelDBA > wrote: >> >> Hi all, >> >> If you add the user to the database specification, then you can limit connections per user: >> db1 = host=localhost dbname=db1 pool_size=2 user=myuser >> >> Regards, >> Michael Vitale >> >> >> Godfrin, Philippe E wrote on 12/13/2021 7:57 AM: >>> On 13/12/21 10:05 π.μ., Daulat wrote: >>>> Hello Team, >>>> How can we limit the user to  make a specific number of connections to the database? >>> Just specify this in the db definition configuration : >>> >>> db1 = host=localhost dbname=db1 pool_size=2 >>> >>> but it will apply to every user, so that every user's pool will have max 2 connections, not only user1. >>> >>> Alternatively you could set "ALTER ROLE .. CONNECTION LIMIT connlimit " for user1 individually. >> -- Achilleas Mantzios DBA, Analyst, IT Lead IT DEPT Dynacom Tankers Mgmt --------------57FB77AC160F93453D8FE1C6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 14/12/21 6:25 μ.μ., Daulat wrote:
still i am able to login more than 2 connections after making changes under the database section . 
[databases]

db1 = host=localhost port=5432 dbname=db1 pool_mode = transaction pool_size=2 max_db_connections=2 user=user1

Thanks
The point with pgbouncer is to map N user connections to n DB connections where N >> n. So pgbouncer will allow up to max_client_conn in total, but every user (user1) will have max 2 real DB connections to this pgbouncer db (db1).
Makes sense?

On Tue, Dec 14, 2021 at 7:16 PM Michaeldba@sqlexec.com <Michaeldba@sqlexec.com> wrote:
Do it under the databases section

Sent from my iPad

On Dec 14, 2021, at 2:28 AM, Daulat <daulat.dba@gmail.com> wrote:


Hi Michael ,

I have tried by setting the below entry under the users section but still I am able to make more than 2 connections. Please suggest what I am missing here ?

[users]
user1 = pool_mode=transaction max_user_connections=2

Here are my other details:

[databases]
db1 = host=localhost dbname=db1

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
pool_mode = session
max_client_conn = 300
max_db_connections = 2
max_user_connections = 2

On Mon, Dec 13, 2021 at 6:38 PM MichaelDBA <MichaelDBA@sqlexec.com> wrote:
Hi all,

If you add the user to the database specification, then you can limit connections per user:
db1 = host=localhost dbname=db1 pool_size=2 user=myuser

Regards,
Michael Vitale


Godfrin, Philippe E wrote on 12/13/2021 7:57 AM:
On 13/12/21 10:05 π.μ., Daulat wrote:
Hello Team,
How can we limit the user to  make a specific number of connections to the database?
Just specify this in the db definition configuration :

db1 = host=localhost dbname=db1 pool_size=2

but it will apply to every user, so that every user's pool will have max 2 connections, not only user1.

Alternatively you could set "ALTER ROLE .. CONNECTION LIMIT connlimit " for user1 individually. 



-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt
--------------57FB77AC160F93453D8FE1C6--