3 ms·

Using RLS to implement multi-tenancy is a terrible idea. Just deploy a database per tenant. It's not hard. Why overcomplicate it?

bicijay 4y ago
Deploying a database per tenant is not that easy. You have a lot of new overhead, migrations become a pain in the ass (already are) and a lot of other little problems... I would say a database per tenant is overcomplicating it.
- mbreese 4y ago
  A database per tenant makes the rest of the workflow significantly easier though. No need to add clauses to SQL WHERE statements for users/groups. Queries are faster (less data). And data can be moved much easier between servers. Yes, it does add extra overhead at account creation, during DB migrations, and for backups. But if you don’t need cross-account or public data access, it can make life much easier.
  - zie 4y ago
    > No need to add clauses to SQL WHERE statements for users/groups. This is basically what RLS does for you. You specify the access and you specify the current user(via a connection, SET ROLE, etc). Then it does all that complicated query filtering stuff for you, to ensure you don't screw it up. > Queries are faster (less data). And data can be moved much easier between servers. Not really, the overhead is just different(and likely more of it) in your solution. It's not wrong nor is using RLS right.
  - nickjj 4y ago
    > No need to add clauses to SQL WHERE statements for users/groups. ORMs have support to add in these conditions automatically, you only have to define the condition in 1 function. This gives you the best of both worlds. You can use your FK based separation and have application level guarantees that your tables with a tenant id are scoped to a tenant by default and you have the option to query multiple tenants to do super admin level reporting across tenants by using explicit function calls that basically say "yes I know I'm bypassing the auto-enforced tenancy check". There's no performance issues since it's all 1 schema and there's no nightmare or overhead of having to run a DB migration for each database. I'm not sure why the author didn't include this in their blog post because it's a popular option in Rails, Flask (SQLAlchemy), etc..
- Gigachad 4y ago
  You end up building just a bit of automation around it and its fine. The migration isn't any harder, you just run an ansible job or something to roll it out to all databases.
pg_bot 4y ago
Deploying multiple databases is typically costly in the infrastructure as a service space. Plus you have more operational overhead in ensuring backups work and keeping things secure. It's much easier to use Postgres' schemas to segment the data within one single database. Frankly schemas are much easier to reason about, maintain, scale, and keep compliant than row level security.
deleted 4y ago
[deleted]
icedchai 4y ago
I worked for a SaaS that did that. We had 1000's of clients. Migrations would take all afternoon. We had custom connection pools, custom migration runners, and other weird stuff (this was about 10 years ago.) It was way too complicated, especially since most of the tenants had very little data.
jasongi 4y ago
Only really worth it if each tenant is creating enough value to justify this. Hard to see how a SaaS product with a cheap/free tier would pull this off.
- icedchai 4y ago
  We did this for the free tier at a previous company. It was terrible in terms of overhead.

bicijay 4y ago

Deploying a database per tenant is not that easy. You have a lot of new overhead, migrations become a pain in the ass (already are) and a lot of other little problems... I would say a database per tenant is overcomplicating it.

mbreese 4y ago
A database per tenant makes the rest of the workflow significantly easier though. No need to add clauses to SQL WHERE statements for users/groups. Queries are faster (less data). And data can be moved much easier between servers. Yes, it does add extra overhead at account creation, during DB migrations, and for backups. But if you don’t need cross-account or public data access, it can make life much easier.
- zie 4y ago
  > No need to add clauses to SQL WHERE statements for users/groups. This is basically what RLS does for you. You specify the access and you specify the current user(via a connection, SET ROLE, etc). Then it does all that complicated query filtering stuff for you, to ensure you don't screw it up. > Queries are faster (less data). And data can be moved much easier between servers. Not really, the overhead is just different(and likely more of it) in your solution. It's not wrong nor is using RLS right.
- nickjj 4y ago
  > No need to add clauses to SQL WHERE statements for users/groups. ORMs have support to add in these conditions automatically, you only have to define the condition in 1 function. This gives you the best of both worlds. You can use your FK based separation and have application level guarantees that your tables with a tenant id are scoped to a tenant by default and you have the option to query multiple tenants to do super admin level reporting across tenants by using explicit function calls that basically say "yes I know I'm bypassing the auto-enforced tenancy check". There's no performance issues since it's all 1 schema and there's no nightmare or overhead of having to run a DB migration for each database. I'm not sure why the author didn't include this in their blog post because it's a popular option in Rails, Flask (SQLAlchemy), etc..
Gigachad 4y ago
You end up building just a bit of automation around it and its fine. The migration isn't any harder, you just run an ansible job or something to roll it out to all databases.

pg_bot 4y ago

Deploying multiple databases is typically costly in the infrastructure as a service space. Plus you have more operational overhead in ensuring backups work and keeping things secure. It's much easier to use Postgres' schemas to segment the data within one single database. Frankly schemas are much easier to reason about, maintain, scale, and keep compliant than row level security.

deleted 4y ago

[deleted]

icedchai 4y ago

I worked for a SaaS that did that. We had 1000's of clients. Migrations would take all afternoon. We had custom connection pools, custom migration runners, and other weird stuff (this was about 10 years ago.) It was way too complicated, especially since most of the tenants had very little data.

jasongi 4y ago

Only really worth it if each tenant is creating enough value to justify this. Hard to see how a SaaS product with a cheap/free tier would pull this off.

icedchai 4y ago
We did this for the free tier at a previous company. It was terrible in terms of overhead.