Connecting Pool (for Postgres transaction) with Auth Session from Clerk using Drizzle ORM
Hi team,
I have auth sessions setup with Clerk for neon-http so I'm able to leverage RLS policies, however, I'm having a hard time finding any documentation on including the auth session in a Pool Connection which is required for a transaction. Ie. I want to make sure the transaction enforces the RLS policies and has access to the current user session. With neon-http this is simple. db.$withAuth(authToken) solves all my problems, however, this is not clear how it would work in a Pool Connection or a transaction. Any help is much appreciated!
9 Replies
genetic-orange•7mo ago
the http session is automatically pooled so you don't need to worry about that. each http connection will use the pooler behind the scenes.
And the http connection is the only type that's currently supported. a standard TCP connection won't automatically inject the session JWT and so you're left attempting to do that with your application connection. It's possible to do this as a connection middleware but often hard to get the session JWT injected into that middleware.
genetic-orange•7mo ago
See current limitations here: https://neon.tech/docs/guides/neon-rls-authorize#current-limitations
Neon
About Neon RLS Authorize - Neon Docs
JSON Web Tokens (JWT) Row level Security (RLS) How Neon RLS Authorize works Neon RLS Authorize Tutorial Postgres Row Level Security tutorial Simplify RLS with Drizzle Neon RLS Authorize integrates wit...
criminal-purpleOP•7mo ago
Hi Bryan, thanks for response. If this is the case, is it possible to create SLQ Transactions with HTTP connection? I didn't think it was possible...
Specifically I receive this error with this code when using a HTTP driver
Property 'transaction' does not exist on type 'Omit<NeonHttpDatabase<typeof import("/project/packages/models/dist/index")> & { $client: NeonQueryFunction<false, false>; }, "transaction" | ... 4 more ... | "$client">'.ts(2339)
And if I modify the type to allow transactions, I get the following error:
So the question stands, how can you combine RLS Auth with an SQL transaction?
criminal-purpleOP•7mo ago
I see this is on your RLS Roadmap, I assume that means it doesn't exist now, is there any status or expected GA date for this feature?
genetic-orange•7mo ago
no date set for TCP and sockets, you are the first feedback related to it.
yes, transactions aren't supported on http, only sockets or tcp (of course)
what kind of transaction are you trying to do?
worst way around this is to create a Postgres function that does the transaction
criminal-purpleOP•7mo ago
We have an inventory management SaaS so ideally we'd love to use transactions to keep everything in sync and ensure atomicity in the simplest way possible. I know the engineering leg work might not be the easiest but RLS + Transactions feels like a critical feature for serious use cases. This only became an issue when trying to migrate the codebase to use RLS instead of manually isolating multi-tenant data. Right now we are using Neon but running the transactions with RLS as an admin user and manually isolating data in the query. I'd much prefer not to use postgres functions for each task and would love to use RLS + Transactions. If that means being able to do transactions over http I'd be fine with that as well.
genetic-orange•7mo ago
There might be other tricks available, you can insert into a view which is a combination of several tables (creating a transaction for the update of different tables into one) and views are much simpler than functions
criminal-purpleOP•7mo ago
Do most people just not use RLS? Because that might be the easier option here.
genetic-orange•7mo ago
RLS works great in many simple scenarios but we've found, like you've found here, those who are running at scale or with complex needs can only use it in a limited fashion.