使用行级安全性（基于租户）在 postgres 中同时设置多个角色
描述：我正在将 NestJS 与 postgres 数据库一起使用。我已在数据库表中应用了行级安全性（RLS）。因为我有 要求该项目以租户为基础。 RLS已成功实施。我遵循以下准则来实施 RLS。
https://www.postgresql.org/docs/current/ddl-rowsecurity。 html
https://www.postgresql.org/docs/current/sql-createpolicy.html
我已使用查询为每个租户创建了角色：
getConnection().query(`CREATE USER "` + id + '";');
此查询在数据库中创建用户。另外，当我在数据库中设置角色时：
getConnection().query(`SET ROLE "` + id + '";');
RLS 表现良好。它通过命令行查询输出数据库中以及应用程序中已设置角色的租户的数据。
每个租户都有自己的子域，但在同一个数据库下，他们的数据在行级别上分开。 我面临的问题是我无法同时设置多个角色。 假设“A”租户登录到其子域 a.xyz.com，则在数据库中设置“A”角色，当“B”租户登录其子域 b.xyz.com 时，在数据库中设置 B 角色数据库。 B 访问该功能后，“A”将自动注销。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我已经修复了它，但发布答案晚了。出现此问题的原因是用户表包含所有信息，并且在该表中实现了 rls。因此，分离表的列并将其放置在新表中而不实施 rls 解决了问题。
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.