We rewrote OpenFGA in pure Postgres
13 comments
·October 21, 2025esafak
I have similar concerns, like how to reconcile authz with search. Search wants to be its own thing, and so does authz. Pray tell how I'm supposed to get a paginated list of authorized results? You have to filter or have the search service call the authz service. Life is easier when the main database can handle everything.
We should develop and translate this extension to other databases.
jschorr
Reconciling externalized authz with search is actually quite a challenging problem. For standard externalized authz, the recommendation is some form of pre-filtering or post-filtering [1], for which we actually built LookupResources (pre-filtering) and CheckBulkPermission (post-filtering) into SpiceDB.
However, as you mentioned, life is easier when the main database can handle everything, so we actually built a solution in that space called Materialize [2], which heavily denormalizes the authorization data and allows for joining within application databases such as Postgres. My colleague Evan actually put together a really cool video about using it with Gitea [3].
Recognizing that even with Materialize, however, the need to consume events can be a bit annoying, I've been doing some work to allow Postgres itself to do native JOINs against SpiceDB (and other operations). I demo it briefly in our recent announcements video [4] and I think it effectively solves this problem within Postgres, while still allowing for all the benefits (scale, performance, redundancy, distribution) of externalized authz.
[1]: https://authzed.com/docs/spicedb/modeling/protecting-a-list-...
[2]: https://authzed.com/products/authzed-materialize
[3]: https://www.youtube.com/live/u3i1SEd9Ll8?si=mCz5mZterxthoEwj
[4]: https://www.youtube.com/live/uz_gxz3whS0?si=g4NUZAIltYVyFzYj...
Disclaimer: I'm cofounder and CTO at AuthZed and we build SpiceDB and Materialize
babelfish
Authorized search isn't difficult. Store authorized readers (people, groups, etc) on each result to pre-filter results, then post-filter results with an additional authorization check in case the authorization store was updated but the search results store was not. Context: I've built an authorized search solution before.
fleahunter
Interesting point about authorized search. I’ve been in situations where the search and auth layers felt like they were constantly at odds. It’s like, how do you balance up-to-date permissions with an efficient retrieval system? I remember building a project where we kept a mapping table of users to permissions for quick lookups, but man, it got messy with data updates.
It’s cool that you’ve done this before. Did you face any performance bottlenecks when you’re post-filtering the results? I wonder if splitting it out into separate services would help keep things tidy or just add to the complexity.
This also makes me think about how often auth requirements change in agile environments. Like, do we even realize how quickly we need to adapt our models? Seems like a good moment to examine the tooling we use, especially if we could create an extension for other databases. The more I think about it, the more I see a push for this kind of flexibility in many applications.
ants_everywhere
> I remember building a project where we kept a mapping table of users to permissions for quick lookups, but man, it got messy with data updates.
Do you mean literally users to permissions?
Usually users map into groups, groups are subgroups of each other, and permissions are granted to groups.
So you can only see the financial data if you're in financial-report-preparers. Can Bob see the report? That depends on if he's an indirect or direct member of financial-report-preparers.
This is different from RBAC where Bob might have an admin or manager role directly. If he has admin access it's because he's a member of a certain group of employees that's a subgroup of another group of employees who all have admin access etc. similarly for groups of end users.
It would be a combinatorial explosion to try to enumerate the permissions of a given user. If people are having lot of syncing problems they may not have granular enough groups.
Groups make it safe. Groups are best.
jschorr
> I remember building a project where we kept a mapping table of users to permissions for quick lookups, but man, it got messy with data updates.
Yep, as I mentioned above, its not an easy problem but once it is solved for you, it becomes "just" watching the events and performing the JOINs.
> especially if we could create an extension for other databases
See my video I linked above about the Postgres FDW: It does exactly this for SpiceDB and works seamlessly as-if there is a denormalized permissions table sitting in your Postgres, while still supporting the full array of complex authorization rules found in ReBAC.
ygouzerh
Interesting read! It kind of a became a rule nowadays to never store logic in the database nowadays, but it's always refreshing to see when it could work. Please give us an update in few months of how it is going on the long run!
null
> The big problem that we kept running into was keeping everything in sync all the time. Every time we add a new user, organization, repository, etc. in our database, we also had to add the corresponding tuples in OpenFGA.
This is a fundamental problem with all Zanzibar-inspired authorization systems[0] that require centralizing ~all authorization data and led us @ Oso to build a more flexible system[1] that grants more control over what authorization-relevant data you centralize vs. decide keep locally.
0: https://www.osohq.com/post/authorization-for-the-rest-of-us
1: https://www.osohq.com/docs/develop/facts/local-authorization
disclosure: founding engineer at Oso