Connection pooling Postgres with multiple schemas and users

Question

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?

How many customers do you have? How many simultaneous connections per customer? I don't see the schemas as being a problem, but users definitely can be. You essentially need a separate pool for each customer. — jjanes
– jjanes, Commented Mar 21, 2020 at 18:37
Lets say hundreds of customers. But how would one have a pool per customer? Having even a few connections per pool, and hundreds of customers, you would end up with way too many connections. — kg_sYy
– kg_sYy, Commented Mar 21, 2020 at 18:59
Yes, that is the problem with having many hundreds of users. You need a pool for each one as one connection cannot cross users, but you can't actually have a pool for each one. — jjanes
– jjanes, Commented Mar 21, 2020 at 20:01

jjanes · Accepted Answer · 2020-03-21 20:40:05Z

2

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.

answered Mar 21, 2020 at 20:40

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kg_sYy Over a year ago

Yes, you have a point. What would you suggest to do? Use one user and query every schema with that, allowing to pool connections? I think the idea has been to not be able to trick a query itself to display other schemas, as the user would not have access to those schemas.

jjanes Over a year ago

I think the standard methods to prevent SQL injection should defend against that. If you don't trust those methods to be used 100%, then adding the user level does achieve something, but at a high cost. Do you need a pool at all? Often a connection pool is an attempt to paper over some other problem--could you identify and solve it some other way?

Collectives™ on Stack Overflow

Connection pooling Postgres with multiple schemas and users

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related