From: | Daulat <daulat(dot)dba(at)gmail(dot)com> |
---|---|
To: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: [EXTERNAL] Re: Limits user connection in pgbouncer |
Date: | 2021-12-15 11:56:54 |
Message-ID: | CALfOM6LT+pFcPopSKwpzCyGGZjbRjo6YFsFS4eWZd9eEH5_+jQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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).
On Wed, Dec 15, 2021 at 2:58 PM Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)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(at)sqlexec(dot)com <
> Michaeldba(at)sqlexec(dot)com> wrote:
>
>> Do it under the databases section
>>
>> Sent from my iPad
>>
>> On Dec 14, 2021, at 2:28 AM, Daulat <daulat(dot)dba(at)gmail(dot)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(at)sqlexec(dot)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
>
>
Attachment | Content-Type | Size |
---|---|---|
pgbouncer.ini | application/octet-stream | 10.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Daulat | 2021-12-15 11:58:23 | Re: ERROR: could not open file "pg_tblspc |
Previous Message | Achilleas Mantzios | 2021-12-15 09:28:28 | Re: [EXTERNAL] Re: Limits user connection in pgbouncer |