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

21–30 of 123 posts

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

#21

Post by SahAssar »

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…

I think the way to handle this (based on how many companies handle GDPR compliance) is to not keep backups older than X months (usually 3 months) and have a clause that all data past that time is deleted.

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

#22

Post by cstejerean »

sgarman wrote: post #19

Am I right in my understanding that EVERY request that comes in to their api creates a new connection to the database? What about reusing connections with connection pools or one level up using pgbouncer or thing. Can you actually use RLS while reusing connections?

You can reuse the connection with a connection pool and use SET ROLE when you check it out.

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

#23

Post by jerryjerryjerry »

bearjaws wrote: post #11
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…

We usually write a "reasonable best effort" clause into our deletion, that it will 100% be deleted from production within 30 days and automatically fall out of backups 60 days from there. This also helps since we can't control our downstream vendors such as Twilio, AWS SES, etc, who all have their own legal obligations and time frames. Even for large health systems they have been okay with it.

I think TTL feature provided by some DB vendors are actually orthogonal to multi tendency, where the former deals with life cycles policy of data but the original problem to delete the data of certain user is more related to privacy policy of data, though overlap may exists.

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

#24

Post by rst »

sgarman wrote: post #19

Am I right in my understanding that EVERY request that comes in to their api creates a new connection to the database? What about reusing connections with connection pools or one level up using pgbouncer or thing. Can you actually use RLS while reusing connections?

Nope. Quoting the article itself:

"In the traditional use case of direct db access, RLS works by defining policies on tables that filter rows based on the current db user. For a SaaS application, however, defining a new db user for each app user is clunky. For an application use case you can dynamically set and retrieve users using Postgres’ current_settings() function ( i.e:

  SET app.current_app_user = ‘usr_123’
and

  SELECT current_settings(‘app.current_app_user)
)."

The policies that they define reference these settings, so they can do a "set" at the start of processing every web request, on a pre-existing db connection.

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

#25

Post by paxys »

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.

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

#26

Post by lemax »

sgarman wrote: post #19

Am I right in my understanding that EVERY request that comes in to their api creates a new connection to the database? What about reusing connections with connection pools or one level up using pgbouncer or thing. Can you actually use RLS while reusing connections?

It's possible to implement this without creating new connections to the database for each request by using SET LOCAL and wrapping every query in a transaction. Instead of applying RLS based on the current user, you apply RLS based on the parameter value you set at the beginning of the transaction. You can set this parameter value based on the user session in your application.

Your RLS policy looks as follows: CREATE POLICY tenant_${tableName}_isolation_policy ON "${tableName}" USING ("tenant_id" = current_setting('app.current_tenant');

Your queries look something like this: BEGIN TRANSACTION SET LOCAL app.current_tenant = '${tenant}'; SELECT * from some_table END TRANSACTION;

You can even initialize your writes with a `tenant_id` column defaulted to your `current_setting('app.current_tenant')`

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

#27

Post by w-j-w »

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.

No post body was provided.

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

#28

Post by lemax »

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.

In my experience we've looked toward this kind of solution in a large legacy single-tenant application that wants to go multi tenant with more safety guarantees.

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

#29

Post by brightball »

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…

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 up because of what you offer, not the termination clause.

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

#30

Post by nordsieck »

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.

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

Is having to avoid use after free really considered a security hole? Isn't that how like every program in existence operates? Coming up with complicated languages and frameworks just because you're scared you will accidentally use a variable after it's been freed seems bizarre to me.

As it turns out, humans are bad at being consistent, whereas computers are much better. Maybe this particularly solution isn't "the right thing", but it's at least an attempt at modifying the environment such that mistakes no longer happen. And at a meta level, that is precisely the right thing to do.

Post reply on HN