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

[Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>]

On 15/12/21 1:56 μ.μ., Daulat wrote:

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

    Unfortunately there is no database-specific tunable in pgbouncer to
    limit max client connections.
    max_user_connections could be used but this would be a hard limit
    for any user.

On Wed, Dec 15, 2021 at 2:58 PM Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxx <Michaeldba@xxxxxxxxxxx> wrote:

Do it under the databases section

Sent from my iPad

On Dec 14, 2021, at 2:28 AM, Daulat <daulat.dba@xxxxxxxxx> 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@xxxxxxxxxxx> 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