使用公用表表达式的行级别安全性-解网

问：

给定此架构：

CREATE TABLE posts (
  id uuid NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
  title text NOT NULL CHECK (char_length(title) > 2),
  author uuid NOT NULL DEFAULT auth.uid() REFERENCES profiles(id) 
    ON DELETE CASCADE ON UPDATE CASCADE,
  content text NOT NULL CHECK (char_length(content) > 3),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE tags (
  name text,
  pid uuid REFERENCES posts(id) ON DELETE CASCADE ON UPDATE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (name, pid)
);

CREATE POLICY "rls_tags_read_public"
  ON tags FOR SELECT
  USING (true);

CREATE POLICY "rls_tags_create_authenticated_own_posts"
  ON tags FOR INSERT TO "authenticated"
  WITH CHECK (EXISTS (
    SELECT 1 FROM posts p WHERE p.author = auth.uid() 
    AND p.id = pid
  ));

我正在尝试使用以下方式插入帖子：

CREATE OR REPLACE FUNCTION insert_post(
  title text,
  content text,
  tags text[]
)
RETURNS SETOF posts
LANGUAGE sql
AS $$
  WITH new_post AS (
    INSERT INTO posts (title, content)
    VALUES (title, content)
    RETURNING *
  ),
  insert_tags AS (
    INSERT INTO tags (name, pid)
    SELECT unnest(insert_post.tags), id FROM new_post
  )
  SELECT * FROM new_post;
$$;

但是，我得到：

'new row violates row-level security policy for table "tags"'

如果我摆脱了 RLS 策略，它似乎有效。

我还可以将语言更改为没有 CTE，它似乎有效：plpgsql

CREATE OR REPLACE FUNCTION insert_post(
  title text,
  content text,
  tags text[]
)
RETURNS SETOF posts
LANGUAGE plpgsql
AS $$
DECLARE
  new_post posts%ROWTYPE;
BEGIN
  INSERT INTO posts (title, content)
  VALUES (title, content)
  RETURNING * INTO new_post;

  INSERT INTO tags (name, pid)
  SELECT unnest(tags), new_post.id;

  RETURN QUERY SELECT * FROM posts WHERE id = new_post.id;
END;
$$;

我想写一些更复杂的交易，但我需要使用和用于其他目的。sqlCTE

RLS 是否不适用于 CTE 事务？

PostgreSQL 通用表表达式 supabase 行级安全性

我实际上重新看了看我的桌子。我检查外键的原因是确认它与包含帖子作者 ID 的表相同。因此，除非您将标签添加到帖子中，并且您是该帖子的作者，否则您无法添加标签。我的问题仍然存在，我无法使用 langauge sql 来解决这个问题。

这就是复合辅助键（又名）索引的用途。UNIQUE CONSTRAINT

这样：

也。。。
- 任何人都不应使用像这样的神秘列名或像 id 这样的模棱两可的名称pid)
- 您的 PK 应该是不可变的。拥有是可以的，但不是。DELETE CASCADEON UPDATE CASCADE
- 您似乎正在使用 Supabase 的扩展 - 我不建议这样做，因为这意味着在存储层和应用程序代码层之间引入硬依赖关系 - 这意味着您将无法使用其他工具轻松手动编辑数据库中的数据，除非您小心禁用引用 Supabase 扩展的所有约束。auth.uid()DEFAULT
- your 和 columns 缺少显式约束。虽然这些列是隐式的，因为它们是如果你要更改 PK 定义，则需要将添加到你的 db-schema-in-source-control 中，否则重新部署将看到它们具有 able 列（并且您将 db-schema 保留在源代码管理中，对吧？tags.nametags.post_idNOT NULLNOT NULLPRIMARY KEYNOT NULLNULL
- 复合键的第一列应始终是最具选择性的：人们会认为它将比更具选择性，因此应该放在第一位。tags.post_idtags.namepost_id
- 考虑养成将陈述的各个部分对齐列的习惯，这样可以使它们更具可读性：CREATE TABLE

CREATE TABLE posts (
  post_id        uuid        NOT NULL DEFAULT uuid_generate_v4(),
  title          text        NOT NULL CHECK ( char_length( title ) > 2 ),
  author_user_id uuid        NOT NULL DEFAULT auth.uid() REFERENCES profiles( user_id ) ON DELETE CASCADE,
  content        text        NOT NULL CHECK ( char_length( content ) > 3 ),
  created_at     timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT PK_posts PRIMARY KEY ( post_id ),
  
  CONSTRAINT UK_posts_author UNIQUE ( post_id, author_user_id ) /* Referenced by tags.FK_tags_posts */
);

CREATE TABLE tags (
  name           text        NOT NULL,
  post_id        uuid        NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  author_user_id uuid        NOT NULL DEFAULT auth.uid() REFERENCES profiles( user_id ) ON DELETE CASCADE,

  CONSTRAINT PK_tags PRIMARY KEY ( post_id, name ),
  
  CONSTRAINT FK_tags_posts FOREIGN KEY ( post_id, author_user_id ) REFERENCES posts ( post_id, author_user_id ) /* This will use `UK_posts_author` */
);

使用公用表表达式的行级别安全性

Row Level Security with Common Table Expression

评论

评论