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 😋

[xiujuan-li]

Hi，I appreciate your advice, but I'm still a little confused.@Louis-Tian There are some SQL in our code using 'LEFT JOIN', 'INNER JOIN', some SQL does not set Where conditions, these need to be unified on the tenantId where conditions, similar to this there are many, not easy to enumerate, this kind of higher-order function how to deal with? The code looks like this:

return await sql<Info[]>`
SELECT
  a.file,
  a.version,
  h.type
FROM
  a
INNER JOIN
  (select * from b where b.name is not null) h
ON
  h.version = a.version
WHERE
  h.code = ${params.code}
`;

We have about hundreds of tenants. Based on RLS policy, each tenant uses its own database link, which will require a lot of links to be used at the same time. If these tenant links are cached in Map, is this solution feasible? Or do you have any good suggestions?

export const sqlOriginal = postgres({
...,
  connection: {
    'myapp.current_value': '1',
  }
  },
});

our RLS policy like this:

CREATE POLICY product_policy ON userlist
    USING (current_setting('myapp.current_value') = id );

[janiu-001]

@porsager @Louis-Tian

Any suggestions or comments on our RLS solution - one connection per tenant, our service are deployed with Cloud Run, there will be many connections

And about the higher-order function, how to handler different kind of sql statement, like 'LEFT JOIN', 'INNER JOIN'..etc

[xiujuan-li]

Hi,@porsager Can you tell us more about the way postgresjs pipes transactions? I'm very interested.

[porsager]

It makes an assumption that if the return value from the begin callback function is not async, it will pipeline the statements (eg. it sendes begin, ...statements, commit without waiting for the result). It expects the return value to either be a Query (sql``) or an array of queries.

[janiu-001]

would you give an example? very appreciated

if we can find a good way to set the variables for the policy, it should be great(select set_config('myapp.current_tenant', ${tenantId},false);)

[janiu-001]

@porsager @Louis-Tian

Looks like the reserve is what we needed

the code is like this, would you share your comments?

export const sql = Object.assign(async <T extends readonly object[]>(strings, ...args) => {
  const tenantId = "2";
  const reserved = await sql.reserve()
  await reserved`select set_config('myapp.current_tenant', ${tenantId},false);`
  await reserved(strings as TemplateStringsArray, ...args)
  await reserved.release()
}, sqlPg);

But it should block the sql in sql, like:

   const device_id = 'simulator-1961';
    const sqlTemp = sql`device_id  = ${device_id}`
    // const ret = await sql.begin((trans)=>{
    //   return trans`SELECT device_id, tenant_id from device where ${sqlTemp} limit 3`
    // })
    const ret = await sql`SELECT device_id, tenant_id from device where ${sqlTemp} limit 3`
    console.log(ret);

[janiu-001]

it should be great if we can find a common way to wrap each SQL statement with a WHERE tenant_id = ${tenantId} clause, like the my-habits in java

[janiu-001]

@porsager would you help to share your suggestions?

[porsager]

Sorry @janiu-001 , I don't have much time these days, so can't do consulting for free, but shoot me an email if you want to talk over your setup.