Signing JWT with RSA-SHA256

[ivanzotov]

In continuation of this post: https://twitter.com/ivanzotov/status/1696793534257307714?s=20 (cc @kiwicopple)

Context: I am currently building a project (thriveondev.com, @thriveondev), in which the backend is completely written in plpgsql functions + @supabase. This is my first time attempting this approach, and so far, everything has been going well. I really like it and have been able to implement everything using PostgreSQL functions, except for one thing: signing a JWT token to enable push notifications using the Firebase API.

I have a pg_cron task that sends push notifications using the following query:

SELECT
      res.status,
      res.content::jsonb
    FROM http((
      'post',
      'https://fcm.googleapis.com/v1/projects/thriveondev/messages:send',
      array[http_header('Authorization', 'Bearer ' || access_token)],
      'application/json',
      (
        SELECT json_build_object(
          'message', json_build_object(
            'token', token,
            'notification', json_build_object(
              'title', title,
              'body', body
            ),
            'android', json_build_object(
              'notification', json_build_object(
                'sound', sound
              )
            ),
            'apns', json_build_object(
              'payload', json_build_object(
                'aps', json_build_object(
                  'sound', sound
                )
              )
            )
          )
        )::varchar
      )
    )) AS res

In this query, access_token is a JWT token signed with a Google private key. Unfortunately, I have not found a way to sign this token inside PostgreSQL, so I had to implement a separate API endpoint that signs it and returns it back to plpgsql:

import crypto from 'crypto';
import {NextApiRequest, NextApiResponse} from 'next';

export default function handler(req: NextApiRequest, res: NextApiResponse) {
  if (req.method !== 'POST') {
    res.status(405).end();
    return;
  }

  if (req.headers?.authorization !== `Bearer ${process.env.API_KEY}`) {
    res.status(401).end();
    return;
  }

  const signer = crypto.createSign('RSA-SHA256');
  signer.write(req.body);
  signer.end();
  const signature = signer.sign(process.env.GOOGLE_PRIVATE_KEY, 'base64');
  const encodedSignature = signature.replace(/\+/g, '-').replace(/\//g, '_').replace(/=/g, '');
  res.status(200).send(encodedSignature);
}

It would be great if I could sign it within the PostgreSQL function. Overall, it's not a significant issue, but if it could be easily implemented, it would be greatly appreciated. Thank you.

[michelp]

pgjwt only support HMAC-SHA symmetric key encryption because pgcrypto, the underlying core library, does not support RSA signing, only encryption. Unfortunately pgcrypto does not appear to be actively maintained and there seems to be no plan to update it to include signing, authenticated encryption, or any form of AEAD.

libsodium does provide public key signing (and many, many other features way beyond pgcrypto) but it does not support RSA, because the libsodium philosophy is to provide fast, efficient, modern ciphers, and RSA is none of those things. Instead libsodium provides Ed25519, a much more time and space efficient elliptic-curve based signature system.

So unfortunately until pgcrypto grows rsa signing support, or the jwt standard permits Ed25519 signing, there isn't any way to support what you want in-database.

[ivanzotov]

Thank you very much for the clarification!