4 ms·

RLS is great for SaaS providers. Stick all of your customer data in a single database. Implement RLS per customer. Now "Pepsi" can't see "Coca-Cola's" data. E

RLS is great for SaaS providers. Stick all of your customer data in a single database. Implement RLS per customer. Now "Pepsi" can't see "Coca-Cola's" data. Essentially creating a virtual private database per customer by using RLS. edit:typo

agopaul 11y ago
This is interesting, but wouldn't that require to create a postgres user for each customer (and then use that user to make the connection to the DB)?
- barsonme 11y ago
  IIRC you can `SET ROLE = ...`, query, `RESET ROLE` and be done with it. Might need transactions, I'm not sure. I fiddled around with it a bit but it was too much overhead to work with.
- kyllo 11y ago
  Something like this has been done, and web app frameworks have had libraries that do it--only with schemas instead of users--for quite some time.
flannell 11y ago
Is this an observation or have you done/seen this in action? I ask because for my next project I'd like to tackle the issue of having to keep lots of Databases up to date with their stored procedures. Kind of wanted a common library of procs that any DB can access. I've seen third party Software do DB versioning etc but too expensive for me. A few do it via package management, but keen to see how others are doing it!
- tiffanyh 11y ago
  I don't know if Postgres can do what you're describing but I know Oracle can and it's called Pluggable Databases (PDB) and it's designed exactly for this use case. (Now I just hope I don't get flammed)
  - flannell 11y ago
    Interesting thanks, I'll look into it. Your earlier comment has sent me down a Postgresql logicdecode/Kafka/Hadoop rabbit hole. Funny where you end up.
- snuxoll 11y ago
  In PostgreSQL extensions are the common way to solve it, you still have to manually install/uninstall in each database but writing them is pretty straightforward and a quick call to CREATE EXTENSION/DROP EXTENSION in your migrations isn't too much to ask in most cases.

agopaul 11y ago

This is interesting, but wouldn't that require to create a postgres user for each customer (and then use that user to make the connection to the DB)?

barsonme 11y ago
IIRC you can `SET ROLE = ...`, query, `RESET ROLE` and be done with it. Might need transactions, I'm not sure. I fiddled around with it a bit but it was too much overhead to work with.
kyllo 11y ago
Something like this has been done, and web app frameworks have had libraries that do it--only with schemas instead of users--for quite some time.

flannell 11y ago

Is this an observation or have you done/seen this in action? I ask because for my next project I'd like to tackle the issue of having to keep lots of Databases up to date with their stored procedures. Kind of wanted a common library of procs that any DB can access. I've seen third party Software do DB versioning etc but too expensive for me. A few do it via package management, but keen to see how others are doing it!

tiffanyh 11y ago
I don't know if Postgres can do what you're describing but I know Oracle can and it's called Pluggable Databases (PDB) and it's designed exactly for this use case. (Now I just hope I don't get flammed)
- flannell 11y ago
  Interesting thanks, I'll look into it. Your earlier comment has sent me down a Postgresql logicdecode/Kafka/Hadoop rabbit hole. Funny where you end up.
snuxoll 11y ago
In PostgreSQL extensions are the common way to solve it, you still have to manually install/uninstall in each database but writing them is pretty straightforward and a quick call to CREATE EXTENSION/DROP EXTENSION in your migrations isn't too much to ask in most cases.