9 ms·
How We Turn Authorization Logic into SQL
- ZikPhil 5y agoMan, OSO write such good blog posts
- epberry 5y ago> Oso’s Django and SQLAlchemy integrations turn partials from Polar into database queries... The SQL they produce relies heavily on nested subqueries Live by ORM, die by ORM. This strikes me as particularly bad because these authorization queries may be running on every request. It's great to see Oso went direct to SQL to address this. And the asides about logical programming were fun as well.
- gfody 5y agoagree this seems like the worst of both worlds between doing things application-side and doing things database-side. imo you're much better off embracing your db's security features
- throw_m239339 5y agoWell (most) ORM often cannot take advantage of CTE, group concat, array types, json types and what not when building queries from code, for obvious reason. So they often can't return multi-dimensional data in a single query. But ORM are still useful to get an application off the ground fast.
- gfody 5y agodo ORMs still target lowest common denominator ansi sql? I only know that EF doesn't - it has "providers" for each rdbms it supports and will liberally emit CTEs and db specific language features like "outer apply" or "join lateral" but then stupidly ignore powerful features like "for json" or "json_agg/json_build_object" which could eliminate the outrageously naive way it encodes 1:m and m:m results - it boggles how smart and stupid orms are at the same time.
- jiggawatts 5y agoJSON is too “lossy” to use automatically.
- aidos 5y agoTo be clear, sqlalchemy does support those features.
- aidos 5y agoBut you can build any query with Sqlalchemy - even when using the ORM rather than the Core, you still have that control, right? Don’t get me wrong, I’ve read stuff by these guys and I know they know what they’re talking about. Certainly, if you have to make it work for Django as well you’d be considering going straight to sql. As and aside, I walked through their stuff a little while back and I’m very interested in what they’re building.
- airstrike 5y agoBuilding bad queries with an ORM isn't necessarily the ORM engine's fault. It's really just a tool and, as such, its value really relies on one's proficiency with it. One simple but illustrative example: https://docs.djangoproject.com/en/3.2/ref/models/querysets/#prefetch-related https://docs.djangoproject.com/en/3.2/ref/models/querysets/#...
- revskill 5y agoGood pattern ! At least a library that makes sense. Thanks. One point about production usage, you should adapt to a NoSQL backend as we want to query authorization logic against a caching layer for performance reason.
- eatonphil 5y agoAre there any policy-language-libraries-backed-by-sql like Polar but that aren't based on logic programming languages? I don't really want to learn logic programming for this purpose nor do I want to require it on my coworkers. I guess I'm just looking for a library + SQL shorthand that can easily interpolate request variables and session variables that gets declared in code where a route is declared. Just spitballing but something like `(blogs.id = $req.blogid).userid = $session.userid OR (users.id = $session.userid).isAdmin`. This [0] is close but it doesn't have enough momentum to be well documented let alone usable as a library in every language you'd want (Go, C#, Python, Node.js, etc.). Edit: Maybe OPA/Rego can in fact do this [1]. [0] https://github.com/mrumkovskis/tresql https://github.com/mrumkovskis/tresql [1] https://blog.openpolicyagent.org/write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4 https://blog.openpolicyagent.org/write-policy-in-opa-enforce...
- nicoburns 5y agoWhat advantage would this have over a middleware that implements this logic as a lambda?
- evancordell 5y agoRego is a nice language to use (IMO), but also has roots in logic programming and is closer to datalog than SQL.
- craz 5y agoIf you found this post interesting, here’s another great post about doing a similar thing with OPA Policies: https://blog.openpolicyagent.org/write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4 https://blog.openpolicyagent.org/write-policy-in-opa-enforce...
- winrid 5y agoISO any articles/documents related to scaling access control, for example if you have 100_000 users and 90k of them have access to some resource, but 10k do not, and you can't use groups that your customer knows about. Obvious solutions are "where allowed_user_ids = ... big list" or "where disallowed_user_ids NE ... small list"; the latter not a solution as you can't optimize this query with a normal tree-like index. I suppose you could use some sort of bloom filter, or create/maintain groups behind the scenes somehow, but haven't seen many articles cover this.
- deepsun 5y agoWasn't LDAP developed exactly for that?
- winrid 5y agoYeah but the question is scaling queries against large data sets with access control, LDAP would only provide the meta data.
- rzzzt 5y agoSeconded! I can get to a million authentication-related articles and services, but "authorization theory" seems to be very hard to find. (It doesn't help that the big text box in the sky treats the two words as somewhat interchangeable.)
- tmoertel 5y agoThere's the paper on Google's Zanzibar: https://research.google/pubs/pub48190/ https://research.google/pubs/pub48190/ "This paper presents the design, implementation, and deployment of Zanzibar, a global system for storing and evaluating access control lists. Zanzibar provides a uniform data model and configuration language for expressing a wide range of access control policies from hundreds of client services at Google, including Calendar, Cloud, Drive, Maps, Photos, and YouTube. Its authorization decisions respect causal ordering of user actions and thus provide external consistency amid changes to access control lists and object contents. Zanzibar scales to trillions of access control lists and millions of authorization requests per second to support services used by billions of people. It has maintained 95th-percentile latency of less than 10 milliseconds and availability of greater than 99.999% over 3 years of production use."
- agentultra 5y agofwiw, 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 https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html
- catmanjan 5y agoDo you have much experience with it? Can you comment on the performance hit?
- SahAssar 5y agoIn 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.
- jansommer 5y agoThe 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.
- agentultra 5y agoI 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.
- pphysch 5y agoThis seems like a fairly brute-force policy management approach that has the shortcomings outlined in this article from TailScale: https://tailscale.com/blog/rbac-like-it-was-meant-to-be/ https://tailscale.com/blog/rbac-like-it-was-meant-to-be/ What happens when you have sweeping changes to existing policies? It seems like you have to chase down every other line of DSL and fix policies individually.
- ewuhic 5y agoSide question - does anyone know of other python auth libraries, which support a fine-grained access control, ideally close to that of AWS IAM?
- deleted 5y ago[deleted]