kysely icon indicating copy to clipboard operation
kysely copied to clipboard

Adds `onReserveConnection` to Postgres and MySQL dialect configs

Open dcousineau opened this issue 1 year ago • 7 comments

  • Adds the onReserveConnection callback to the Postgres and MySql dialect drivers
  • This method is called for every connection checkout, regardless if the connection has been seen before or not.
  • Companion to onCreateConnection

Problem

My projects use row-level security in Postgres to ensure multi-tenancy. As a result I have a need to ensure Postgres session variables are set on a given connection to ensure context is carried through. The pre-existing onCreateConnection callback for the Postgres dialect only executes on new connections, meaning if a connection doesn't expire and is re-used by the pool the callback will not fire and context setting will not happen.

For example, assume we run SET app.org_id = ${org_id} for every connection:

  1. Pool is empty (no connections)
  2. Request handler asks for a connection for OrgA.
  3. A new connection, Con1, is created and returned
  4. Kysely executes onCreateConnection which runs SET app.org_id = OrgA on Con1
  5. Request handler completes, Con1 is returned to the Pool
  6. Request handler asks for connection for OrgB
  7. Con1 is reused and returned by the Pool (as it has not expired and closed)
  8. Kysely does NOT execute onCreateConnection
  9. Request handler fails to complete as sql queries are blocked by RLS policies (context is set to OrgA, but app attempts to make OrgB queries)

Adding an additional callback that runs every time a connection is pulled from Kysely mitigates the issue by ensuring the session variables can be set every time at the expense of possibly running it more often than is required.

dcousineau avatar May 20 '24 16:05 dcousineau