@cerbos/orm-prisma

v4.1.0

Published

a month ago

Prisma adapter for Cerbos query plans

0High
0Medium
0Low

alexolivier

ahaines

cerbos prisma orm authorization permissions

Cerbos + Prisma ORM Adapter

An adapter library that takes a Cerbos Query Plan (PlanResources API) response and converts it into a Prisma where clause object. This is designed to work alongside a project using the Cerbos Javascript SDK.

Features

Supported Operators

Basic Operators

Logical operators: and, or, not
Comparison operators: eq, ne, lt, gt, lte, gte, in
String operations: startsWith, endsWith, contains
Null checks: eq/ne against a null value map to { equals: null } / { not: null }. The Cerbos planner emits no existence operator — R.attr.x != null arrives as a plain ne.

Relation Operators

One-to-one: is, isNot
One-to-many/Many-to-many: some, none, every
Collection operators: exists, all, except (exists_one requires counting matches, which Prisma where-filters cannot express — it throws rather than silently degrading to exists; filter only appears inside other expressions)
Set operations: hasIntersection

Arithmetic

add, sub, mult, div with a constant side are solved algebraically to a plain column comparison: R.attr.aNumber + 1 > 2 → { aNumber: { gt: 1 } } (Prisma where-filters cannot express column arithmetic). Multiplying/dividing by a negative constant mirrors directional operators. Arithmetic on both sides of a comparison, division BY a column, and equality or inequality over fractional addition throw. The latter cannot be solved reversibly in IEEE-754 arithmetic.
String concatenation solving: P.attr.context == "projects:" + R.attr.id → { id: { equals: "123" } }

Field-to-field comparisons

Comparisons between two columns of the same model compile to Prisma field references. Pass the Prisma model name via the model option (root columns) or relation.model in the mapper (columns of a related model inside a collection expression). Comparisons across models throw — Prisma only supports references between fields of the same model.

Hierarchy Operators

hierarchy(string), hierarchy(string, delimiter), hierarchy([segments])
overlaps: segment-wise prefix comparison between two hierarchies
ancestorOf / descendentOf: strict prefix relationship between hierarchies

Advanced Features

Deep nested relations support
Automatic field inference
Collection mapping and filtering
Complex condition combinations
Type-safe field mappings
Timestamp comparisons against Prisma DateTime columns. Mark the field mapping with valueType: "dateTime"; applying timestamp() to an untyped/string mapping throws. Literals must be strict RFC 3339 instants in CEL's supported year 0001–9999 range and exactly representable at millisecond precision (fractional digits after the third may only be zero). The mapped column/database must preserve that precision.
Outer-column references inside collection expressions (e.g. R.attr.tags.exists(t, t.name == "x" && R.attr.aBool)) are hoisted or case-split so every filter lands on the model it belongs to
Three-valued-logic guards for nullable element columns: mark a relation field as nullable: true in the mapper and collection macros (all, negated exists, hasIntersection over map) exclude rows whose elements hold NULL in that column, matching Cerbos's treatment of a missing attribute as a deny

Known limitations (loud failures, never silently-wrong filters)

LIKE wildcards: Prisma emits LIKE without an ESCAPE clause, so contains/startsWith/ endsWith with a needle containing % or _, or with a column-valued needle, throws. (A constant receiver with a column needle — "a-b".startsWith(R.attr.x) — is translated exactly by enumerating candidate needles into an in filter.)
Hierarchy prefixes: ancestorOf, descendentOf and overlaps narrow a column with a startsWith, so they throw when the constant hierarchy contains %, _ or [. [ is rejected as well as the two LIKE wildcards because SQL Server opens a character class on [ even when an ESCAPE clause is declared, so it cannot be matched literally at all.
Counting: exists_one, size() thresholds other than empty/non-empty, and string-length comparisons throw (_count is not supported inside Prisma where).
Cross-model column comparisons throw (Prisma field references are same-model only). This includes membership between an outer scalar column and a related collection column.

Database collation is an authorization invariant

Cerbos string comparisons are case-sensitive. Prisma delegates comparison semantics to the database collation, so a case-insensitive or accent-insensitive collation can make a generated authorization filter return rows that Cerbos would deny. Treat the database collation used by mapped authorization columns as part of the policy contract:

PostgreSQL: use a deterministic, case-sensitive collation and avoid citext or an insensitive Prisma query mode for mapped fields.
MySQL/MariaDB: choose a case-sensitive (_cs) or binary collation rather than the common case-insensitive (_ci) defaults.
SQL Server: use a case-sensitive (_CS_) collation rather than a case-insensitive (_CI_) collation.
SQLite: do not apply COLLATE NOCASE to mapped fields, and also set PRAGMA case_sensitive_like = ON — see below.

On SQLite, collation is not enough. contains, startsWith and endsWith lower to LIKE, and SQLite's LIKE is case-insensitive for ASCII regardless of the column's collation: a COLLATE BINARY column answers = 'one' case-sensitively and LIKE '%one%' case-insensitively in the same query. Only PRAGMA case_sensitive_like = ON changes it, and the pragma is per-connection, so it must be set on every connection the application uses — not once at schema creation.

That distinction is why this was missed for so long: the corpus's cs-eq action proved equality was case-sensitive on SQLite, and equality is the one operator collation does govern. The cs-contains, cs-startswith and cs-endswith actions now prove the string operators separately, and without the pragma this adapter over-grants every one of them on SQLite.

The adapter cannot override a column's collation, or set a pragma, from inside a Prisma where filter. See Prisma's case-sensitivity documentation for provider-specific details.

NULL attribute representation

R.attr.x == null compiles to the same eq(x, null) plan node however your application represents a NULL column in the attributes it sends to check(), so the adapter cannot infer the convention and has to be told which one you use.

| attributes you send for a NULL column | check() on that row | IS NULL filter | | --- | --- | --- | | {"x": null} — explicit null | allow | selects it — aligned | | {} — attribute omitted | deny (CEL missing-attribute error) | selects it — over-grants |

nullAttributeRepresentation defaults to "explicit", preserving the historical IS NULL translation. If your application omits attributes for NULL columns, set it to "omitted": the adapter then rejects every null comparison operand instead of emitting a filter that returns rows the PDP denies.

queryPlanToPrisma({ queryPlan, mapper, nullAttributeRepresentation: "omitted" });

The rejection is deliberately wider than the shapes that actually over-grant — x != null and !(x == null) are aligned under both conventions — because Prisma applies negation by wrapping ({ NOT: ... }) rather than pushing it into the leaf, so a leaf cannot tell whether an enclosing not will flip IS NOT NULL back into a NULL-selecting predicate. Rejecting every null operand is correct under any nesting. See #302.

Declare the convention per attribute

The option above is a whole-call default, and one policy suite can legitimately use both conventions: the same column mapped twice, sent as an explicit null under one attribute name and omitted under another. Declare it on the mapping instead and the call-level option only covers what the mapping does not:

const mapper = {
  // sent as an explicit null when the column is NULL
  "request.resource.attr.owner": {
    field: "ownerId",
    nullAttributeRepresentation: "explicit",
  },
  // omitted when the column is NULL — the call-level default applies
  "request.resource.attr.department": { field: "department" },
};

Declaring "explicit" asserts two things: the column can be NULL, and a NULL reaches check() as an explicit null. The equality family (eq, ne, in) over that attribute is then rendered so it can never be SQL UNKNOWN — CEL holds a null value under this convention, so null != "x" is TRUE and the row must come back, while UNKNOWN would drop it under both polarities. Ordering and string operators are left alone: a null receiver raises a no-overload error in CEL, which denies exactly as UNKNOWN does.

Leaving an attribute undeclared keeps the historical rendering — so nothing changes for a mapping that says nothing, and != against a constant keeps under-granting the NULL rows until you declare it.

Declare both sides of a field-to-field comparison, or neither. Mixing the conventions across one comparison has no faithful rendering — the declared side needs a definite answer for its NULL, the undeclared side needs UNKNOWN — so the adapter throws rather than picking a direction. See #308 and ADR 0004.

Conformance contract

The adapter is differentially tested against Cerbos PDP 0.54.0 checkResource decisions using 21 hostile seed rows, both Prisma 6 and 7, and both SQLite and PostgreSQL. The Spring Data adapter defines the reference semantics for this compatibility snapshot.

| Classification | Coverage | | --- | --- | | Oracle-tested | 136 reference actions | | Fail-closed | 51 reference actions plus the 10 reference-unsupported shapes (61 actions total) | | Representation-dependent | null-eq-missing — rejected under nullAttributeRepresentation: "omitted"; translated as IS NULL under the default, which over-grants if the caller omits attributes for NULL columns | | Attribute NULL convention | The equality family (eq, ne, in) over an attribute the caller sends as an explicit null renders definitely, so a NULL row is included where CEL's null value says it should be. Declare it per attribute — nullAttributeRepresentation: "explicit" on the mapper entry — or the historical rendering applies and != against a constant under-grants those rows (cerbos/query-plan-adapters#308) | | Known planner divergence | has() on a missing attribute is folded by the Cerbos planner to ALWAYS_ALLOWED, while checkResource denies the missing-attribute rows. Until the planner is fixed, use R.attr.x != null for database-backed attributes instead of has(R.attr.x) |

The fail-closed set consists of literal LIKE cases Prisma cannot escape safely, cross-model field references, arbitrary relation counts and string lengths, exists_one, unsolved column arithmetic, sub-millisecond now() thresholds, the reference probes for regex, ordered indexing, and timestamp() over a string field, mod, a positional read of a scalar list, and list equality over a map() projection. Supported timestamp plans require a mapper entry with valueType: "dateTime" and a strict, millisecond-exact RFC 3339 literal in CEL's supported instant range. These shapes throw instead of producing a broader authorization filter. Every fail-closed shape's error message is pinned in the shared corpus (conformance/actions.json) and asserted by this adapter's conformance run, so a classification proves the throw names its declared mechanism rather than merely that something threw.

The where input each of these actions produces is pinned separately, in the translator unit test (npm test) — every corpus action, classified there exactly once as an emitted filter, an unconditional plan kind, or a throw. That is what makes a change to the emitted SQL show up as a diff even when it selects the same rows from the corpus seeds.

Providers the contract is proved on

The classification above holds where the corpus is executed, not where the emitted filter merely looks plausible. Until #320 it was executed on SQLite only, and the Prisma 6/7 matrix is an engine matrix, not a provider one — it says nothing about how a provider coerces a value or reads a LIKE pattern. The suite now runs on SQLite and PostgreSQL:

The store and the Prisma major are independent dimensions, so there are four runs and CI does all four:

npm run test:adversarial:v7            # SQLite,     Prisma 7
npm run test:adversarial:v6            # SQLite,     Prisma 6
npm run test:adversarial:postgres:v7   # PostgreSQL, Prisma 7  (testcontainers)
npm run test:adversarial:postgres:v6   # PostgreSQL, Prisma 6  (testcontainers)

MySQL, SQL Server and CockroachDB are not executed. Where a fail-closed reason names one of them, it is reasoned from that provider's documented LIKE and escaping behaviour rather than observed.

Breaking change in this release. endsWith/contains/startsWith with a needle containing a backslash, and hierarchy prefixes containing one, now throw instead of returning a filter. A backslash is the default LIKE escape character on PostgreSQL and MySQL and has no meaning at all on SQLite, so one needle meant two different things: contains("a\\b") matched "ab" on PostgreSQL — a row the PDP denies — and endsWith("\\") failed the query outright with SQLSTATE 22025. There is no needle spelling that is correct on every provider without an ESCAPE clause Prisma does not emit, so the shape is refused. If you match on backslashes, compare the whole value with == or move the predicate out of the policy.

Mapping hazards

The conformance contract above proves the plan side — given a policy shape, does the filter select the rows check() allows. The other half is the mapping: the records the nested filter reads must be the records the application put into the resource attributes. Six ways that can break are catalogued in the shared corpus, and every adapter has to record a position on each of them.

This adapter names a Prisma relation, so the mapping looks as though the ORM will apply the application's own narrowing to the nested some/every/none. It will not. Prisma has no schema-level filtered relation and no @Where equivalent; a where injected by a client extension or by $extends/middleware rewrites the top-level query and leaves nested relation filters untouched. In corpus terms this adapter is class 1 — a bare-table subquery — despite naming a relation.

Where the application narrows its own reads of a related model, declare the same predicate as subqueryFilter on the relation and the adapter reproduces it. Declaring nothing emits exactly the filter this adapter emitted before the field existed — it cannot detect the omission, so silence is not a warning.

| Hazard | Position | Mechanism to check | |---|---|---| | Filtered association | Caller-owned, reproducible with subqueryFilter | A $extends/$use client extension or middleware that injects a where for the related model, or a repository helper that always appends one. None of them rewrite the nested filter this adapter returns | | Default scope on the target model | Caller-owned, reproducible with subqueryFilter | A soft-delete column (deletedAt: null), a tenant column, a published flag — anything every application read of the related model filters on. Prisma has no default-scope construct, so the convention lives in your own query code and only you can see it | | Subtype discrimination | Caller-owned, reproducible with subqueryFilter | A type/kind discriminator column where one model holds several row kinds. Declare { type: "…" } | | To-one relation used as a collection | Rejected by Prisma | A type: "one" mapping compiles to is, an argument Prisma only accepts on a relation its own schema declares to-one, and @relation(references: …) must already point at a unique field. Mapping a to-many relation as type: "one" is therefore a query-validation error from Prisma, not a silently wider subquery | | Composite association key | Reproduced by Prisma | Prisma resolves multi-column foreign keys itself from @relation(fields: […], references: […]). The mapping names the relation, never its columns, so there is no key for the adapter to get wrong | | Absent to-one parent | Reproduced, and proved by the corpus (w1-all-chain, rel-not-bool-hop and siblings) | None — every operator reached through a relation requires its to-one hops separately, so a missing parent stays denied under both polarities (#309, #315, #375). Behaviour change in #375: this previously held only for a chain of two or more relations. A negation over a SINGLE to-one hop — !R.attr.parent.aBool against a type: "one" mapping — returned every row whose relation was absent. It now emits AND: [{ parent: { is: {} } }, { NOT: … }] and returns fewer rows: an over-grant fix, and consumer-visible for any policy with that shape |

Declaring the application's own predicate

const result = queryPlanToPrisma({
  queryPlan,
  mapper: {
    "request.resource.attr.tags": {
      relation: {
        name: "tags",
        type: "many",
        field: "name",
        // Exactly the predicate your own reads of `Tag` apply.
        subqueryFilter: { deletedAt: null, kind: "label" },
      },
    },
  },
});

subqueryFilter is a Prisma where-input over the related model. It is ANDed into the nested filter, so it narrows the records the subquery examines rather than the records it requires, and it applies to every operator reached through the relation — exists, all, except, membership, hasIntersection, emptiness checks — and to the hop-existence guard, so an intermediate hop must exist and be visible.

all() is the one operator that cannot simply absorb the predicate: every: AND(declared, P) would require every record to satisfy the declaration, which is the opposite of ignoring what the application hides. It is rewritten to none: AND(declared, NOT P) — no visible record violates P. Note the consequence, which is correct rather than surprising: if the declaration hides every record of a relation, all() over it is vacuously true, exactly as it is for the application, which sends check() an empty list for the same reason.

Requirements

Cerbos > v0.40
@cerbos/http or @cerbos/grpc client
Prisma >= v6.0 (v7 supported)

System Requirements

Node.js >= 22.0.
Prisma CLI & Client >= 6.0 (v7 supported)
A database supported by Prisma (SQLite/PostgreSQL/MySQL/etc.) so the Prisma client can communicate with stored data

Installation

npm install @cerbos/orm-prisma

Usage

The package exports a function:

import { queryPlanToPrisma, PlanKind } from "@cerbos/orm-prisma";

queryPlanToPrisma({
  queryPlan,                // The Cerbos query plan response
  mapper,                   // Map Cerbos field names to Prisma field names
}): {
  kind: PlanKind,
  filters?: any             // Prisma where conditions
}

Basic Example

Create a basic policy file in the policies directory:

apiVersion: api.cerbos.dev/v1
resourcePolicy:
  resource: resource
  version: default
  rules:
    - actions: ["view"]
      effect: EFFECT_ALLOW
      roles: ["USER"]
      condition:
        match:
          expr: request.resource.attr.status == "active"

Start Cerbos PDP:

docker run --rm -i -p 3592:3592 -v $(pwd)/policies:/policies ghcr.io/cerbos/cerbos:latest

Create Prisma schema (prisma/schema.prisma):

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Resource {
  id     Int     @id @default(autoincrement())
  title  String
  status String
}

Implement the mapper

import { GRPC as Cerbos } from "@cerbos/grpc";
import { PrismaClient } from "@prisma/client";
import { queryPlanToPrisma, PlanKind } from "@cerbos/orm-prisma";

const prisma = new PrismaClient();
const cerbos = new Cerbos("localhost:3592", { tls: false });

// Fetch query plan from Cerbos
const queryPlan = await cerbos.planResources({
  principal: { id: "user1", roles: ["USER"] },
  resource: { kind: "resource" },
  action: "view",
});

// Convert query plan to Prisma filters
const result = queryPlanToPrisma({
  queryPlan,
  mapper: {
    "request.resource.attr.title": { field: "title" },
    "request.resource.attr.status": { field: "status" },
  },
});

if (result.kind === PlanKind.ALWAYS_DENIED) {
  return [];
}

// Use filters in Prisma query
const records = await prisma.resource.findMany({
  where: result.filters,
});

// Use filters in Prisma query with other conditions
const records = await prisma.resource.findMany({
  where: {
    AND: [
      {
        status: "DRAFT"
      },
      result.filters,
    ]
});

Collection Operators

The adapter understands the full Cerbos collection operator set, including except. For example, the configuration below ensures a resource’s categories do not have any sub-category named finance:

const result = queryPlanToPrisma({
  queryPlan,
  mapper: {
    "request.resource.attr.categories": {
      relation: {
        name: "categories",
        type: "many",
        fields: {
          subCategories: {
            relation: {
              name: "subCategories",
              type: "many",
              fields: {
                name: { field: "name" },
              },
            },
          },
        },
      },
    },
  },
});

queryPlanToPrisma emits the necessary nested NOT structure so Prisma receives a valid filter for the entire relation chain.

Field Name Mapping

Fields can be mapped using either an object or a function:

// Object mapping
const result = queryPlanToPrisma({
  queryPlan,
  mapper: {
    "request.resource.attr.fieldName": { field: "prismaFieldName" },
  },
});

// Function mapping
const result = queryPlanToPrisma({
  queryPlan,
  mapper: (fieldName) => ({
    field: fieldName.replace("request.resource.attr.", ""),
  }),
});

Relations Mapping

Relations are mapped with their types and optional field configurations. Fields can be automatically inferred from the path if not explicitly mapped.

The nested filters this produces read the related model unfiltered — Prisma has no schema-level filtered relation, and an injected where does not reach them. If your own reads of that model apply a predicate, declare it as subqueryFilter on the relation. See Mapping hazards.

const result = queryPlanToPrisma({
  queryPlan,
  mapper: {
    // Simple relation mapping - fields will be inferred
    "request.resource.attr.owner": {
      relation: {
        name: "owner",
        type: "one", // "one" for one-to-one, "many" for one-to-many
      },
    },

    // Relation with explicit field mapping
    "request.resource.attr.tags": {
      relation: {
        name: "tags",
        type: "many",
        field: "name", // Optional: specify field for direct comparisons
      },
    },

    // Relation with nested field mappings
    "request.resource.attr.nested": {
      relation: {
        name: "nested",
        type: "one",
        fields: {
          // Optional: specify mappings for nested fields
          aBool: { field: "aBool" },
          aNumber: { field: "aNumber" },
        },
      },
    },
  },
});

Field Inference Example

When using relations, fields are automatically inferred from the path unless explicitly mapped:

// These mappers are equivalent for handling: request.resource.attr.nested.aNumber
{
  "request.resource.attr.nested": {
    relation: {
      name: "nested",
      type: "one",
      fields: {
        aNumber: { field: "aNumber" }
      }
    }
  }
}

// Shorter version - aNumber will be inferred from the path
{
  "request.resource.attr.nested": {
    relation: {
      name: "nested",
      type: "one"
    }
  }
}

Handling `in` Operators

queryPlanToPrisma normalises Cerbos in expressions to match Prisma expectations:

Single values become equality comparisons ({ field: "value" }).
Arrays remain { field: { in: [...] } }.
Relation-backed fields retain their relation structure while still applying the appropriate equality or in operator at the leaf.

Complex Example with Multiple Relations and Direct Fields

const result = queryPlanToPrisma({
  queryPlan,
  mapper: {
    "request.resource.attr.status": { field: "status" },
    "request.resource.attr.owner": {
      relation: {
        name: "owner",
        type: "one",
      },
    },
    "request.resource.attr.tags": {
      relation: {
        name: "tags",
        type: "many",
        field: "name",
      },
    },
  },
});

// Results in Prisma filters like:
const result = await primsa.resource.findMany({
  where: {
    AND: [
      { status: { equals: "active" } },
      { owner: { is: { id: { equals: "user1" } } } },
      { tags: { some: { name: { in: ["tag1", "tag2"] } } } },
    ];
  }
})

Complex Examples

Lambda Expression Examples

// Using exists with lambda expressions
const result = queryPlanToPrisma({
  queryPlan,
  mapper: {
    "request.resource.attr.comments": {
      relation: {
        name: "comments",
        type: "many",
        fields: {
          author: {
            relation: {
              name: "author",
              type: "one",
            },
          },
          status: { field: "status" },
        },
      },
    },
  },
});

// This can handle complex exists queries like:
// "Does the resource have any approved comments by specific users?"
const result = await primsa.resource.findMany({
  where: {
    comments: {
      some: {
        AND: [
          { status: { equals: "approved" } },
          {
            author: {
              is: {
                id: { in: ["user1", "user2"] },
              },
            },
          },
        ],
      },
    },
  },
});

Development

Running Tests

npm test

This is the translator unit test: for every action in the shared conformance corpus, the where input this adapter emits. It reads its plans from conformance/wire-fixtures/ — the golden PlanResources responses captured against the pinned Cerbos version — so it needs no Cerbos sidecar, no database, and no generated Prisma client, and it is engine-agnostic (there is no v6/v7 split). It also pins the shapes the adapter refuses, with the message conformance/actions.json records, and the parts of the mapper contract no policy can reach: the nullAttributeRepresentation boundary, subqueryFilter, and malformed input.

Every wire fixture must be classified there exactly once, so adding a corpus action fails this suite until someone records the filter it produces. See ADR 0006.

Whether those filters return the rows the PDP allows is a separate question, answered by the adversarial suite — see Conformance contract above, which lists the four runs and what each one covers. That suite does need a Cerbos sidecar, and resets prisma/dev-adversarial.db with prisma db push --force-reset, so run it only against disposable development databases.

Types

Query Plan Response Types

The adapter is fully typed and provides clear type definitions for all responses:

import { PlanKind, QueryPlanToPrismaResult } from "@cerbos/orm-prisma";

// The result will be one of these types:
type QueryPlanToPrismaResult =
  | {
      kind: PlanKind.ALWAYS_ALLOWED | PlanKind.ALWAYS_DENIED;
    }
  | {
      kind: PlanKind.CONDITIONAL;
      filters: Record<string, any>;
    };

// Example usage with type narrowing:
const result = queryPlanToPrisma({ queryPlan });

if (result.kind === PlanKind.CONDITIONAL) {
  // TypeScript knows `filters` exists here
  const records = await prisma.resource.findMany({
    where: result.filters,
  });
} else if (result.kind === PlanKind.ALWAYS_ALLOWED) {
  // No filters needed
  const records = await prisma.resource.findMany();
} else {
  // Must be ALWAYS_DENIED
  return [];
}

Mapper Types

The mapper configuration is also fully typed:

type MapperConfig = {
  field?: string;
  valueType?: "dateTime";
  nullable?: boolean;
  relation?: {
    name: string;
    type: "one" | "many";
    model?: string;
    field?: string;
    fields?: {
      [key: string]: MapperConfig; // Recursive for nested fields
    };
  };
};

type Mapper = { [key: string]: MapperConfig } | ((key: string) => MapperConfig);

Full Example

A complete example application using this adapter can be found at https://github.com/cerbos/express-prisma-cerbos

This repository also carries a runnable example/, which installs the adapter from the artifact npm publish would upload and exercises it against a live PDP over the shared demo domain:

# from the repository root
demo/scripts/run-example.sh prisma

Unlike the test suites, it resolves the adapter through its published surface — the exports map, types, the files allowlist, and the peer range — and covers usage shapes past a single flat query: pagination, and the adapter's filter composed with an application-owned filter.