How to utilise Postgres RLS for Application level users?

Question

I want to structure an application with scalability in mind and use postgres RLS for user authorization

1. I have a Node JS & Express server
2. I have a sequelize connection to postgres instance with connection pool set up

const sequelize = new Sequelize({
 database: 'your_database',
 username: 'your_username',
 password: 'your_password',
 host: 'your_host',
 dialect: 'postgres',
 pool: {
   max: 10,
   min: 0,
   acquire: 30000,
   idle: 10000,
 },
});
export default sequelizeClient;

3. For my APIs I have middleware that reads JWT
4. After parsing this JWT, I get user role and user ID
5. Now to utilise Postgres RLS, I'm thinking of doing a

await sequelize.query(`SET ROLE ${userRole}`);
await sequelize.query('SET LOCAL jwt.claims.userId = :userId', {
      replacements: { userId },
    });

where userId and userRole comes from JWT parsed in the middleware 6. Then going ahead with executing all the SQL queries for that API and this sql role and local variable will be used in the RLS policy to authorize the operation or reject it

Example code for one such API:

async function getUserData(userId, userRole) {
  try {
    await sequelize.query(`SET ROLE ${userRole}`);
    await sequelize.query('SET LOCAL jwt.claims.userId = :userId', {
      replacements: { userId },
    });

    // Perform your Sequelize operations
    const userData = await User.findByPk(userId, { raw: true });
    return userData;
  } catch (err) {
    console.log('ERROR: ', err);
 }
}

Now my questions are as follows:

1. Are there any chances of the SET LOCAL variable interfering with two different user's simultaneous API calls?
2. Will sequelize ensure that subsequent queries (Set local, role and user find query) will be executed in the same connection from the connection pool?
3. Can two distinct API queries from distinct APIs endup using the same connection while one API is processing?
4. Does Sequelize asks for a connection from connection pool for every individual queries?

I know supabase does similar stuff, but I want my own backend server, but if there is a way to utilise sequelize with supervisor provided by supabase for similar functionality please suggest that too.

Also if there is a better and more robust way to handle this please suggest it. Thank you

Answer 1

Based on my understanding of the source code:

When you issue a request with sequelize.query:

1. A connection is acquired from the connection pool

2. The query is executed

3. The connection is release back to the pool

   1. The connection is detstroyed if maxUses is reached see options.pool.maxUses

   2. The connecion is made available for further usage see sequelize-pool/blob/master/src/Pool.ts#L455

Based on this:

Are there any chances of the SET LOCAL variable interfering with two different user's simultaneous API calls?

No, once a connection is acquire it cannot be acquired until release. However further connection reuse could access to your "set local variable" if you dont clean it.

Will sequelize ensure that subsequent queries (Set local, role and user find query) will be executed in the same connection from the connection pool?

No, except by using a transaction. The cost of that is it will block the release of your connection until the commit. I think this could reduce the overall performace of your application and impact the database perfornance by creating more connection.

Can two distinct API queries from distinct APIs endup using the same connection while one API is processing?

Same as first point

Does Sequelize asks for a connection from connection pool for every individual queries?

Yes, until you use a transaction or specify the connection to use, see sequelize.query source code which leads to pool.acquire call

You may try to leverage hooks or global hooks to set your rls variable with before hook and after hook for cleaning operation. https://sequelize.org/docs/v6/other-topics/hooks/#global--universal-hooks