How We Turn Authorization Logic into SQL

ISO any articles&#x2F;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&#x27;t use groups that your customer knows about. Obvious solutions are &quot;where allowed_user_ids = ... big list&quot; or &quot;where disallowed_user_ids NE ... small list&quot;; the latter not a solution as you can&#x27;t optimize this query with a normal tree-like index.<p>I suppose you could use some sort of bloom filter, or create&#x2F;maintain groups behind the scenes somehow, but haven&#x27;t seen many articles cover this.

&gt; Oso’s Django and SQLAlchemy integrations turn partials from Polar into database queries... The SQL they produce relies heavily on nested subqueries<p>Live by ORM, die by ORM. This strikes me as particularly bad because these authorization queries may be running on every request. It&#x27;s great to see Oso went direct to SQL to address this. And the asides about logical programming were fun as well.

fwiw, PostgreSQL has a built-in mechanism for filtering rows based on authorization rules: <i>row-level security</i> [0].<p>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&#x27; role.<p>If you like Polar but can&#x27;t use it for whatever reason it does a lot of what Polar does.<p>[0] <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.5&#x2F;ddl-rowsecurity.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.5&#x2F;ddl-rowsecurity.html</a>

This seems like a fairly brute-force policy management approach that has the shortcomings outlined in this article from TailScale: <a href="https:&#x2F;&#x2F;tailscale.com&#x2F;blog&#x2F;rbac-like-it-was-meant-to-be&#x2F;" rel="nofollow">https:&#x2F;&#x2F;tailscale.com&#x2F;blog&#x2F;rbac-like-it-was-meant-to-be&#x2F;</a><p>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.

Are there any policy-language-libraries-backed-by-sql like Polar but that aren&#x27;t based on logic programming languages? I don&#x27;t really want to learn logic programming for this purpose nor do I want to require it on my coworkers.<p>I guess I&#x27;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`.<p>This [0] is close but it doesn&#x27;t have enough momentum to be well documented let alone usable as a library in every language you&#x27;d want (Go, C#, Python, Node.js, etc.).<p>Edit: Maybe OPA&#x2F;Rego can in fact do this [1].<p>[0] <a href="https:&#x2F;&#x2F;github.com&#x2F;mrumkovskis&#x2F;tresql" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;mrumkovskis&#x2F;tresql</a><p>[1] <a href="https:&#x2F;&#x2F;blog.openpolicyagent.org&#x2F;write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4" rel="nofollow">https:&#x2F;&#x2F;blog.openpolicyagent.org&#x2F;write-policy-in-opa-enforce...</a>

Man, OSO write such good blog posts

If you found this post interesting, here’s another great post about doing a similar thing with OPA Policies: <a href="https:&#x2F;&#x2F;blog.openpolicyagent.org&#x2F;write-policy-in-opa-enforce-policy-in-sql-d9d24db93bf4" rel="nofollow">https:&#x2F;&#x2F;blog.openpolicyagent.org&#x2F;write-policy-in-opa-enforce...</a>

Side question - does anyone know of other python auth libraries, which support a fine-grained access control, ideally close to that of AWS IAM?

Good pattern ! At least a library that makes sense. Thanks.<p>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.