PostgREST: How to use?

DISCLAIMER: Image is generated using `ChatGPT`.

Of all the databases I have worked with, PostgreSQL, is my favourite database. When I am doing unit test, I always go with SQLite as I find it easy to use in unit test. However if I am building a toy application, then I go with MySQL. It has enough features to build a complete application. Having said, for serious work, PostgreSQL, is my first choice. The best part about it is, open-source.

Recently I had a chance to look at Multi-Version Concurrency Control feature of PostgreSQL. I published 3-part series on the subject, please do checkout if you haven’t already.

What is PostgREST

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API.

I find it fun to playwith, honestly.

They have user friendly tutorials: Get it Running and The Golden Key

In this post, I am sharing my experience with PostgREST.

I am running Ubuntu 24.04.1 LTS on WSL2.

$ uname -a
Linux manwar 6.6.87.2-microsoft-standard-WSL2 #1 SMP PREEMPT_DYNAMIC Thu Jun  5 18:30:46 UTC 2025 x86_64 x86_64 x86_64 GNU/Linux

SETUP

Let’s pull the tarball and install the binary.

$ wget https://github.com/PostgREST/postgrest/releases/download/v14.13/postgrest-v14.13-linux-static-x86-64.tar.xz
$ tar xJf postgrest-v14.13-linux-static-x86-64.tar.xz
$ sudo mv postgresqt /usr/local/bin/
$ postgrest -v
PostgREST 14.13

We need PostgreSQL database for demo and Docker is the best choice.

$ docker run --name tutorial \
             -p 5432:5432 \
             -e POSTGRES_PASSWORD=notused \
             -d postgres
$ docker ps -f "name=tutorial" --format "{{.Status}}"
Up 50 seconds

Create Database

$ docker exec -it tutorial psql -U postgres
psql (18.4 (Debian 18.4-1.pgdg13+1))
Type "help" for help.

postgres=# create schema api;
CREATE SCHEMA
postgres=# create table api.todos (
  id int primary key generated by default as identity,
  done boolean not null default false,
  task text not null,
  due timestamptz
);
CREATE TABLE
postgres=# insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');
INSERT 0 2
postgres=#

Create Role/Permission

postgres=# create role web_anon nologin;
CREATE ROLE
postgres=# grant usage on schema api to web_anon;
GRANT
postgres=# grant select on api.todos to web_anon;
GRANT
postgres=# create role authenticator noinherit login password 'mysecretpassword';
CREATE ROLE
postgres=# grant web_anon to authenticator;
GRANT ROLE
postgres=# \q

Create Configuration

File: tutorial.conf

db-uri = "postgres://authenticator:mysecretpassword@localhost:5432/postgres"
db-schemas = "api"
db-anon-role = "web_anon"

Start Server

$ postgrest tutorial.conf
04/Jul/2026:20:42:10 +0100: Starting PostgREST 14.13...
...
...
...
04/Jul/2026:20:42:10 +0100: Config reloaded
04/Jul/2026:20:42:10 +0100: Schema cache queried in 107.2 milliseconds
04/Jul/2026:20:42:10 +0100: Schema cache loaded in 1.1 milliseconds

Test API Call

$ curl http://localhost:3000/todos
[{"id":1,"done":false,"task":"finish tutorial 0","due":null},
 {"id":2,"done":false,"task":"pat self on back","due":null}]

Create Trusted User

Earlier we created a web_anon role in the database with which to execute anonymous web requests.

Let’s make a role called todo_user for users who authenticate with the API. This role will have the authority to do anything to the todo list.

$ docker exec -it tutorial psql -U postgres
psql (18.4 (Debian 18.4-1.pgdg13+1))
Type "help" for help.

postgres=# create role todo_user nologin;
CREATE ROLE
postgres=# grant todo_user to authenticator;
GRANT ROLE
postgres=# grant usage on schema api to todo_user;
GRANT
postgres=# grant all on api.todos to todo_user;
GRANT
postgres=#

Add JWT Secret

We need to add secret, jwt-secret to the configuration file.

$ cat tutorial.con
db-uri = "postgres://authenticator:mysecretpassword@localhost:5432/postgres"
db-schemas = "api"
db-anon-role = "web_anon"
jwt-secret = "supersecretpassphraseatleast32charslong"

After updating the configuration, we need to restart the service: postgrest tutorial.conf

Create Token

$ export TOKEN=$(python3 -c "
import base64, hmac, hashlib, json, time

def b64url(b): return base64.urlsafe_b64encode(b).decode('utf-8').rstrip('=')

# 1. Calculate epoch time 5 minutes (300 seconds) from right now
five_minutes_from_now = int(time.time()) + 300

h = b64url(json.dumps({'alg':'HS256','typ':'JWT'}).encode())

# 2. Add the 'exp' claim to the payload dictionary
p = b64url(json.dumps({'role':'todo_user', 'exp': five_minutes_from_now}).encode())

sig = b64url(hmac.new(b'supersecretpassphraseatleast32charslong', f'{h}.{p}'.encode(), hashlib.sha256).digest())
print(f'{h}.{p}.{sig}')
")

POST call

$ curl http://localhost:3000/todos \
     -X POST \
     -H "Authorization: Bearer $TOKEN"   \
     -H "Content-Type: application/json" \
     -d '{"task": "learn how to auth"}'

Test the result:

$ curl http://localhost:3000/todos
[{"id":2,"done":false,"task":"pat self on back","due":null},
 {"id":1,"done":false,"task":"finish tutorial 0","due":null},
 {"id":3,"done":false,"task":"learn how to auth","due":null}]

FILTER

$ curl http://localhost:3000/todos?id=eq.1
[{"id":1,"done":false,"task":"finish tutorial 0","due":null}]

COLUMN SELECTION

$ curl http://localhost:3000/todos?select=task
[{"task":"finish tutorial 0"},
 {"task":"pat self on back"},
 {"task":"learn how to auth"}]

DELETE

$ curl http://localhost:3000/todos?id=eq.2 \
     -X DELETE \
     -H "Authorization: Bearer $TOKEN"

UPDATE

$ curl http://localhost:3000/todos?id=eq.3 \
     -X PATCH
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer $TOKEN" \
     -d '{"task": "UPDATE: learn how to auth"}'

Happy Hacking !!!