Row Level Security with Common Table Expression

Question

Given this schema:

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

I'm trying to insert a post using:

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;
$$;

However, I get:

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

If I get rid of the RLS policy, it seems to work.

I also am able to change the langauge to plpgsql without CTE, and it seems to work:

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;
$$;

I want to write up some more complicated transactions, but I need to use sql and CTE for other purposes.

Does RLS not work with CTE transactions?

Answer 1

I actually relooked at my table. The reason I am checking the foreign key is to confirm it is the same table that contains the post author Id. Hence, you can't add a tag unless you are adding it to a post, and you are the author of that post. My problem persists where I can't use langauge sql for this to work.

That's what composite secondary-keys (aka UNIQUE CONSTRAINT) indexes are for.

Like so:

• Also...
  • No-one should be using cryptic column names like pid, or ambiguous names like id)
  • Your PKs should be immutable. Having DELETE CASCADE is fine, but ON UPDATE CASCADE is not.
  • You appear to be using Supabase's auth.uid() extension - I don't recommend this because it means introducing a hard dependency between your storage-layer and your application-code layer - this means that you won't be able to easily manually edit data in the database using other tooling unless you're careful to disable all DEFAULT constraints referencing Supabase extensions.
  • Your tags.name and tags.post_id columns are missing explicit NOT NULL constraints. While the columns are implicitly NOT NULL because they're part of the PRIMARY KEY if you were to ever change the PK definition you'll need to add the NOT NULL to your db-schema-in-source-control otherwise a redeployment will see them having NULLable columns (and you are keeping your db-schema in source-control, right?)
  • The first column of a composite-key should always be the most selective: one would assume tags.post_id is going to be more selective than tags.name, so post_id should go first.
  • Consider getting into the habit of aligning your CREATE TABLE statements's parts into columns, it makes them significantly more readable:

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` */
);