SupabaseS
Supabase•4y ago
Daniel

RLS How to read using join on column value?

Hello! I have two schemas, messages and rooms.

This is the rooms schemas:
CREATE TABLE rooms (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id_one uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    user_id_two uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE
);


messages schema:
CREATE TABLE messages (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_user_id uuid NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    message text NOT NULL,
    room_id integer NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    create_time timestamp with time zone NOT NULL DEFAULT now(),
    message_id uuid NOT NULL UNIQUE
);


I want to create an RLS policy on the messages table for SELECT such that a user can only read messages for which they are either part of from_user_id to to_user_id.

This is what I have

SELECT m.room_id
 FROM messages m
          JOIN rooms r ON r.id = m.room_id
 WHERE user_id_one = '8dd172ba-7cfc-4c43-a427-601e505709f6'
    OR user_id_two = '8dd172ba-7cfc-4c43-a427-601e505709f6'


But how can I use my RPC function to match up with the valid room_id

CREATE FUNCTION get_rooms_with_user_id(p_user_id uuid)


Basically, I want to check that the only messages a user is getting is one where they are in that room.

Any ideas?
Was this page helpful?