Обсуждение: Limits user connection in pgbouncer

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.

>
> Suppose, I have a user "user1" and I want to limit it to make only 2 connections. I have tried but not done.
>
> Here are my pgbouncer.ini options.
>
> 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
>
> Thanks


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

-----Original Message-----
From: Achilleas Mantzios <achill@matrix.gatewaynet.com> 
Sent: Monday, December 13, 2021 2:43 AM
To: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: Limits user connection in pgbouncer

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.

>
> Suppose, I have a user "user1" and I want to limit it to make only 2 connections. I have tried but not done.
>
> Here are my pgbouncer.ini options.
>
> 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
>
> Thanks


Take a peek in the delivers pgbouncer.ini file:

;; User-specific configuration
[users]
#postgres=pool_mode=sessio
my_useracct=pool_mode=session max_user_connections=6
your_useracct = pool_mode=transaction max_user_connections=2

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.



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> 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.

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

On Tue, Dec 14, 2021 at 7:16 PM Michaeldba@sqlexec.com <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> 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> 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

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@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 <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> 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> 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

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@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 <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> 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> 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

;; User-specific configuration

[users]

#postgres=pool_mode=session

testuid=pool_mode=session max_user_connections=2

 

 

 

From: Daulat <daulat.dba@gmail.com>
Sent: Tuesday, December 14, 2021 11:35 AM
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; pgsql-admin <pgsql-admin@lists.postgresql.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.dba@gmail.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.dba@gmail.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.Godfrin@nov.com> wrote:

> 

> 

>From: Daulat daulat.dba@gmail.com

>Sent: Tuesday, December 14, 2021 1:28 AM

>To: MichaelDBA MichaelDBA@sqlexec.com

>Cc: Godfrin, Philippe E Philippe.Godfrin@nov.com; Achilleas Mantzios achill@matrix.gatewaynet.com; pgsql-admin@lists.postgresql.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@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.

> 

> 

 

Works for me, with these relevant parameters:

postgres@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@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@] # \conninfo

You are connected to database "tsdb" as user " testuid " on host "localhost" (address "127.0.0.1") at port "6444".

[tsdb@] #

**************************************************************************************************

 

postgres@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@] # \conninfo

You are connected to database "tsdb" as user " testuid " on host "localhost" (address "127.0.0.1") at port "6444".

[tsdb@] #

**************************************************************************************************

 

postgres@tstudb01:/postgres/admin/pgb=> sq -p6444 - testuid -dtsdb

Pager usage is off.

**************************************************************************************************

 

postgres@tstudb01:~=> sq -p6444 - testuid -dtsdb

Pager usage is off.

 

**************************************************************************************************

NOTE:

Looking at active processes does not tell you the whole story:

 

postgres@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