4 ms·
Has anyone used RLS in a standard web service? Are there any common use cases where this is handy? I'm thinking it won't work for your standard permissioning sy
by joshma 10y ago
Has anyone used RLS in a standard web service? Are there any common use cases where this is handy? I'm thinking it won't work for your standard permissioning system since it seems clunky if you have any slightly sophisticated logic (X is in org Y and can see anything Y has read access to).
Maybe it'd be more useful if you're using a single DB for a multi-tenant setup, and you know each tenant's data is strictly isolated?
- tiffanyh 10y agoRLS 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 10y agoThis 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 10y agoIIRC 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 10y agoSomething 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 10y agoIs 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 10y agoI 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 10y agoInteresting 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 10y agoIn 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.