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

Viewed 830

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
  )
);
1 Answers
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
                    )
            )
Related