pgjwt: JSON Web Tokens

The pgjwt (Postgres JSON Web Token) extension allows you to create and parse JSON Web Tokens (JWTs) within a Postgres database. JWTs are commonly used for authentication and authorization in web applications and services.

Enable the extension

Dashboard

Go to the Database page in the Dashboard.
Click on Extensions in the sidebar.
Search for pgjwt and enable the extension.

SQL

-- Enable the "pgjwt" extension
create extension pgjwt schema extensions;

-- Disable the "pgjwt" extension
drop extension if exists pgjwt;

Even though the SQL code is create extension, this is the equivalent of enabling the extension. To disable an extension you can call drop extension.

It's good practice to create the extension within a separate schema (like extensions) to keep the public schema clean.

API

sign(payload json, secret text, algorithm text default 'HS256'): Signs a JWT containing payload with secret using algorithm.
verify(token text, secret text, algorithm text default 'HS256'): Decodes a JWT token that was signed with secret using algorithm.

Where:

payload is an encrypted JWT represented as a string.
secret is the private/secret passcode which is used to sign the JWT and verify its integrity.
algorithm is the method used to sign the JWT using the secret.
token is an encrypted JWT represented as a string.

Usage

Once the extension is installed, you can use its functions to create and parse JWTs. Here's an example of how you can use the sign function to create a JWT:

select
  extensions.sign(
    payload   := '{"sub":"1234567890","name":"John Doe","iat":1516239022}',
    secret    := 'secret',
    algorithm := 'HS256'
  );

The pgjwt_encode function returns a string that represents the JWT, which can then be safely transmitted between parties.

              sign
---------------------------------
 eyJhbGciOiJIUzI1NiIsInR5cCI6IkpX
 VCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiw
 ibmFtZSI6IkpvaG4gRG9lIiwiaWF0Ijo
 xNTE2MjM5MDIyfQ.XbPfbIHMI6arZ3Y9
 22BhjWgQzWXcXNrz0ogtVhfEd2o
(1 row)

To parse a JWT and extract its claims, you can use the verify function. Here's an example:

select
  extensions.verify(
    token := 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJuYW1lIjoiRm9vIn0.Q8hKjuadCEhnCPuqIj9bfLhTh_9QSxshTRsA5Aq4IuM',
    secret    := 'secret',
    algorithm := 'HS256'
  );

Which returns the decoded contents and some associated metadata.

           header            |    payload     | valid
-----------------------------+----------------+-------
 {"alg":"HS256","typ":"JWT"} | {"name":"Foo"} | t
(1 row)

Resources

Official pgjwt documentation