Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
1–10 of 123 posts
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#2This 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-tenant and we point to our SDLC + PG docs and they go 'K'.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#3This 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.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#4This 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 challenges easier.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#5This 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.
Legacy.
If you have thousands of lines of code relying on Oracle the cost to migrate would be enormous.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#6This 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…
Agree - performance on row level security (at least on SQL Server) is terrible, sharing by database is fairly easy.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#7This 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.
Sharding is the only scalable way per my experience. The point about scalability here is, i can control the load as the data gets bigger.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#8Earlier quoted context omitted.
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.
Legacy. If you have thousands of lines of code relying on Oracle the cost to migrate would be enormous.
Amazon has a great post on this topic.
https://aws.amazon.com/blogs/aws/migration-complete-amazons-...
I thought it was cool they retrained their Oracle DBAs into other roles as part of the project.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#9This 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…
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.
Re: Shipping Multi-Tenant SaaS Using Postgres Row-Level Security
#10I don't fully understand the performance implications here.
Say I was using this for a blog engine, and I wanted to run this SQL query:
select * from entries;
Would PostgreSQL automatically turn the above query into the equivalent of this:
select * from entries where author_id = 57;
Or would it need to run an additional SQL query check for every single row returned by my query to check row permissions, adding up to a lot of extra overhead?