A connection per tenant might cause you to reach your database’s connection limit rather quickly. On top of this, AR isn’t exactly fast at establishing new connections.
The short version is that you should look at the Apartment gem’s Postgres schema switching support if you’re on a small database, or stick with the regular foreign key scoping (ie tenant_id on the records that belong to a particular tenant) if you’re on a large databases.
The problem lies in that if you aren’t connected as a specified user, you can’t use the databases built in features to secure the tenants data like Row Level Security. Or even secure the schema per customer. Simply because the database, postgres in this case, doesn’t know who you are and therefore cant enforce security rules.
Therefore each tenant should have its own db user.
Even if you reach the database connections limit, thats up for the database layer to solve. Rails shouldn’t be in the way of this.
Allowing a connection pool per client is definitely a wrong way of do this, given the high overhead of PG backends and connection limits.
I believe there are other ways to accomplish this even with row level security. For example, you could set a per-connection variable in PG on connection checkout and have your row security policies check that variable rather than the current DB user. Not only will this give you far greater performance, it will also be far more versatile.
The only time you should have different DB users per customer is if you’re running a separate app layer instance(s) per customer. Otherwise you’re creating a flawed design that will bit you later on. Database users exist to restrict at a lower level than multi-tenant-per-app policies; they’re about securing the database for different use cases (such as some apps only needing to write to certain tables, or a reporting user/app only having read access, etc.) I believe you’re misunderstanding the purpose of database level users.
Also, pro tip: you’re more likely to get help if you don’t demand a premade solution and and instead demonstrate that you’ve already researched this yourself. Google is your friend, but you don’t really seem to have used it.
If everything in the whole world were done perfectly, nothing would ever be vulnerable to anything. But out here in the real world where murphy’s law exists, an attitude of “defense in depth” is more practical, in addition to doing one’s very best to be as perfect as possible.
We’ve done it using MySQL views. You construct all tenant-dependent views with a tenant_id column and set the formula to check against a session variable. We put insert triggers to set the tenant_id initially and an update trigger to throw an error if someone tried to change it (they can’t through our interface but just for security). The views are named like “users” and the data tables are named like “users_data”. AR doesn’t care if it’s pulling from a view, so it thinks it’s just doing a normal query, but since it’s accessing the view it’s impossible for it to get data from other tenants.
Probably not the best approach from a speed perspective, but it has scaled really well for us and we only have to maintain one schema. Additionally we never have to mess with connections because each app server (we run passenger) has its own database connection which means its own session variable which means its own scoping. You just set the variable at the very beginning of each request (for us it’s based on subdomain).