[BUG] [Postgres/Kysely] "missing FROM-clause entry" when including relation with specific @@allow policy

[Natansal]

Issue Description

I am encountering a missing FROM-clause entry error when running a findMany query that includes a relation (User -> Association).

The issue seems to be caused by a specific Access Policy on the related model (Association) that compares a field to a static string (or potentially an injected value) twice. ZenStack generates a SQL query where it attempts to use the outer alias (User$associations$sub) inside the inner LATERAL subquery scope, where that alias is not yet defined.

Reproduction

1. Schema (schema.zmodel)

model User {
    id String @id @default(uuid())
    associations Association[]

    @@allow('all', true)
}

model Association {
    id String @id @default(uuid())
    userId String
    user User @relation(fields: [userId], references: [id])
    organizationId String?
    something Boolean @allow(userId == 'other static-uuid') // 💥 First security check

    // 💥 Second security check
    @@allow('read', organizationId == 'some-static-uuid')
    
    // (Note: It also happens if checking against auth() or other conditions, 
    // but this specific structure triggers the alias scoping bug)
    // The error does NOT happen if we omit even 1 of the 2 conditions
}

2. The Query
Running a standard findMany with an include:

const users = await db.user.findMany({
    where: { id: 'some-user-id' },
    include: {
        associations: true // <--- Trigger
    }
});

Error Log

kysely:error: error: missing FROM-clause entry for table "User$associations$sub"
    at .../pg/lib/client.js:624:17
    ...
    at PostgresConnection.executeQuery (.../kysely/dist/cjs/dialect/postgres/postgres-driver.js:93:49)
    ...
    at FindOperationHandler.read (.../zenstack/orm/src/client/crud/operations/base.ts:304:23)

ERROR [ExceptionsHandler] ORMError: Failed to execute query: error: missing FROM-clause entry for table "User$associations$sub"

The Malformed SQL

The generated SQL attempts to filter by User$associations$sub.organizationId inside the subquery that defines User$associations$sub.

LEFT JOIN LATERAL (
  SELECT ...
  FROM (
    SELECT ...
    FROM "public"."association" as "Association"
    -- ❌ BUG: "User$associations$sub" is not defined in this scope. 
    -- It is defined as the alias for the *result* of this subquery (see line below).
    WHERE "User$associations$sub"."organizationId" = $94 
  ) as "User$associations$sub" -- 👈 Alias defined here
  WHERE "User$associations$sub"."userId" = "User"."id"
) as "User$associations" ON true

Environment

• Database: PostgreSQL
• Adapter: Kysely (via @zenstackhq/orm)
• Framework: NestJS
• Packages:
• @zenstackhq/orm: 3.3.3 (or your current version)
• zenstack: 3.3.3
• kysely: 0.28.11
• pg: 8.18.0

Possible Cause

It appears the Kysely query generator is injecting the Access Policy filter (WHERE organizationId = ...) using the outer alias (User$associations$sub) instead of the inner table name (Association) when constructing the LATERAL JOIN subquery. Postgres scoping rules prevent the inner query from seeing the alias assigned to its own result.

[ymc9]

Hi @Natansal , I'm trying with v3.3.3 and can't seem to reproduce the issue. My TS code:

    const user = await db.user.create({
        data: {
            associations: {
                create: [
                    {
                        organizationId: 'some-static-uuid',
                    },
                    {
                        organizationId: 'other-uuid',
                    },
                ],
            },
        },
    });

    const authDb = db.$use(new PolicyPlugin());

    const result = await authDb.user.findMany({
        where: { id: user.id },
        include: { associations: true },
    });

    console.log(result);

Could you share a repro project?

[Natansal]

@ymc9 Hi! thanks for looking into the error.
I have made a mistake and the provided example does not reproduce the error.
I have updated the issue description and here is a repo with the error reacurring:
https://github.com/Natansal/Zenstack_error_reproduction

Please run -> npm run generate -> npm start

[ymc9]

Fixed in 3.4.0