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

61–70 of 123 posts

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

#61

Post by e1g »

brightball wrote: post #29
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…

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…

A recent example from the other side: a client contacts me and says they will have to exit from our existing contract unless we can update our (AWS) infrastructure to use their (AWS) encryption keys for servers and databases handling their tenancy. In Enterprise, some tenants are very opinionated about what cloud you use and how their data lives/flows within it. I run all our infosec, including SOC2 & ISO27001 programs, and I know that using their encryption keys is nothing but security theater. But with $500k p.a. on the line, I also know when it's showtime.

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

#63

Post by Gigachad »

bicijay wrote: post #16
nbevans wrote: post #12

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

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.

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.

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

#65

Post by bvirb »

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.

It's pretty nice using RLS that the entire query will follow the rules applied in the database. So for complex queries with say joins and/or subqueries they will all automatically follow the RLS policies as well. In our case we also have some global lookup tables that don't have RLS policies which can also be joined.

We've found it pretty nice to cut out a whole class of possible bugs by being able to defer it to the database level. At the application level we end up with a wrapper that sets (and guarantees unsetting) multi-tenant access to the correct tenant, and then we never have to add "tenant_id = ..." anywhere, regardless of the query. Regardless of whether we forget in some query (which we almost surely would), it cuts out quite a bit of extra code.

You can also do some cool stuff like add RLS policies for read-only multi-tenant access. Then you can query data across multiple tenants while enforcing that nothing accidentally gets written.

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

#67

Post by gwen-shapira »

semitones wrote: post #54
gwen-shapira wrote: post #48

Earlier quoted context omitted.

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

oops :) Too many tabs. Fixed and thank you.

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

#68

Post by throwaway787544 »

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.

They're like a tick. Very good at burrowing in and hard to remove. They have a lot of clients for whom a dozen million dollars is a drop in the bucket, and moving away is a decade-long millions-of-dollars project.

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

#69

Post by beachy »

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 had the ability to do this for decades ("virtual private database"), so whatever is keeping them alive, it's nothing to do with this particular nifty Postgres feature.

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

#70

Post by gz5 »

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

And PG supports layer 3 shut-down of link listeners and inbound fw ports. So you can combine the L7 tenancy with a secure networking architecture which eliminates the problems of managing firewalls and ACLs. One of the open source examples: https://youtu.be/s-skpw7bUfI
Post reply on HN