From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Limits user connection in pgbouncer
Date: Wed, 15 Dec 2021 14:49:33 +0200
Message-ID: <5009534f-3f4a-f59e-eccd-ec750c767796@matrix.gatewaynet.com> (raw)
In-Reply-To: <CALfOM6LT+pFcPopSKwpzCyGGZjbRjo6YFsFS4eWZd9eEH5_+jQ@mail.gmail.com>
References: <CALfOM6JUgfknBvP7q1uB=bSzFA0ARWWBRhSTvsk_yw+tnzKykg@mail.gmail.com>
<5B05AAB3-8FAB-45E2-ABE5-277E4C0902E8@sqlexec.com>
<CALfOM6Ln2qn8EWpD60bXWFypVuJRKKUVpw4nwyKxc2RP=ObkAg@mail.gmail.com>
<ec4eeb46-990e-8858-8eee-6478a8b41825@matrix.gatewaynet.com>
<CALfOM6LT+pFcPopSKwpzCyGGZjbRjo6YFsFS4eWZd9eEH5_+jQ@mail.gmail.com>
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 .
>
> 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).
Unfortunately there is no database-specific tunable in pgbouncer to limit max client connections.
max_user_connections could be used but this would be a hard limit for any user.
>
> On Wed, Dec 15, 2021 at 2:58 PM Achilleas Mantzios <achill@matrix.gatewaynet.com <mailto: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 <mailto:Michaeldba@sqlexec.com> <Michaeldba@sqlexec.com <mailto: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 <mailto: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 <mailto: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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com, pgsql-admin@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Limits user connection in pgbouncer
In-Reply-To: <5009534f-3f4a-f59e-eccd-ec750c767796@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox