Setting local config in SQL before INSERT

Question

newbie in SQL coming from a JS world needing some advice on triggers.
My user has an id column and my GraphQL API always calls INSERT INTO .... RETURNING * and then doing the transforms on the GraphQL layer to return what I want.
The goal is to allow a query like INSERT INTO .... RETURNING * work with RLS in place.

The policies are:

CREATE POLICY USER_SELECT_RESTRICTIONS 
ON "user" 
FOR SELECT
USING ("id" = current_user_id() OR current_user_role() = 'admin' OR current_user_role() = 'partner');

CREATE POLICY USER_INSERT_RESTRICTIONS
ON "user" 
FOR INSERT
WITH CHECK (true);

This breaks for guest users (more context at the bottom) because they are allowed to INSERT but cannot SELECT (because of the restriction that only authorized users can select their own rows).

So I had the idea to somehow set the user setting manually in a trigger before/after insert (don't know which because I couldn't move forward due to the syntax error).

I've tried this but I get a syntax error at the NEW.id? (it just says "Syntax Error" to me)

CREATE OR REPLACE FUNCTION after_user_insert()
RETURNS TRIGGER AS $$
BEGIN
  SET LOCAL jwt.claims.userId NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_user_id_on_insert
AFTER INSERT ON "user"
FOR EACH ROW
EXECUTE PROCEDURE after_user_insert();

Searched around a lot and to be honest I think I may just not find anything because I am missing the correct terminology to find what I need to find.

Would appreciate not only help on what on this specific problem but also any related advice on policies for guest users that need priviliges.

Context:

These are the relevant tables and functions

CREATE TYPE "user_role" AS ENUM (
  'customer',
  'partner',
  'admin'
);
​
CREATE TABLE "user" (
  "id" uuid UNIQUE PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
  "first_name" varchar NOT NULL,
  "last_name" varchar NOT NULL,
  "email" text UNIQUE NOT NULL,
  "role" user_role NOT NULL DEFAULT 'customer',
  "created_at" timestamp NOT NULL DEFAULT NOW(),
  "updated_at" timestamp NOT NULL DEFAULT NOW()
);
​
CREATE FUNCTION current_user_id() RETURNS uuid AS $$
  SELECT nullif(current_setting('jwt.claims.userId', true), '')::uuid;
$$ LANGUAGE SQL stable;
​
CREATE FUNCTION current_user_role() RETURNS user_role AS $$
  SELECT nullif(current_setting('jwt.claims.role', true), '')::user_role;
$$ LANGUAGE SQL stable

The RLS restricts SELECT to rows where the id column of the user table matches current_setting('jwt.claims.userId').
This was set previously by Postgraphile as seen here (https://www.graphile.org/postgraphile/security/).

One possible workaround I thought of would be this but I don't know if this would lead to some kind of vulnerabilities because of the obvious role elevation:

CREATE OR REPLACE FUNCTION after_user_insert()
RETURNS TRIGGER AS $$
BEGIN
  SET LOCAL jwt.claims.role TO "customer";
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION before_user_insert()
RETURNS TRIGGER AS $$
BEGIN
  SET LOCAL jwt.claims.role TO "admin";
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER before_insert
BEFORE INSERT ON "user"
FOR EACH ROW
EXECUTE PROCEDURE before_user_insert();

CREATE TRIGGER after_insert
AFTER INSERT ON "user"
FOR EACH ROW
EXECUTE PROCEDURE after_user_insert();

Answer 1

Your don't say what a guest is, but your approach seems wrong.

Rather than disabling your checks on a low level, which gives you a bad feeling for good reasons, you should choose one of the following approaches:

• fix your policies to allow the necessary operation (perhaps by adding a permissive policy)

• have certain operations performed by a SECURITY DEFINER function that belongs to a user not subject to the restrictions.

If you insist on a trigger based solution, you have to use a BEFORE trigger. Also, consider that you cannot use parameterss with a SET statement. You'd either have to use dynamic SQL or (better) use a function:

CREATE OR REPLACE FUNCTION before_user_insert() RETURNS TRIGGER AS
$$BEGIN
   SELECT set_config('jwt.claims.userId', NEW.id::text, TRUE);
   RETURN NEW;
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER set_user_id_on_insert BEFORE INSERT ON "user"
FOR EACH ROW EXECUTE PROCEDURE before_user_insert();