I wonder if the following is possible in PostgreSQL using RLS (or any other mechanism). I want a user to be able to get certain rows of a table if its id matches a column in another table.
For e.g. we have following tables:
"user" table: columns: id, name
| id | name |
| --- | --- |
| 1 | one |
| 2 | two |
| 3 | three|
| 4 | four |
"tenant" table: columns: id, name
| id | name |
| --- | --- |
| 1 | t1 |
| 2 | t2 |
"user_tenant" table: columns: user_id, tenant_id
| user_id | tenant_id|
| --- | --- |
| 1 | t1 |
| 2 | t2 |
| 3 | t1 |
| 4 | t2 |
Now I want only users who has same tenant_id.
output:
| id | name |
| --- | --- |
| 1 | one |
| 3 | three|
To achieve this, I need to create policy something like this:
CREATE POLICY tenant_policy ON "user" USING (tenant_id = current_setting('my_user.current_tenant')::uuid);
but with above policy it's not working as I am getting all users.
Note: user & tenant table have many-to-many relationship.
P.S. I know we can do this either by join or some other condition. But I want to achieve the above output using PostgreSQL using RLS(row level security)
Thanks in advance!!
If row level security is not working that may be because one of the following applies:
you didn't enable row level security:
ALTER TABLE "user" ENABLE ROW LEVEL SECURITY;
the user owns the table
You can enable row level security for the owner with
ALTER TABLE "user" FORCE ROW LEVEL SECURITY;
you are a superuser, which is always exempt from RLS
you are a user defines with BYPASSRLS
the parameter row_security is set to off
Other than that, you will probably have to join with user_tenant in your policy:
CREATE POLICY tenant_policy ON "user"
USING (
EXISTS(SELECT 1 FROM user_tenant AS ut
WHERE ut.user_id = "user".id
AND ut.tenant_id = current_setting('my_user.current_tenant')::uuid
)
);