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

91–100 of 123 posts

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

#91

Post by Spivak »

alberth wrote: post #88
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.

This pretty much mirrors stored procedures though which have the option of running as the definer or the invoker. Breaking this with “when RLS is enabled stored procedures and views set to run with the permissions of the definer intersect with the RLS policy of the invoker” is crazy weird.

Maybe it would actually be good behavior but it would super super unintuitive.

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

#93

Post by infogulch »

markhalonen wrote: post #72
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…

This was a shock to us after using RLS for a while. The solution outlined here worked great for us: https://www.benburwell.com/posts/row-level-security-postgres...

So they create table-valued functions which support the "SECURITY INVOKER" security context, and then select from that function to form the view. I suppose there's a feature request somewhere to support the "SECURITY INVOKER" feature for views directly?

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

#94

Post by POPOSYS »

e1g wrote: post #61
brightball wrote: post #29

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

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…

Would you please like to explain why "using their encryption keys is nothing but security theater"? Thanks!

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

#95

Post by POPOSYS »

bvirb wrote: post #60
shaicoleman wrote: post #18

We'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 bec…

Would you like to explain the deal-breaker a little bit more? I do not understand the limitation you hit. It seems like it is no problem to access foreign keys in a different schema as long permissions do allow that. Thanks!

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

#96

Post by POPOSYS »

a-dub wrote: post #66

this is cool. next up, row level encryption with private information retrieval methods for enabling queries and searches homomorphically (on data encrypted by the client that the service provider never has a key for).

This is interesting - do you have any hits / docs on how to implement that?

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

#97

Post by alberth »

Spivak wrote: post #91
alberth wrote: post #88

Earlier quoted context omitted.

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.

This pretty much mirrors stored procedures though which have the option of running as the definer or the invoker. Breaking this with “when RLS is enabled stored procedures and views set to run with the permissions of the definer intersect with the RLS policy of the invoker” is crazy weird. Maybe it would actually be good behavior but it would super super unintuitive.

Isn’t it also super unintuitive that developers will have to manage partitioning customer data at the Application Layer even though they think they are using a Data Layer security policy.

When RLS is enabled, their should never be a situation where Customer A might be able to gain access to Customer B data. That’s literally the entire objective of the feature.

This is giving people a false sense of security, and for multi-tenant applications has massive consequences since this will leak data.

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

#98

Post by jamesfinlayson »

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.

Is account creation automated?

I know there are some resources where you can only have one per region (I think you can only have one AWS::EC2::VPCEndpoint per... type and service per region) but I don't know if letting multiple tenants use the same VPC endpoint is a risk or not.

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

#99

Post by ksec »

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

Fortune Top 10

1 Walmart

2 Amazon

3 Apple

4 CVS Health

5 UnitedHealth Group

6 Exxon Mobil

7 Berkshire Hathaway

8 Alphabet

9 McKesson

10 AmerisourceBergen

We can rule out 2,3,7,8 …

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

#100

Post by buzer »

POPOSYS wrote: post #94
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…

Would you please like to explain why "using their encryption keys is nothing but security theater"? Thanks!

The only real extra protection that I can think of that they get is that they can revoke the access at any time (e.g. right after terminating contract). If someone malicious gets into the provider's AWS account it's highly likely that they can get the access to the running machines and extract the data. I guess if attacker is stupid and just tries to restore the encrypted backup it could alert the customer instead of just the provider assuming that's a non-standard operation.

If there was a very deep integration, essentially column-level, it could make a lot more sense, but that could give quite a big performance & cost hit as well as limit what you can do (essentially you wouldn't be able to do any matching on content level).

Post reply on HN