pgjwt cannot be installed into a different schema from pgcrypto

[colinemonds]

The function algorithm_sign(signables text, secret text, algorithm text) incorrectly has the following line:

SELECT @extschema@.url_encode(@extschema@.hmac(signables, secret, alg.id)) FROM alg;

Because the hmac function is qualified with @extschema@, which is the schema that pgjwt is installed to, it will never be found if it's not in the same schema as pgjwt (not even if pgcrypto is installed in the public schema).

This makes it impossible to have the pgjwt extension installed into a separate schema (e.g. create extension pgjwt with schema jwt), which is highly desirable because of its very generic function names. It's clear what jwt.sign(json, text, text) does, sign(json, text, text) is much less clear, and also pollutes the global search path with a very generic identifier.

A currently possible workaround is to install both extensions into the same schema, which is probably what most people are doing (install both extensions to the public schema, or install both extensions to a shared extensions schema).

The reason this @extschema@ qualification is in the code in the first place is that it was intended to fix a different issue related to an incorrect search_path (see #6).

The correct fix for that issue, however, is not to qualify the hmac call, but to add @extschema@ to the function's search_path, like so:

CREATE OR REPLACE FUNCTION algorithm_sign(signables text, secret text, algorithm text)
RETURNS text
LANGUAGE sql
AS $$
WITH
  alg AS (
    SELECT CASE
      WHEN algorithm = 'HS256' THEN 'sha256'
      WHEN algorithm = 'HS384' THEN 'sha384'
      WHEN algorithm = 'HS512' THEN 'sha512'
      ELSE '' END AS id)  -- hmac throws error
SELECT @extschema@.url_encode(hmac(signables, secret, alg.id)) FROM alg;
$$ IMMUTABLE;

ALTER FUNCTION algorithm_sign(text, text, text)
SET SEARCH_PATH = @extschema@, public;

This way, the hmac function will be found both in the extension's schema and in public. However, the function will still fail if pgcrypto is installed in a non-public schema that is different from pgjwt's schema. To fix it properly, some dynamic SQL is needed:

CREATE OR REPLACE FUNCTION algorithm_sign(signables text, secret text, algorithm text)
RETURNS text
LANGUAGE sql
AS $$
WITH
  alg AS (
    SELECT CASE
      WHEN algorithm = 'HS256' THEN 'sha256'
      WHEN algorithm = 'HS384' THEN 'sha384'
      WHEN algorithm = 'HS512' THEN 'sha512'
      ELSE '' END AS id)  -- hmac throws error
SELECT @extschema@.url_encode(hmac(signables, secret, alg.id)) FROM alg;
$$ IMMUTABLE;

DO $$
DECLARE
  pgcrypto_schema TEXT;
BEGIN
  SELECT nspname
  FROM pg_catalog.pg_extension
  INNER JOIN pg_catalog.pg_namespace ON (
	pg_catalog.pg_namespace.oid = pg_catalog.pg_extension.extnamespace
  )
  WHERE extname = 'pgcrypto'
  INTO pgcrypto_schema;

  IF pgcrypto_schema IS NULL THEN
    RAISE EXCEPTION 'pgcrypto is not installed, aborting extension installation';
  END IF;
  
  EXECUTE 'ALTER FUNCTION algorithm_sign(text, text, text) SET SEARCH_PATH = ' || pgcrypto_schema;
END;
$$;