encode/decode support for base64url

On 4 Mar 2025, at 09:54, Przemysław Sztoch <przemyslaw@sztoch.pl> wrote:

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.

--
Daniel Gustafsson

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

On 7 Mar 2025, at 4:40 PM, Aleksander Alekseev <aleksander@timescale.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)

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

On 7 Mar 2025, at 4:40 PM, Aleksander Alekseev <aleksander@timescale.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

On 10 Mar 2025, at 12:28, Florents Tselai <florents.tselai@gmail.com> wrote:

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.

+ <function>base64url_encode</function> ( <parameter>input</parameter> <type>bytea</type> )

Shouldn't this be modelled around how base64 works with the encode() and
decode() functions, ie encode('123\001', 'base64')?

https://www.postgresql.org/docs/devel/functions-binarystring.html

--
Daniel Gustafsson

On Mon, Mar 10, 2025, 14:32 Daniel Gustafsson <daniel@yesql.se> wrote:

On 10 Mar 2025, at 12:28, Florents Tselai <florents.tselai@gmail.com>

wrote:

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.

+ <function>base64url_encode</function> (
<parameter>input</parameter> <type>bytea</type> )

Shouldn't this be modelled around how base64 works with the encode() and
decode() functions, ie encode('123\001', 'base64')?

https://www.postgresql.org/docs/devel/functions-binarystring.html

--
Daniel Gustafsson

Oh well - you're probably right.
I guess I was blinded by my convenience.
Adding a 'base64url' option there is more appropriate.

Oh well - you're probably right.
I guess I was blinded by my convenience.
Adding a 'base64url' option there is more appropriate.

I agree with it too. It is neater to add "base64url" as a new option for
encode() and decode() SQL functions in encode.c.

In addition, you may also want to add the C versions of base64rul encode
and decode functions to "src/common/base64.c" as new API calls so that
the frontend, backend applications and extensions can also have access
to these base64url conversions.

Cary Huang
-------------
HighGo Software Inc. (Canada)
cary.huang@highgo.ca
www.highgo.ca

On 07.03.2025 15:40, Aleksander Alekseev 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?

1. It is my current workaround:

SELECT convert_from(decode(rpad(translate(jwt_data, E'-_\n', '+/'),
(ceil(length(translate(jwt_data, E'-_\n', '+/')) / 4::float) *
4)::integer, '='::text), 'base64'), 'UTF-8')::jsonb AS jwt_json

But it's not very elegant. I won't propose my own patch, but if someone
does it, I'll be very grateful for it. :-)

2. My colleagues also have a proposal to add hex_space, dec and dec_space.

hex_space and dec_space for obvious readability in some conditions.

dec and dec_space are also sometimes much more convenient for debugging
and interpreting binary data by humans. 3. In addition to base64,
sometimes base32 would be useful (both from rfc4648), which doesn't have
such problems:

The resulting character set is all one case, which can often be
beneficial when using a case-insensitive filesystem, DNS names, spoken
language, or human memory. The result can be used as a file name because
it cannot possibly contain the '/' symbol, which is the Unix path
separator.

--
Przemysław Sztoch | Mobile +48 509 99 00 66

On Tue, Mar 11, 2025 at 12:51 AM Cary Huang <cary.huang@highgo.ca> wrote:

Oh well - you're probably right.
I guess I was blinded by my convenience.
Adding a 'base64url' option there is more appropriate.

I agree with it too. It is neater to add "base64url" as a new option for
encode() and decode() SQL functions in encode.c.

Attaching a v2 with that.

In addition, you may also want to add the C versions of base64rul encode
and decode functions to "src/common/base64.c" as new API calls so that
the frontend, backend applications and extensions can also have access
to these base64url conversions.

We could expose this in base64.c - it'll need some more checking
A few more test cases, especially around padding, are necessary.
I'll come back to this.

On Tue, Mar 11, 2025 at 10:08 AM Florents Tselai <florents.tselai@gmail.com>
wrote:

On Tue, Mar 11, 2025 at 12:51 AM Cary Huang <cary.huang@highgo.ca> wrote:

Oh well - you're probably right.
I guess I was blinded by my convenience.
Adding a 'base64url' option there is more appropriate.

I agree with it too. It is neater to add "base64url" as a new option for
encode() and decode() SQL functions in encode.c.

Attaching a v2 with that.

In addition, you may also want to add the C versions of base64rul encode
and decode functions to "src/common/base64.c" as new API calls so that
the frontend, backend applications and extensions can also have access
to these base64url conversions.

We could expose this in base64.c - it'll need some more checking
A few more test cases, especially around padding, are necessary.
I'll come back to this.

Here's a v3 with some (hopefully) better test cases.

Hi Florents,

Here's a v3 with some (hopefully) better test cases.

Thanks for the new version of the patch.

```
+    encoded_len = pg_base64_encode(src, len, dst);
+
+    /* Convert Base64 to Base64URL */
+    for (uint64 i = 0; i < encoded_len; i++) {
+        if (dst[i] == '+')
+            dst[i] = '-';
+        else if (dst[i] == '/')
+            dst[i] = '_';
+    }
```

Although it is a possible implementation, wouldn't it be better to
parametrize pg_base64_encode instead of traversing the string twice?
Same for pg_base64_decode. You can refactor pg_base64_encode and make
it a wrapper for pg_base64_encode_impl if needed.

```
+-- Flaghsip Test case against base64.
+-- Notice the = padding removed at the end and special chars.
+SELECT encode('\x69b73eff', 'base64');  -- Expected: abc+/w==
+  encode
+----------
+ abc+/w==
+(1 row)
+
+SELECT encode('\x69b73eff', 'base64url');  -- Expected: abc-_w
+ encode
+--------
+ abc-_w
+(1 row)
```

I get the idea, but calling base64 is redundant IMO. It only takes
several CPU cycles during every test run without much value. I suggest
removing it and testing corner cases for base64url instead, which is
missing at the moment. Particularly there should be tests for
encoding/decoding strings of 0/1/2/3/4 characters and making sure that
decode(encode(x)) = x, always. On top of that you should cover with
tests the cases of invalid output for decode().

--
Best regards,
Aleksander Alekseev

Hi,

In the strings.sql file there is such code
SELECT encode('\x69b73eff', 'base64'); -- Expected: abc+/w==

In the strings.out file
+SELECT encode('\x69b73eff', 'base64'); -- Expected: abc+/w==
+ encode
+----------
+ abc+/w==
+(1 row)
+

maybe you should remove the additional description of the expected value in this way?

strings.sql
SELECT encode('\x69b73eff', 'base64') = "abc+/w=="

strings.out
SELECT encode('\x69b73eff', 'base64') = "abc+/w=="
----------
t
(1 row)

Regards,
Pavel

Thanks for the review Aleksander;

On Mon, Mar 31, 2025 at 5:37 PM Aleksander Alekseev <
aleksander@timescale.com> wrote:

Hi Florents,

Here's a v3 with some (hopefully) better test cases.

Thanks for the new version of the patch.

```
+    encoded_len = pg_base64_encode(src, len, dst);
+
+    /* Convert Base64 to Base64URL */
+    for (uint64 i = 0; i < encoded_len; i++) {
+        if (dst[i] == '+')
+            dst[i] = '-';
+        else if (dst[i] == '/')
+            dst[i] = '_';
+    }
```

Although it is a possible implementation, wouldn't it be better to
parametrize pg_base64_encode instead of traversing the string twice?
Same for pg_base64_decode. You can refactor pg_base64_encode and make
it a wrapper for pg_base64_encode_impl if needed.

```
+-- Flaghsip Test case against base64.
+-- Notice the = padding removed at the end and special chars.
+SELECT encode('\x69b73eff', 'base64');  -- Expected: abc+/w==
+  encode
+----------
+ abc+/w==
+(1 row)
+
+SELECT encode('\x69b73eff', 'base64url');  -- Expected: abc-_w
+ encode
+--------
+ abc-_w
+(1 row)
```

I get the idea, but calling base64 is redundant IMO. It only takes
several CPU cycles during every test run without much value. I suggest
removing it and testing corner cases for base64url instead, which is
missing at the moment. Particularly there should be tests for
encoding/decoding strings of 0/1/2/3/4 characters and making sure that
decode(encode(x)) = x, always. On top of that you should cover with
tests the cases of invalid output for decode().

--
Best regards,
Aleksander Alekseev

here's a v4 patch set

- Extracted pg_base64_{en,de}_internal with an additional bool url param,
to be used by other functions
- Added a few more test cases

Cary mentioned above

In addition, you may also want to add the C versions of base64rul encode

and decode functions to "src/common/base64.c" as new API calls

Haven't done that, but I could;
Although I think it'd probably be best to do it in a separate patch.

GH PR View https://github.com/Florents-Tselai/postgres/pull/23

Hi Florents,

Thanks for the update!

here's a v4 patch set

- Extracted pg_base64_{en,de}_internal with an additional bool url param, to be used by other functions
- Added a few more test cases

Cary mentioned above

In addition, you may also want to add the C versions of base64rul encode

and decode functions to "src/common/base64.c" as new API calls

Haven't done that, but I could;
Although I think it'd probably be best to do it in a separate patch.

I reviewed and tested v4. To me it looks as good as it will get.
Personally I would change a few minor things here and there and
probably merge all three patches into a single commit. This however is
up to the committer to decide.

Changing the CF entry status to "RfC".

Thanks for the review Aleksander,

On 9 Jul 2025, at 10:45 PM, Aleksander Alekseev <aleksander@tigerdata.com> wrote:

Hi Florents,

Thanks for the update!

here's a v4 patch set

- Extracted pg_base64_{en,de}_internal with an additional bool url param, to be used by other functions
- Added a few more test cases

Cary mentioned above

In addition, you may also want to add the C versions of base64rul encode

and decode functions to "src/common/base64.c" as new API calls

Haven't done that, but I could;
Although I think it'd probably be best to do it in a separate patch.

I reviewed and tested v4. To me it looks as good as it will get.
Personally I would change a few minor things here and there and
probably merge all three patches into a single commit. This however is
up to the committer to decide.

Attaching a single-file patch

Hi Florents,

On Jul 9, 2025, at 23:25, Florents Tselai <florents.tselai@gmail.com> wrote:

I reviewed and tested v4. To me it looks as good as it will get.
Personally I would change a few minor things here and there and
probably merge all three patches into a single commit. This however is
up to the committer to decide.

Attaching a single-file patch

Somehow missed this thread previously. Had a quick look and had the same question Aleksander asked up-thread:

Although it is a possible implementation, wouldn't it be better to
parametrize pg_base64_encode instead of traversing the string twice?
Same for pg_base64_decode. You can refactor pg_base64_encode and make
it a wrapper for pg_base64_encode_impl if needed.

It looks as though there could be complements to _base64 and b64urllookup:

```patch
diff --git a/src/backend/utils/adt/encode.c b/src/backend/utils/adt/encode.c
@@ -273,6 +273,9 @@ hex_dec_len(const char *src, size_t srclen)
static const char _base64[] =
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";

+static const char _base64url[] =
+"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-_";
+
 static const int8 b64lookup[128] = {
 	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
 	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
@@ -284,6 +287,18 @@ static const int8 b64lookup[128] = {
 	41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, -1, -1, -1, -1, -1,
 };

+static const int8 b64urllookup[128] = {
+	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
+	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
+	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, 62, -1, -1,
+	52, 53, 54, 55, 56, 57, 58, 59, 60, 61, -1, -1, -1, -1, -1, -1,
+	-1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
+	15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, -1, -1, -1, -1, 62,
+	-1, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
+	41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, -1, -1, -1, -1, -1,
+};
+
+
 static uint64
 pg_base64_encode(const char *src, size_t len, char *dst)
 {
```

And then add the implementation functions that take argument with the proper lookup tables.

Best,

David

On 10 Jul 2025, at 10:07 PM, David E. Wheeler <david@justatheory.com> wrote:

Hi Florents,

On Jul 9, 2025, at 23:25, Florents Tselai <florents.tselai@gmail.com> wrote:

I reviewed and tested v4. To me it looks as good as it will get.
Personally I would change a few minor things here and there and
probably merge all three patches into a single commit. This however is
up to the committer to decide.

Attaching a single-file patch

Somehow missed this thread previously. Had a quick look and had the same question Aleksander asked up-thread:

Although it is a possible implementation, wouldn't it be better to
parametrize pg_base64_encode instead of traversing the string twice?
Same for pg_base64_decode. You can refactor pg_base64_encode and make
it a wrapper for pg_base64_encode_impl if needed.

It looks as though there could be complements to _base64 and b64urllookup:

```patch
diff --git a/src/backend/utils/adt/encode.c b/src/backend/utils/adt/encode.c
@@ -273,6 +273,9 @@ hex_dec_len(const char *src, size_t srclen)
static const char _base64[] =
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";

+static const char _base64url[] =
+"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-_";
+
static const int8 b64lookup[128] = {
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
@@ -284,6 +287,18 @@ static const int8 b64lookup[128] = {
41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, -1, -1, -1, -1, -1,
};

+static const int8 b64urllookup[128] = {
+	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
+	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1,
+	-1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, -1, 62, -1, -1,
+	52, 53, 54, 55, 56, 57, 58, 59, 60, 61, -1, -1, -1, -1, -1, -1,
+	-1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,
+	15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, -1, -1, -1, -1, 62,
+	-1, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
+	41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, -1, -1, -1, -1, -1,
+};
+
+
static uint64
pg_base64_encode(const char *src, size_t len, char *dst)
{
```

And then add the implementation functions that take argument with the proper lookup tables.

Best,

David

Why isn’t this sufficient?

static uint64
pg_base64_encode_internal(const char *src, size_t len, char *dst, bool url)
{
const char *alphabet = url ? _base64url : _base64;
There’s already a a bool url param and the alphabet is toggled based on that

On Jul 10, 2025, at 16:38, Florents Tselai <florents.tselai@gmail.com> wrote:

Why isn’t this sufficient?

static uint64
pg_base64_encode_internal(const char *src, size_t len, char *dst, bool url)
{
const char *alphabet = url ? _base64url : _base64;

Ah, it is. I hadn’t got that far. I was tripped up to see this in your patch:

```patch
+static uint64
+pg_base64url_encode(const char *src, size_t len, char *dst)
+{
+	uint64 encoded_len;
+	if (len == 0)
+		return 0;
+
+	encoded_len = pg_base64_encode(src, len, dst);
+
+	/* Convert Base64 to Base64URL */
+	for (uint64 i = 0; i < encoded_len; i++) {
+		if (dst[i] == '+')
+			dst[i] = '-';
+		else if (dst[i] == '/')
+			dst[i] = '_';
+	}
+
+	/* Trim '=' padding */
+	while (encoded_len > 0 && dst[encoded_len - 1] == '=')
+		encoded_len--;
+
+	return encoded_len;
+}
```

I didn’t realize it was a set of patches for stuff you did and then later undid. Could you flatten the patch into just what’s changed at the end?

Best,

David

On Thu, Jul 10, 2025 at 11:55 PM David E. Wheeler <david@justatheory.com>
wrote:

On Jul 10, 2025, at 16:38, Florents Tselai <florents.tselai@gmail.com>
wrote:

Why isn’t this sufficient?

static uint64
pg_base64_encode_internal(const char *src, size_t len, char *dst, bool

url)

{
const char *alphabet = url ? _base64url : _base64;

Ah, it is. I hadn’t got that far. I was tripped up to see this in your
patch:

```patch
+static uint64
+pg_base64url_encode(const char *src, size_t len, char *dst)
+{
+       uint64 encoded_len;
+       if (len == 0)
+               return 0;
+
+       encoded_len = pg_base64_encode(src, len, dst);
+
+       /* Convert Base64 to Base64URL */
+       for (uint64 i = 0; i < encoded_len; i++) {
+               if (dst[i] == '+')
+                       dst[i] = '-';
+               else if (dst[i] == '/')
+                       dst[i] = '_';
+       }
+
+       /* Trim '=' padding */
+       while (encoded_len > 0 && dst[encoded_len - 1] == '=')
+               encoded_len--;
+
+       return encoded_len;
+}
```

I didn’t realize it was a set of patches for stuff you did and then later
undid. Could you flatten the patch into just what’s changed at the end?

Attached