alt Hacker NewsI built something similar for Neon[1]. We went with the route that the user sessions for JWT RLS had very few privileges, and that the context was managed by our ingress/connection pool. In our case, all incoming queries had to come via our sql-over-http interface, but that was mostly just to reduce scope rather than being integral to the security.
The general idea is that the connection pool ingress is trusted to provide the context properly, but the client is not. The pool would generate a random JWK for each connection and each request would use a newly signed JWT and the token ID is strictly monotonic, which makes any attempts for the client to smuggle their own token contexts in near impossible.
The JWTs the pool signs for each transaction will contain the claims that were correctly validated from the client.
A `DISCARD ALL` before returning the connection to the available pool will clear the context so it cannot be re-used and a new context must be provided fresh.