Cannot access request.jwt.claims when running Postgres 13 or lower

[reaganmac]

Hi,

I've discovered that if your instance on Supabase was created a while ago, it might not be running Postgres 14. If that is the case, then trying to access request.jwt.claims will not work, as the name for the parameter has changed (to see what version of Postgres your instance is running, you can execute show server_version in the SQL Editor).

If that is the case, the functions in install.sql will need to be altered.

I had to change the body of is_claims_admin to the following:

CREATE OR REPLACE FUNCTION is_claims_admin() RETURNS "bool"
  LANGUAGE "plpgsql" 
  AS $$
  BEGIN
    IF session_user = 'authenticator' THEN
      --------------------------------------------
      -- To disallow any authenticated app users
      -- from editing claims, delete the following
      -- block of code and replace it with:
      -- RETURN FALSE;
      --------------------------------------------
      IF extract(epoch from now()) > coalesce((current_setting('request.jwt.claim.exp', true)::jsonb), '0')::numeric THEN
        return false; -- jwt expired
      END IF; 
      IF coalesce((current_setting('request.jwt.claim.app_metadata', true)::jsonb)->'claims_admin', 'false')::bool THEN
        return true; -- user has claims_admin set to true
      ELSE
        return false; -- user does NOT have claims_admin set to true
      END IF;
      --------------------------------------------
      -- End of block 
      --------------------------------------------
    ELSE -- not a user session, probably being called from a trigger or something
      return true;
    END IF;
  END;
$$;

Similarly, I altered get_my_claims() to:

CREATE OR REPLACE FUNCTION get_my_claims() RETURNS "jsonb"
    LANGUAGE "sql" STABLE
    AS $$
  select 
  	coalesce(nullif(current_setting('request.jwt.claim.app_metadata', true), '')::jsonb, '{}'::jsonb)::jsonb
$$;

and get_my_claim() becomes

CREATE OR REPLACE FUNCTION get_my_claim(claim TEXT) RETURNS "jsonb"
    LANGUAGE "sql" STABLE
    AS $$
  select 
  	coalesce(nullif(current_setting('request.jwt.claim.app_metadata', true), '')::jsonb -> claim, null)
$$;

---

I think this could be solved in the install.sql file by checking the version number in these function definitions, perhaps with

IF coalesce((select current_setting('server_version_num', true))::int, 0) < 140000 THEN ...

or something similar.

[burggraf]

Thanks for this.