Pass current user id to Postgres
In our application we've added a trigger that logs changes to tables. As part of the logs we want to store the id of the user who did the change, and the function that's executed by the trigger gets this by calling
current_setting('app.current_user_id', true). Hence the logic on the DB side expects the current user id to be passed using this setting app.current_user_id.
On the application side of things we want to pass the user id for all queries. We can do this using a client extension that sets the user id when a query is being ran against the DB (Postgres). However this is a bit impractical as the PrismaClient should only be instantiated once in the application for management of the connection pool, and not once per unique user using the application.
How can we make Prisma pass the app.current_user_id setting to Postgres, whilst still keeping Prisma managing the connection pool in a good way?3 Replies
I’m not sure this is possible outside of a client extension.
Would it be possible to update your client extension code to have the current user ID fetched at runtime rather than at creation time?
I guess that's what we need to do somehow, make the Prisma middleware/extension fetch the current user id during runtime. The question is how to do that in practice. We have the current user available in the HTTP request context but the PrismaClient would live as a singleton outside that context. It's challenging 🤔
I'd also like to avoid having each call to a PrismaClient method have to pass down the user. I guess this should be possible by extending Prisma but it's not really desirable as it will add complexity to many other parts of the application.
One idea is to use JS Reflections to proxy the calls being made to Prisma, thereby adding the possibility to intervene and add the behaviour of passing down a user to Postgres. But that comes with some complexity as well.
you may be change db structure