Postgres RLS政策和功能

Question

我在Postgres函数上违反了RLS策略。我相信这是因为策略依赖于在函数中创建的行。函数中运行一个SELECT命令。新行不可用，因为它们仍在事务中。

以下是功能：

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;

以下是政策：

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()));

想法：

1. 在连接表中添加一个字段以简化策略，但它违反了常规形式。
2. 返回用户输入而不是运行SELECT，但是输入可以转义，我应该能够运行SELECT命令
3. ，分为两个函数。创建message行，然后添加message_tag。我在运行明信片，所以有两种变异。我在两个人之间建立了外交关系。我不知道图形文件会不会做那个automatically.

错误消息：

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

我在运行函数时会收到错误。我希望函数成功运行，在message表中插入一行，并将输入数组转换为message_tag表的行，message_tag.message_id=message.id是最后插入的id。我需要一个适当的策略，这样来自连接关系的用户只会看到他们自己的组织的message_tag行。

下面是INSERT命令的另一个策略。如果用户登录，它允许插入：

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()));

Answer 1

根据错误消息，SQL语句的这一部分将导致错误：

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 *

您需要添加另一个带有适当WITH CHECK子句的策略WITH CHECK。

Answer 2

最后，我向联接表添加了一个字段，并使用该字段创建了一个策略。这样，RLS验证不需要在函数中间创建行。