"Invalid input syntax" when reading current_setting('request.jwt.claim.user_id')

[elimisteve]

Environment

• PostgreSQL version: 9.6
• PostgREST version: Tried PostgREST 20210305, 7.0.1, 7.0.0, and 0.4.3.0; all version have this problem, though it might take you 8-10 requests to "lose the race"
• Operating system: Debian 9.13

Description of issue

PostgREST sometimes parses current_setting('request.jwt.claim.user_id', true) before it's used, but sometimes doesn't, spitting out empty string instead of either NULL or the correct value (when the payload contains a user_id).

CREATE TABLE users (
    id      serial    NOT NULL PRIMARY KEY,
    created timestamp WITH time zone NOT NULL DEFAULT now(),
    user_minilock_id      text NOT NULL CHECK (40 <= LENGTH(user_minilock_id) AND LENGTH(user_minilock_id) <= 55),
    user_auth_minilock_id text NOT NULL CHECK (40 <= LENGTH(user_auth_minilock_id) AND LENGTH(user_auth_minilock_id) <= 55),
    csrf_token            text NOT NULL,
    is_invitee            boolean NOT NULL DEFAULT false;
);

ALTER TABLE users ENABLE ROW LEVEL SECURITY;


CREATE TABLE csrf_tokens (
    token   text      NOT NULL PRIMARY KEY CHECK (LENGTH(token) = 36),
    created timestamp WITH time zone NOT NULL DEFAULT now()
);

CREATE ROLE can_insert_users_if_csrf_header;
CREATE ROLE can_select_users_if_self_or_csrf_header;

CREATE POLICY insert_users
ON users
FOR INSERT
TO can_insert_users_if_csrf_header
WITH CHECK (
    current_setting('request.header.X-Csrf-Token', true) = ANY(SELECT token FROM csrf_tokens)
);

CREATE POLICY select_users
ON users
FOR SELECT
TO can_select_users_if_self_or_csrf_header
USING (
    users.id = (current_setting('request.jwt.claim.user_id', true) || 'RACE_CONDITION')::integer
    OR
    users.csrf_token = current_setting('request.header.X-Csrf-Token', true)
);

Then run this once in psql:

my_db_name=# INSERT INTO csrf_tokens (token) VALUES ('7bca85c4-2510-4c29-7789-29c89f8b8fcc');

And run this many times till you get the error {"hint":null,"details":null,"code":"22P02","message":"invalid input syntax for integer: \"RACE_CONDITION\""} --

curl -i -X POST -H 'Content-Type: application/json; charset=utf-8' -H 'Prefer: return=representation' -H 'X-Csrf-Token: 7bca85c4-2510-4c29-7789-29c89f8b8fcc' -d '{"csrf_token": "7bca85c4-2510-4c29-7789-29c89f8b8fcc", "is_invitee": true, "user_auth_minilock_id": "3NTxSJmFBYY94aNeiLyepKQvw2N2Xbp8reNY6mEXKhigz", "user_minilock_id": "sR8avZPD2zKj5TqxSjHFYk4vUaLp8TbcuvYHDd3p1AQvs"}' localhost:3000/users

Again, the fundamental problem is that current_setting('request.jwt.claim.user_id', true) evaluates to '' rather than NULL, and thus ``current_setting('request.jwt.claim.user_id', true)::integer` blows up instead of not.

[elimisteve]

(In my real DB I make various things UNIQUE, but dropped that here so it wouldn't be a pain to test this.)

[steve-chavez]

@elimisteve This is a pg limitation. To reproduce, check: #1642 (comment)

You need to use nullif to address it, like mentioned here: #1642 (comment)

[elimisteve]

@steve-chavez Whew, thanks!