Add an option to pass additional current_setting variable to PostgresDriver

[praseetha-nair]

Need to pass some variable to PostgreSQL current_setting to fetch store specific data in multi-tenant application. On checking PostgresDriver package code, there is an option to SET current settings variable as following

async prepareConnection(conn, options = {
             executionTimeout: this.config.executionTimeout ? (this.config.executionTimeout) * 1000 : 600000
    }) {
        await conn.query(`SET TIME ZONE '${this.config.storeTimezone || 'UTC'}'`);
        await conn.query(`SET statement_timeout TO ${options.executionTimeout}`);
        await this.loadUserDefinedTypes(conn);
    }

I need to set one(or more) additional current settings variable, so that my view will return current store specific data only.

Describe the solution you'd like I need an option to pass some additional variables and it should loop and set current settings before running the cube queries.

PostgresDriver Initialization

new PostgresDriver({
        database: process.env.CUBEJS_DEMOAG_DB_NAME,
        host: process.env.CUBEJS_DEMOAG_DB_HOST,
        user: process.env.CUBEJS_DB_USER,
        password: process.env.CUBEJS_DB_PASS,
        port: process.env.CUBEJS_DB_PORT,
        options: {
               storeid: 'store-1',
               role: 'admin'
        }
      })

prepareConnection function should loop options given and set variable accordingly.

async prepareConnection(conn, options = {
             executionTimeout: this.config.executionTimeout ? (this.config.executionTimeout) * 1000 : 600000
    }) {
        await conn.query(`SET TIME ZONE '${this.config.storeTimezone || 'UTC'}'`);
        await conn.query(`SET statement_timeout TO ${options.executionTimeout}`);

         **await conn.query(`SET ${given_options.variable_name} = ${given_options.value}`);**

        await this.loadUserDefinedTypes(conn);
    }

[github-actions[bot]]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you. If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines. You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[igorlukanin]

Related: #6780