pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLimits user connection in pgbouncer
16+ messages / 5 participants
[nested] [flat]
* Limits user connection in pgbouncer
@ 2021-12-13 08:05 Daulat <daulat.dba@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Daulat @ 2021-12-13 08:05 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Hello Team,
How can we limit the user to make a specific number of connections to the
database?
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Limits user connection in pgbouncer
@ 2021-12-13 08:42 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Daulat <daulat.dba@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2021-12-13 08:42 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* RE: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-13 12:57 Godfrin, Philippe E <Philippe.Godfrin@nov.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 16+ messages in thread
From: Godfrin, Philippe E @ 2021-12-13 12:57 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
-----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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-13 13:07 MichaelDBA <MichaelDBA@sqlexec.com>
parent: Godfrin, Philippe E <Philippe.Godfrin@nov.com>
0 siblings, 1 reply; 16+ messages in thread
From: MichaelDBA @ 2021-12-13 13:07 UTC (permalink / raw)
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-14 07:27 Daulat <daulat.dba@gmail.com>
parent: MichaelDBA <MichaelDBA@sqlexec.com>
0 siblings, 2 replies; 16+ messages in thread
From: Daulat @ 2021-12-14 07:27 UTC (permalink / raw)
To: MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
>
>
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-14 13:45 Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
parent: Daulat <daulat.dba@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Michaeldba@sqlexec.com @ 2021-12-14 13:45 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; +Cc: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org
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.
>>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-14 16:25 Daulat <daulat.dba@gmail.com>
parent: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
0 siblings, 1 reply; 16+ messages in thread
From: Daulat @ 2021-12-14 16:25 UTC (permalink / raw)
To: Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>; +Cc: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
>>
>>
>>
^ permalink raw reply [nested|flat] 16+ messages in thread
* RE: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-14 16:41 Godfrin, Philippe E <Philippe.Godfrin@nov.com>
parent: Daulat <daulat.dba@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Godfrin, Philippe E @ 2021-12-14 16:41 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; MichaelDBA <MichaelDBA@sqlexec.com>; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
>
>
>From: Daulat daulat.dba@gmail.com<mailto:daulat.dba@gmail.com>
>Sent: Tuesday, December 14, 2021 1:28 AM
>To: MichaelDBA MichaelDBA@sqlexec.com<mailto:MichaelDBA@sqlexec.com>
>Cc: Godfrin, Philippe E Philippe.Godfrin@nov.com<mailto:Philippe.Godfrin@nov.com>; Achilleas Mantzios achill@matrix.gatewaynet.com<mailto:achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org<mailto: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<mailto: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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-14 17:31 Daulat <daulat.dba@gmail.com>
parent: Godfrin, Philippe E <Philippe.Godfrin@nov.com>
0 siblings, 1 reply; 16+ messages in thread
From: Daulat @ 2021-12-14 17:31 UTC (permalink / raw)
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-14 17:35 Daulat <daulat.dba@gmail.com>
parent: Daulat <daulat.dba@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Daulat @ 2021-12-14 17:35 UTC (permalink / raw)
To: Godfrin, Philippe E <Philippe.Godfrin@nov.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-15 09:28 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Daulat <daulat.dba@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Achilleas Mantzios @ 2021-12-15 09:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 <mailto:Michaeldba@sqlexec.com> <Michaeldba@sqlexec.com <mailto: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 <mailto: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 <mailto: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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-15 11:56 Daulat <daulat.dba@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 2 replies; 16+ messages in thread
From: Daulat @ 2021-12-15 11:56 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
>
Attachments:
pgbouncer.ini (application/octet-stream, 10.1K) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-15 12:49 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Daulat <daulat.dba@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Achilleas Mantzios @ 2021-12-15 12:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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@matrix.gatewaynet.com <mailto: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 <mailto:Michaeldba@sqlexec.com> <Michaeldba@sqlexec.com <mailto: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 <mailto: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 <mailto: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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-15 13:19 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Daulat <daulat.dba@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Achilleas Mantzios @ 2021-12-15 13:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 .
Read this : http://www.pgbouncer.org/config.html
max_db_connections is a generic not a db-specific setting, so adjust your ini file accordingly.
>
> 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 <mailto: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 <mailto:Michaeldba@sqlexec.com> <Michaeldba@sqlexec.com <mailto: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 <mailto: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 <mailto: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
^ permalink raw reply [nested|flat] 16+ messages in thread
* RE: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-15 19:57 Godfrin, Philippe E <Philippe.Godfrin@nov.com>
parent: Daulat <daulat.dba@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Godfrin, Philippe E @ 2021-12-15 19:57 UTC (permalink / raw)
To: Daulat <daulat.dba@gmail.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
;; 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 userOn Tue, Dec 14, 2021 at 11:01 PM Daulat <daulat.dba@gmail.com<mailto:daulat.dba@gmail.com>> wrote:I am getting below an error message while
<https://us.report.cybergraph.mimecast.com/alert-details/?dep=ThT1XVzCS2%2FMTxt3YxY9nw%3D%3DHsoI7crpQ...;
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<mailto: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<mailto: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<mailto:Philippe.Godfrin@nov.com>> wrote:
>
>
>From: Daulat daulat.dba@gmail.com<mailto:daulat.dba@gmail.com>
>Sent: Tuesday, December 14, 2021 1:28 AM
>To: MichaelDBA MichaelDBA@sqlexec.com<mailto:MichaelDBA@sqlexec.com>
>Cc: Godfrin, Philippe E Philippe.Godfrin@nov.com<mailto:Philippe.Godfrin@nov.com>; Achilleas Mantzios achill@matrix.gatewaynet.com<mailto:achill@matrix.gatewaynet.com>; pgsql-admin@lists.postgresql.org<mailto: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<mailto: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<http://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<http://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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: [EXTERNAL] Re: Limits user connection in pgbouncer
@ 2021-12-16 09:38 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Godfrin, Philippe E <Philippe.Godfrin@nov.com>
0 siblings, 0 replies; 16+ messages in thread
From: Achilleas Mantzios @ 2021-12-16 09:38 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.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 <mailto: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 <mailto: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 <mailto:Philippe.Godfrin@nov.com>> wrote:
>
> >
>
> >
>
> >From: Daulat daulat.dba@gmail.com <mailto:daulat.dba@gmail.com>
>
> >Sent: Tuesday, December 14, 2021 1:28 AM
>
> >To: MichaelDBA MichaelDBA@sqlexec.com <mailto:MichaelDBA@sqlexec.com>
>
> >Cc: Godfrin, Philippe E Philippe.Godfrin@nov.com <mailto:Philippe.Godfrin@nov.com>; Achilleas Mantzios achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>;
> pgsql-admin@lists.postgresql.org <mailto: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 <mailto: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 <http://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 <http://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
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2021-12-16 09:38 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-13 08:05 Limits user connection in pgbouncer Daulat <daulat.dba@gmail.com>
2021-12-13 08:42 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2021-12-13 12:57 ` Godfrin, Philippe E <Philippe.Godfrin@nov.com>
2021-12-13 13:07 ` MichaelDBA <MichaelDBA@sqlexec.com>
2021-12-14 07:27 ` Daulat <daulat.dba@gmail.com>
2021-12-14 13:45 ` Michaeldba@sqlexec.com <Michaeldba@sqlexec.com>
2021-12-14 16:25 ` Daulat <daulat.dba@gmail.com>
2021-12-15 09:28 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2021-12-15 11:56 ` Daulat <daulat.dba@gmail.com>
2021-12-15 12:49 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2021-12-15 13:19 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2021-12-14 16:41 ` Godfrin, Philippe E <Philippe.Godfrin@nov.com>
2021-12-14 17:31 ` Daulat <daulat.dba@gmail.com>
2021-12-14 17:35 ` Daulat <daulat.dba@gmail.com>
2021-12-15 19:57 ` Godfrin, Philippe E <Philippe.Godfrin@nov.com>
2021-12-16 09:38 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox