Authentication sporadically fails when postgres has a defined search path

[shaheedazaad]

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.

[thetutlage]

This approach is buggy in first place, because Node.js can handle multiple requests in parallel and therefore the schema set by second first will overwrite the schema set by first request.

Also, it has nothing to do with Auth at all, you are overwriting global properties while handling concurrent requests

[shaheedazaad]

Thanks! I had thought setting the search path would be the same as using .withSchema for all queries in that request.

I suppose setting the schema for the connection, and not on the DB, is not possible?

I don’t want to take the one-database-per-tenant approach since it prevents me from having a shared connection pool between my cluster.