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

On 15/12/21 9:57 μ.μ., Godfrin, Philippe E wrote:
>
> ;; User-specific configuration
>
> [users]
>
> #postgres=pool_mode=session
>
> testuid=pool_mode=session max_user_connections=2
>
Those are server connections. The OP asked about app (client) connections.
>
> *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 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
>

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