SET multiple role simultaneously in postgres using row level security (Tenant basis)

Viewed 324

Description: I am using NestJS with a postgres database. I have applied Row Level Security(RLS) in the database table. As I have the requirement that the project will be on the tenant basis. RLS has been implemented successfully. I have followed the following guidelines to implement the RLS.

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

https://www.postgresql.org/docs/current/sql-createpolicy.html

https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql

https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

I have created the role for each tenant using the query:

getConnection().query(`CREATE USER "` + id + '";');

This query creates the user in the database. Also when I set the role in the database :

getConnection().query(`SET ROLE "` + id + '";');

RLS is performing well . It output the data of tenant whose role has been set in the database by command line query and also in the application.

Each tenant will have their own sub-domain but under the same database, their data is separated on the row level basis. The problem I am facing is I cannot set multiple role simultaneously. Suppose 'A' tenant logged into his sub-domain a.xyz.com then 'A' role is set in the database and when 'B' tenant logged in his sub-domain b.xyz.com, B role is set in the database. After B access the feature then 'A' is logged out automatically.

1 Answers

I have fixed it but posting the answer late. The issue has occurred because the user table has all the information and also rls is implemented in that table. So separating the table's column and placing it in the new table without implementing the rls fixed the problem.

Related