Using Postgres with a schema per customer. For isolation and security. A different user per schema to limit access. Looking for a way to efficiently pool connections across the schemas.
Have tried to use application level connection pool (Hikari), but I don't see it being able to pool across schemas efficiently. Don't want to hit limits on Postgress connection counts by growing number of connections per schema/user. And in general it does not seem most effective way to pool connections if they grow as a factor of schemas.
Also tried pgbouncer but not sure how to configure it effectively for this purpose. Tried to use Hikari on the application side per customer, and pgbouncer to map these to fewer postgres connections. In session mode, pgbouncer seems to be just acting as a proxy and the number of connections grows in line with each connection from Hikari. In transaction mode pgbouncer and Hikari seem to get out of sync somehow, and I get protocol error messages from Postgres.
The problem seems quite similar to this question from a few years back. Unfortunately, I do not see a clear answer on how to manage this type of connection pooling effectively.
So, potentially having quite a few schemas, as per customer, the question is how to properly do connection pooling for Postgres when using multiple schemas and users?
A different user per schema to limit access.
Is this actually effective? Doesn't your application server need to know how to connect as each user, in order to do its job? If I can trick the app server into showing me data from the wrong schema, couldn't I just as easily trick it into connecting as the wrong user before doing so?
If so, I think these two "layers" of security aren't really independent from each other, so they aren't really two layers.