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

81–90 of 123 posts

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

#81

Post by macNchz »

andy_ppp wrote: post #77

I find adding loads of stuff to Postgres exciting and fun, but I want all of my logic in the code in GitHub, rather that floating around in my global data store. Has anyone thought about a data layer that allows you to define this stuff programmatically rather than in SQL but then it configures your data layer to work like this. Not necessarily an ORM but more a business logic layer that compiles everything down to u…

I’ve set something like that up a handful of times in a kind of ad-hoc manner, by subclassing/extending the autogeneration tools from existing db migration frameworks to just detect changes in a directory of .sql files. Has worked pretty well to keep stored procedures/triggers/materialized views up to date with the repo.

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

#82

Post by victor9000 »

brightball wrote: post #73
e1g wrote: post #61

Earlier quoted context omitted.

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

Fwiw, we also priced in dedicated database instances for people who wanted it where we had that capability. For the extra costs, nobody ever took us up on it.

This is the right answer. You always say yes, and tell them how much it will cost. Then you can sit back and enjoy the sweet sound of crickets.

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

#83

Post by hamandcheese »

andy_ppp wrote: post #77

I find adding loads of stuff to Postgres exciting and fun, but I want all of my logic in the code in GitHub, rather that floating around in my global data store. Has anyone thought about a data layer that allows you to define this stuff programmatically rather than in SQL but then it configures your data layer to work like this. Not necessarily an ORM but more a business logic layer that compiles everything down to u…

Flyway is a plain-sql migration tool with support for “repeatable migrations” which somewhat do what you want:

https://flywaydb.org/documentation/tutorials/repeatable

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

#84

Post by koolba »

uhoh-itsmaciek wrote: post #36

>Another issue we caught during testing was that some requests were being authorized with a previous request’s user id. This is the terrifying part about RLS to me: having to rely on managing the user id as part of the database connection session seems like an easy way to shoot yourself in the foot (especially when combined with connection pooling). Adding WHERE clauses everywhere isn't great, but at least it's expli…

If you do this right, it ends up in just one place and the code that handles checking in / out connections from your pool will handle wrapping your connection usage with the appropriate session context. And of course subsequently clearing it with a DISCARD ALL.

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

#85

Post by slt2021 »

beckingz wrote: post #49
gav wrote: post #15

Earlier quoted context omitted.

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.

lift and shift migrations dont really make sense. It would make sense to do architectural rewrite, from on-prem Oracle monolith to cloud native serverless stack for example. Digital transformation, yeah

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

#86

Post by icedchai »

jasongi wrote: post #80
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?

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.

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

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

#87

Post by ibejoeb »

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.

PostgreSQL is a great product, but Oracle has so many more features. Even just in the query language. There's so much power embedded in just the model clause that would be all custom software in Postgres.

If you buy Oracle, you should use Oracle. Like really lean into it. If you really need it, it will be worth the money. I don't like dealing with Oracle sales, but the product is killer.

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

#88

Post by alberth »

mkurz wrote: post #59

Be 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). Howeve…

That seems like a bug to me, and a significant one as well.

The underlining promise of RLS (sometimes even referred to as “virtual private database”) in an RDBMS, is that data should never leak because it’s handled transparently by the db.

This seems like a significant leakage point that the user has to personally manage.

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

#89

Post by lmeyerov »

macNchz wrote: post #81
andy_ppp wrote: post #77

I find adding loads of stuff to Postgres exciting and fun, but I want all of my logic in the code in GitHub, rather that floating around in my global data store. Has anyone thought about a data layer that allows you to define this stuff programmatically rather than in SQL but then it configures your data layer to work like this. Not necessarily an ORM but more a business logic layer that compiles everything down to u…

I’ve set something like that up a handful of times in a kind of ad-hoc manner, by subclassing/extending the autogeneration tools from existing db migration frameworks to just detect changes in a directory of .sql files. Has worked pretty well to keep stored procedures/triggers/materialized views up to date with the repo.

Yep -- postgres enforces, while config-as-code can inject as usual bc the whole point is it is just SQL, so policy changes are just migrations (and subject to SDLC)

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

#90

Post by mrtesthah »

axlee wrote: post #53
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…

It makes BI work an absolute hellscape as well. Tradeoffs.

Just make the application collect the statistics you need. Put them into a separate shared DB or use one of the many existing off the shelf SaaS collection frameworks.
Post reply on HN