PostgreSQL 18 OAuth Playground

PostgreSQL 18 includes support for OAuth and i want to play around with it. Unfortunately there's not much documentation out there yet but with the help of ChatGPT I've got something working. Here is quick 🧠-dump on how to compile everything.

I've set up a fresh Ubuntu 22.04 machine for the entire experiment.

Note: I do not provide support for this.

Install Dependencies

sudo apt install build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt1-dev libssl-dev libpam0g-dev libldap2-dev libedit-dev pkg-config

Download and Extract Source Code

wget https://ftp.postgresql.org/pub/source/v18beta1/postgresql-18beta1.tar.gz

tar -xzf postgresql-18beta1.tar.gz

Configure Postgres Build

Note: It's important to add --with-libcurl because this also enables OAuth support.

cd postgresql-18beta1

./configure --prefix=/usr/local/pgsql18 --with-openssl --with-libcurl

Build PostgreSQL and install it

make -j$(nproc)

sudo make install

Setup PostgreSQL User and Data Directory

sudo adduser postgres
sudo mkdir /usr/local/pgsql18/data
sudo chown postgres /usr/local/pgsql18/data

Initialize the Database Server

sudo -u postgres /usr/local/pgsql18/bin/initdb -D /usr/local/pgsql18/data

Compile and Install the custom OAuth Validator

Note: I just copied the one from the PostgreSQL repo's test suite. This module permits everyone.

cd ./my_oauth_validator

make

sudo make install

Configure PostgreSQL to use OAuth

sudo nano /usr/local/pgsql18/data/postgresql.conf

Configure this:

oauth_validator_libraries = 'jwt_validator'

sudo nano /usr/local/pgsql18/data/pg_hba.conf

Disable the other local login methods and add:

host    all             all             0.0.0.0/0               oauth scope=openid issuer=https://login.your-server.com validator=jwt_validator

Start PostgreSQL Server

sudo -u postgres /usr/local/pgsql18/bin/pg_ctl -D /usr/local/pgsql18/data start

Try to connect using psql

/usr/local/pgsql18/bin/psql -Atx "host=localhost port=5432 dbname=postgres user=postgres oauth_issuer=https://login.your-server.com oauth_client_id=postgres"

This will give you a device login prompt.

Stop PostgreSQL Server

sudo -u postgres /usr/local/pgsql18/bin/pg_ctl -D /usr/local/pgsql18/data stop