Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

thenile.dev

Post reply on HN
Print view View original

41–50 of 123 posts

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#41

Post by HatchedLake721 »

gervwyk wrote: post #9
e1g wrote: post #4

Earlier 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…

Also second this, we even split our AWS org into an AWS account per tentant. Although, this will maybe be a problem if we have +100s of clients. But it makes onboarding and off-loading simple.

It depends on an annual contract value (ACV), doesn't it? You can't give an AWS account to every $99 p/m plan, but you can for enterprise $50-100k+ deals.

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#42

Post by zie »

mbreese wrote: post #38
bicijay wrote: post #16

Earlier quoted context omitted.

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.

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.

> 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.

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#44

Post by galaxyLogic »

paxys wrote: post #25

Is having to write "SELECT [...] WHERE user_id= " really considered a security hole? Isn't that how like every service in existence operates? Coming up with complicated auth systems and patterns just because you are scared you will accidentally skip that WHERE clause seems bizarre to me.

When you say 'user_id' do you mean each end-user of the system or each customer?

I assume you have a few customers and then very many users belonging to each customer.

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#45

Post by jquery_dev »

e1g wrote: post #4
bearjaws wrote: post #2

This 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…

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.

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#46

Post by Scarbutt »

eastbound wrote: post #3
bearjaws wrote: post #2

This 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-…

I honestly don’t understand how Oracle is still alive. Postgres has so many of these killer features. Also, I wonder how others do tenant separation, what other solutions there are.

oracle has flashback

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#47

Post by gervwyk »

HatchedLake721 wrote: post #41
gervwyk wrote: post #9

Earlier quoted context omitted.

Also second this, we even split our AWS org into an AWS account per tentant. Although, this will maybe be a problem if we have +100s of clients. But it makes onboarding and off-loading simple.

It depends on an annual contract value (ACV), doesn't it? You can't give an AWS account to every $99 p/m plan, but you can for enterprise $50-100k+ deals.

Yeah, that sums it up. I guess it means it can't be labelled as "multi-tenant" then..

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#48

Post by gwen-shapira »

jzelinskie wrote: post #13

As 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 company without authz experts to built a multi-tenant MVP safely. I've yet to see a single pre-PMF company that worries about authorization beyond that, this is a series-B concern in my experience.

Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

#49

Post by beckingz »

gav wrote: post #15
mritchie712 wrote: post #5

Earlier quoted context omitted.

Legacy. If you have thousands of lines of code relying on Oracle the cost to migrate would be enormous.

Ignoring the cost, there's the risk/reward alignment you see in large enterprises. Imagine you're a new CIO. You know you're probably looking at a 3-5 year tenure at this new company and you want to lead with some big wins to set the tone and show your value. You're reviewing proposals from your senior leadership. One of the options is an Oracle migration. It could cost a million dollars to migrate, but you'd save a…

Exactly. The risk/cost profile for migrations is bad: If it goes well, decent return. If it goes poorly, catastrophic.
Post reply on HN