Cannot access request settings in pre-request function

[mdr1384]

Environment

• PostgreSQL version: 13.10
• PostgREST version: 11.0.1 (binary)
• Operating system: FreeBSD 13.2

Description of issue

Following the pre-request example in the docs - I cannot seem to access the request.headers and request.jwt.claims settings inside the pre-request function. The result is always NULL. I can access other settings such as role and search_path. My pre-request function is:

CREATE OR REPLACE FUNCTION _api.login_auth()
    RETURNS void
    LANGUAGE plpgsql VOLATILE
    AS
$pgsql$
DECLARE
    _hdrs JSON;
    _claims JSON;
    _err TEXT := '';
    _txt TEXT;

BEGIN
    _hdrs := CURRENT_SETTING('request.headers', TRUE)::JSON;
    IF _hdrs IS NULL THEN
        _err := _err || ' headers are NULL';
    END IF;

    _claims := CURRENT_SETTING('request.jwt.claims', TRUE)::JSON;
    IF _claims IS NULL THEN
        _err := _err || ' claims are NULL';
    END IF;

    _txt := CURRENT_SETTING('role', TRUE);
    IF _txt IS NULL THEN
        _err := _err || ' role is NULL';
    ELSE
        _err := _err || ' role is ' || _txt;
    END IF;

    _txt := CURRENT_SETTING('search_path', TRUE);
    IF _txt IS NULL THEN
        _err := _err || ' search_path is NULL';
    ELSE
        _err := _err || ' search_path is ' || _txt;
    END IF;

    IF _err <> '' THEN
        RAISE WARNING 'Oops,%', _err;
    END IF;
END
$pgsql$;

GRANT EXECUTE ON FUNCTION _api.login_auth()
    TO public, anon, rest_user_max;

and my default.conf file has db-pre-request = "_api.login_auth" in it.

When accessing an endpoint with a valid JWT:

GET https://xxxxxxx.com/api/test                                                                                       200
Request Headers:
    cache-control:"no-cache"
    Postman-Token:"06985e3e-e730-4fd0-a7c6-86b569b817e7"
    Authorization:"Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ...X0.nOoKjqLw6XkhEaJ5-o68CHJF032Kx_7kWNTPmo1PlZ0"
    User-Agent:"PostmanRuntime/7.6.0"
    Accept:"*/*"
    Host:"xxxxxxx.com"
    accept-encoding:"gzip, deflate"
Response Headers:
    Date:"Fri, 07 Jul 2023 18:25:12 GMT"
    Server:"postgrest/11.0.1 (4197d2f)"
    Content-Range:"0-0/*"
    Content-Location:"/test"
    Content-Type:"application/json; charset=utf-8"
    Cache-Control:"max-age=0, no-cache, no-store, must-revalidate"
    Pragma:"no-cache"
    Expires:"Wed, 31 Dec 1980 11:59:59 GMT"
    Content-Length:"642"
    Keep-Alive:"timeout=5, max=100"
    Connection:"Keep-Alive"

the pre-request function is called and the exception raised shows:

WARNING:  Oops, headers are NULL claims are NULL role is rest_user_max search_path is api
CONTEXT:  PL/pgSQL function _api.login_auth() line 34 at RAISE

[laurenceisla]

Since you're using PostgreSQL v13, then PostgREST may be using the old way of accessing the GUCs. Try deactivating it in your config file by adding db-use-legacy-gucs = false.

[mdr1384]

Well that certainly changed something. Now no requests work, regardless of whether pre-request script is enabled or not. It is raising an error whenever our code is accessing the custom JWT claims:

ERROR:  unrecognized configuration parameter "request.jwt.claim.xxxxxxx_user_id"
STATEMENT:  WITH pgrst_source AS ( SELECT "api"."test".* FROM "api"."test"     )  SELECT null::bigint AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, coalesce(json_agg(_postgrest_t), '[]')::character varying AS body, nullif(current_setting('response.headers', true), '') AS response_headers, nullif(current_setting('response.status', true), '') AS response_status FROM ( SELECT * FROM pgrst_source ) _postgrest_t

Weirdly I note that inside the pre-request script the supposed unrecognized claim IS THERE AND USABLE when the legacy GUCs setting is false. But apparently outside of the pre-request script it is not.

[laurenceisla]

Maybe there are some other functions (maybe triggers or RLS?) that are using the old configuration. If it's too much to migrate right now, you could return everything as it was before (remove the db-use-legacy-gucs=false) and follow these examples instead: https://postgrest.org/en/v8.0/api.html#accessing-request-headers-cookies-and-jwt-claims

[mdr1384]

I updated my pre-request script to use the old-style settings access method - same thing happens that I originally reported. All the settings are NULL inside the script. It seems clear to me that (at least in PostgreSQL 13) you simply cannot access the settings in the pre-request script AND ALSO the regular views/trigger functions. You can only access the settings in one or the other based on the db-use-legacy-gucs value (false = can access inside pre-request only, true = can access outside pre-request only).

Pre-request script:

CREATE OR REPLACE FUNCTION _api.login_auth()
    RETURNS void
    LANGUAGE plpgsql VOLATILE
    AS
$pgsql$
DECLARE
    _txt TEXT;
    _err TEXT := '';

BEGIN
    _txt := CURRENT_SETTING('request.headers.authorization', TRUE);
    IF _txt IS NULL THEN
        _err := _err || ' authorization is NULL';
    ELSE
        RAISE WARNING 'Request authorization: %', _txt;
    END IF;

    _txt := CURRENT_SETTING('request.headers.postman-token', TRUE);
    IF _txt IS NULL THEN
        _err := _err || ' postman-token is NULL';
    ELSE
        RAISE WARNING 'Request postman-token: %', _txt;
    END IF;

    _txt := CURRENT_SETTING('request.jwt.claims.xxxxxxx_user_id', TRUE);
    IF _txt IS NULL THEN
        _err := _err || ' user id is NULL';
    ELSE
        RAISE WARNING 'Request user_id: %', _txt;
    END IF;

    IF _err <> '' THEN
        RAISE WARNING 'Oops,%', _err;
    END IF;
END
$pgsql$;

Resulting exception:

WARNING:  Oops, authorization is NULL postman-token is NULL user id is NULL
CONTEXT:  PL/pgSQL function _api.login_auth() line 29 at RAISE

[mdr1384]

I discovered that as you suggested, if I do migrate all my code to the new settings access method and use db-use-legacy-gucs=false - then I can access the settings in both the pre-request script and the regular code. It's apparently just not possible using the old access method. That's fine we will be upgrading PostgreSQL soon anyway. So thank you for the hints @laurenceisla. I think this can be considered closed.