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.

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.