E! [inputs.pgbouncer] Error in plugin: ERROR: unrecognized configuration parameter "stats" (SQLSTATE 42704)
Hi guys, for some reason, my pgbouncer installation keeps spamming this message in the logs lots of time per second (my log is more than 4gb only of this by now):
2021-02-24 17:21:30.731 UTC  LOG S-0x55a5fa41b060: firstname.lastname@example.org:5437 new connection to server (from 127.0.0.1:44832) 2021-02-24 17:21:30.738 UTC  LOG S-0x55a5fa41b060: email@example.com:5437 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1 2021-02-24 17:21:30.839 UTC  LOG S-0x55a5fa41ac00: firstname.lastname@example.org:5437 closing because: evicted (age=2s)
What does the
evicted closing reason mean? It seems like every connection is being closed right after its creation.
max_user_connectionsis reached and an idle connection is closed to make room for a new connection. It's not really a problem by itself, but if it happens too often, then of course it wastes resources. The fix, if any, is to adjust some of the
max_*settings or pool sizes.
auth_type = md5and my auth_file uses "md5xxxxxx" syntax? does pgbouncer pass the straight up "md5xxxxxx" hash to the DB node? or does pgbouncer pass the original password value to postgres? (any link to src code would also be helpful). thank you so much
max_user_connectionsthis means that the issue would be my
max_db_connections = 20right? In my case, I have 3 users that can use the pgbouncer pool to access the database. So if I understood it correctly, the issue is that they would compete with each other for these 20 DB connections, pgbouncer would need to evict these connections a lot.
max_db_connectionsbut this would mean that I would need to increase my connections in the database configuration too. And the whole point I started using pgbouncer was to decrease that number because of memory constraints.
Hey gang! I've been tasked with limiting the number of connections for a specific user when their application goes bananas and eats all the connections, so that other users can continue to access the database.
This is to be done during an incident, so I'm working on an SOP.
max_user_connections and figured that's what I'm after, however when I set:
[users] user1 = max_user_connections=2
and send SIGHUP to pgbouncer, that user can no longer log in:
psql: ERROR: password authentication failed
The config file:
[databases] %include /etc/pgbouncer/something-staging/databases.plain.ini %include /etc/pgbouncer/something-staging/databases.secret.ini [users] %include /etc/pgbouncer/something-staging/users.ini [pgbouncer] admin_users = pgbouncer-stats application_name_add_host = 1 auth_file = /etc/pgbouncer/something-staging/userlist.txt auth_hba_file = /etc/pgbouncer/something-staging/hba.conf auth_type = hba auth_user = pgbouncer ... max_client_conn = 500 pool_mode = session
It seems that I cannot set
[users] without clobbering their password.
Does anyone know how to limit connections for a specific user (using
auth_type = hba?)
auth_usersettings in 1.15.0 but I don't know if they're related (thanks for looking in to this btw :+1:)
Hello, me again - asking my question a different way:
max_user_connectionsfor particular badly behaving applications/users
It seems the relevant code for user authentication is at https://github.com/pgbouncer/pgbouncer/blob/master/src/client.c#L333-L360
Under normal circumstances
find_user() will return
NULL and PgBouncer will hit the backend server to get user information :+1: .
When a user is defined in
user_app1 = max_user_connections=10 then
find_user() will return this as the user, and PgBouncer will reject the authn request, probably because that user struct/object has no password.
[users]section that would make PgBouncer pull credentials from the backend server and apply
max_user_connectionsback to PgBouncer that PgBouncer will enforce?
Thanks for reading my second wall of text :smile:
I am using CentOS 7 and running multiple pgbouncer on the same port for TCP/IP connections using the so_reuseport option.
I have started 4 pgbouncer processes on the same host and port. The problem is that when logging in as admin user (pgbouncer user) to check active pools or clients, I am not able to determine which 1 of the 4 processes I am connected to. Is there a way to specify which processes of the pgbouncer I'm connecting to?
1 - I'm connecting to different processes if I log in as admin multiple times. Just not able to determine which one.
2 - I know this as the show servers; command gives different output for different logins.
@Vruttant1403 You need to create a separate socket that is not shared between the instances, for example a Unix-domain socket, or use systemd socket activation to create additional sockets. That is described in the blog post you linked to.
@petere I tried that but due to some issue with RHEL 7 systemd version, its not running on the same socket and giving an error saying socket is in use when using unix domain socket. So, to run it currently I have disabled unix socket in the pgbouncer.ini file. Is there a workaround for this?