Support multiple-tenancy

[janiu-001]

Dear expert,

Our project has migrated from typeorm to postgres, and now we plan to support multi-tenant. The primary issue we face is to support tenant Id in all sql statements. We hope to have a common method for centralized processing, but unfortunately, we have not found a good method.

Do experts have any good suggestions? very appreciated for your response

Thanks

[Louis-Tian]

What do you mean by "centralised processing"? How are you modelling the multi-tenancy in your database in the first place? One database per tenant? One schema per tenant? Via Row Level Security?

[springmvcGitHub]

What do you mean by "centralised processing"? How are you modelling the multi-tenancy in your database in the first place? One database per tenant? One schema per tenant? Via Row Level Security?

What I mean by centralized processing is not adding the tenant id filter condition to every statement. It can be processed uniformly, and our logic does not care about the existence of tenant. We are currently adding the tenent_id field to all databases in a schema, hoping that we can add the tenant_id condition to the common consent (instead of adding tenant_id to every statement).Can you explain separately how to implement schema per tenant and RLS through Postgres.

[Louis-Tian]

tenent_id field to all databases in a schema

Please be more precise with the terminologies. It causes more confusion than explanation. There is no database in a schema. Schemas are in a database. Do you actually mean adding a tenant_id column for all tables in a schema ? If that's indeed the case, the options are creates views to filter the rows so you don't have have little the where clause all over your application code or leverage on RLS if you need to protect against the writes as well.

Can you explain separately how to implement schema per tenant and RLS through Postgres.

This is not the place for this kind of conversation. There are plenty of articles online for that, you should do your own research.

This is not really a issue nor a question directly about the postgres.js rather a generic multi-tenacy data modelling question.

[xiujuan-li]

@Louis-Tian Hi，I would like to confirm whether postgres.js provides public methods that can be used to support the addition of all table public fields?

[xiujuan-li]

Hi，@Louis-Tian We use the shared tables to implement multi-tenancy. On this basis,Just like the 'getRooms' methods in the code, we have many similar concatenated SQL that need to add 'tenantId' into where conditions, but don't want to handle every SQL manually, do you have any public methods to support it?

The RLS strategy I am using now can theoretically achieve this goal, but the difficulties encountered in practical development. I want to add RLS in batches to the existing concatenated sql, but using.begin() will be executed immediately. The concatenated SQL will lose the PendingQuery state, do you have any good suggestions?

My code looks something like this

//select function -- concatenated SQL 
export const getRooms = async (
    params: QueryFilter,
): Promise<RoomInfo[]> => {
  const roomQuery =
      params.type !== undefined
          ? sql`AND type = ${params.type}`
          : sql``;

  const result = await sql<Room[]>`
    SELECT room_id, type
    FROM room
    WHERE room_id IN ${sql(params.roomIds)}
    ${roomQuery}
`;
return result;
}

export async function sql<T extends readonly object[]>(strings: any, ...args: any[]){
  const tenantId = '1';
    return sqlOriginal.begin(async (pg_sql) => {
      pg_sql`select set_config('myapp.current_tenant', ${tenantId},false);`;
      return pg_sql<T>(strings as TemplateStringsArray, ...args)
    });
}

export const sqlOriginal = postgres({
  username: getRequired('POSTGRES_USER'),
  password: getRequired('POSTGRES_PASSWORD'),
  database: getRequired('POSTGRES_DB'),
  host: getRequired('POSTGRES_HOST'),
  port: Number(getRequired('POSTGRES_PORT')),
  ssl: getEnv().POSTGRES_ENABLE_UNAUTHORIZED ? false : true,
  types: {
    bigint: {
      to: 20,
      from: [20],
      serialize: (v: number) => v.toString(),
      parse: (v: string) => parseInt(v),
    },
  },
  onnotice: () => {
    // ignore notices
  },
});

[Louis-Tian]

No, there is no magic way of inserting a where condition for every query.

I don't know why are you setting the current_tenant as GUC. Is it just for passing tenant id number to the getRoom query?

How about using higher order functions like:

import postgres from 'postgres'

const pg = postgres({
  user: 'postgres',
  pass: 'password',
});

function tenant(tenantId){
  return (query) => sql`${query} and tenant = ${tenantId}`
}

function getRooms(roomIds) {
  return sql`select * from room where room_id in ${roomIds}`;
}

await tenant(1)(getRooms(2))

[porsager]

@xiujuan-li there is no exposed handler for this, but your way of doing it in a transaction is the most straight forward. If you ensure returning an array without await in the begin callback you can ensure Postgres.js pipelines the transaction.

I've played around with CTE's to see if there was a way to avoid the transaction, but unfortunately nested CTEs don't allow data modifications. If they did you could do some clever wrapping there 😋