Dá uma olhada nesse artigo: Multi-tenant data isolation with PostgreSQL Row Level Security . Durante o exemplo de conexão creio que vai dar uma resposta boa para ti.
Sobre a pergunta, acho que vai ter que usar o middleware para saber quem tá connectando e só depois disso add na sessao da conexao do DB. Ele dá uma dica de usar sessão no banco de dados. Creio que pode trazer pro app para assim que se connectar no controler ele setar o tenant para aquela request completa e depois já morrer essa "sessão"
Agradeço muito a sua resposta!
Então, já tinha lido essa documentação, me esclareceu algumas dúvidas, mas no ponto de setar a variavel apenas pra uma conexão não.
Não sei se você já utilizou o Adonis, mas ele utiliza o Knex (https://knexjs.org/guide), então cheguei próximo a esse ponto:
const connectionNode = Database.manager.get('pg');
const pool = connectionNode?.connection?.pool;
pool?.on('acquireSuccess', async function (eventId, resource) {
await resource.query(SET app.tenant_id = '${tenant_id}');
});
A principio ele pega a pool atual e no sucesso seta a variavel, mas mesmo assim acaba setando pra todos.