How to reference query values in Postgres res policy

Question

I am trying to build a system with Postgres in which users can connect with each other by sending requests. I am handling the security logic with RLS. However, I am having some trouble with the structure of one of my policies.

Here are the tables of concern, stripped of any nonessential columns:

CREATE TABLE profile (
  id UUID PRIMARY KEY,
  name text
);

CREATE TABLE request (
  id serial PRIMARY KEY,
  sender_id UUID,
  recipient_id UUID
);

CREATE TABLE connection (
  id serial PRIMARY KEY,
  owner_id UUID,
  contact_id UUID
);

Users are only allowed to add a connection if:

There is a row which references their profile id in recipient_id in the request table.
There is no existing connection that involves both users (no matter which one is the owner_id and which is the contact_id)

Here is the Policy I've written:

CREATE POLICY "Participants INSERT" ON public.connection FOR
INSERT
  WITH CHECK (
    auth.uid() = owner_id
    AND NOT EXISTS(
      SELECT
        *
      FROM
        public.connection c
      WHERE
        (
          (
            c.owner_id = owner_id --> Problem: How to reference query
            AND c.contact_id = contact_id
          )
          OR (
            c.contact_id = owner_id
            AND c.owner_id = contact_id
          )
        )
    )
    AND EXISTS(
      SELECT
        *
      FROM
        public.request r
      WHERE
        (
          r.sender_id = contact_id
          AND r.recipient_id = owner_id
        )
    )
  );

However, whenever I try to create a contact with a user whose id is already present in any row in the connection table (irrespective if it exists together with the correct contact_id), I get a policy violation error.

I think I might be referencing owner_id and contact_id wrong in the subquery because if I replace them manually with the appropriate id as a string, it works.

I'd really appreciate everyones input.

score 4 · Answer 1 · answered Jan 30 '22 at 16:40

I found the answer. The parent query can be accessed through the name of the table. So all I had to do was to access owner_id and contact_id like this:

connection.owner_id
connection.contact_id

On a side-note: The query values can NOT be accessed like this:

public.connection.owner_id
public.connection.contact_id

How to reference query values in Postgres res policy

1 Answers1

Linked