cube-js/cube

Add an option to pass additional current_setting variable to PostgresDriver

Open

#6686 opened on Jun 5, 2023

View on GitHub
 (2 comments) (0 reactions) (0 assignees)Rust (19,563 stars) (1,965 forks)batch import
help wanted

Description

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);
    }

Contributor guide