Masih Tavassoli wrote:
> But there doesn't seem to be a way doing it in postgres.
> Has anyone got any suggesions?
RFC 7519 says:
A JWT is represented as a sequence of URL-safe parts separated by
period ('.') characters. Each part contains a base64url-encoded
value. The number of parts in the JWT is dependent upon the
representation of the resulting JWS using the JWS Compact
Serialization or JWE using the JWE Compact Serialization.
base64url is similar to base64 except that the two characters
+ and / are replaced by - and _
Postgres provides decode(..., 'base64') but it's stricter than the
Oracle version showed in your sample code (which seems to ignore the
dot character that is illegal in base64 whereas Postgres would reject
it).
The JWT may be decoded with built-in Postgres functions by
splitting the dot-separated parts with regexp_split_to_table(),
converting them from base64url into binary, then into UTF-8,
and then the results could be cast into the json type if needed.
So the SQL code could be:
create function decode_base64url(text) returns bytea as $$
select decode(
rpad(translate($1, '-_', '+/') -- pad to the next multiple of 4 bytes
,4*((length($1)+3)/4)
,'=')
,'base64');
$$ language sql strict immutable;
with parts(x,n) as (
select * from regexp_split_to_table('<insert the JWT here>', '\.')
with ordinality
)
select n, convert_from(decode_base64url(x), 'utf-8')
from parts where n in (1,2);
"n" in the query is the part number, 1 for the header, 2 for the
payload, 3 for the signature which is ignored here.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite