Row level security requires creating a Postgresql role for every user if I understand correctly.

Is this something that is done in practice for web applications? Like in my Django app, create a Postgresql role for every user in my auth_user table?

It doesn't, you can use any variable available. So for example in postgrest you get all variables in a JWT exposed as GUC (Grand Unified Configuration) variables and you can use that in your RLS checks.

The most common way is to create a role though, yeah. I find that creating a role is not a downside, since it is generally just a row in a table that postgresql's permission system also uses.

How does this work in terms of Postgres connections/pools? My understanding of this is that you'd do something like `SET SESSION user_id TO 123;` which sets the variable `user_id` for that connection. Subsequent queries could use that variable to do row-based authorization but they need to use the same connection. Is this how Postgrest does it? (guaranteeing the same connection for the lifecycle of a request that is)

IIRC postgrest starts a transaction for each request, and inside of that sets the variables. I've done some testing to make sure that variables do not leak between requests.

See the local option here for more options: https://www.postgresql.org/docs/current/functions-admin.html...

If using the role-mapping feature of postgrest it works similarly, only setting the ROLE instead of the variables.

Do you have much experience with it? Can you comment on the performance hit?

The performance hit can be quite big. I had a query that went from 1s to 400ms by disabling RLS. The security policy was a simple where org = abc. When I encounter performance hits like these, I refactor the slow query into a function with SECURITY DEFINER and a huge warning that you're on your own regarding security. Besides that, it's nice not having to worry if your SQL is accessing stuff the current role isn't allowed to see.

I do and the answer is always it depends. I'm not being glib! There are a lot of variables at play that will affect performances.

In general it moves computation closer to the data and in aggregate that generally offsets most increases in query times.

If you design your schemas carefully the performance cost is easy to swallow. As always analyze your queries under different table sizes and see what works for you.

The benefit is that your application code doesn't have to use any complex RBAC->SQL compilation. You can just 'select foo, bar, baz from mytable;` and RLS will take care of making sure your application servers never see the data that the user doesn't have access to.

In my experience the performance hit is not much larger than having the permission check part of your SELECT/UPDATE/DELETE query, but I don't have hard numbers.