PostgreSQL PostgREST

Prerequisites

• An active Azure subscription, an SSH public key, and a VNet with a subnet in the target region
• A subscription to this listing on Azure Marketplace
• A Network Security Group that allows 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. Select the image from Azure Marketplace, choose your VM size, and supply 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:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:postgresql-postgrest-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-resource-group \
  --name my-postgrest-vm \
  --image sac1647283583153:postgresql-postgrest-ubuntu-24-04:default:latest \
  --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 when 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. The token signing secret and the database password appear in plain text only in this file.

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 four things: anonymous callers are refused table data, a token signed with this VM's secret is accepted, a forged token is rejected, and the database password works over TLS.

sudo postgrest-selfcheck

Confirm the services are healthy

Both services start automatically on boot. Check them, and confirm that 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 table data without a token. The request is refused. PostgreSQL refuses it, not the API layer, so 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 that is 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 tokens 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. Add the Prefer: return=representation header to have PostgREST return the created record:

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 database identity sequence assigns the id, so the exact number depends on how many rows have been inserted on your VM. The row is returned exactly as stored.

Update rows with PATCH and remove them with DELETE. Both are 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

The URL drives all query features, so you write no endpoint code for them. Write operators 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. Request a total count with the Prefer: count=exact header.

Add your own tables

The example api schema is a starting point. 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 bigserial 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:

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 this only for data you are content to publish, because anyone who can reach port 3000 can then read it.

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. pg_hba.conf rejects a non-TLS connection attempt 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. PostgreSQL grants enforce the refusal, not 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 VM 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 it writes the per-VM password and signing secret. The API is never served with a missing 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 the 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. Leave port 5432 closed unless you 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. Create additional narrow roles for callers that need less than full access.

Operations

Common tasks and the commands that run them:

• Service status: systemctl status postgrest
• Restart the API: sudo systemctl restart postgrest
• Restart the database: sudo systemctl restart postgresql
• API logs: sudo journalctl -u postgrest -n 100 --no-pager
• Database logs: sudo tail -n 100 /var/log/postgresql/postgresql-17-main.log
• Reload the schema cache: sudo -u postgres psql -d appdb -c "NOTIFY pgrst, 'reload schema';"
• Mint an API token: sudo postgrest-token api_user 3600
• Verify the appliance: sudo postgrest-selfcheck
• Read per-VM credentials: sudo cat /root/postgresql-postgrest-credentials.txt

Server components:

• PostgREST 14.16: serves the REST API generated from the database schema, on port 3000
• PostgreSQL 17: relational database and the authorisation engine, on port 5432
• Ubuntu Server 24.04 LTS: base operating system

Key paths:

• /etc/postgrest/postgrest.conf: PostgREST configuration, written on first boot
• /etc/postgrest/jwt.secret: the token signing secret for this VM, readable only by root
• /root/postgresql-postgrest-credentials.txt: all per-VM secrets, readable only by root
• /etc/postgresql/17/main/: PostgreSQL configuration including pg_hba.conf
• /var/lib/postgresql/17/main/: database data directory
• /usr/local/bin/postgrest-token: token minting helper
• /usr/local/sbin/postgrest-selfcheck: end-to-end appliance verification

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 is always rejected, because the signing secret is unique per VM.

A new table returns HTTP 404. PostgREST caches the schema, so reload it with sudo -u postgres psql -d appdb -c "NOTIFY pgrst, 'reload schema';". If the request still returns 404, confirm that the table is in the api schema, because 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 that the Network Security Group allows TCP 3000 from your client address, and that systemctl is-active postgrest reports active. Run ss -tln | grep 3000 on the VM to confirm that it is listening.

postgrest.service will not start. It is gated on the first-boot marker /var/lib/sac/data/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/sac/sac-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

SecureAnyCloud provides 24/7 support for this image. Contact sales@secureanycloud.com with your Azure subscription ID and the VM name.

For PostgREST itself, the upstream documentation at docs.postgrest.org covers the full query syntax. The PostgreSQL documentation covers roles and grants.

Deploy on Azure

Launch PostgREST Instant REST API with PostgreSQL 17 on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com