zenstackhq
Invalid result when using take (LIMIT)
Description and expected behavior Using take on enhanced prisma returns different result than prisma for the same expected result.
Exemple code:
- usuarioId on Aluno table is the user id
` export async function getPrismaEnhanced(req: NextApiRequest, res: NextApiResponse) { const userId = await userIdFromRequest(req); let user: auth.Usuario | null = null;
if (userId != null) {
user = { id: userId };
} else {
sendError(res, StatusCodes.LOCKED, "LOCKED");
return;
}
const dispositivoId =
req.headers["x-dispositivo-id"] ?? req.headers["x-device-id"];
if (dispositivoId != null) {
prisma.$executeRawUnsafe(`SET var.device_id = '${dispositivoId}'`);
}
const teste = await prisma.aluno.findMany({
take: 10,
where: {
usuarioId: userId,
},
});
console.log("teste", teste);
const prismaEnhanced = enhance(
prisma,
{ user: user ?? undefined },
{
logPrismaQuery: true,
},
);
const teste2 = await prismaEnhanced.aluno.findMany({
take: 10,
});
console.log("teste2", teste2);
return prismaEnhanced;
}`
Logging the statements at prisma level on pure prisma: SELECT "public"."aluno"."id", "public"."aluno"."usuarioid", "public"."aluno"."dthrcriacao", "public"."aluno"."dthratualizacao", "public"."aluno"."nome", "public"."aluno"."sobrenome", "public"."aluno"."celular", "public"."aluno"."email", "public"."aluno"."observacoes", "public"."aluno"."saldodeaulas", "public"."aluno"."dtnascimento", "public"."aluno"."peso", "public"."aluno"."altura", "public"."aluno"."removido" FROM "public"."aluno" WHERE "public"."aluno"."usuarioid" = $1 ORDER BY "public"."aluno"."id" ASC LIMIT $2 offset $3
And on enhanced:
SELECT "public"."aluno"."id", "public"."aluno"."usuarioid", "public"."aluno"."dthrcriacao", "public"."aluno"."dthratualizacao", "public"."aluno"."nome", "public"."aluno"."sobrenome", "public"."aluno"."celular", "public"."aluno"."email", "public"."aluno"."observacoes", "public"."aluno"."saldodeaulas", "public"."aluno"."dtnascimento", "public"."aluno"."peso", "public"."aluno"."altura", "public"."aluno"."removido" FROM "public"."aluno" WHERE 1 = 1 ORDER BY "public"."aluno"."id" ASC LIMIT $1 offset $2
and some records are skipped: prisma:info [policy] dropping aluno entity due to entity checker prisma:info [policy] dropping aluno entity due to entity checker prisma:info [policy] dropping aluno entity due to entity checker prisma:info [policy] dropping aluno entity due to entity checker prisma:info [policy] dropping aluno entity due to entity checker prisma:info [policy] dropping aluno entity due to entity checker prisma:info [policy] dropping aluno entity due to entity checker prisma:info [policy] dropping aluno entity due to entity checker
the records from other user...
An workaround is using on enhanced the same where
Environment (please complete the following information):
- ZenStack version: 2.2.3
- Prisma version: 5.15.0
- Database type: 16
Additional context I guess need be highlighted the fact that the where need be still used;
Hi @israelins85 , thanks for reporting this. Could you share a ZModel snippet that can be used to reproduce the problem?
abstract model AbsModelPadrao {
id String @id @default(dbgenerated("uuid_generate_v7()")) @db.Uuid
dtHrCriacao DateTime @default(now()) @db.Timestamptz
dtHrAtualizacao DateTime @default(now()) @updatedAt() @db.Timestamptz
}
model Usuario extends AbsModelPadrao {
nome String
sobrenome String?
login String @unique
senha String @password @omit
codIdioma String @default("en_US")
dtFimTeste DateTime @db.Date @default(dbgenerated("(CURRENT_DATE + '30 days'::INTERVAL)")) @deny("update", true)
pin String? @omit
status UsuarioStatus @default(NaoAtivado)
// users can change its own data
@@allow("all", auth() != null && auth().id == id)
@@auth()
alunos Aluno[]
}
abstract model AbsModelDadosDoUsuario {
id String @id @default(dbgenerated("uuid_generate_v7()")) @db.Uuid
usuarioId String @db.Uuid @default(auth().id)
dtHrCriacao DateTime @default(now()) @db.Timestamptz
dtHrAtualizacao DateTime @default(now()) @updatedAt() @db.Timestamptz
// relations
usuario Usuario @relation(fields: [usuarioId], references: [id], onDelete: Cascade)
// allow acess to myself
@@allow("all", auth() != null && auth().id == usuarioId)
@@index([usuarioId])
}
model Aluno extends AbsModelDadosDoUsuario {
nome String
sobrenome String?
celular String?
email String?
observacoes String?
saldoDeAulas Int @default(0)
dtNascimento DateTime? @db.Date
peso Float?
altura Float?
removido Boolean @default(false)
@@prisma.passthrough("@@index([nome(ops: raw(\"gin_trgm_ops\"))], type: Gin, name: \"Aluno_nome_idx\")")
}
Hi @israelins85 , I had a try but couldn't reproduce this issue with v2.2.3. I've shared a project here: https://github.com/ymc9/issue-1519
Could you check what differences it may have with your project? Thanks!
Hey, I'm having same issue.
Example of such model would be:
model Space {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
name String @unique @db.Text
userAccess UserAccess[]
@@allow('read', userAccess?[(userId == auth().id && spaceId == this.id)])
}
model UserAccess {
id String @id @default(cuid())
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
userId String
space Space @relation(fields: [spaceId], references: [id], onDelete: Cascade)
spaceId String
@@allow('read', userId == auth().id)
}
model SpaceBot{
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
name String
space Space @relation(fields: [spaceId], references: [id])
spaceId String
@@allow('read', space.userAccess?[userId == auth().id]
}
If I do:
db.spaceBot.findMany({take: 1})
and I have many SpaceBots where user has no access to, then result is just empty with one message:
prisma:info [policy] dropping patient entity due to entity checker
Prisma queries are like:
===================
prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2
prisma:query SELECT "public"."UserAccess"."id", "public"."UserAccess"."userId", "public"."UserAccess"."spaceId" FROM "public"."UserAccess" WHERE "public"."UserAccess"."spaceId" IN ($1,$2,$3,$4,$5) OFFSET $6
prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2
What I noticed is that generated queries do LIMIT and OFFSET furst on all records and then policy filters are applied which can lead to returning not expecting results.
A workaround for this is always fill the where's with the expected filter of data, forgetting the fact zenstack will do this, because internally the limit runs without any where, and before returned to us they are filtered.
Thanks for suggestion. But I thought that's exactly what ZenStack is made for. I have feeling like something is off either with zmodel or there's bug. I'll try to reproduce it in new project and share it here.
Hey, I'm having same issue.
Example of such model would be:
model Space { id String @id @default(cuid()) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt name String @unique @db.Text userAccess UserAccess[] @@allow('read', userAccess?[(userId == auth().id && spaceId == this.id)]) } model UserAccess { id String @id @default(cuid()) user User @relation(fields: [userId], references: [id], onDelete: Cascade) userId String space Space @relation(fields: [spaceId], references: [id], onDelete: Cascade) spaceId String @@allow('read', userId == auth().id) } model SpaceBot{ id String @id @default(cuid()) createdAt DateTime @default(now()) updatedAt DateTime @updatedAt name String space Space @relation(fields: [spaceId], references: [id]) spaceId String @@allow('read', space.userAccess?[userId == auth().id] }If I do:
db.spaceBot.findMany({take: 1})and I have many SpaceBots where user has no access to, then result is just empty with one message:
prisma:info [policy] dropping patient entity due to entity checkerPrisma queries are like:
=================== prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2 prisma:query SELECT "public"."UserAccess"."id", "public"."UserAccess"."userId", "public"."UserAccess"."spaceId" FROM "public"."UserAccess" WHERE "public"."UserAccess"."spaceId" IN ($1,$2,$3,$4,$5) OFFSET $6 prisma:query SELECT "public"."Space"."id", "public"."Space"."createdAt", "public"."Space"."updatedAt", "public"."Space"."name" FROM "public"."Space" WHERE 1=1 ORDER BY "public"."Space"."updatedAt" DESC LIMIT $1 OFFSET $2What I noticed is that generated queries do LIMIT and OFFSET furst on all records and then policy filters are applied which can lead to returning not expecting results.
I tried this sample but still couldn't reproduce it. Could you guys see if you still run into it with the latest version?
Hey @ymc9, since I can't include real code/model where my problem occurs I missed one additional relation in example which I shared above.
However I managed to replicate an issue in forked repo from your example https://github.com/igorsimko/issue-1519. I can mainly see one difference: as soon as there is && in Collection Predicate Expression, it seems that take is not behaving as I'd expect.
(issue occurs at https://github.com/igorsimko/issue-1519/blob/main/script.ts#L55 and previous queries with take)
Could you have a look at my repo, maybe you can spot what's wrong there.
Hey @ymc9, did you by any chance have time to look into example I provided. It seems like quite a important bug to solve or at least to understand why it's behaving in that way. Any feedback would be very much approciated!
This is a tricky problem ... The root comes from the fact that Prisma doesn't support comparing fields from different tables.
For example, in the rule in https://github.com/igorsimko/issue-1519/blob/main/schema.zmodel:
model SpaceEnvironmentBot {
...
@@allow('read', space.userAccess?[userId == auth().id && environmentId == this.environmentId])
}
The environmentId of SpaceEnvironmentBot is compared against environmentId of UserAccess. Since there's no way to achieve this via native Prisma calls, ZenStack falls back to checking the condition post-read, and apparently, this fallback doesn't work with fetching with a limit 🥲 - a fixed number of items are read but some of them are dropped.
I really wish Prisma could implement this feature ... before that, maybe ZenStack has to do repeated fetch - which is more complex and less performant.
Btw, I'm not sure if the cross-model field comparison is a must-have for your rules @igorsimko
Btw, there's actually a check in ZenStack's validator that should capture comparisons between different models and report an error ... However your rules somehow didn't get caught by the check. I'll check why.
Hey @ymc9, thanks for looking into that and for explanation.
Btw, I'm not sure if the cross-model field comparison is a must-have for your rules @igorsimko
This model design was only which I could came up with to ensure that User must have access to Bots having combination of Space and Environment at the same time. Do you maybe see some obvious solution how can we change schema to ensure same functionality?
If not, do you see some other workaround than including explicitly UserAccess to each query? I understand that this will stop working anyway as soon as you fix ZenStack's validator, am I right?
Hey @ymc9 did you have a chance to look into validator issue? And possibly suggest some workaround other than explicitly including relations?
Hey @ymc9 did you have a chance to look into validator issue? And possibly suggest some workaround other than explicitly including relations?
Add explicitly the where's that are make by the access restrictions and you will be good.
@israelins85 thanks for suggestion again, but this is very error prone and it makes then whole zenstack need for this use case redundant. Only thing I can think of is to extend all find* queries of allModels and dynamically add where/include clause for UserAccess. But again this is not really maintainable since you would have to refine it with every new model in schema to make sure it still includes all relations.
@israelins85 thanks for suggestion again, but this is very error prone and it makes then whole zenstack need for this use case redundant. Only thing I can think of is to extend all
find*queries ofallModelsand dynamically add where/include clause forUserAccess. But again this is not really maintainable since you would have to refine it with every new model in schema to make sure it still includes all relations.
Hi @israelins85 , the blocker here is how to express the condition environmentId == this.environmentId with a Prisma query. Do you currently have a way to write the filter manually with Prisma without using ZenStack?
@ymc9 I think @israelins85 was refering to just always include (or add where) UserAccess to your queries like
instead of
const shouldBeBot22withTake1 = await prismaEnhanced.spaceEnvironmentBot.findMany({
take: 1,
});
do
const shouldBeBot22withTake1 = await prismaEnhanced.spaceEnvironmentBot.findMany({
take: 1,
include: {
environment: { include: { userAccess: true } },
space: { include: { userAccess: true } }
}
});
With this suggestion, generated query correctly includes filter on user id t2.userId = ?
SELECT
`main`.`SpaceEnvironmentBot`.`id`,
`main`.`SpaceEnvironmentBot`.`createdAt`,
`main`.`SpaceEnvironmentBot`.`updatedAt`,
`main`.`SpaceEnvironmentBot`.`name`,
`main`.`SpaceEnvironmentBot`.`spaceId`,
`main`.`SpaceEnvironmentBot`.`environmentId`
FROM
`main`.`SpaceEnvironmentBot`
LEFT JOIN `main`.`Environment` AS `j1` ON (`j1`.`id`) = (`main`.`SpaceEnvironmentBot`.`environmentId`)
LEFT JOIN `main`.`Space` AS `j3` ON (`j3`.`id`) = (`main`.`SpaceEnvironmentBot`.`spaceId`)
WHERE
(
(
(`j1`.`id`) IN (
SELECT
`t2`.`environmentId`
FROM
`main`.`UserAccess` AS `t2`
WHERE
(
`t2`.`userId` = ?
AND `t2`.`environmentId` IS NOT NULL
)
)
AND (`j1`.`id` IS NOT NULL)
)
AND (
(`j3`.`id`) IN (
SELECT
`t4`.`spaceId`
FROM
`main`.`UserAccess` AS `t4`
WHERE
(
`t4`.`userId` = ?
AND `t4`.`spaceId` IS NOT NULL
)
)
AND (`j3`.`id` IS NOT NULL)
)
)
ORDER BY
`main`.`SpaceEnvironmentBot`.`id` ASC
LIMIT
?
OFFSET
?
But doing this for every query and keeping in mind you always have to add include/where is very error prone 🫤
Sorry I was clear in the previous comment. What I meant was for this specific rule:
model SpaceEnvironmentBot {
id String @id @default(cuid())
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
name String
space Space @relation(fields: [spaceId], references: [id])
spaceId String
environment Environment @relation(fields: [environmentId], references: [id], onDelete: Cascade)
environmentId String
@@allow('read', space.userAccess?[userId == auth().id && environmentId == this.environmentId]) // <-- HERE
}
The condition environmentId == this.environmentId requires comparing fields from the SpaceEnvironmentBot and UserAccess tables. Just by using Prisma (without ZenStack), is there currently a way to express such a filter? Because AFAIK Prisma hasn't implemented cross-table field comparison yet.
Thanks for explanation, that I don't know. I also tried adding user access directly to user while ehancing prisma and changing condition to:
@@allow('read', auth().userAccess?[environmentId == this.environmentId && spaceId== this.spaceId])
but it was same issue. @ymc9 does example above also requires cross-table field comparison? I though it would behave similary like accessing/comparing auth().id or other user fields
I'm afraid there's no way to express this with Prisma either ... considering it needs to be transformed into a where filter like:
prisma.spaceEnvironmentBot.findMany({
where: {
environmentId: { in: { auth.userAccess.map(a => a. environmentId) }},
spaceId: ?? // somehow get the same `auth.userAccess` element that matches the `environmentId` field above ...
}
});
prisma.spaceEnvironmentBot.findMany({
where: {
environment: { userAccess: { some: { userId: auth.userId } } },
spaces: { userAccess: { some: { userId: auth.userId, environment: { userAccess: { some: { userId: auth.userId } } } } }
}
});
prisma.spaceEnvironmentBot.findMany({ where: { environment: { userAccess: { some: { userId: auth.userId } } }, spaces: { userAccess: { some: { userId: auth.userId, environment: { userAccess: { some: { userId: auth.userId } } } } } } });
But in your revised rule, the auth.userAccess (which is an array) is needed to determine the access, right?
@@allow('read', auth().userAccess?[environmentId == this.environmentId && spaceId== this.spaceId])