PostgreSQL Row Level Security in Node JS

Question

I have a database which is shared amongst multiple tenants/users. However, I want to add row-level-security protection so that any given tenant can only see those rows that belong to them.

As such, for each tenant I have a user in PostgreSQL, such as "client_1" and "client_2". In each table, there is a column "tenant_id", the default value of which is "session_user".

Then, I have row level security as such:

CREATE POLICY policy_warehouse_user ON warehouse FOR ALL
TO PUBLIC USING (tenant_id = current_user);
ALTER TABLE warehouse ENABLE ROW LEVEL SECURITY;
This works great, and if I set the user "SET ROLE client_1" I can only access those rows in which the tenant_id = "client_1".

However, I am struggling with how to best set this up in the Node JS back-end. Imortantly, for each tenant, such as "client_1", there can be multiple users connected. So several users on our system, all who work at company X, will connect to the database as "client_1".

What I am currently doing is this:

let config = {
        user: 'test_client2',
        host: process.env.PGHOST,
        database: process.env.PGDATABASE,
        max: 10, //default value
        password: 'test_client2',
        port: process.env.PGPORT,
    }

const pool = new Pool(config);
const client = await pool.connect()

await client.query('sql...')

client.release();

I feel like this might be a bad solution, especially since I am creating a new Pool each time a query is executed. So the question is, how can I best ensure that each user executes queries in the database using the ROLE that corresponds to their tenant?

JuanDM · Accepted Answer · 2023-01-11 22:21:43Z

0

Maybe you can have a setupDatabase method that returns the pool for your app this will be called once at app bootstrap:

function setUpDatabase {
  let config = {
    user: 'test_client2',
    host: process.env.PGHOST,
    database: process.env.PGDATABASE,
    max: 10, //default value
    password: 'test_client2',
    port: process.env.PGPORT,
  }

  const pool = new Pool(config);
  return pool
}

and then when you identify the tenant before executing the query you set the role

await client.query('set role $tenant', currentTenant);
// my assumption is that next line will use the role you set before
await client.query('select * from X where Y');

This is just a suggestion, I haven't tested it.

answered Jan 11, 2023 at 22:21

JuanDM

1,33011 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fabian Svensson Over a year ago

Good idea, I will try that. Then I would have to set-up a middleware for all queries to always set the role before executing any query.

Pushker Yadav Over a year ago

this wont work in case of concurrent request.

Collectives™ on Stack Overflow

PostgreSQL Row Level Security in Node JS

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related