0

I'm trying to set session level variable on a connection in pg-promise, the variable value will be read by trigger in the database level with current_setting('var_name'). This session variable will be different for each user, while I'm also sharing the same database connection for all users.

I found this somewhat related question utilizing the connect event, but I have concern that since I'm sharing the same connection for all users that the session var will not be set correctly when different users call the query method.

Is there another way to safely set this session var and make sure that it's isolated for each user while still sharing the same database object?

const pgPromise = pgp({
  promiseLib: Promise, // overriding the default (ES6 Promise)
  async connect(client, dc, useCount) {
    // const cp = client.connectionParameters;
    // console.log('Connected to database:', cp.database, dc);
    if (dc && dc.email) {
      console.log(useCount);
      const email = encodeURI(dc.email);
      await client.query(`SET SESSION "app.user" = '${email}'`);
    }
  }
});

get tenantDb() {
  const state = this.request.sessionState();
  const config = { host, database, port };
  // pass state as database context, 
  // we'll get warning of duplicate database object for the same connection
  const connection = pgPromise(config, state);
  return connection;
}

Update

It turns out that I need to upgrade pg-promise version to the latest version, I was using v7 which doesn't differentiate the connection object based on the context, once I upgrade pg-promise to v10 the warning disappear, a more optimized solution would be if we can somehow set the session settings along with the schema callback in the initOptions parameter when we initiate the database, that way we only need one extra query execution together with the schema.

Community
  • 1
  • 1
Luthfi
  • 111
  • 3
  • Please show us the code you have tried so far and any error messages you received. – RyanNerd Feb 03 '20 at 07:21
  • add sample code of what I've tried, previously I'm caching the database object from pg-promise but that way I cannot assign unique database context per user, so now I'm creating the database object every time but I got that duplicate database object warnings – Luthfi Feb 03 '20 at 07:39
  • I have a similar issue but I don't think it's possible to share a database object if you have a separate connection per user? Each user will have their own connection pool? Unless you mean sharing the library initialisation? Please will you update how it works as this would be really helpful for me – anotherfred Oct 21 '21 at 11:58

0 Answers0