PostgREST Instant REST API with PostgreSQL 17 on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of PostgREST with PostgreSQL 17 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It pairs the PostgreSQL 17 relational database with PostgREST, a standalone server that reads your database schema and serves it as a RESTful JSON API. Tables and views become endpoints, columns become fields, and filtering, ordering, pagination and writes all come for free. There is no application tier to write and no ORM to configure.

Both components run on the same single VM. PostgREST listens on port 3000 and connects to the local PostgreSQL backend over the loopback interface on port 5432. PostgreSQL is also directly reachable on 5432 over TLS for administration, migrations and reporting.

PostgREST has no web interface. It is an API server: HTTP requests in, JSON out. Everything in this guide is done with curl, psql and standard HTTP clients, which is what a REST API is meant for. Point your own application, dashboard or API client at it.

Security by design, deny by default. An unauthenticated REST API sitting on top of a database is a serious exposure risk, so this image refuses first and grants only what you ask for. Authorisation is enforced by PostgreSQL's own role and grant system, not by an application layer that can be bypassed. There are three database roles:

• web_anon is the identity of every unauthenticated request. It can read exactly one object, the api.health view, which returns a literal status and a timestamp. It has no grant on any table, so an anonymous request for data is refused by the database itself.

• api_user is the identity named in a verified JSON Web Token. It has full read and write access to the api schema.

• authenticator is the only role PostgREST logs in as. It is NOINHERIT, so it holds no privileges of its own and can only switch to one of the two roles above.

On first boot each VM generates a unique password for the postgres superuser, a unique password for the authenticator role, a unique JSON Web Token signing secret, and a unique self signed TLS certificate, then writes them to the root only file /root/postgresql-postgrest-credentials.txt. Nothing is baked into the image: a token minted on any other machine cannot be valid on yours.

What is included:

• PostgreSQL 17 from the official PostgreSQL PGDG repository, running under systemd as postgresql.service

• PostgREST 14.16 from the official upstream release, verified against a pinned SHA 256 checksum at build time, running as postgrest.service

• A default database appdb with a worked example api schema containing a health view and an items table, so the API is live and queryable the moment it boots

• Per VM passwords, a per VM token signing secret and a per VM TLS certificate, all generated on first boot and written to a root only credentials file

• postgrest-token, a helper that mints signed tokens, and postgrest-selfcheck, which verifies the whole appliance end to end

• Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

• Active Azure subscription, an SSH public key, and a VNet and subnet in the target region

• Subscription to this listing on Azure Marketplace

• A Network Security Group allowing TCP 22 for administration and TCP 3000 for the REST API. Open TCP 5432 as well only if you need direct PostgreSQL access. In production, restrict both application ports to your client subnet.

Recommended virtual machine size: Standard_B2s with 2 vCPU and 4 GB RAM for development and light workloads. For higher request rates, choose a larger size such as Standard_D2s_v5 or above.

Deploy the virtual machine

Deploy from the Azure Portal by selecting the image from Azure Marketplace, choosing your VM size, and supplying your SSH public key for the azureuser account. Or deploy from the Azure CLI:

These commands run on your own workstation, not on the VM:

az vm create \
  --resource-group my-resource-group \
  --name my-postgrest-vm \
  --image <this-marketplace-image> \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

az vm open-port --resource-group my-resource-group --name my-postgrest-vm --port 3000

Connect over SSH once the VM is running:

ssh azureuser@<vm-ip>

Retrieve your per VM credentials

First boot generates every secret for this VM and writes them to a root only file. Read it first, because the token signing secret and the database password are shown in plain text only here.

sudo cat /root/postgresql-postgrest-credentials.txt

The file records the API address, the schema and role names, the token signing secret, the postgres superuser password and the authenticator password.

Verify the whole appliance end to end at any time with the bundled self check. It proves that anonymous callers are refused table data, that a token signed with this VM's secret is accepted, that a forged token is rejected, and that the database password works over TLS:

sudo postgrest-selfcheck

Confirm the services are healthy

Both services start automatically on boot. Check them, and confirm the two ports are listening:

systemctl is-active postgresql postgrest
postgrest --version
sudo -u postgres psql -tAc 'SELECT version();'

Expected output

active
active
PostgREST 14.16
PostgreSQL 17.10 (Ubuntu 17.10-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit

Call the API without a token

Anonymous callers are allowed to read the health endpoint and nothing else. This is useful for load balancer probes and uptime monitoring:

curl -s http://localhost:3000/health

Expected output

[{"status":"ok","checked_at":"2026-08-06T20:58:07.616093+00:00"}]

Now ask the same API for actual table data without a token. The request is refused, and it is refused by PostgreSQL rather than by the API layer, which is why it cannot be worked around:

curl -s -o /dev/null -w 'HTTP %{http_code}\n' http://localhost:3000/items
curl -s http://localhost:3000/items

Expected output

HTTP 401
{"code":"42501","details":null,"hint":null,"message":"permission denied for table items"}

Call the API with a token

Mint a signed token with the bundled helper. It reads the signing secret unique to this VM, so tokens are valid only here. The first argument is the database role the token grants, the second is the lifetime in seconds:

TOKEN=$(sudo postgrest-token api_user 3600)
curl -s -H "Authorization: Bearer $TOKEN" 'http://localhost:3000/items?select=id,name'

Expected output

[{"id":1,"name":"example-item-1"}, 
 {"id":2,"name":"example-item-2"}]

Present the token as a standard Authorization: Bearer header from any HTTP client. Any token library can generate these instead of the helper: sign an HS256 token whose payload carries a role claim and an exp expiry claim, using the postgrest.jwt_secret value from the credentials file.

Write through the API

The same token authorises writes. Create a row with POST, and ask PostgREST to return the created record with the Prefer: return=representation header:

TOKEN=$(sudo postgrest-token api_user 3600)
curl -s -X POST \
  -H "Authorization: Bearer $TOKEN" \
  -H 'Content-Type: application/json' \
  -H 'Prefer: return=representation' \
  -d '{"name":"orders","description":"a row created through the REST API"}' \
  'http://localhost:3000/items?select=id,name,description'

Expected output

[{"id":8,"name":"orders","description":"a row created through the REST API"}]

The id is assigned by the database identity sequence, so the exact number depends on how many rows have been inserted on your VM. The row itself is returned exactly as stored.

Update rows with PATCH and remove them with DELETE, both filtered the same way as a read:

TOKEN=$(sudo postgrest-token api_user 3600)
curl -s -o /dev/null -w 'PATCH HTTP %{http_code}\n' -X PATCH \
  -H "Authorization: Bearer $TOKEN" -H 'Content-Type: application/json' \
  -d '{"description":"updated through the API"}' \
  'http://localhost:3000/items?name=eq.orders'
curl -s -o /dev/null -w 'DELETE HTTP %{http_code}\n' -X DELETE \
  -H "Authorization: Bearer $TOKEN" 'http://localhost:3000/items?name=eq.orders'

Expected output

PATCH HTTP 204
DELETE HTTP 204

Filter, order and paginate

Query features are driven entirely by the URL, so no endpoint code is written for them. Operators are written as column=operator.value:

TOKEN=$(sudo postgrest-token api_user 3600)
curl -s -H "Authorization: Bearer $TOKEN" \
  'http://localhost:3000/items?name=like.example*&select=id,name&order=id.desc&limit=1'

Expected output

[{"id":2,"name":"example-item-2"}]

Common operators are eq, neq, gt, gte, lt, lte, like, ilike, in and is. Combine them with and and or, select nested related rows through foreign keys, and request a total count with the Prefer: count=exact header.

Add your own tables

The example api schema is a starting point, not a fixture. Create your own tables in the api schema and grant the roles you want to expose them to. Anything you do not grant stays invisible to the API:

sudo -u postgres psql -d appdb -c "CREATE TABLE api.customers (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE, signed_up timestamptz NOT NULL DEFAULT now());"
sudo -u postgres psql -d appdb -c "GRANT SELECT, INSERT, UPDATE, DELETE ON api.customers TO api_user;"
sudo -u postgres psql -d appdb -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA api TO api_user;"

PostgREST caches the schema, so tell it to reload after a schema change. The new table is then available at /customers immediately:

sudo -u postgres psql -d appdb -c "NOTIFY pgrst, 'reload schema';"

To expose a table to anonymous callers, grant SELECT on it to web_anon. Do that deliberately and only for data you are content to publish, because it becomes readable by anyone who can reach port 3000.

Connect directly to PostgreSQL

The database is also reachable directly for administration, migrations and reporting. On the VM itself, the unix socket needs no password:

sudo -u postgres psql -d appdb -c "SELECT count(*) FROM api.items;"

From a remote client, connect over TLS with the per VM password from the credentials file. Replace <vm-ip> with your VM's address and <POSTGRES_PASSWORD> with the value from the file:

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<vm-ip> port=5432 dbname=appdb user=postgres sslmode=require" -c "SELECT now();"

Remote connections are accepted only over TLS. A non TLS connection attempt is rejected by pg_hba.conf before any password is checked.

Security posture

• The API is deny by default. Unauthenticated callers can read only the api.health view. Every other endpoint requires a token signed with this VM's secret, and the refusal is enforced by PostgreSQL grants rather than by application logic.

• No credential is baked into the image. The postgres superuser and the authenticator role both ship with no password at all, and the image contains no PostgREST configuration file and no signing secret. All of them are generated on first boot, so no two VMs share a secret and a token from one is worthless on another.

• The API server cannot start before its secrets exist. postgrest.service is gated on a marker file that first boot creates only after writing the per VM password and signing secret, so there is no window in which the API is served with a placeholder or absent secret.

• Three guard layers on each exposed port. The Azure Network Security Group is the first. For port 3000 the second is PostgreSQL's grants and the third is token signature verification. For port 5432 the second is TLS only pg_hba.conf rules and the third is the per VM scram-sha-256 password.

• Restrict the Network Security Group. Allow port 3000 only from the clients that need the API, and leave port 5432 closed unless you specifically need remote database access.

• Rotate secrets when you need to. To rotate the token signing secret, write a new value into /etc/postgrest/jwt.secret, update the matching jwt-secret line in /etc/postgrest/postgrest.conf, restart with sudo systemctl restart postgrest, and update the credentials file. Every previously issued token stops working immediately. To rotate the superuser password, run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'" and update the credentials file.

• Give applications short lived tokens. Mint tokens with the smallest sensible lifetime for the job rather than long lived ones, and create additional narrow roles for callers that need less than full access.

Operations

Server components

Key paths

Troubleshooting

Every request returns HTTP 401. Confirm the token is being sent as Authorization: Bearer <token> and that it has not expired. Mint a fresh one with sudo postgrest-token api_user 3600. A token minted on a different VM will always be rejected, because the signing secret is unique per VM.

A new table returns HTTP 404. PostgREST caches the schema. Reload it with sudo -u postgres psql -d appdb -c "NOTIFY pgrst, 'reload schema';". If it still 404s, confirm the table is in the api schema, since only that schema is exposed.

A new table returns HTTP 401 or 403 with a valid token. The table exists but no grant was made. Run GRANT SELECT, INSERT, UPDATE, DELETE ON api.<table> TO api_user; and reload the schema cache.

The API is unreachable from outside the VM. Confirm the Network Security Group allows TCP 3000 from your client address, and that systemctl is-active postgrest reports active. Check ss -tln | grep 3000 on the VM to confirm it is listening.

postgrest.service will not start. It is gated on the first boot marker /var/lib/cloudimg/postgresql-postgrest-ready. If first boot did not complete, inspect sudo journalctl -u postgresql-postgrest-firstboot -n 100 --no-pager and sudo cat /var/log/cloudimg-firstboot.log.

Insert fails with a sequence permission error. Grant sequence usage: GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA api TO api_user;.

Support

cloudimg provides 24/7 support for this image. Contact support@cloudimg.co.uk with your Azure subscription ID and the VM name. For PostgREST itself, the upstream documentation at docs.postgrest.org covers the full query syntax, and PostgreSQL documentation covers roles and grants.