Postgres RLS policy: Only allow SELECT on row based on existence of foreign relation

Question

I have the following Postgres tables:

users

+---------+--------------+
| id (PK) |  full_name   |
+---------+--------------+
| uuid()  | varchar      |
+---------+--------------+

organizations

+---------+---------+
| id (PK) |  name   |
+---------+---------+
| uuid()  | varchar |
+---------+---------+

organization_memberships

+---------+----------------------+--------------+
| id (PK) | organization_id (FK) | user_id (FK) |
+---------+----------------------+--------------+
| uuid()  | uuid()               | uuid()       |
+---------+----------------------+--------------+

I am trying to write a Postgres Row-Level Security (RLS) policy.

The policy must enforce that users are only able to SELECT from the organizations table when there is a corresponding entry in the organization_memberships table.

This is what I currently have, but doesn't seem to be working as I would expect. Note the uid() method is provided by my DBAAS solution to get the authenticated user's id.

ALTER POLICY "User can only Select organizations where they are members" ON public.organizations USING (
  (
    EXISTS (
      SELECT
        orgmembs.user_id,
        orgmembs.organization_id
      FROM
        organization_memberships orgmembs
      WHERE
        (
          (orgmembs.organization_id = organizations.id)
          AND (orgmembs.user_id = uid())
        )
    )
  )
);

I've also tried this slightly-different version, but I receive the error, Error updating policy: missing FROM-clause entry for table "organization":

ALTER POLICY "User can only Select organizations where they are member" ON public.organizations USING (
  auth.uid() IN (
    SELECT
      user_id
    FROM
      organization_memberships
    WHERE
      organization_memberships.organization_id = organization.id
  )
);

Your policy looks fine. Please describe how it misbehaves. Perhaps the user running the query owns the table or is otherwise exempt from RLS. — Laurenz Albe, Jul 12 '21 at 04:21

Meysam Asadi · Accepted Answer · 2021-07-11T23:48:40.790

0

alter POLICY "User can only Select organizations where they are member"
      ON organizations 
      USING (id  IN (
                        SELECT organization_id 
                        FROM organization_memberships om 
                        JOIN users u ON om.user_id = u.id
                    )
            )

edited Jul 11 '21 at 23:48

answered Jul 11 '21 at 23:01

Meysam Asadi

6,438
3
7
17

Thanks for the reply. This doesn't seem to work as-is. Can you elaborate a bit more? – tdc Jul 11 '21 at 23:06
I did not understand your question correctly. What exactly do you want the policy to be? – Meysam Asadi Jul 11 '21 at 23:07
The goal is a user may only select rows from the `organizations` table if there is a corresponding row in `organization_memberships` which contains the `user.id` and the `organization.id`. – tdc Jul 11 '21 at 23:11
Correct.. The `organization_memberships` table maps `users` to `organizations`. I don't want users to be able to `SELECT` organzations that they don't belong to. Is this behavior not possible? This seems like a common relation pattern – tdc Jul 11 '21 at 23:18
I edited the code. When requested by the organization, the ones that the user join to organization_memberships are displayed. – Meysam Asadi Jul 11 '21 at 23:53

Postgres RLS policy: Only allow SELECT on row based on existence of foreign relation

1 Answers1