CODEWITHSUNDEEP
sqlpostgresql
piyush
piyush

Reputation: 53

PostgreSQL row-level security involving foreign key with other table

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

Upvotes: 3

Views: 3351

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

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
            )
      );

Upvotes: 8

Related Questions