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

111–120 of 123 posts

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

#111

Post by spiffytech »

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

I'm surprised this simplified balancing for you. When I worked somewhere with per-customer DBs, we had constant problems with rebalancing load. Some customers grew too big for where we put them, some nodes usually performed fine until the wrong subset of customers ran batch jobs simultaneously, etc.

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

#112

Post by vlovich123 »

X-Istence wrote: post #101
POPOSYS wrote: post #94

Earlier quoted context omitted.

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

Human systems (contracts/legal) step in when technological systems can’t. Until there’s homomorphic encryption, enforcing that you’re using their key can give them piece of mind that they can revoke it. While true that you could be doing anything with that data, a contract in good standing and normal human ethics probably adds a high degree of likelihood that you’re not. However, if the relationship sours, they want the freedom to revoke quickly without needing your good will. If your backups aren’t using their keys, I think you’d be violating the contract.

But yes, from a purely technological perspective security theater. They could also misunderstand what’s happening and it’s also not worth it to try to explain for you at the risk of losing the contract.

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

#113

Post by BeefWellington »

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…

One DB (or perhaps schema, depending on DBMS) per tenant is the way to go IMO. It simplifies so much of your work, from DR to deletion to scaling.

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

#114

Post by [deleted] »

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…

[deleted]

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

#115

Post by e1g »

LAC-Tech wrote: post #104
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…

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?

We had cases when a prospect said we must use AWS (because that's what they use, and are comfortable with how our data bridges would work/integrate), and when a prospect said we must not use AWS (because they are in retail and want to avoid feeding the 1,000-pound gorilla that's mauling them).

I don't want to imply this is common - it's not. Such cases come from the "arbitrary enterprise rules" side rather than the "SaaS/tech" side.

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

#116

Post by e1g »

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!

Other comments from X-Istence, vlovich123, and buzer are correct. If our apps/admins can get to client data, using their keys does not help to mitigate any plausible attack vector. If we become hostile or compromised, their data is toast anyway. Adding this technical control increases friction and makes the overall system more rigid/brittle but does nothing to increase security. Hence "security theater".

Other examples of such meaningless-and-impotent controls in infosec include "must run a firewall on your Linux server on a private subnet", and any policy requiring password complexity/rotation. But as a business, it's more productive for us to just tick the checkboxes than to spend resources on educating the market about the actual best practice (unless you're in the business of education).

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

#117

Post by X-Istence »

vlovich123 wrote: post #112
X-Istence wrote: post #101

Earlier quoted context omitted.

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

Human systems (contracts/legal) step in when technological systems can’t. Until there’s homomorphic encryption, enforcing that you’re using their key can give them piece of mind that they can revoke it. While true that you could be doing anything with that data, a contract in good standing and normal human ethics probably adds a high degree of likelihood that you’re not. However, if the relationship sours, they want…

I don't know how this is for others, but in the environments I am in we use a different KMS key for our backups so that if something were to happen, like a mis-click in a web interface or an accidental terraform destroy, we can recover the data.

It is also stored in a different location than the original (different AWS account).

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

#118

Post by infogulch »

infogulch wrote: post #93
markhalonen wrote: post #72

Earlier quoted context omitted.

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?

Well well lookie here:

commitdiff 2022-03-22: Add support for security invoker views. - https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...

discussion 2021-12-17: [PATCH] Add reloption for views to enable RLS - https://www.postgresql.org/message-id/b66dd6d6-ad3e-c6f2-8b9...

explanatory blog post: 2022-03-22: Waiting for PostgreSQL 15 – Add support for security invoker views. - https://www.depesz.com/2022/03/22/waiting-for-postgresql-15-...

This seems to be slated for PG15: https://www.postgresql.org/docs/15/release-15.html#id-1.11.6...

> E.1.3.1.6. Privileges: Allow view access to be controlled by privileges of the view user (Christoph Heiss) Previously, view access could only be based on the view owner.

Syntatically it will look like:

    CREATE VIEW vista WITH (security_invoker=true) AS SELECT 'Hello World';

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

#120

Post by mixmastamyk »

victor9000 wrote: post #82
brightball wrote: post #73

Earlier quoted context omitted.

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.

Or "cha-ching!"
Post reply on HN