PostgreSQL: policy check fails with "operator does not exist: text = uuid" when auth() field has @db.Uuid

[niehaus1301]

Description

When using PostgreSQL with UUID primary keys (@db.Uuid) and access policies that reference auth().userId, the policy-check SQL fails at runtime with:

operator does not exist: text = uuid

Reproduction

ZModel schema

model User {
  userId String @id @db.Uuid @default(uuid())
  @@auth()
}

model Client {
  clientId     String     @id @db.Uuid @default(uuid())
  dealershipId String     @db.Uuid
  Dealership   Dealership @relation(fields: [dealershipId], references: [dealershipId])

  @@allow('all', Dealership.UserAccess?[userId == auth().userId])
}

model DealershipUserAccess {
  dealershipId String @db.Uuid
  userId       String @db.Uuid
  // ...relations
}

What happens

1. auth().userId is resolved as a JavaScript string value (e.g., "550e8400-e29b-41d4-a716-446655440000")
2. The valueMemberAccess method in expression-transformer.ts calls transformValue(value, 'String') — because the ZModel type is String
3. transformValue passes the string through to the PostgreSQL dialect's transformInput, which is a no-op for strings
4. Kysely creates a parameterized query binding the value as $1 with PostgreSQL type text
5. The generated SQL compares this text parameter against a uuid column: "DealershipUserAccess"."userId" = $1
6. PostgreSQL raises operator does not exist: text = uuid because there is no implicit cast from text to uuid

Root cause

In packages/plugins/policy/src/expression-transformer.ts, the transformValue method receives only the BuiltinType (e.g., 'String'). It does not have access to the field's native database type attribute (@db.Uuid), which is available in the FieldDef.attributes array as { name: "@db.Uuid" }.

The PostgreSQL dialect's transformInput method (in packages/orm/src/client/crud/dialects/postgresql.ts) has no handling for UUID values — it falls through to the default pass-through for strings.

Expected behavior

When a String field has @db.Uuid (or other native type attributes), the generated SQL should cast the parameter value appropriately. For example:

-- Current (broken):
"DealershipUserAccess"."userId" = $1   -- $1 is text, column is uuid → ERROR

-- Expected (working):
"DealershipUserAccess"."userId" = $1::uuid   -- explicit cast

Potential fix approaches

1. Extend transformValue / transformInput to accept native type info: Pass the @db.Uuid attribute information from FieldDef.attributes through to the value transformation, and have the PostgreSQL dialect wrap the value in a ::uuid cast using sql.raw or eb.cast().

2. Handle it at the binary comparison level: In _binary or transformAuthBinary, when one side is a column reference to a @db.Uuid field and the other is a value, wrap the value with a cast.

Current workaround

Users can work around this by adding an implicit cast in a Prisma migration:

CREATE CAST (text AS uuid) WITH INOUT AS IMPLICIT;

However, this is a global database-level change that may have unintended side effects.

Environment

• ZenStack: v3.3.3
• PostgreSQL: 16
• Prisma: 6.x
• Node.js: 22

[uzarsalan]

Second code path with the same issue: postUpdateCheck → buildValuesTableSelect

While the fix in #2396 correctly addresses the auth().field → valueMemberAccess path, there is a second, independent code path that produces the same operator does not exist: uuid = text error.

Where it happens

When a model has @@deny post-update policies that reference before(), ZenStack runs a postUpdateCheck after the UPDATE. This check builds a VALUES(...) subquery via buildValuesTableSelect in PostgresCrudDialect (packages/orm/src/client/crud/dialects/postgresql.ts):

buildValuesTableSelect(fields, rows) {
  return eb.selectFrom(sql`(VALUES ...)`.as("$values"))
    .select(fields.map((f, i) => {
      const mappedType = this.getSqlType(f.type); // "String" → always "text"
      // ...
      return this.eb.cast(sql.ref(`$values.column${i+1}`), castType).as(f.name);
    }));
}

getSqlType("String") always returns "text", so UUID primary keys get CAST AS text. The resulting query then does:

LEFT JOIN (
  SELECT CAST("$values"."column1" AS text) AS "id", ...  -- BUG: should be uuid
  FROM (VALUES ($1::text, ...)) AS "$values"
) AS "$before" ON exchange_requests.id = "$before".id   -- uuid = text → ERROR

Reproduction

Any model with:

1. UUID primary key (@db.Uuid)
2. @@deny post-update policy that uses before() accessor

model ExchangeRequest {
  id     String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  status String

  @@allow('update', true)
  @@deny('post-update', before().status == status)  // triggers buildValuesTableSelect
}

Calling .update({ where: { id }, data: { status: 'new' } }) on this model throws:

operator does not exist: uuid = text

Fix

In buildValuesTableSelect, check for @db.Uuid attribute and use "uuid" instead of "text":

buildValuesTableSelect(fields, rows) {
  return eb.selectFrom(sql`(VALUES ...)`.as("$values"))
    .select(fields.map((f, i) => {
      let mappedType = this.getSqlType(f.type);
      if (f.attributes?.some((attr) => attr.name === "@db.Uuid")) {
        mappedType = "uuid";
      }
      const castType = f.array ? sql`${sql.raw(mappedType)}[]` : sql.raw(mappedType);
      return this.eb.cast(sql.ref(`$values.column${i+1}`), castType).as(f.name);
    }));
}

TDD verification (SQL level)

RED — without fix:

SELECT COUNT(1) = 1 AS "$condition"
FROM test_items
LEFT JOIN (
  SELECT CAST("$values"."column1" AS text) AS "id"  -- text!
  FROM (VALUES ($1::text)) AS "$values"
) AS "$before" ON test_items.id = "$before".id       -- uuid = text → ERROR ✓
WHERE test_items.id = $2::uuid

GREEN — with fix:

SELECT CAST("$values"."column1" AS uuid) AS "id"    -- uuid ✓

This is a separate bug from #2394 / #2396 — it lives in @zenstackhq/orm rather than @zenstackhq/plugin-policy, and is triggered specifically by postUpdateCheck with before() accessor in post-update policies.

[ymc9]

Expected behavior

When a String field has @db.Uuid (or other native type attributes), the generated SQL should cast the parameter value appropriately. For example:

-- Current (broken):
"DealershipUserAccess"."userId" = $1 -- $1 is text, column is uuid → ERROR

-- Expected (working):
"DealershipUserAccess"."userId" = $1::uuid -- explicit cast

Hi @niehaus1301 , I'm trying to better understand the root cause of your case. As far as I understand, when pg compares a field with a literal, it'll automatically try converting the literal to a compatible case, so in your example, $1 should be internally implicitly cast to uuid (of course, if it's a valid one).

Such query seems fine in pg ("id" is a uuid field):

select * from foo where id = '41652c18-1a29-45c0-bdb4-d5e26893cb25';

I do see there'll be issues in other cases where the operand is not a literal (e.g., comparing against a "String" field, or for cases as shown by @uzarsalan ).

[niehaus1301]

Hi @ymc9

I have these patch files which I currently use for this issue:

@zenstackhq+orm+3.4.5.patch

diff --git a/node_modules/@zenstackhq/orm/dist/index.cjs b/node_modules/@zenstackhq/orm/dist/index.cjs
index dfaae24..e07f785 100644
--- a/node_modules/@zenstackhq/orm/dist/index.cjs
+++ b/node_modules/@zenstackhq/orm/dist/index.cjs
@@ -2211,7 +2211,8 @@ var PostgresCrudDialect = class _PostgresCrudDialect extends LateralJoinDialectB
     }
     const eb = (0, import_kysely4.expressionBuilder)();
     return eb.selectFrom(import_kysely4.sql`(VALUES ${import_kysely4.sql.join(rows.map((row) => import_kysely4.sql`(${import_kysely4.sql.join(row.map((v) => import_kysely4.sql.val(v)))})`), import_kysely4.sql.raw(", "))})`.as("$values")).select(fields.map((f, i) => {
-      const mappedType = this.getSqlType(f.type);
+      // Workaround: https://github.com/zenstackhq/zenstack/issues/2394
+      const mappedType = f.attributes?.some((attr) => attr.name === "@db.Uuid") ? "uuid" : this.getSqlType(f.type);
       const castType = f.array ? import_kysely4.sql`${import_kysely4.sql.raw(mappedType)}[]` : import_kysely4.sql.raw(mappedType);
       return this.eb.cast(import_kysely4.sql.ref(`$values.column${i + 1}`), castType).as(f.name);
     }));
diff --git a/node_modules/@zenstackhq/orm/dist/index.js b/node_modules/@zenstackhq/orm/dist/index.js
index 0d320ea..68fe08c 100644
--- a/node_modules/@zenstackhq/orm/dist/index.js
+++ b/node_modules/@zenstackhq/orm/dist/index.js
@@ -2165,7 +2165,8 @@ var PostgresCrudDialect = class _PostgresCrudDialect extends LateralJoinDialectB
     }
     const eb = expressionBuilder3();
     return eb.selectFrom(sql3`(VALUES ${sql3.join(rows.map((row) => sql3`(${sql3.join(row.map((v) => sql3.val(v)))})`), sql3.raw(", "))})`.as("$values")).select(fields.map((f, i) => {
-      const mappedType = this.getSqlType(f.type);
+      // Workaround: https://github.com/zenstackhq/zenstack/issues/2394
+      const mappedType = f.attributes?.some((attr) => attr.name === "@db.Uuid") ? "uuid" : this.getSqlType(f.type);
       const castType = f.array ? sql3`${sql3.raw(mappedType)}[]` : sql3.raw(mappedType);
       return this.eb.cast(sql3.ref(`$values.column${i + 1}`), castType).as(f.name);
     }));

@zenstackhq+plugin-policy+3.4.5.patch

diff --git a/node_modules/@zenstackhq/plugin-policy/dist/index.cjs b/node_modules/@zenstackhq/plugin-policy/dist/index.cjs
index b0981f6..1cb4090 100644
--- a/node_modules/@zenstackhq/plugin-policy/dist/index.cjs
+++ b/node_modules/@zenstackhq/plugin-policy/dist/index.cjs
@@ -349,7 +349,7 @@ var ExpressionTransformer = class {
   _field(expr2, context) {
     if (context.contextValue) {
       const fieldDef2 = import_orm3.QueryUtils.requireField(this.schema, context.modelOrType, expr2.field);
-      return this.transformValue(context.contextValue[expr2.field], fieldDef2.type);
+      return this.transformValue(context.contextValue[expr2.field], fieldDef2.type, fieldDef2);
     }
     const fieldDef = import_orm3.QueryUtils.requireField(this.schema, context.modelOrType, expr2.field);
     if (!fieldDef.relation) {
@@ -626,7 +626,7 @@ var ExpressionTransformer = class {
       ]);
     }
   }
-  transformValue(value, type) {
+  transformValue(value, type, fieldDef) {
     if (value === true) {
       return trueNode(this.dialect);
     } else if (value === false) {
@@ -634,6 +634,10 @@ var ExpressionTransformer = class {
     } else if (Array.isArray(value)) {
       return this.dialect.buildArrayValue(value.map((v) => new import_kysely2.ExpressionWrapper(this.transformValue(v, type))), type).toOperationNode();
     } else {
+      // Workaround for https://github.com/zenstackhq/zenstack/issues/2394
+      if (fieldDef?.attributes?.some((attr) => attr.name === "@db.Uuid") && typeof value === "string") {
+        return import_kysely2.sql`${import_kysely2.sql.val(value)}::uuid`.toOperationNode();
+      }
       const transformed = this.dialect.transformInput(value, type, false) ?? null;
       if (typeof transformed !== "string") {
         return import_kysely2.ValueNode.createImmediate(transformed);
@@ -822,9 +826,10 @@ var ExpressionTransformer = class {
         curr = import_kysely2.ValueNode.createImmediate(null);
         break;
       }
-      currType = import_orm3.QueryUtils.requireField(this.schema, currType, field).type;
+      const fieldDef = import_orm3.QueryUtils.requireField(this.schema, currType, field);
+      currType = fieldDef.type;
       if (i === expr2.members.length - 1) {
-        curr = this.transformValue(curr, currType);
+        curr = this.transformValue(curr, currType, fieldDef);
       }
     }
     return curr;
diff --git a/node_modules/@zenstackhq/plugin-policy/dist/index.js b/node_modules/@zenstackhq/plugin-policy/dist/index.js
index b0f79ee..cda6b5f 100644
--- a/node_modules/@zenstackhq/plugin-policy/dist/index.js
+++ b/node_modules/@zenstackhq/plugin-policy/dist/index.js
@@ -325,7 +325,7 @@ var ExpressionTransformer = class {
   _field(expr2, context) {
     if (context.contextValue) {
       const fieldDef2 = QueryUtils.requireField(this.schema, context.modelOrType, expr2.field);
-      return this.transformValue(context.contextValue[expr2.field], fieldDef2.type);
+      return this.transformValue(context.contextValue[expr2.field], fieldDef2.type, fieldDef2);
     }
     const fieldDef = QueryUtils.requireField(this.schema, context.modelOrType, expr2.field);
     if (!fieldDef.relation) {
@@ -602,7 +602,7 @@ var ExpressionTransformer = class {
       ]);
     }
   }
-  transformValue(value, type) {
+  transformValue(value, type, fieldDef) {
     if (value === true) {
       return trueNode(this.dialect);
     } else if (value === false) {
@@ -610,6 +610,10 @@ var ExpressionTransformer = class {
     } else if (Array.isArray(value)) {
       return this.dialect.buildArrayValue(value.map((v) => new ExpressionWrapper(this.transformValue(v, type))), type).toOperationNode();
     } else {
+      // Workaround for https://github.com/zenstackhq/zenstack/issues/2394
+      if (fieldDef?.attributes?.some((attr) => attr.name === "@db.Uuid") && typeof value === "string") {
+        return sql`${sql.val(value)}::uuid`.toOperationNode();
+      }
       const transformed = this.dialect.transformInput(value, type, false) ?? null;
       if (typeof transformed !== "string") {
         return ValueNode2.createImmediate(transformed);
@@ -798,9 +802,10 @@ var ExpressionTransformer = class {
         curr = ValueNode2.createImmediate(null);
         break;
       }
-      currType = QueryUtils.requireField(this.schema, currType, field).type;
+      const fieldDef = QueryUtils.requireField(this.schema, currType, field);
+      currType = fieldDef.type;
       if (i === expr2.members.length - 1) {
-        curr = this.transformValue(curr, currType);
+        curr = this.transformValue(curr, currType, fieldDef);
       }
     }
     return curr;

This is how I declare my zmodel

model User {
  userId String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid

Let me know if there is anything I can do to help you better understand the issue.

Thanks,
Leonard

[ymc9]

Fixed in 3.5.3