Re: encode/decode support for base64url

On Sun, Mar 9, 2025 at 12:28 AM Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
wrote:

>
>
> On 7 Mar 2025, at 4:40 PM, Aleksander Alekseev <aleksander(at)timescale(dot)com>
> wrote:
>
> Hi,
>
> Sometimes support for base64url from RFC 4648 would be useful.
> Does anyone else need a patch like this?
>
>
> While not a frequent ask, it has been mentioned in the past. I think it
> would
> make sense to add so please do submit a patch for it for consideration.
>
>
> IMO it would be nice to have.
>
> Would you like to submit such a patch or are you merely suggesting an
> idea for others to implement?
>
> --
> Best regards,
> Aleksander Alekseev
>
>
>
> Just to confirm:
>
> In a plan SQL flavor, we’re talking about something like this, correct?
>
> CREATE FUNCTION base64url_encode(input bytea) RETURNS text AS $$
> SELECT regexp_replace(
> replace(replace(encode(input, 'base64'), '+', '-'), '/', '_'),
> '=+$', '', 'g'
> );
> $$ LANGUAGE sql IMMUTABLE;
>
> CREATE FUNCTION base64url_decode(input text) RETURNS bytea AS $$
> SELECT decode(
> rpad(replace(replace(input, '-', '+'), '_', '/'), (length(input) + 3)
> & ~3, '='),
> 'base64'
> );
> $$ LANGUAGE sql IMMUTABLE;
>
> With minimal testing, this yields the same results with
> https://base64.guru/standards/base64url/encode
>
> select base64url_encode('post+gres')
> base64url_encode
> ------------------
> cG9zdCtncmVz
> (1 row)
>
>
Here's a C implementation for this, along with some tests and documentation.
Tests are copied from cpython's implementation of urlsafe_b64encode and
urlsafe_b64decode.

The signatures look like this:

SELECT base64url_encode('www.postgresql.org'::bytea) →
d3d3LnBvc3RncmVzcWwub3Jn
SELECT convert_from(base64url_decode('d3d3LnBvc3RncmVzcWwub3Jn'), 'UTF8') →
http://www.postgresql.org