Can I have a PgBouncer connection pool per db instead of per role/db pair?

Question

Users of my web app can create kubernetes jobs from the web app's UI. Each user has a dedicated role and password in a postgres database (hosted outside of the k8s cluster). Having a dedicated role and password is necessary because users provide a script that is executed in a job. A malicious user could read the env vars in a pod that stores role and password. So, every user has their own.

When a job is created, that job can read and write to postgres via pgbouncer, using user's pg role and password. I am using auth_query for auth:

[databases]
datastore1 = host=<some_ip> port=5432 dbname=postgres
datastore2 = host=<some_ip> port=5432 dbname=postgres
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer_auth_user
auth_query = SELECT p_user, p_password FROM public.lookup(1ドル)
pool_mode = transaction

There are hundreds of web app users and they can create a lot of jobs. I was hoping that pgbouncer would help with reducing connections but as I understand, a pool is created only for a unique role/database pair. I was hoping to have a pool per database so that all users would share it.

Is there a way to achieve my aim? If not, what do you recommend? I am new to pgbouncer so my assumptions may be wrong and I'd be happy to be shown the right way of doing things.

---

Every pod is guaranteed to read or write from postgres. I want to avoid opening a new connection every time a pod is created. At any given time, hundreds of users are running around 10 jobs each.

All users don't share the same permissions.

I have not hit max_connections errors because I have not yet used pgbouncer and postgres. I am trying to add persistent storage functionality to k8s jobs as we speak.

Currently a lot of jobs are running without persistence. I want to introduce postgres with pgbouncer to be ready for a lot of concurrent connections.

The reason for using postgres is that the scripts that run as jobs are written in python and are going to use a dict-like wrapper over a postgres table. So a table would be a key-val store with transaction support.

I can't hardcode credentials in userlist.txt because pg roles are dynamically created from the web app. Maybe this will make the problem clearer: Every web app user has its own pg role which owns one dedicated table. So, a job created by the web app user can only write to and read from that table.

• mustaccio: This sounds more and more like an X-Y problem. Why don't you rephrase the question to explain what you're trying to achieve (which is now spread across multiple comments) instead of how you're trying to achieve that. Something like "I will have a lot of users and a lot of connections; I want to persist each user's state in the database in such a way that no user can access what's not theirs, and I will need a connection pool in front of the database".

Answer 1

Can I have a PgBouncer connection pool per db instead of per role/db pair?

No. You need a role (user) name to establish a database connection. The purpose of the connection pool, such as PgBouncer, is to keep established database connections and reuse them, instead of opening a new one each time.

While you can change a user's privilege level in an existing session using SET ROLE, this does not remove the requirement to supply authentication credentials when establishing the connection.