postgresql - How do I insert into a table with RLS and get the ID back before I have the record linked to the user? - Stack Over

admin管理员组

文章数量:1314514

Using Supabase/Postgres, I have 3 tables. auth.users, public.boards, and public.user_boards.

When a user creates a new board, I need to insert a record into public.boards and return the id so that I can create a new record in public.user_boards that shows the user owns that board.

But with RLS enabled, the user can't select from boards and get the ID it just created.

const { data: newBoard, error } = await supabase
  .from('boards')
  .insert([{ title: newBoardTitle.trim() }])
  .select() // <--- this fails unless I allow all users to select everything
  .single()

Using Supabase/Postgres, I have 3 tables. auth.users, public.boards, and public.user_boards.

When a user creates a new board, I need to insert a record into public.boards and return the id so that I can create a new record in public.user_boards that shows the user owns that board.

But with RLS enabled, the user can't select from boards and get the ID it just created.

const { data: newBoard, error } = await supabase
  .from('boards')
  .insert([{ title: newBoardTitle.trim() }])
  .select() // <--- this fails unless I allow all users to select everything
  .single()

• postgresql
• supabase
• rls

Share Improve this question Follow edited Jan 30 at 12:29 jonrsharpe 122k3030 gold badges267267 silver badges474474 bronze badges asked Jan 30 at 12:24 Kyle HKyle H 3,30355 gold badges3333 silver badges3939 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You will have to insert into user_boards first. To do that, you need to

• define the foreign key from user_boards to boards as deferred constraint, so that it is checked at the end of the transaction:

  ALTER TABLE user_boards
  ADD FOREIGN KEY (id) REFERENCES boards
      DEFERRABLE INITIALLY DEFERRED;
  

• know the name of the sequence that generates values for boards.id — let's assume it is called boards_id_seq

Then you can do it as follows:

-- start a transaction
BEGIN;

-- no error, because the constraint is deferred
INSERT INTO public.user_boards
VALUES (nextval('public.boards_id_seq'), current_user)
RETURNING id;
-- let's assume the result value is 42

-- now the policy will allow the following statement
INSERT INTO public.boards (id) OVERRIDING SYSTEM VALUE VALUES (42);

-- now the constraint will be checked and is satisfied
COMMIT;

本文标签：