pg-promise and Row Level Security

I am looking at implementing Row Level security with our node express + pg-promise + postgres service.

We've tried a few approaches with no success:

1. create a getDb(tenantId) wrapper which calls the SET app.current_tenant = '${tenantId}';` sql statement before returning the db object
2. getDb(tenantId) wrapper that gets a new db object every time - this works for a few requests but eventually causes too many db connections and errors out (which is understandable as it is not using pg-promise's connection pool management)
3. getDb(tenantId) wrapper that uses a name value (map) to store a list of db connections per tenant. This works for a short while but eventually results in too many db connections).
4. utilising the initOptions > connect event - have not found a way to get hold of the current request object (to then set the tenant_id)

Can someone (hopefully vitaly-t :)) please suggest the best strategy for injecting the current tenant before all sql queries are run inside a connection.

Thank you very much

here is an abbreviated code example:

const promise = require('bluebird');

const initOptions = {
  promiseLib: promise,
  connect: async (client, dc, useCount) => {
    try {
      // "hook" into the db connect event - and set the tenantId so all future sql queries in this connection 
      // have an implied WHERE tenant_id = app.current_setting('app.current_tenant')::UUID   (aka PostGres Row Level Security)
      const tenantId = client.$ctx?.cn?.tenantId || client.$ctx?.cnOptions?.tenantId;
      if (tenantId) {
        await client.query(`SET app.current_tenant = '${tenantId}';`);
      }
    } catch (ex) {
      log.error('error in db.js initOptions', {ex});
    }
  }
};
const pgp = require('pg-promise')(initOptions);

const options = tenantIdOptional => {
  return {
    user: process.env.POSTGRES_USER,
    host: process.env.POSTGRES_HOST,
    database: process.env.POSTGRES_DATABASE, 
    password: process.env.POSTGRES_PASSWORD,
    port: process.env.POSTGRES_PORT,
    max: 100,
    tenantId: tenantIdOptional
  };
};

const db = pgp(options());
const getDb = tenantId => {
  // how to inject tenantId into the db object
  // 1. this was getting an error "WARNING: Creating a duplicate database object for the same connection  and  Error: write EPIPE"
  // const tmpDb = pgp(options(tenantId));
  // return tmpDb;

  // 2. this was running the set app.current_tenant BEFORE the database connection was established
  // const setTenantId = async () => {
  //   await db.query(`SET app.current_tenant = '${tenantId}';`);
  // };
  // setTenantId();
  // return db;

  // 3. this is bypassing the connection pool management - and is not working
  // db.connect(options(tenantId));
  // return db;
  return db;
};

// Exporting the global database object for shared use:
const exportFunctions = {
  getDb,
  db        // have to also export db for the legacy non-Row level security areas of the service  
};
module.exports = exportFunctions;

SET operation is connection-bound, i.e. the operation only has effect while the current connection session lasts. For fresh connections spawned by the pool, you need to re-apply the settings.

The standard way of controlling current connection session is via tasks:

await db.task('my-task', async t => {
    await t.none('SET app.current_tenant = ${tenantId}', {tenantId});

    // ... run all session-related queries here
});

Or you can use method tx instead, if a transaction is needed.

But if you have tenantId known globally, and you want it automatically propagated through all connections, then you can use event connect instead:

const initOptions = {
    connect({client}) {
        client.query('SET app.current_tenant = $1', [tenantId]);
    }
};

The latter is kind of an after-thought work-around, but it does work reliably, has best performance, and avoids creating the extra tasks.

have not found a way to get hold of the current request object (to then set the tenant_id)

This should be very straightforward for any HTTP library out there, but is outside of scope here.