[ggregoire]

I discovered row-level security when I started using PostgREST [1] [2].

It was eye opening for me. In every traditional codebase I worked on, this is usually handled is such a slow and messy way, adding another layer of filtering on top of already slow and complex queries. This is always one of the first things that needs to be cached in Redis. Instead, row-level security solves the problem in a very elegant, simple and performant way in my opinion.

Obviously it works better when all your logic is already at the DB level (e.g. PostgREST). I wouldn't imagine using DB roles and row-level security in a traditional backend where all the logic is at the application level (e.g. Django, Rails…). Edit: seems like there are workarounds to use RLS with Django [3].

[1] https://postgrest.org

[2] https://postgrest.org/en/stable/auth.html#roles-for-each-web...

[3] https://pganalyze.com/blog/postgres-row-level-security-djang...

[RedShift1]

Yeah it's ridiculous how many features of a modern database server we are leaving on the table in favour of spending more time re-inventing these things for every new app or middle layer. Even MSSQL has row level security, I doubt it's being used very much.

[pow_pp_-1_v]

I don't see why RLS would mandate all your logic living in the DB level. Basically what the database does when you enable RLS is add the RLS policy clause to every query you run against a table that has the policy applied. So if you have a policy saying "A = 'blah'" on table "dummy", a query like "SELECT * FROM dummy WHERE a_col = 123" becomes "SELECT * FROM dummy WHERE a_col = 123 and A = 'blah'".

[ggregoire]

Indeed. I was thinking about the RLS use case where the policy is based on the current user and its role. It wasn't obvious to me at first but you could just add a middleware to your app that dynamically set the role in the DB for the user making the request (as in the third article I posted in my edit). Basically what PostgREST does.

[Winsaucerer]

Looking at that django link, rather than creating a new role for every user ID, you can set a value in each transaction that can contain whatever you like, including a user ID [1] [2]. What I don't like about that django solution is that it's very django dependent. If you ever had another system that can create users, the django signal wouldn't fire and the new role wouldn't be created. Apart from that, you'll have a lot of unnecessary roles in postgres itself.

[1] https://news.ycombinator.com/item?id=30706295

[2] https://news.ycombinator.com/item?id=30703881

[paulryanrogers]

Some features just don't scale or cannot easily integrate into app layers which need them. For example Pg connections are expensive, so you need a Pooler, now you don't want a DB user per end user. FK constraints too can prove hard to scale as one ends up with extra writes and contention, or do sharing.

[timando]

> now you don't want a DB user per end user.

`RESET ROLE; SET ROLE app_username;` could be done for each query / transaction / when fetching the connection from the pool.

[paulryanrogers]

Interesting. But won't that require the connection user be privileged enough to do that? And therefore you're still one SQL injection away from someone taking on another role for escalation or impersonation?

[sekun]

You can create a role whose sole job is to switch to the roles needed. Doesn’t require you to escalate to superuser-level privileges that way. But still, if SQL injections aren’t properly considered then it’s possible for a user to gain more privileges than planned. Although SQL injections are usually mitigated by the DB libraries these days.

Also, it’s more convenient to use SET LOCAL ROLE <ROLE_NAME>, since that only keeps the role for the transaction. Manually resetting it is error prone (IME), and forgetting will have the supposedly “temporary” role bleed to the next transaction.