Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Database

pgjwt: JSON Web Tokens

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

Enable the extension

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

API

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:


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

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


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

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


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

Which returns the decoded contents and some associated metadata.


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

Resources