3 ms·
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 application
by cocoflunchy 5y ago
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?
- SahAssar 5y agoIt 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.
- scottwick 5y agoHow 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)
- SahAssar 5y agoIIRC 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#FUNCTIONS-ADMIN-SET 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.