I once implemented RLS/Postgres for Django.
It worked pretty well.
The basic mechanism was to intercept all outbound SQL queries and wrap them in postgres environment variables that set up the RLS.
Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
51–60 of 123 posts
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#52I use this for a startup in a re-write of their solution. It simplifies my queries and mutations, and security concerns. It also drammatically reduces the complexity of my code. There's also ROLES (Guest/Public user, Authenticated, Admin) and combinding the roles with Row Level Security.
I like it so much I don't want to go back!
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#53This is such a killer feature in PG, my new job uses it and it makes audits of our tenancy model dead simple. Coming from a SaaS company that used MySQL, we would get asked by some customers how we guarantee we segmented their data, and it always ended at the app layer. One customer (A fortune 10 company) asked if we could switch to SQL Server to get this feature... Our largest customers ask how we do database multi-…
Every B2B client who asked us how we handle multi-tenancy also asked how we ensure their data is erased at the end of the contract. Using a shared database with RLS means you have to go through all DB backups, delete individual rows for that tenant, then re-generate the backup. That’s a non-starter, so we opted for having one DB per tenant which also makes sharding, scaling, balancing, and handling data-residency cha…
It makes BI work an absolute hellscape as well. Tradeoffs.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#54As the developer of an external authorization system (full disclosure)[0], I feel obligated to chime in the critiques of external authorization systems in this article. I don't think they're far off base, as we do recommend RLS for use cases like what the article covers, but anyways, here's my two cents: 1+2: Cost + Unnecessary complexity: this argument can be used against anything that doesn't fit the given use case…
The blog explicitly said that if the requirements involve actual authorization models (beyond simple tenancy) then RLS is not the best fit (see: https://thenile.dev/blog/multi-tenant-rls#if-you-have-sophis... ). I think this covers both the complexity aspect and the difference between what you get from RLS and what external authz brings to the table (schema, for example). I do think that RLS is a great way for a comp…
Btw that's a localhost link
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#55Earlier quoted context omitted.
I filled out a ton of enterprise questionnaires on this stuff before and we just told people that it would be deleted when the backups expired after X days because we didn't have the capability to delete specific rows from our backups. Nobody ever argued. There's not a single customer I've ever run across who's going to halt a contract because you can't purge their data from your backups fast enough. They're signing…
i did the same with the same results
thirded
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#56Earlier quoted context omitted.
Every B2B client who asked us how we handle multi-tenancy also asked how we ensure their data is erased at the end of the contract. Using a shared database with RLS means you have to go through all DB backups, delete individual rows for that tenant, then re-generate the backup. That’s a non-starter, so we opted for having one DB per tenant which also makes sharding, scaling, balancing, and handling data-residency cha…
I filled out a ton of enterprise questionnaires on this stuff before and we just told people that it would be deleted when the backups expired after X days because we didn't have the capability to delete specific rows from our backups. Nobody ever argued. There's not a single customer I've ever run across who's going to halt a contract because you can't purge their data from your backups fast enough. They're signing…
This is the way -- also never had an issue across US healthcare and enterprise SaaS.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#57Earlier quoted context omitted.
I filled out a ton of enterprise questionnaires on this stuff before and we just told people that it would be deleted when the backups expired after X days because we didn't have the capability to delete specific rows from our backups. Nobody ever argued. There's not a single customer I've ever run across who's going to halt a contract because you can't purge their data from your backups fast enough. They're signing…
i did the same with the same results
same. but! it’s a liability nonetheless, go talk with legal etc
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#58Earlier quoted context omitted.
Every B2B client who asked us how we handle multi-tenancy also asked how we ensure their data is erased at the end of the contract. Using a shared database with RLS means you have to go through all DB backups, delete individual rows for that tenant, then re-generate the backup. That’s a non-starter, so we opted for having one DB per tenant which also makes sharding, scaling, balancing, and handling data-residency cha…
How do you manage your backend in this case? Do you have an insurance of backend for each customer or do you allow backend to make connections to all the DBs. I'm interested in doing similar and wondering about the best way to handle the routing between the databases from a single backend.
It really depends on your requirements, both functional and cost. Having a full stack per customer can be great for a lot of reasons. It's probably the safest because you never have to worry about something getting messed up in the code and creating cross-customer access. Once you're sure the environment is set up correctly you can sleep well at night. You also don't have to worry about one customer doing something to impact the performance of other environments (unless you're using shared infra, like putting all your DBs on a single cluster). And it can make maintenance easier, for example you can data migrations can start with small and/or less important customers for practice. It also can give you more flexibility if you need to make special snowflakes (i.e. some big customer wants weird IP whitelisting rules that can't work with your normal env setup).
Downsides are that it's probably more expensive and more work. Even if your infra spin up is totally automated, you still need to keep track of all the environments, you still need to keep your Infrastructure-as-Code (e.g. your terraform scripts) up to date, more can go wrong when you make changes, there's more chance for environments to drift.
So, in short, separate stacks usually means more safety & simpler application architecture in exchange for more cost and more effort to manage the fleet.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#59Be aware when using RLS with views: By default the RLS policy will be executed with the permissions of the owner of the view instead with the permissions of the user executing the current query. This way it can easily happen that the RLS policy will be bypassed because the owner of the view is a admin account or the same account that owns the underlying table (see the the gotchas section of the original post).
However, upcoming PostgreSQL 15 adds support for security invoker views: https://github.com/postgres/postgres/commit/7faa5fc84bf46ea6... That means you can then define the security_invoker attribute when creating a view and this "... causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner" (see https://www.postgresql.org/docs/15/sql-createview.html) PG15 beta 1 release notes: https://www.postgresql.org/about/news/postgresql-15-beta-1-r...
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#60We're currently using the schema-per-tenant, and it's working very well for us: * No extra operational overhead, it's just one database * Allows to delete a single schema, useful for GDPR compliance * Allows to easily backup/restore a single schema * Easier to view and reason about the data from an admin point of view * An issue in a single tenant doesn't affect other tenants * Downtime for maintenance is shorter (e.…
We ran a multi-tenant SaaS product for years w/ a schema-per-tenant approach. For the most part it all worked pretty great.
We ran into issues here and there but always found a way to work around them:
* Incremental backups were a pain because of needing to lock so many objects (# of schemas X # of tables per schema).
* The extra code to deal w/ migrations was kinda messy (as you mentioned).
* Globally unique IDs become the combination of the row ID + the tenant ID, etc...
For us though the real deal-breaker turned out to be that we wanted to have real foreign keys pointing to individual rows in tenant schemas from outside of the tenant schema and we couldn't. No way to fix that one since with multi-schema the "tenant" relies on DB metadata (the schema name).
We ended up migrating the whole app to RLS (which itself was a pretty interesting journey). We were afraid of performance issues since the multi-schema approach kinda gives you partitioning for free, but with the index usage on the RLS constraints we've had great performance (at least for our use case!).
After quite a bit of time working with both multi-schema & RLS I probably wouldn't go back to multi-schema unless I had a real compelling reason to do so due to the added complexity. I really liked the multi-schema approach, and I think most of the critiques of it I found were relatively easy to work around, but RLS has been a lot simpler for us.