Are pre-request functions run when role cannot be set as expected during jwt verification?

[dnk8n]

From the Tutorial part 1: "PostgREST allows us to specify a stored procedure to run during attempted authentication. The function can do whatever it likes, including raising an exception to terminate the request."

Is this function not run when claim is rejected, and web_anon (PGRST_DB_ANON_ROLE) is reverted to?

I was hoping that it would be run. I use asymmetric keys for jwt-secret and Azure AD to manage loging. I trust a decoded jwt claim from there (I can also do a check on tenant ID to be doubly sure they aren't able to manipulate the role coming from a different tenant for example).

In this case, I just want to create a role of a previously unseen user, set the role and get on with the request.

e.g. where PGRST_DB_PRE_REQUEST: auth.pre_request

CREATE OR REPLACE FUNCTION auth.pre_request()
RETURNS void AS $$
DECLARE
    CLAIMED_USER text;
BEGIN
    CLAIMED_USER := current_setting('request.jwt.claims')::json->>'role';
    IF CURRENT_USER != CLAIMED_USER AND CLAIMED_USER LIKE '%@hellodnk8n.onmicrosoft.com' THEN
        PERFORM auth.onboard_user(CLAIMED_USER);
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION auth.onboard_user(role_name text)
RETURNS void AS $$
BEGIN
    CREATE ROLE role_name;
    SET LOCAL ROLE role_name;
END;
$$ LANGUAGE plpgsql;

[dnk8n]

Actually, thinking about this a bit more. I wouldn't want the condition IF CURRENT_USER != CLAIMED_USER AND CLAIMED_USER LIKE '%@hellodnk8n.onmicrosoft.com' to be run at each and every request. I will see if I can split it out to an /rpc/ endpoint, which the frontend can run and redirect to where it was.

[dnk8n]

Also, this must be by design that it isn't working the way I expected. Think it is a good decision that it is how it is.

[wolfgangwalther]

Right. Pre-request functions are only run after SET ROLE.

I have a similar requirement in one of my projects to allow a demo version of the product login with any credentials provided. This creates the user on the fly. But I create JWTs in postgres via a /rpc/authenticate function - so this is a natural place to create those users when they don't exist.

[dnk8n]

I found a solution, and answered my own question here - https://dba.stackexchange.com/questions/325318/in-postgres-is-an-unprivileged-role-able-to-execute-a-function-with-elevated-pr

So I have a /rpc/onboard endpoint that an anonymous user can send a POST request body (they received from Azure AD auth flow) to create their user on the API for the first time.

I chose onboard, as that is something you typically only do once right at the start. From then on, usual authentication (with JWT token in the headers) works.