What

This project demonstrates using PostgREST with Auth0.

Why

Auth0 offers Regular Web Applications as a category of application, and has Quickstarts available for many back-end technologies. Unfortunately, PostgREST is not one of them. This project is an arena for exploring, developing, and demonstrating how Auth0 can be integrated with traditional web app that runs on a server, where that server is PostgREST.

How

This project uses a docker-compose.yaml file to launch two services:

postgres

PostgreSQL database server

postgrest

PostgREST API and web server

It sets up the database with setup.sql which does a few things:

1. Create the anonymous and authenticator database roles as per the PostgREST documentation on authentication.
2. Create the xml2 extensions to enable easy templating in the database (analogous to but still quite different from php or ASP.NET, instead using XSLT).
3. Create a resource view to make it easier to work with Large Objects in the database as resource files (like stylesheets).
4. Create a index function that can be called with GET in PostgREST to return a simple “index.html” generated by applying an XSLT stylesheet stored as a Large Object and accessed via the resource view.
5. Loads an XSLT stylesheet demo.xsl as a Large Object to be used to generate the “index.html” page described above.

The goal is for the “index.html” page to have a “Login” link to the Auth0 Universal Login and also to show by way of diagnostics the PostgREST Request Headers, Cookies, and JWT claims in order to exhibit the effect of logging in with Auth0 and obtaining a JWT.

Steps

Step 1: Check out the GitHub repository postgrest-auth0-demo.

git clone https://github.com/davidaventimiglia/postgrest-auth0-demo.git

Step 2: Set environment variables.

There are a variety of ways to do this. One way is just to create a setenv.bash script and source it.

cat <<EOF > setenv.bash
export LOGINURL=<Auth0 Universal Login URL>
export PGRST_SERVER_PORT=<an available web port e.g. 3001>
export PGPASSWORD=<a PostgreSQL password e.g. postgres>
export PGUSER=<a PostgreSQL user e.g. postgres>
export PGDATABASE=<a PostgreSQL database e.g. postgres>
export PGPORT=<an available database port e.g. 5433> 
export PGHOST=<database host e.g. localhost>
export PGRST_JWT_AUD=<audience "aud" which for Auth0 is the Application Client ID>
EOF
source setenv.bash

Step 3: Retrieve the Auth0 JWKS for your tenant.

For example:

wget -O ./initdb.d-postgres/rsa.jwk.pub https://dev-5nectqauhb53peug.us.auth0.com/.well-known/jwks.json

Step 3: Launch the services with Docker Compose.

docker-compose up

or

docker compose up

Step 4: Go to the index page.

Open a browser to http://${PGHOST}:${PGRST_SERVER_PORT}/rpc/index and try the “Login” link.