Note how to protect JWT secret at rest in the db about postgrest-docs

[begriffs]

@deinspanjer any update on this?

[deinspanjer]

Shoot, this slipped my mind cause I have been working on a different part of our stack, but I will try to dig up my notes and put an update in here early next week.

[begriffs]

I believe this documentation, once written, will address the concerns in PostgREST/postgrest#809

[begriffs]

Note to self: we also want to give an example of hiding the jwt functions themselves in a separate schema from the public api schema.

[deinspanjer]

Here is some DDL that pertains to the management of the jwt_secret and generation of signed tokens without relying on a DATABASE level setting (which isn't possible in Amazon RDS):

CREATE ROLE auther;
CREATE SCHEMA private;

ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMA private FROM PUBLIC;
GRANT USAGE ON SCHEMA private TO auther;

CREATE TABLE private.jwt_secret (
  secret TEXT NOT NULL,
  modified_on TIMESTAMPTZ DEFAULT NOW()
);
CREATE UNIQUE INDEX single_row ON private.jwt_secret ((secret IS NOT NULL));

REVOKE ALL ON TABLE private.jwt_secret FROM PUBLIC;
GRANT SELECT ON TABLE private.jwt_secret TO auther;

INSERT INTO private.jwt_secret (secret) VALUES('notso');

-- Install pgjwt then:

CREATE TYPE private.jwt_claims AS (iss TEXT, exp INT, sub TEXT, aud TEXT, jti UUID, rol NAME, role NAME);
CREATE OR REPLACE FUNCTION public.create_jwt_token(
  iss    TEXT,
  dur    INTERVAL,
  sub    TEXT,
  rol    NAME,
  "role" NAME
)
  RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  exp INT;
  jti UUID;
  aud TEXT;
  claims private.jwt_claims;
  signed_token TEXT;
BEGIN

  aud := 'http://localhost:8080/';
  exp := extract(EPOCH FROM (NOW() + dur));
  jti := gen_random_uuid();

  PERFORM set_config('request.jwt.claim.sub', sub, true);
  PERFORM set_config('request.jwt.claim.rol', rol, true);

  SELECT
      iss    AS iss
    , exp    AS exp
    , sub    AS sub
    , aud    AS aud
    , jti    AS jti
    , rol    AS rol
    , "role" AS "role"
  INTO claims;

  SELECT public.sign(row_to_json(claims), (SELECT secret FROM private.jwt_secret))
  INTO signed_token;
  RETURN signed_token;
END
$$;

[begriffs]

Thanks for that.