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

;; User-specific configuration
[users]
#postgres=pool_mode=session
testuid=pool_mode=session max_user_connections=2

From: Daulat <daulat(dot)dba(at)gmail(dot)com>
Sent: Tuesday, December 14, 2021 11:35 AM
To: Godfrin, Philippe E <Philippe(dot)Godfrin(at)nov(dot)com>; pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: 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 userOn Tue, Dec 14, 2021 at 11:01 PM Daulat <daulat(dot)dba(at)gmail(dot)com<mailto:daulat(dot)dba(at)gmail(dot)com>> wrote:I am getting below an error message while ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌ ‌

<https://us.report.cybergraph.mimecast.com/alert-details/?dep=ThT1XVzCS2%2FMTxt3YxY9nw%3D%3DHsoI7crpQ0bVMWgO%2BFGsANZLSOOloEZX0ak74z%2Fg0kaMIsQeFb9teOlFJgHvbCtW44k0I3Pp02rfc%2BLnC9RtxVj7NfK2eRXkQjx4h40M0H9%2Bk%2FPWY6nPgO9vnyWaN%2FyBAeKGGlOAy%2BWhH9615GOxqYkSZ%2FzrvDawrKaWj07YQUcat8vCjdIBDc9lQ7HVyU%2Fr4X2IwhyAicnwIYOXWtHu6Z5i%2BRO87tDcm9CiveGz6e81F%2FNtSOP89jY%2FPaw7Ha1U%2FbOaYOzluLmc8XZz%2B4uimNqY9OBTU2BrdZwDs%2FUecYKW31B5D64BF%2BBwaTPt8LDC%2BB0bxjTETjaJyoIeH3%2F%2BUyQpxhvLbzx2JjzF3sIMNz5d20hTTWiU5Jb1JLbxLTKa6XkhB1Ydzanx30L4UVnByOLu1NrX%2BxTZ%2FSHwF9wiA5%2BYtL%2BmVqI6tSAajkTjrPb717iheM6AaWK4ES%2Fjx1pQe6uKHGDfH%2BmXkYbYuLFxkM032Hhrxzb1VkPHQ%2B6GwAmr3wp93t9VGAXlJSZ7jLntkhYLeIYYLLjohe%2BGfk4jC7IRQwa%2FBuYySvyxzGzf40WkG%2FD7op%2BzT4h8PDaSkohbXbQDIfSy9qyPyCzmxfQXaHvjMxv5e9AlnWGNe4xL0z9QdBqx7yD2vt32iwmRx6189EdTRdc8zuixexP17TusISQ%3D>
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<mailto: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<mailto: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<mailto:Philippe(dot)Godfrin(at)nov(dot)com>> wrote:
>
>
>From: Daulat daulat(dot)dba(at)gmail(dot)com<mailto:daulat(dot)dba(at)gmail(dot)com>
>Sent: Tuesday, December 14, 2021 1:28 AM
>To: MichaelDBA MichaelDBA(at)sqlexec(dot)com<mailto:MichaelDBA(at)sqlexec(dot)com>
>Cc: Godfrin, Philippe E Philippe(dot)Godfrin(at)nov(dot)com<mailto:Philippe(dot)Godfrin(at)nov(dot)com>; Achilleas Mantzios achill(at)matrix(dot)gatewaynet(dot)com<mailto:achill(at)matrix(dot)gatewaynet(dot)com>; pgsql-admin(at)lists(dot)postgresql(dot)org<mailto: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<mailto: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<http://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<http://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