https://authzed.com logo
Join Discord
Powered by
Pattern for enforcing SpiceDB permissions at the P...
# spicedb
n
Hey all, would love some feedback on enforcing auth-scoped data access at the database level. I'm building an ATS (applicant tracking system). The SpiceDB schema is included below and mirrors the Postgres schema (managed via Prisma). ## How the auth model works Every entity (e.g. message, thread, application) ladders up to an anchor resource (location, company, or enterprise), which belongs to a resource_group, on which users have permissions. Resource groups can be nested via the 
parent
relation for permission inheritance. ## The problem I need to enforce data access at the Postgres query level. These checks happen on every DB read, so they need to be cheap. I'm considering two approaches and would love feedback on whether there's a better pattern. ### Approach 1: LookupResources Ask SpiceDB for the set of entity IDs a user can access, then pass those into a 
WHERE id IN (...)
clause. Concern: For tables with large row counts, the returned ID set can be massive — potentially too large for a WHERE IN clause and too slow for SpiceDB to compute. At our current scale the set is small, but I don't want to commit to a pattern that breaks as we grow. ### Approach 2: Resolve accessible anchors, filter via joins Ask SpiceDB for the anchor resources (locations, companies, enterprises) a user can access — a much smaller set — then join from the target entity up to the anchor and filter there. Example: to fetch messages, resolve the user's accessible companies, then query 
messages → threads → locations → companies WHERE company_id IN (...)
. Concerns: - Join complexity: every entity needs a join path back to an anchor, and that join runs on every query. - Ambiguous join paths: some entities can reach an anchor through multiple paths (e.g. a thread relates to a location, a candidate, and an application). Which path do I join through? ### What I'm leaning toward Approach 1 feels much easier to implement, but the where in limit is a serious concern
Copy code
definition user {}

definition resource_group {
    relation parent: resource_group

    relation rel_enterprise_write: user
    relation rel_enterprise_read: user
    
    relation rel_company_write: user
    relation rel_company_read: user
    
    relation rel_location_write: user
    relation rel_location_read: user
    

    permission enterprise_read = rel_enterprise_read + parent->enterprise_read
    permission enterprise_write = rel_enterprise_write + parent->enterprise_write

    permission company_read = rel_company_read + parent->company_read
    permission company_write = rel_company_write + parent->company_write

    permission location_read = rel_location_read + parent->location_read
    permission location_write = rel_location_write + parent->location_write

}

definition enterprise {
  relation resource_group: resource_group

  permission write = resource_group->enterprise_write
  permission read = resource_group->enterprise_read
}

definition company {
  relation resource_group: resource_group
  relation parent: enterprise
  
  permission write = parent->write + resource_group->enterprise_write + resource_group->company_write
  permission read = parent->read + resource_group->enterprise_read + resource_group->company_read
}

definition location {
  relation resource_group: resource_group
  relation parent: company
  
  permission write = parent->write + resource_group->enterprise_write + resource_group->company_write + resource_group->location_write
  permission read = parent->read + resource_group->enterprise_read + resource_group->company_read + resource_group->location_read
}
Copy code
definition department {
  relation parent: company

  permission write = parent->write
  permission read = parent->read
}

definition job {
  relation parent: location | company | department

  permission write = parent->write
  permission read = parent->read
}

definition application {
  relation parent: company | candidate | job

  permission write = parent->write
  permission read = parent->read
}

definition candidate {
  relation parent: enterprise

  permission write = parent->write
  permission read = parent->read
}

definition thread {
  relation parent: location | candidate | application

  permission write = parent->write
  permission read = parent->read
}

definition message {
  relation parent: thread

  permission write = parent->write
  permission read = parent->read
}
y
if you're using postgres, have you seen the [foreign data wrapper](https://github.com/authzed/spicedb/tree/main/internal/fdw) implementation? it might let you offload some of the questions of prefiltering vs postfiltering to postgres itself. the caveat is that it's still an early/experimental feature.
and re: approaching your question more directly, there isn't a one-size-fits-all approach in my experience. if your data are small enough that LRs will work for your use case, that's typically the simplest from a conceptual standpoint; the fdw would let you implement code that looks a lot like this. postfiltering (and the subsequent join implications) get much more interesting, but you may be forced that direction if there are too many LR responses (ime it happens somewhere in the neighborhood of 1-10k, depending on schema complexity). the last option is that we've built [materialize](https://authzed.com/docs/authzed/concepts/authzed-materialize) to allow you to precompute permission checks and store them in your postgres database, where they'd turn into normal joins. that's a feature of our paid platform.
31 Views
PreviousNext
Powered by