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
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.