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

101–110 of 123 posts

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

#101

Post by X-Istence »

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!

If you give me access to use your KMS key to encrypt/decrypt an EBS volume, and grant me access to that EBS volume/mount it to an AWS EC2 instance I manage, I can read/write data on that EBS volume all day long.

The fact that you own the KMS key doesn't stop me from reading/writing that EBS volume. It doesn't offer any additional security guarantees, especially if I was already encrypting the EBS volumes with a KMS key.

If I am a SaaS offering, whether I use a KMS key I own or one you own doesn't change the fact that I still have access to all of the unencrypted data that is silently being encrypted by those KMS keys, I've got access to the layer above it.

Sure, if the contract ended they could revoke the KMS key and now the data on the EBS volumes is no longer readable by me, but any backups I have of that data is still within my purview.

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

#102

Post by X-Istence »

jamesfinlayson wrote: post #98
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.

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.

You can have an instance of a VPC Endpoint per VPC. You can have multiple VPC's all with the same VPC Endpoints.

You just get billed for them ;-)

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

#103

Post by simlevesque »

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…

Supabase kinda does this.

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

#104

Post by LAC-Tech »

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…

Interesting that they are so privy to how you administer stuff. Would they have terminated on the spot if you had been using azure or google or digital ocean? Or was using AWS in the initial contract?

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

#105

Post by LAC-Tech »

simlevesque wrote: post #103
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…

Supabase kinda does this.

Does superbase basically give you a pgAdmin accessible instance to play with, then generate a JSON API using postgREST? It's not 100% clear to me from skimming the docs.

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

#106

Post by a-dub »

POPOSYS wrote: post #96
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?

it's an open area of research as far as i know. the technical terms are "private information retrieval", "private information query", "homomorphic encryption" and "secure multiparty computation."

it's some of the coolest stuff in cryptography but it also tends to be experimental or very computationally expensive. zero knowledge proofs are the simplest form i know of.

as far as i know, row level end to end encrypted databases with indices that preserve privacy do not exist yet, but i fully expect that they will.

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

#107

Post by keybits »

LAC-Tech wrote: post #105
simlevesque wrote: post #103

Earlier quoted context omitted.

Supabase kinda does this.

Does superbase basically give you a pgAdmin accessible instance to play with, then generate a JSON API using postgREST? It's not 100% clear to me from skimming the docs.

Yes

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

#108

Post by kennethh »

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…

I worked in a bank previously and we migrated all our databases from Oracle to MS SQL Server. I think we used like 7-8 years to do it so I can understand people who are hesistant to convert. I think the advantages at the time (this was 10 years ago) was lower price for the db servers but also more people who are familiar with Sql Server compared to Oracle.

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

#109

Post by Dave3of5 »

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…

> we opted for having one DB per tenant which also makes sharding, scaling, balancing, and handling data-residency challenges easier

When you say 1 DB I suspect you mean you have a single DB Server and multiple DB's on that server. Then I don't think this really solves the data-residency problem as the clients data is just in a different DB but still on the same instance. It makes other problems for you as well for example you now have 2 DB's to run maintenance, upgrades, data migrations on. Current company uses a similar model for multiple types of systems and it makes upgrading the software very difficult.

It also makes scaling more difficult as instead of having a single DB cluster that you can tweek for everyone you'll need to tweek each cluster individually depending on the tenants that are on those clusters. You also have a practical limit to how many DB's you can have on any physical instance so your load balancing will become very tricky.

There are other problems it causes like federation which Enterprise Customers often want.

Post reply on HN