Postgrest: JWT from SQL documentation question...

[NickEmpetvee]

Regarding this: https://postgrest.org/en/v5.0/auth.html#jwt-from-sql

When I ran this SQL I got the error underneath it. However the sign() function had no problem when running the code in this section - https://postgrest.org/en/v5.0/auth.html#logins. Wondering if you might know what I'm doing wrong.

CREATE FUNCTION jwt_test() RETURNS public.jwt_token
    LANGUAGE sql
    AS $$
  SELECT sign(
    row_to_json(r), 'reallyreallyreallyreallyverysafe'
  ) AS token
  FROM (
    SELECT
      'my_role'::text as role,
      extract(epoch from now())::integer + 300 AS exp
  ) r;
$$;

THE ERROR:

ERROR:  function sign(json, unknown) does not exist
LINE 4:   SELECT sign(
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 89

[steve-chavez]

Seems related to the search_path, check https://postgrest.org/en/v5.0/api.html#explicit-qualification.

[NickEmpetvee]

Thanks. I will investigate.

[nigelrmtaylor]

I went through a similar loop last week getting the example login function to work and the trick seems to be to set the search path for the login function to include the schema where the extensions are loaded.

So, in my case, postgrest is exposing the 'core' schema, the extensions are loaded into the 'public' schema and the users table is in the basic_auth schema.

this creates the extensions...

-- add the pgcrypto extension to handle the cryptography
CREATE EXTENSION IF NOT EXISTS pgcrypto schema public;

-- add the pgjwt extension to handle the signing og the jwt tokens
CREATE EXTENSION IF NOT EXISTS pgjwt SCHEMA public;

then, once the login function is created ( just like in the documentation), we add public to the search path so it can access the two extensions.

ALTER FUNCTION core.login(email text, pass text) SET search_path = core, public;

That works for me.

Shout if ir doesnt for you

[NickEmpetvee]

It was the search_path. Thank you the link helped.