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.