1

I have a RLS policy violation on a Postgres function. I believe it's because the policy relies on rows created in the function. A SELECT command is run in the function. New rows are not available because they are still in a transaction.

Here is the function:

CREATE FUNCTION public.create_message(organization_id int, content text, tags Int[])
RETURNS setof public.message
AS $$

-- insert message, return PK
WITH moved_rows AS (
  INSERT INTO public.message (organization_id, content)
    VALUES($1, $2)
  RETURNING *
),

-- many to many relation
moved_tags AS (
  INSERT INTO public.message_tag (message_id, tag_id)
  SELECT moved_rows.id, tagInput.tag_id
  FROM moved_rows, UNNEST($3) as tagInput(tag_id)
  RETURNING *
)

SELECT moved_rows.* FROM moved_rows LEFT JOIN moved_tags ON moved_rows.id = moved_tags.message_id

$$ LANGUAGE sql VOLATILE STRICT;

Here is the policy:

CREATE POLICY select_if_organization
      on message_tag
      for select 
      USING ( message_id IN (
        SELECT message.id 
          FROM message
          INNER JOIN organization_user ON (organization_user.organization_id = message.organization_id) 
          INNER JOIN sessions ON (sessions.user_id = organization_user.user_id)
          WHERE sessions.session_token = current_user_id()));

Ideas:

  1. Add a field to the joining table to simplify the policy, but it violates normal form.
  2. Return user input instead of running the SELECT, but input may be escaped and I should be able to run a SELECT command
  3. Split into two functions. Create the message row, then add the message_tag. I'm running postgraphile, so two mutations. I have foreign key relations setup between the two. I don't know if graphile will do that automatically.

Error message:

ERROR:  new row violates row-level security policy for table "message_tag"
CONTEXT:  SQL function "create_message" statement 1

I receive the error when I run the function. I want the function to run successfully, insert one row in the message table, and turning the input array into rows for the message_tag table with message_tag.message_id=message.id, the last inserted id. I need a policy in place so users from that join relation only see their own organization's message_tag rows.

Here is another policy on the INSERT command. It allows INSERT if a user is logged in:

create policy insert_message_tag_if_author
  on message_tag
  for insert
  with check (EXISTS (SELECT * FROM sessions WHERE sessions.session_token = current_user_id()));
steve76
  • 302
  • 2
  • 9
  • 1
    Your function `RETURNS setof public.message`. What even is the point of joining the result against the `moved_tags`? It only would introduce duplicates. Instead, return your inserted message (which probably should be a single one), then select the tags of that using graphql. This will ensure the `message_tag` table is queried only after all rows are inserted. – Bergi Jul 19 '21 at 22:38
  • 1
    "*I have a RLS policy violation on a Postgres function.*" - what exactly is the error? What results are you getting when you call the function? What is the expected result? – Bergi Jul 19 '21 at 22:39
  • @Bergi Thank you. That makes sense. Graphile has a query field in it's mutation payload. – steve76 Jul 19 '21 at 23:25
  • I removed the SELECT statement that returns, but I still receive the error. Looks like it's the INSERT. I updated the question with more info please. – steve76 Jul 20 '21 at 01:18
  • What if I create a new policy based on a config value set in the function? Before the insert, set the config value to true. After, set it to false. – steve76 Jul 20 '21 at 01:28

2 Answers2

0

According to the error message, this part of your SQL statement causes the error:

INSERT INTO public.message_tag (message_id, tag_id)
  SELECT moved_rows.id, tagInput.tag_id
  FROM moved_rows, UNNEST($3) as tagInput(tag_id)
  RETURNING *

You need to add another policy FOR INSERT with an appropriate WITH CHECK clause.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I haven't seen an AND in a FOR before. When I tried doing it, I got a syntax error, pointing right at the AND. I copied the policy I had, and added to INSERT, but with the same error. I also have another policy which just checks for a user session on INSERT. I think this has to do with the row not existing because it's in the middle of a function. I run the commands separate on the CLI, and it works. – steve76 Jul 20 '21 at 14:47
  • I forgot that a policy can only have a single `FOR`. You'll have to add a second policy. The error message is unambiguous, it complains about an illegal `INSERT`. You didn't show that other policy, so I can't say what exactly is wrong. – Laurenz Albe Jul 20 '21 at 14:58
  • I updated the question with the other policy. One success I'm having is running in my function SELECT set_config('policy.allow_message_tag', 't', true). Then, having a policy that allows insert if that config is set. At the end of the function, I set that config back to 'f'. Looks like it's working on the command line but not from a graphile endpoint, which I'm working on. – steve76 Jul 20 '21 at 15:52
  • Then it is that policy that is failing. `current_user_id()` must be a user-defined function. At least you know in which direction to debug. – Laurenz Albe Jul 20 '21 at 18:45
0

I ended up adding a field to the joining table, and creating a policy with that. That way, RLS validation does not require a row which would be created in a middle of a function.

steve76
  • 302
  • 2
  • 9