Objection.js: Multitenant Pattern with Postgres - Role and Schema per Tenant On Same Connection

[mashaalmemon]

Hi There,

We have a multi-tenancy setup with postgres where:
1) multiple schemas exist in the same database, and each schema is identical to the other.
2) To keep data secure we intend on using roles to secure each schema (i.e. SCHEMA schema_tenant1 can only be accessed by ROLE role_tenant1.
3) As we grow we'll likely add multiple databases, with multiple schemas as described above.

I'm looking for an ORM that can satisfy all of these requirements. Based on my reading, for 3), I noted the recipe in the documentation for multi-tenancy (multiple databases) which is great. So this is a non issue.

However, it's not clear how I might achieve 1) or 2). Note the reason why we decided to have our mutli-tenancy handled via 1/2 is that we can essentially have multiple tenants have secure and independant data stores, but share connections between the two

Some more insight into how we setup the schema's in a database and the roles before using objection.js:

# SQL for creating a tenant
CREATE ROLE role_tenant1
CREATE SCHEMA IF NOT EXISTS schema_tenant1
GRANT ALL ON ALL TABLES IN SCHEMA schema_tenant1 TO role_tenant1
GRANT ALL ON SCHEMA schema_tenant1 TO role_tenant1
# create tables, functions, views etc within the schema for a customer
CREATE TABLE schema_tenant1.users ... etc # for example 

# ... do same for tenant2 through tenantn

To acheive 1) and 2), I need help in getting objection.js to do what follows. Once a connection is established to a databases, to switch between schemas and run our queries on a particular database, all we need to do is this on a particular connection is run a transaction as follows:

# SQL to be produced by objection.js
BEGIN;
SET ROLE role_tenant1;
SET search_path TO schema_tenant1;
SELECT * from users # is actually executing SELECT * from schema_tenant1.users 
COMMIT;

To be clear, this all works great from an SQL perspective. I just need some guidance on how to get this working with objection.js (if possible). So what I need to figure out is, with objection.js can I, on a transaction:

• run query to set role
• run query to set schema
• pass the transaction to each model so I can use models

Any help with this would be greatly appreciated. Thanks in advance.

[koskimas]

You can follow the multitenant recipe but in addition to selecting/creating the knex instance, also create a transaction for each request and the use bindTransaction function instead of bindKnex. If you want to use the "Database passing pattern" you can pass the transaction exactly like the knex instance to each query. You simply need to figure out how to commit/rollback the transaction for each request. With koa middleware this would be trivial:

const { transaction } = require('objection')

app.use((async (ctx, next) => {
  ctx.knex = await createOrSelectKnexForTenant(ctx)
  ctx.trx = await createTransactionForTenant(ctx)
  try {
    await next()
    await ctx.trx.commit()
  } catch (err) {
    await ctx.trx.rollback()
  }
})

You can set the ROLE and search_path using trx.raw

async function createTransactionForTenant(ctx) {
  const trx = await transaction.start(ctx.knex)
  await trx.raw('SET ROLE role_tenant1')
  await trx.raw('SET search_path TO schema_tenant1')
  return trx
}

[mashaalmemon]

The approach outlined and the objection commands to do it are just what I was looking for. Many thanks @koskimas for the quick response.