Обсуждение: JWT decoder

You could probably use Pl/Python. Python has base64 module and urllib module which can deal with all URL strings I am aware of.  Pl/Perl would probably do as well.

Hi experts,

I am trying to find a way to decode a URL request header and extract its JSON fields in postgreql .

I can do this in Oracle sql using 

select    (utl_raw.cast_to_varchar2 (utl_encode.base64_decode( utl_raw.cast_to_raw (regexp_replace ( ( 'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4YV9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6BgefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNjicyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkwPO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxBsGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ngzRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA'

), '[[:space:]]', ''))))) from dual

But there doesn't seem to be a way doing it in postgres.

Has anyone got any suggesions?

Thanks

Masih

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Hello,

On Mon, 2021-08-09 at 02:16 +0000, Masih Tavassoli wrote:
> Hi experts,
> 
> I am trying to find a way to decode a URL request header and extract
> its JSON fields in postgreql .
> 
> I can do this in Oracle sql using 
> 
> select    (utl_raw.cast_to_varchar2 (utl_encode.base64_decode(
> utl_raw.cast_to_raw (regexp_replace ( (
> 'eyJhbGciOiJSUzI1NiIsImtpZCI6InBhLWFwaS1zaWduIn0.eyJzY29wZSI6W10sInN1
> YiI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW5nLm5ldC5hdSIsImNsaWVudCI6InB4Y
> V9hY19zZXR0c3BsdXMiLCJ1c2VybmFtZSI6Imp1bGllQHNhcHBoaXJlY29udmV5YW5jaW
> 5nLm5ldC5hdSIsImV4cCI6MTU1NDA4NzU2N30.OZwVETCP+HUDsSkudCfa52eQT2Fyt6B
> gefFpYr5xpJROjZJeVnXtzsU6MS8aHauqyyOwQSz8iG6OC2YjYgrGiZ8YTwTh1uHptNji
> cyCFmCkh++d0oGdK78ZOKIAvuslW3GX4V13hPUJADzGLkyO8hy01YF9Id4+7GrvmnRRkw
> PO7C2eUvKTB8pxDAA94Jpw74s4BiNTmm1Uv+56AXqdI8m2KFkLNwGoUR3OSNvsifl5uxB
> sGGmP4wbgNe/Dy1q2UsL2eBAW/GuWryHGCRz1/PR2jIV1v21ClQ8YLiFAzM3YxHb8m2ng
> zRynKof+Nw3nZvuafRQ16MV5THz0ahGFYLA'
> ), '[[:space:]]', ''))))) from dual
> 
> 
> 
> But there doesn't seem to be a way doing it in postgres.
> 
> Has anyone got any suggesions?
> 
> Thanks
> Masih
> 


Have a look at the orafce extension on github.
It handles "select from dual", etc.

Cheers,
Rob

Hmmm, Pl/Python and Pl/Perl are languages usable from within Postgres. You can write Python functions in Postgres. I apologize for not making that clear.

There are lots of decoders but I need to do it within postgresql.

On Monday, August 9, 2021, 01:24:33 PM GMT+10, Mladen Gogala <gogala.mladen@gmail.com> wrote:

You could probably use Pl/Python. Python has base64 module and urllib module which can deal with all URL strings I am aware of.  Pl/Perl would probably do as well.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

    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