News for hackers. Stuff that matters.
Re:How We Turn Authorization Logic into SQL by agentultra on Tuesday November 23, 2021 @04:44PM (#29323943)
fwiw, PostgreSQL has a built-in mechanism for filtering rows based on authorization rules: row-level security [0].

This can simplify your data-access layers quite a lot and pushes you towards better security practices like limiting the scope of permissions granted to your applications' role.

If you like Polar but can't use it for whatever reason it does a lot of what Polar does.

[0] https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html

Re:How We Turn Authorization Logic into SQL by cocoflunchy on Wednesday November 24, 2021 @01:27AM (#29327413)
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?

Re:How We Turn Authorization Logic into SQL by SahAssar on Wednesday November 24, 2021 @03:57AM (#29328138)
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.

Re:How We Turn Authorization Logic into SQL by scottwick on Wednesday November 24, 2021 @11:42AM (#29331899)
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)
Re:How We Turn Authorization Logic into SQL by SahAssar on Wednesday November 24, 2021 @11:52AM (#29332041)
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.

Re:How We Turn Authorization Logic into SQL by catmanjan on Tuesday November 23, 2021 @06:24PM (#29324876)
Do you have much experience with it? Can you comment on the performance hit?
Re:How We Turn Authorization Logic into SQL by jansommer on Wednesday November 24, 2021 @05:59AM (#29328794)
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.
Re:How We Turn Authorization Logic into SQL by agentultra on Wednesday November 24, 2021 @09:34AM (#29330298)
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.

Re:How We Turn Authorization Logic into SQL by SahAssar on Wednesday November 24, 2021 @03:58AM (#29328145)
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.