Filtering database results with Cerbos query plans

Authorization decisions often happen at two distinct moments. First, when checking if a user can act on a specific resource, they already have. Second, when listing all the resources a user is allowed to access before they are even fetched. That second scenario has traditionally been a pain point for authorization systems.

The PlanResources API solves this elegantly through partial evaluation. Instead of checking every single resource in your database against your policies, Cerbos analyzes your policies and generates a query plan. This plan describes the exact conditions that resources must meet for the principal to have access. You then translate these conditions into your database query language and fetch only the permitted resources in a single, efficient query.

Understanding partial evaluation in PlanResources

When you call the PlanResources API, you provide the principal, the resource kind, and the action. Unlike a standard CheckResources call, you don't provide a specific resource instance. Cerbos evaluates your policies as far as it can with only this information and returns one of three results.

The first possibility is KIND_ALWAYS_ALLOWED. The principal can perform the action on all resources of this type, regardless of their attributes. Think of an admin viewing all leave requests. The second is KIND_ALWAYS_DENIED. No matter what attributes the resources have, this principal cannot perform this action. A contractor trying to approve employee leave requests would hit this. The third and most interesting case is KIND_CONDITIONAL. The principal is allowed, but only for resources that meet specific criteria.

What makes this powerful is that Cerbos returns these criteria as an abstract syntax tree (AST). A policy condition like request.resource.attr.status == "PENDING_APPROVAL" && request.resource.attr.department == "marketing" becomes a nested structure of operators and operands. This structured format makes it straightforward to convert into any query language.

Dissecting a real-world adapter

An adapter's purpose is to transform the Cerbos query plan AST into a filter object native to a specific ORM or database. Analyzing an existing adapter, like the one for Prisma, reveals the key patterns for building your own.

The core of any adapter is a recursive function that traverses the AST. This function inspects each node and determines if it's an expression (like equals or and), a variable (a field name), or a literal value (a string, number, or boolean).

The real work happens in the operator-handling logic. This is typically a switch statement that maps each Cerbos operator to its backend-specific equivalent. For example:

The adapter must also support relational operators like some, none, is, and isNot. These are crucial for policies that filter records based on their relationships. For instance, a policy to "find all projects that have some tasks with a 'high' priority" relies on this capability.

A crucial feature is the field mapping system. Your Cerbos policies reference attributes like request.resource.attr.ownerId, but your database schema might name the field owner_id. A configurable mapper bridges this gap, keeping your policies decoupled from your database schema.

Finally, for typed languages, returning a discriminated union is a powerful pattern. The result type clearly distinguishes between ALWAYS_ALLOWED, ALWAYS_DENIED, and CONDITIONAL states. This provides compile-time type safety, preventing runtime errors and making the adapter easier and safer to use.

Building your own query plan adapter

Creating an adapter for your database or ORM starts with understanding your target query language's filtering capabilities.

1. Parse the AST
The first step is to create a recursive visitor function that walks the query plan AST. For each node, it will delegate to the appropriate handler based on the node type: expression, value, or variable.

function visitNode(node) {
  if (node.expression) {
    const { operator, operands } = node.expression;
    // Recursively visit operands before passing to the operator handler
    return handleOperator(operator, operands.map(visitNode));
  }
  if (node.value !== undefined) {
    // Return the literal value for the backend to use
    return node.value;
  }
  if (node.variable) {
    // Look up the field name in your mapper
    return mapFieldName(node.variable);
  }
}

2. Implement operator handlers
Translate each Cerbos operator into your backend's equivalent. Start with the basics like eq, ne, lt, gt, and logical operators and/or/not. Pay special attention to operators like in, which require handling arrays, and isSet, which checks for the presence or absence of a field and often translates to a NULL check.

3. Design a flexible field mapper
Your adapter needs to transform Cerbos's request.resource.attr.fieldName paths into the database column or document field names. Your mapping layer should handle simple name changes and nested paths for relationships.

4. Test thoroughly
Your test suite should verify every supported operator. Use static query plan JSON objects as input and assert that the generated filter matches the exact structure your backend expects. Look at reference adapters for examples of comprehensive test cases covering logical, comparison, and relational operators.

Optimizing query performance

Query plans push authorization logic down to the database, where it can be highly optimized.

The conditions Cerbos generates often align with your application's common query patterns. If users frequently filter records by department and status, your authorization policies likely check these same fields. This means your existing database indexes may already accelerate your authorization queries.

Pay close attention to policies that use or conditions or filter on related entities, as these can generate less efficient queries. Monitor your database's slow query logs after deploying your adapter to identify opportunities for adding composite or foreign key indexes.

Debugging and maintenance

Production issues often stem from mismatched field names or unexpected null values. Your adapter should have robust logging that captures the input query plan and the generated native query. Seeing them side-by-side is the fastest way to spot transformation errors.

The filterDebug field in the query plan response provides a human-readable representation of the conditions. Logging this during development can help you verify your adapter's logic.

Design your adapter to fail gracefully. If it encounters an unknown operator from a future Cerbos version, it should fail closed (deny access) rather than open. Log these events so you know which new operators to implement next. As Cerbos evolves - for instance, by adding string operations like startsWith or endsWith - you can incrementally update your adapter to support them.

By translating abstract authorization rules into concrete, optimized database queries, query plans transform authorization from a potential bottleneck into a highly efficient data-layer operation. A well-built adapter is the key to unlocking this performance and simplifying your application code.

If you haven’t tried Cerbos Hub yet, you can learn more and try it for free here. Feel free to book a call with a Cerbos engineer to see how our solution can help streamline access control in your applications.