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 1mxUBj-0005zF-JM for pgsql-admin@arkaria.postgresql.org; Wed, 15 Dec 2021 13:19:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mxUBi-0002mN-D5 for pgsql-admin@arkaria.postgresql.org; Wed, 15 Dec 2021 13:19:18 +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 1mxUBh-0002mE-QI for pgsql-admin@lists.postgresql.org; Wed, 15 Dec 2021 13:19:18 +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 1mxUBe-0001Te-Eo for pgsql-admin@lists.postgresql.org; Wed, 15 Dec 2021 13:19:16 +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 1BFDJ6tT008891 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Wed, 15 Dec 2021 15:19:11 +0200 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id 1BFDJ6hu009930 for ; Wed, 15 Dec 2021 15:19:06 +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: <32e9b37c-ba06-67df-9b09-4d7fc865e528@matrix.gatewaynet.com> Date: Wed, 15 Dec 2021 15:19:06 +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="------------4F17DA39A79EF3BBE5E55511" Content-Language: en-US X-Virus-Scanned: clamav-milter 0.102.4 at hermes X-Virus-Status: Clean X-Spam-Status: No, score=-35.9 required=5.0 tests=ALL_TRUSTED,DYN_HAM_COORD, DYN_HAM_IT,DYN_HAM_IT_PARTNERS,DYN_HAM_NAUTICAL,DYN_HAM_SIGNAT1, DYN_SPAM_MED6,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. --------------4F17DA39A79EF3BBE5E55511 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 15/12/21 1:56 μ.μ., Daulat wrote: > Thanks  ! > max_connections =100 in postgresql.conf > > I am attaching my .ini file , help where I need to change . Read this : http://www.pgbouncer.org/config.html max_db_connections is a generic not a db-specific setting, so adjust your ini file accordingly. > > The Goal is to limit the number of connections from a specific app - when the db name is db1 and user is user1, only 55 connections are allowed (max_db_connections). > > On Wed, Dec 15, 2021 at 2:58 PM Achilleas Mantzios > wrote: > > 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 > -- Achilleas Mantzios DBA, Analyst, IT Lead IT DEPT Dynacom Tankers Mgmt --------------4F17DA39A79EF3BBE5E55511 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
On 15/12/21 1:56 μ.μ., Daulat wrote:
Thanks  !
max_connections =100 in postgresql.conf

I am attaching my .ini file , help where I need to change .
Read this : http://www.pgbouncer.org/config.html
max_db_connections is a generic not a db-specific setting, so adjust your ini file accordingly.
 
The Goal is to limit the number of connections from a specific app - when the db name is db1 and user is user1, only 55 connections are allowed (max_db_connections).

On Wed, Dec 15, 2021 at 2:58 PM Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
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


-- 
Achilleas Mantzios
DBA, Analyst, IT Lead
IT DEPT
Dynacom Tankers Mgmt
--------------4F17DA39A79EF3BBE5E55511--