Created
June 29, 2023 12:07
-
-
Save pank-su/afe88aaee86519494c9b6cd23b319f3a to your computer and use it in GitHub Desktop.
Create pgjwt functions without using extension.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION url_encode(data bytea) RETURNS text LANGUAGE sql AS $$ | |
SELECT translate(encode(data, 'base64'), E'+/=\n', '-_'); | |
$$ IMMUTABLE; | |
CREATE OR REPLACE FUNCTION url_decode(data text) RETURNS bytea LANGUAGE sql AS $$ | |
WITH t AS (SELECT translate(data, '-_', '+/') AS trans), | |
rem AS (SELECT length(t.trans) % 4 AS remainder FROM t) -- compute padding size | |
SELECT decode( | |
t.trans || | |
CASE WHEN rem.remainder > 0 | |
THEN repeat('=', (4 - rem.remainder)) | |
ELSE '' END, | |
'base64') FROM t, rem; | |
$$ IMMUTABLE; | |
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 url_encode(hmac(signables::bytea, secret::bytea, alg.id)::bytea) FROM alg; | |
$$ IMMUTABLE; | |
CREATE OR REPLACE FUNCTION sign(payload json, secret text, algorithm text DEFAULT 'HS256') | |
RETURNS text LANGUAGE sql AS $$ | |
WITH | |
header AS ( | |
SELECT url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}', 'utf8')) AS data | |
), | |
payload AS ( | |
SELECT url_encode(convert_to(payload::text, 'utf8')) AS data | |
), | |
signables AS ( | |
SELECT header.data || '.' || payload.data AS data FROM header, payload | |
) | |
SELECT | |
signables.data || '.' || | |
algorithm_sign(signables.data, secret, algorithm) FROM signables; | |
$$ IMMUTABLE; | |
CREATE OR REPLACE FUNCTION try_cast_double(inp text) | |
RETURNS double precision AS $$ | |
BEGIN | |
BEGIN | |
RETURN inp::double precision; | |
EXCEPTION | |
WHEN OTHERS THEN RETURN NULL; | |
END; | |
END; | |
$$ language plpgsql IMMUTABLE; | |
CREATE OR REPLACE FUNCTION verify(token text, secret text, algorithm text DEFAULT 'HS256') | |
RETURNS table(header json, payload json, valid boolean) LANGUAGE sql AS $$ | |
SELECT | |
jwt.header AS header, | |
jwt.payload AS payload, | |
jwt.signature_ok AND tstzrange( | |
to_timestamp(try_cast_double(jwt.payload->>'nbf')), | |
to_timestamp(try_cast_double(jwt.payload->>'exp')) | |
) @> CURRENT_TIMESTAMP AS valid | |
FROM ( | |
SELECT | |
convert_from(url_decode(r[1]), 'utf8')::json AS header, | |
convert_from(url_decode(r[2]), 'utf8')::json AS payload, | |
r[3] = algorithm_sign(r[1] || '.' || r[2], secret, algorithm) AS signature_ok | |
FROM regexp_split_to_array(token, '\.') r | |
) jwt | |
$$ IMMUTABLE; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment