Authentication sporadically fails when postgres has a defined search path

I have a multitenant app in which tenants share a database and are separated by the schema. To switch between them, I use a middleware that gets the request subdomain and switches connections:

const config = makeConfig(tenantName)
Database.manager.add('tenant_' + tenantName, config)
Database.primaryConnectionName = 'tenant_' + tenantName
await next()

The database config looks something like this:

 {
    client: 'pg',
    connection: {
      host: Env.get('PG_HOST'),
      port: Env.get('PG_PORT'),
      user: Env.get('PG_USER'),
      password: Env.get('PG_PASSWORD'),
      database: Env.get('PG_DB_NAME'),
      ssl: true
    },
    searchPath: ['tenant_' + tenantName],
  }

For the most part, this works as intended - as I switch subdomains, so does the schema.

However, seemingly randomly, authentication fails as I visit routes protected by auth middleware.

"tenant_tenant" (20 ms) SELECT * FROM "api_tokens" WHERE "id" = ? AND "type" = ? LIMIT ? [ '154', 'api', 1 ]
[05:36:42.044] ERROR (adons-api/17683): E_INVALID_API_TOKEN: Invalid API token

Sometimes, auth fails on the first request following authentication. Other times, I can make a bunch of requests to auth-protected routes before it fails.

Oddly, if I restart the process, authentication with the same token works again. I don't have issues when accessing unprotected routes that also query the database, so I don't think this is a database issue.

Originally posted by @shaheedazaad in https://github.com/adonisjs/core/discussions/4404

adonisjs / auth

Authentication sporadically fails when postgres has a defined search path #234