Supporting session-dependent queries like Postgres' SET across queries of a request
#5128
Comments
matthewmueller
added
kind/feature
matthewmueller
changed the title
|
Thanks for opening a separate issue. Would love to see this supported. |
|
Maybe an approach with middlewares allowing to tweak the SQL request could be useful? |
|
I want to add a plus on to this. I have been using supabase lately and it would be really great to take advantage of RLS. I haven't really found a great way Of handling things like multi-tenancy using prisma/supabase and think that this could really unlock a new realm of possibilities |
|
Although I understand the current challenges to link a connection to the request, I would love to see some way to support RLS. I am not familiar with the internals of Prisma engine, but I am throwing an idea and would love to hear your thoughts. generic example - get a command to be executed before everyother command in the transaction on the same connection const [userList, updateUser] = await prisma.$transaction([
prisma.post.findMany({ where: { title: { contains: 'prisma' } } }),
prisma.post.count(),
],
prisma.$executeRaw(`SET current_user_id = ${currentUser.id}`)
)RLS specific example - get the name and value of the parameter to be set on the connection before executing each of the commands in the transaction const [userList, updateUser] = await prisma.$transaction([
prisma.post.findMany({ where: { title: { contains: 'prisma' } } }),
prisma.post.count(),
],
"current_user_id",
currentUser.id) |
|
Thanks for the suggestion @yuval-hazaz! I like your suggestion of offering some options in the transaction API that could do this. That could also be a good place for transaction settings (read committed, read uncommitted, etc.) The fundamental problem is that you need to SET within the same connection as the write, otherwise when you go to write, you may pull out a different connection from the pool that doesn't have that setting. So they need to be within the same prisma call, can't be separate calls like your first example. A couple things that we'll also need to check:
|
|
Thank you @matthewmueller. |
|
@yuval-hazaz did you also try the following? const [ignore, userList, updateUser] = await prisma.$transaction([
prisma.$executeRaw(`SET current_user_id = ${currentUser.id}`),
prisma.post.findMany({ where: { title: { contains: 'prisma' } } }),
prisma.post.count(),
])Looks like you may also need to SET LOCAL to avoid the 3rd issue. |
|
I didn't, since you wrote "Without access to the connection pool, there's no way to guarantee you'll get the same connection each query" - so, even if it will work on my dev machine, I cannot guarantee it will work on prod |
|
In context of a transaction, started via |
|
ho... wow.. that is interesting... IIUC if that will work, it is all we need. |
|
Interested in this issue as well: I want to implement RLS in postgres. const userList = await prisma.post.findMany({ where: { title: { contains: 'prisma' } } })with const [ignore, userList] = await prisma.$transaction([
prisma.$executeRaw(`SET current_user_id = ${currentUser.id}`),
prisma.post.findMany({ where: { title: { contains: 'prisma' } } }),
])Is there any way to force every request from the prisma client to go through this transaction + SET? const userList = await rlsPrisma.post.findMany({ where: { title: { contains: 'prisma' } } })I could have a class that exposes every find/create/delete... for each prisma object and wraps them in a transaction, but that would not be very generic... Maybe there is a prisma mechanism that would help? Edit: In the meantime I will use a middleware to force a where clause on the current_id, but a RLS solution to provide a check at the database layer would be best. |
|
You might be able to force the transaction and query in a middleware as well. You do not have to execute |
|
I tried the middleware solution and it seems to do the job. rlsMiddleware = async (params, next) => {
if (params.runInTransaction) return next(params);
// Generate model class name from model params (PascalCase to camelCase)
const modelName =
params.model.charAt(0).toLowerCase() + params.model.slice(1);
const [, results] = await prisma.$transaction([
prisma.$executeRaw(
`SET current_user_id = ${currentUser.id}`,
),
// Call function
// Assumptions:
// - prisma model class is params.model in camelCase
// - prisma function name is params.action
prisma[modelName][params.action](params.args),
]);
return results;
}; prisma.$use(rlsMiddleware);I took two assumptions that are hope are true:
The main issue I see with this is does not work with transactions. One workaround would be to be able to pass a context to the middleware, as described here: #6882 |
Getting it to work with transactions might just be workable by either unraveling the transaction and then creating a new one (if thats possible). My other concern is, i dont want to have to pass in a param of currentUser on every request. AFAIK, middlewares are stateless, it doesnt have context |
My main concern here is that there's no way to get the currentUser unless you are adding it to all queries, which is terrible. @janpio I've been tracking all these issues for months now, is there anything in the roadmap to allow us to have contextual data in prisma middlewares? |
|
No, not at this time. You can see the public roadmap at http://pris.ly/roadmap |
Thank you! Have a nice weekend |
|
@remioo I've been using something similar to your snippet to use RLS via supabase for a few months, but have run into a situation where I'm leveraging |
|
Indeed I have tweaked the middleware to handle rlsMiddleware = async (params, next) => {
if (params.runInTransaction) return await next(params);
let results;
if (
params.model &&
typeof params.model === 'string' &&
params.model.length > 0
) {
// Generate model class name from model params (PascalCase to camelCase)
const modelName =
params.model.charAt(0).toLowerCase() + params.model.slice(1);
[, results] = await this.prismaRWClient.$transaction([
this.prismaRWClient.$executeRaw(
`SET app.current_user_id = ${currentUser.id}`,
),
// Call function
this.prismaRWClient[modelName][params.action](params.args),
]);
} else if (params.action === 'executeRaw') {
[, results] = await this.prismaRWClient.$transaction([
this.prismaRWClient.$executeRaw(
`SET app.current_user_id = ${currentUser.id}`,
),
// Call function
this.prismaRWClient.$executeRaw(params.args),
]);
} else if (params.action === 'queryRaw') {
[, results] = await this.prismaRWClient.$transaction([
this.prismaRWClient.$executeRaw(
`SET app.current_user_id = ${currentUser.id}`,
),
// Call function
this.prismaRWClient.$queryRaw(params.args.query),
]);
}
return results;
};Let me know if that works for you. |
Hello, we are currently using a mix of a prisma RLS middleware, and using a CLS-based library to perform Postgres queries, that change the RLS variables depending on the content of the request(but you could use any CLS library, and not restrict to requests). We are using fastify, so we are using https://github.com/fastify/fastify-request-context, to have a context shared between our fastify server, and the prisma server(but there is also this: https://www.npmjs.com/package/express-request-context, or you could use something more low-level like https://www.npmjs.com/package/cls-hooked, or even the node-based AsyncLocalStorage): import Fastify from 'fastify'
import { fastifyRequestContextPlugin } from 'fastify-request-context';
const app = Fastify();
app.register(fastifyRequestContextPlugin, {});Then, when the request gets parsed, we have a function that is responsible to feed data in that "request context"(we are using graphql, so it is the server context creation function, but it could also be an express middleware that does this): import { requestContext } from 'fastify-request-context';
async function createContext(req) {
// ...
const account = await getAccount(req);
const organization = await getOrganization(req);
const authContext: AuthContext = { account, organization };
requestContext.set('authContext', authContext);
// ...
}Then we have a prisma middleware that gets that information and applies to a local variable in Postgres(like the above mentioned solution): export const createRlsMiddleware = (prisma: PrismaClient) =>
async (
params: Prisma.MiddlewareParams,
next: (params: Prisma.MiddlewareParams) => Promise<any>
) => {
if (params.runInTransaction) return next(params);
// TODO: validate
if (params.model == null) return next(params);
// Generate model class name from model params (PascalCase to camelCase)
const modelName =
params.model.charAt(0).toLowerCase() + params.model.slice(1);
const { organization } = requestContext.get('authContext') as AuthContext | null ?? {};
// warning: set local does not work completely as you expect
// https://www.postgresql.org/message-id/flat/56842412.5000005@joeconway.com
const organizationPolicy = prisma.$executeRaw(`SET LOCAL app.current_organization = '${organization?.id ?? ''}'`);
// @ts-ignore
const prismaAction = prisma[modelName][params.action](params.args);
const results = await prisma.$transaction([organizationPolicy, prismaAction]);
return results.pop();
};Hope this helps someone :) |
|
Does anyone know of anything similar we could use with Mongodb? |
|
would love to get a first-class support for this on prisma, or maybe a sanctioned middleware like what @eduhenke's solution? |
@jawadst If the connection limit of each PrismaClient is set to 5(as in each I've done your suggestion and when I increase that limit to anything higher than 1, I sometimes get the error from Postgres that the variable was not set. So I always set the connection limit to 1. Do you also have that problem, or you've found a workaround for that? |
|
@eduhenke I do have connection limit set to 1 in my code as that was enough for my use case. It's possible more than 1 does not work indeed with the code that I posted, I have not tested it. I updated my comment to make that clear. There might be a way to run a query when a new connection is open in Prisma. |
|
This is also relevant to #14749. Prisma text search uses the postgres default language, which could be set per connection if supported (there's a workaround which I posted on that issue) |
|
Does anyone have a working workaround? |
Yes - I am using a similar approach to the one very thoroughly documented above by @wladiston. If you read all the comments people have posted above you should find success. TL;DR -- wrap all your relevant Prisma calls inside transactions, and use
If you want to do this in many places throughout your app, most of the work is in creating a way to use the above approach in a reusable way, e.g. creating a Prisma middleware (various example code in comments above). The middleware approach seems to be working for others but struck me as quite a major hack to the internals of how Prisma works and tricky to make work for all cases, so I'm using an alternative approach using a wrapper function around all my Prisma calls (and considering exploring whether a Proxy object around the Prisma client might work better - not tried yet). (If you meant a workaround for a full implementation of row-level security, that is also doable but is a broader topic beyond the (current) scope of Prisma that requires a bunch of Postgres-specific work using |
|
@andyjy I took a swing at the Proxy object based implementation since I also found that the approach using middleware is very hacky and actually alters how prisma is handling the request internally. In fact, the middleware approach simply does not work for export const prisma = new Proxy(
getClient(() => {
const client = new PrismaClient({
log:
env.NODE_ENV === "development" ? ["query", "error", "warn"] : ["error"],
datasources: {
db: {
url: process.env.DATABASE_URL_APP,
},
},
});
return client;
}),
{
get(prismaProxy, p, receiver) {
const org = storage.getStore()?.orgId;
//catch calls directly on the prisma client such as $queryRaw and $executeRaw, but ignore internal methods
if (
typeof p == "string" &&
p.length > 0 &&
(p.charAt(0) == "$" || p.charAt(0) != "_") &&
!p.endsWith("Internal")
) {
// @ts-expect-error
return new Proxy(prismaProxy[p], {
apply(func, thisArg, argArray) {
return prismaProxy
.$transaction([
prismaProxy.$executeRawUnsafe(
`SET LOCAL request.orgId = '${org}'`
),
Reflect.apply(func, thisArg, argArray) as PrismaPromise<any>,
])
.then((value) => {
const [, result] = value;
return result;
});
},
get(modelProxy, pp, receiver) {
//handle the case when they are accessing a model, such as prisma.model.findMany({})
if (
typeof p == "string" &&
p.length > 0 &&
p.charAt(0) != "_" &&
!p.endsWith("Internal")
) {
return new Proxy(modelProxy[pp], {
//function call on model, such as model.findMany()
apply(func, thisArg, argArray) {
return prismaProxy
.$transaction([
prismaProxy.$executeRawUnsafe(
`SET LOCAL request.orgId = '${org}'`
),
Reflect.apply(
func,
thisArg,
argArray
) as PrismaPromise<any>,
])
.then((value) => {
const [, result] = value;
return result;
});
},
});
} else {
return Reflect.get(modelProxy, pp, receiver);
}
},
});
}
return Reflect.get(prismaProxy, p, receiver);
},
}
); |
@joe-giunti-kiefa really interesting what you did. Thanks for sharing. |
I'm not sure if $queryRaw and $executeRaw work, They throw this error And I believe this is related to issue#5083 |
This a great work, did you manage to fix if the caller already uses a transaction? how to destructure all of them into a single transaction? @joe-giunti-kiefa |
In the recent prisma version we had to replace |
|
@remioo does this code actually return data for you? From what we found with this approach is that all the fields of the data are undefined when returned to the client, despite being able to see the correct data in
|
That's great, was any one able to make it work with the fluent API? We need to be able to use it with the fluent API, as it solves the N+1 problem, regarding query optimization: https://www.prisma.io/docs/guides/performance-and-optimization/query-optimization-performance |
@IbrahimFathy19 Unfortunately I have not figured out a way to account for the caller already having a transaction. |
janpio
changed the title
SET across queries of a request
|
Thanks, everyone. @wladiston, I used your guide for Supabase. I also stumbled upon a new feature at the preview stage called "Prisma Client Extensions". Prisma docs mention in the docs, that it can be used for RLS. There is also a blog post about a custom Prisma client for RLS, developed before Prisma Client Extensions. The writer explains nicely what issues he had with several methods. Prisma points to examples published by @sbking. There is an RLS example developed as a Prisma Client Extension. As a result, I create an example for row level security. I'm new to Supabase and Prisma. Can the example below be used in the long run? (Memory consumption or leak, performance problem, etc.) Notes & Thoughts:
TLDR; Examplefunction rlsClient(jwtClaim = "{}"): (client: any) => PrismaClient<any, any, any, Types.Extensions.Args> {
return Prisma.defineExtension((prisma) =>
// @ts-ignore (Excessive stack depth comparing types...)
prisma.$extends({
query: {
$allModels: {
async $allOperations({ args, query }) {
const [, result] = await prisma.$transaction([
prisma.$executeRawUnsafe(`SELECT set_config('request.jwt.claim', '${jwtClaim}', TRUE)`),
query(args),
]);
return result;
},
},
},
})
);
}Supabase & Prisma & Nuxt RLS ExampleMy RLS implementation is using "businessId" of the user. This data is added to the server/middleware/1.user.ts import type { H3Event } from "h3";
import { serverSupabaseUser } from "#supabase/server";
// Server middlewares are executed in reverse order. 02 -> 01 -> 00 etc...
/**
* Adds logged in user into the context.
*
* @example
* const user = event.context._user
*/
export default eventHandler(async (event: H3Event) => {
await serverSupabaseUser(event);
});server/middleware/0.prisma.ts import { Prisma, PrismaClient } from "@prisma/client";
import type { Types } from "@prisma/client/runtime/index";
import type { H3Event } from "h3";
// Server middlewares are executed in reverse order. 02 -> 01 -> 00 etc...
/** Singleton prisma client. */
let prisma: PrismaClient;
/**
* Prisma type declaration for event context.
* Add `prisma` attribute to the `event.context` type to
* `const prisma = event.context.prisma;`
*/
declare module "h3" {
interface H3EventContext {
prisma: PrismaClient;
}
}
/**
* Decodes given JWT without verifying.
*
* @param token is the token.
* @returns object data decoded from JWT as a string.
*/
function decodeJwt(token: string): string | undefined {
return token ? Buffer.from(token.split(".")[1], "base64").toString() : undefined;
}
/**
* Nuxt middleware function which adds extended prisma RLS client into context.
* Middleware handlers will run on every request before any other server route.
*/
export default eventHandler((event: H3Event) => {
if (!prisma) prisma = new PrismaClient();
const token = event.context._user ? event.context._token : undefined;
event.context.prisma = prisma.$extends(rlsClient(decodeJwt(token))) as any as PrismaClient;
});
/**
* Creates a Prisma Client Extension with RLS which injects Supabase JWT data
* into PostgreSQL local config.
*
* Queries are wrapped in a transaction, because PostgreSQL connection pool
* may give different connections for the same session. Transactions overcome
* this problem.
*
* @param jwtClaim JWT object as a string.
* @returns Prisma Client Extension with RLS
* @see https://www.prisma.io/docs/concepts/components/prisma-client/client-extensions
* @see https://github.com/sbking/prisma-client-extensions/blob/main/examples/row-level-security/script.ts
* @see https://github.com/prisma/prisma/issues/5128#issuecomment-1059814093
*/
function rlsClient(jwtClaim = "{}"): (client: any) => PrismaClient<any, any, any, Types.Extensions.Args> {
return Prisma.defineExtension((prisma) =>
// @ts-ignore (Excessive stack depth comparing types...)
prisma.$extends({
query: {
$allModels: {
async $allOperations({ args, query }) {
const [, result] = await prisma.$transaction([
prisma.$executeRawUnsafe(`SELECT set_config('request.jwt.claim', '${jwtClaim}', TRUE)`),
query(args),
]);
return result;
},
},
},
})
);
}server/api/business.get.ts export default eventHandler(async (event) => {
const prisma = event.context.prisma;
const business = await prisma.business.findMany();
return business;
});Business Table SQL -- Record user "businessId" as 'bid' in the user.raw_user_meta_data JSONB field
-- 1) Fetch 'bid' using supabase client from user.raw_user_meta_data. (Usually supabase client)
-- 2) Fetch 'bid' from app.bid (Usually for Prisma or 3rd party ORM)
CREATE TABLE "Business" (
"id" UUID DEFAULT gen_random_uuid() PRIMARY KEY
)
ALTER TABLE "Business" ENABLE ROW LEVEL SECURITY;
CREATE POLICY "BusinessAccess" ON "Business"
FOR ALL
USING (id = (COALESCE(auth.jwt(), '{}')->'user_metadata'->>'bid')::uuid); |
|
The Prisma extension library ZenStack we are building uses a declarative way in the schema to handle access control by code generation instead of using RLS. So actually, you can use it for whatever database, not limited to Postgres. To achieve what Postgres can do in the original problem, you could simply add an allow policy rule in the schema as below: model Post {
id Int @id() @default(autoincrement())
title String
owner User @relation(fields: [ownerId], references: [id], onDelete: Cascade)
ownerId String
// Only the owner has full access.
// auth() returns current user
@@allow('all', auth() == author)
}And then create a wrapper of Prisma client using // the standard Prisma client
const prisma = new PrismaClient();
app.get('/posts', (req, res) => {
const db = withPresets(prisma, { user: getSessionUser(req) });
res.json(await db.post.findMany());
})The above API only returns the data filtered by Of course, you could have more flexible fine-grained control over it using Access Policy of ZenStack, below is a tutorial post for a more complicated use case: How to build a collaborative SaaS product using Next.js and ZenStack's access control policy We would really appreciate it if you could share your opinions by commenting or joining our Discord to help us make ZenStack the right thing to solve your problems. |
|
Here's a multi-tenant NestJS implementation that uses Prisma Client Extensions and AsyncLocalStorage (with nestjs-cls): A complete example app can be found here |
janpio
changed the title
SET across queries of a requestSET across queries of a request
|
Jumping in here as we have basically the exact same issue for our multi-tenancy solution. I want to create one connection pool per MSSQL server rather than one pool for each database. This is because one connection pool per databases causes a lot of overhead and consumes a lot of memory, and it's generally just cleaner to have one shared pool. This also lets me more easily utilize the metrics (preview) feature. This means I need to run I could use use client extensions to wrap each query in a transaction and call the // Extend Prisma so that we can switch database on the fly. This allows us to use one single connection pool for each SQL server.
// In order to avoid leaking connections, we must do these two calls within a transaction.
// This is far from optimal.
const extendedClient = client.$extends({
query: {
async $allOperations({query, operation, args}) {
const [, result] = await client.$transaction([
client.$executeRawUnsafe(`USE [${databaseName}]`),
query(args),
]);
return result;
},
},
});However, I don't really want a transaction for each query. It will cause an additional performance overhead that I simply do not want. I would like to suggest a new function. One that behaves similar to // Extend Prisma so that we can switch database on the fly. This allows us to use one single connection pool for each SQL server.
const extendedClient = client.$extends({
query: {
async $allOperations({query, operation, args}) {
const [, result] = await client.$withinSameConnection([
client.$executeRawUnsafe(`USE [${databaseName}]`),
query(args),
]);
return result;
},
},
});I'm not familiar with the source code of Prisma. But I feel like this shouldn't be too much work (if you are OK with the suggested solution). Here is where it starts: I can't really find out where it picks a connection from the pool. I would've guessed somewhere here: But it doesn't seem to do that either. Maybe someone can point me in the right direction? |
|
One more thing I came to think about regarding my suggested solution above. Since my intention is to run this as an extension on all calls and I still want the option to run certain things in a transaction, For example: await client.$transaction(async transaction => {
// the following statement would simply yield the same connection that the transaction is in
await transaction.$withinSameConnection([
transaction.something(...),
transaction.somethingElse(...),
]);
});This might be obvious, but I just thought I'd share. |
and others
Problem
In Postgres you can set a user for a connection on the database:
This SET can then be used in combination with row-level security (RLS) to issue queries like this:
that only give you back messages from that user. This technique is used by Postgraphile and Postgrest and really takes advantage of what Postgres offers.
Without access to the connection pool, there's no way to guarantee you'll get the same connection each query. Since SET values are bound to the query, subsequent queries may be missing the SET or may even override another request's SET.
I'm not sure what the best approach is. Tying a connection to the lifecycle of a request has its own performance implications.
A point of reference potentially worth investigating. Go's standard SQL library uses a connection pool under the hood that's transparent to developers. Do they run into this problem too? If so, do they or how do they deal with it?
Originally from: #4303 (comment)
The text was updated successfully, but these errors were encountered: