Re: [EXTERNAL] Re: Limits user connection in pgbouncer

is this entry " testuid=pool_mode=session max_user_connections=2 " under
databases section ?

or under

;; access to dest database will go with single user

On Tue, Dec 14, 2021 at 11:01 PM Daulat <daulat(dot)dba(at)gmail(dot)com> wrote:

> I am getting below an error message while making changes.
>
> 2021-12-14 17:11:37.230 UTC [5023] ERROR skipping database user1 because
> of unknown parameter in connstring: max_user_connections
>
> .
> [databases]
> ;;postgres = host=localhost port=5432 dbname=postgres
> postgres = host=localhost dbname=postgres
> user1=pool_mode=session max_user_connections=2
>
>
> On Tue, Dec 14, 2021 at 10:44 PM Daulat <daulat(dot)dba(at)gmail(dot)com> wrote:
>
>> Thanks for sharing !
>> If you don't mind . Can you please share your configuration file. I want
>> to see what I am missing.
>> That will help alot !
>>
>> Regards,
>>
>> On Tue, Dec 14, 2021 at 10:11 PM Godfrin, Philippe E <
>> Philippe(dot)Godfrin(at)nov(dot)com> wrote:
>>
>>> >
>>>
>>> >
>>>
>>> >From: Daulat daulat(dot)dba(at)gmail(dot)com
>>>
>>> >Sent: Tuesday, December 14, 2021 1:28 AM
>>>
>>> >To: MichaelDBA MichaelDBA(at)sqlexec(dot)com
>>>
>>> >Cc: Godfrin, Philippe E Philippe(dot)Godfrin(at)nov(dot)com; Achilleas Mantzios
>>> achill(at)matrix(dot)gatewaynet(dot)com; pgsql-admin(at)lists(dot)postgresql(dot)org
>>>
>>> >Subject: Re: [EXTERNAL] Re: Limits user connection in pgbouncer
>>>
>>> >
>>>
>>> >
>>>
>>> >
>>>
>>> >
>>>
>>> >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.
>>>
>>> >
>>>
>>> >
>>>
>>>
>>>
>>> Works for me, with these relevant parameters:
>>>
>>> postgres(at)tstudb01:/postgres/admin/pgb=> grep "^[[:alpha:]]"
>>> pgbo_644401.ini
>>>
>>> testuid=pool_mode=session max_user_connections=2
>>>
>>> pool_mode = transaction
>>>
>>> max_client_conn = 1000
>>>
>>> reserve_pool_size = 50
>>>
>>> reserve_pool_timeout = 5
>>>
>>> max_user_connections = 300
>>>
>>> server_connect_timeout = 15
>>>
>>> server_login_retry = 1
>>>
>>> client_idle_timeout = 0
>>>
>>> client_login_timeout = 30
>>>
>>>
>>>
>>> SESSIONS:
>>>
>>> postgres(at)tstudb01:~=> sq -p6444 - testuid -dtsdb
>>>
>>> Pager usage is off.
>>>
>>> SET
>>>
>>> Time: 0.296 ms
>>>
>>> psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))
>>>
>>> Type "help" for help.
>>>
>>>
>>>
>>> [tsdb(at)] # \conninfo
>>>
>>> You are connected to database "tsdb" as user " testuid " on host
>>> "localhost" (address "127.0.0.1") at port "6444".
>>>
>>> [tsdb(at)] #
>>>
>>>
>>> **************************************************************************************************
>>>
>>>
>>>
>>> postgres(at)tstudb01:~=> sq -p6444 - testuid -dtsdb
>>>
>>> Pager usage is off.
>>>
>>> SET
>>>
>>> Time: 3.105 ms
>>>
>>> psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))
>>>
>>> Type "help" for help.
>>>
>>>
>>>
>>> [tsdb(at)] # \conninfo
>>>
>>> You are connected to database "tsdb" as user " testuid " on host
>>> "localhost" (address "127.0.0.1") at port "6444".
>>>
>>> [tsdb(at)] #
>>>
>>>
>>> **************************************************************************************************
>>>
>>>
>>>
>>> postgres(at)tstudb01:/postgres/admin/pgb=> sq -p6444 - testuid -dtsdb
>>>
>>> Pager usage is off.
>>>
>>>
>>> **************************************************************************************************
>>>
>>>
>>>
>>> postgres(at)tstudb01:~=> sq -p6444 - testuid -dtsdb
>>>
>>> Pager usage is off.
>>>
>>>
>>>
>>>
>>> **************************************************************************************************
>>>
>>> NOTE:
>>>
>>> Looking at active processes does not tell you the whole story:
>>>
>>>
>>>
>>> postgres(at)tstudb01:/postgres/admin/pgb=> psh |grep testuid
>>>
>>> postgres 22980 2899 0 16:26 ? 00:00:00 postgres: unidb01:
>>> testuid tsdb 172.31.1.53(35638) idle
>>>
>>> postgres 23122 20603 0 16:29 pts/4 00:00:00
>>> /usr/lib/postgresql/13/bin/psql -p6444 -dtsdb -hlocalhost -p6444 -Utestuid
>>> -dtsdb
>>>
>>> postgres 23157 2899 0 16:29 ? 00:00:00 postgres: unidb01:
>>> godfrinpe tsdb 172.31.1.53(35806) idle
>>>
>>> postgres 23158 20718 0 16:29 pts/5 00:00:00
>>> /usr/lib/postgresql/13/bin/psql -p6444 -dtsdb -hlocalhost -p6444 - Utestuid
>>> -dtsdb
>>>
>>> postgres 23168 16349 0 16:29 pts/2 00:00:00
>>> /usr/lib/postgresql/13/bin/psql -p6444 -dtsdb -hlocalhost -p6444 - Utestuid
>>> -dtsdb
>>>
>>> postgres 23176 23048 0 16:29 pts/1 00:00:00
>>> /usr/lib/postgresql/13/bin/psql -p6444 -dtsdb -hlocalhost -p6444 - Utestuid
>>> -dtsdb
>>>
>>>
>>>
>>> The idle processes are the only two that are actually connected…
>>>
>>> phil
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>