Multi-tenant data isolation with PostgreSQL Row Level Security

[KevinBrustolin]

Hello,

I'm starting a new projet with multi-tenant data isolation with Postgres RLS.

Has anyone implemented this with Adonis yet ?

I'll share my work here if someone is interested.

Thank you.

[vitortoigo]

Hello Kevin,
I have this implemented in a project in production.
Basically, when making a new connection to the pool (can see tarn.js and knex docs) you need to set the variable for that specific connection.

Can use the adonis event db:connection:connect,
And for get the tenant_id inside the event can use the Adonis ALS (Async Local Storage).

[akatora28]

@vitortoigo Do you have a code sample for how you implemented this? I'm trying to implement in v6 currently. I was trying to do something like this, but ctx.auth doesn't seem like the auth middleware runs until after the database config:

    postgres: {
      client: 'pg',
      connection: {
        host: env.get('DB_HOST'),
        port: env.get('DB_PORT'),
        user: env.get('DB_USER'),
        password: env.get('DB_PASSWORD'),
        database: env.get('DB_DATABASE'),
      },
      pool: {
        afterCreate(conn, done) {
          const ctx = HttpContext.getOrFail()
          conn.query(
            `SET app.current_organization_id = '${ctx.auth?.user?.currentOrganizationId}';`
          )
          done()
        },
      },
    },