Hacker News Reader

Comment by joshma | Hacker News Reader

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?

View on HN

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

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)?

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.

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.

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!

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)

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.

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.

Reply on news.ycombinator.com