PostGraphile on Ubuntu 24.04 on Azure User Guide

Overview

PostGraphile builds a powerful, secure and performant GraphQL API from a PostgreSQL schema in seconds. It introspects your tables, views, foreign key relationships and functions and generates a complete GraphQL API, with no resolvers to write, plus a built in Ruru GraphiQL IDE for exploring and running queries in the browser. The cloudimg image runs PostGraphile v5 on Node.js under systemd, in a production posture, against a bundled local PostgreSQL 16, and locks it down for a marketplace appliance. PostGraphile has no login of its own, so it is bound to loopback 127.0.0.1:5000 and never exposed directly. An nginx reverse proxy on port 80 adds a per VM HTTP Basic Auth gate (user admin, unique password generated on first boot) in front of the whole surface (the Ruru IDE and the /graphql endpoint), plus an unauthenticated /healthz endpoint for load balancer probes. Crucially, the GraphQL API connects to PostgreSQL as a dedicated least privilege role that is granted only the demo schema and is never a superuser, so the API cannot reach beyond the data it is meant to serve. A seeded demo schema of related tables, a view and a function ships so the API and the IDE are populated on first boot. Backed by 24/7 cloudimg support.

What is included:

• PostGraphile 5.1.0 installed on Node.js 22 and running as the postgraphile systemd service
• The Ruru GraphiQL IDE and the /graphql endpoint on :80, fronted by nginx with PostGraphile bound to loopback only
• Per VM HTTP Basic Auth (user admin) protecting the whole surface, with a unique password generated on first boot
• A dedicated least privilege PostgreSQL role (postgraphile_public) for the GraphQL connection - never a superuser, granted only the demo schema
• A seeded demo schema (app_public) with two related tables, a view and a function so the API and the IDE are populated on first boot
• PostGraphile bound to 127.0.0.1:5000 - the application is never exposed to the network directly
• An unauthenticated /healthz endpoint for Azure Load Balancer health probes
• postgraphile.service, nginx.service and postgresql.service as systemd units, enabled and active
• The bcrypt Basic Auth hash stored on disk only - no plaintext password ships in the image
• 24/7 cloudimg support

Prerequisites

An active Azure subscription, an SSH key pair, and a VNet plus subnet in the target region. Standard_B2s (2 vCPU / 4 GiB RAM) is a comfortable starting point. NSG inbound: allow 22/tcp from your management network, 80/tcp for the web UI, and 443/tcp if you add TLS. PostGraphile serves plain HTTP on port 80; for production use, terminate TLS in front of it with your own domain and restrict access to trusted IP ranges (see Maintenance).

Step 1 - Deploy from the Azure Marketplace

Sign in to the Azure Portal, choose Create a resource, search the Marketplace for PostGraphile by cloudimg, and select Create. On Basics pick your subscription, resource group, region and size; under Administrator account choose SSH public key and paste your key; under Inbound port rules allow SSH (22) and HTTP (80). Then Review + create -> Create.

Step 2 - Deploy from the Azure CLI

az vm create \
  --resource-group <your-rg> \
  --name postgraphile \
  --image <marketplace-image-urn> \
  --size Standard_B2s \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_ed25519.pub \
  --vnet-name <your-vnet> --subnet <your-subnet> \
  --public-ip-sku Standard

az vm open-port --resource-group <your-rg> --name postgraphile --port 80 --priority 1010

Step 3 - Connect to your VM

ssh azureuser@<vm-public-ip>

Step 4 - Confirm the services are running

systemctl is-active postgraphile.service nginx.service postgresql.service
sudo ss -tlnp | grep -E '127.0.0.1:5000 |:80 '

All three report active. PostGraphile serves the GraphQL API and the Ruru IDE on the loopback address 127.0.0.1:5000; nginx fronts it on port 80 and adds the per VM HTTP Basic Auth gate. The application is never bound to a public interface, so nginx is the only way in.

Step 5 - Retrieve your web password

nginx protects the whole PostGraphile surface with HTTP Basic Auth. The username is admin and a unique password is generated on the first boot of your VM and written to a root only file:

sudo cat /root/postgraphile-credentials.txt

This file contains POSTGRAPHILE_USERNAME, POSTGRAPHILE_PASSWORD, the POSTGRAPHILE_URL to open in a browser, the GRAPHQL_ENDPOINT, and the details of the least privilege database role the API connects as. The web password is stored on disk only as a bcrypt hash in /etc/nginx/.postgraphile.htpasswd, so no plaintext password ships in the image. Store the password somewhere safe.

Step 6 - Confirm the health endpoint

nginx serves an unauthenticated health endpoint for load balancers and probes:

curl -s http://localhost/healthz

It returns ok. This endpoint never requires authentication, so it is safe for an Azure Load Balancer health probe.

Step 7 - Confirm authentication

Because a password is set on first boot, an unauthenticated request to the IDE returns HTTP 401, so nobody reaches PostGraphile without the password. The following reads the per VM password from the credentials file and proves the round trip - unauthenticated is rejected, a wrong password is rejected, and the correct password authenticates and reaches the Ruru IDE:

PW=$(sudo grep '^POSTGRAPHILE_PASSWORD=' /root/postgraphile-credentials.txt | cut -d= -f2-)
echo "unauth  : $(curl -s -o /dev/null -w '%{http_code}' http://127.0.0.1/)"
echo "wrongpw : $(curl -s -o /dev/null -w '%{http_code}' -u admin:wrong-pw http://127.0.0.1/)"
echo "authed  : $(curl -s -o /dev/null -w '%{http_code}' -u admin:$PW http://127.0.0.1/)"

It prints unauth : 401, wrongpw : 401 and authed : 200. The whole surface is only reachable with the per VM password because PostGraphile itself is bound to loopback and nginx is the only way in.

Step 8 - Query the GraphQL API from the command line

The GraphQL endpoint is at /graphql, behind the same Basic Auth gate. This reads the per VM password and posts a query that walks the seeded demo schema - every author and the number of posts each one has written through the generated postsByAuthorId relation:

PW=$(sudo grep '^POSTGRAPHILE_PASSWORD=' /root/postgraphile-credentials.txt | cut -d= -f2-)
curl -s -u admin:$PW -X POST http://127.0.0.1/graphql \
  -H 'Content-Type: application/json' \
  --data '{"query":"{ allAuthors { nodes { name postsByAuthorId { totalCount } } } }"}' | jq .

It returns the three seeded authors (Ada Lovelace, Alan Turing and Grace Hopper) with their post counts. PostGraphile generated this entire API - the allAuthors collection and the postsByAuthorId relation - automatically by introspecting the tables and their foreign key.

Step 9 - Sign in and run a query in the Ruru IDE

Browse to http://<vm-public-ip>/. Your browser prompts for a username and password: enter admin and the password from Step 5. The Ruru GraphiQL IDE opens. In the left hand editor, type a query against the demo schema and run it with the play button (or Ctrl+Enter). For example, list every author with their biography and the titles of the posts they have written:

{
  allAuthors {
    nodes {
      name
      bio
      postsByAuthorId {
        nodes { title published }
      }
    }
  }
}

The results appear in the right hand pane, populated with the seeded demo data. The nested postsByAuthorId field is the foreign key relation between the two demo tables, exposed automatically as GraphQL.

Step 10 - Query a PostgreSQL function through GraphQL

PostGraphile also exposes PostgreSQL functions as GraphQL fields. The demo schema includes a search_posts function, which appears in the API as searchPosts. Run it to search the demo posts by keyword:

{
  searchPosts(search: "algorithm") {
    nodes { title body published }
  }
}

The results pane shows the posts whose title or body matches the search term. This is a database function running behind a generated GraphQL query field - no application code required.

Step 11 - Explore the generated schema

Click Show Documentation Explorer (the book icon at the top left of the IDE) to open Ruru's schema browser. It lists the root Query and Mutation types and every type PostGraphile generated from your database - Author, Post, PublishedPost (from the demo view), the connection and edge types for pagination, and createAuthor / createPost mutations. This is the whole API, discoverable and self documenting, built entirely from the schema.

Step 12 - View the generated GraphQL SDL

Click Show SDL (the schema icon) to see the full generated schema in GraphQL Schema Definition Language. This is the complete, typed contract of your API - every type, field, argument and description - that PostGraphile produced by introspecting the PostgreSQL schema. You can download it or copy it to feed into client code generators and tooling.

Step 13 - Point PostGraphile at your own schema

The image ships a seeded demo schema so the IDE is not empty. When you are ready, point PostGraphile at your own database and schema. Edit the connection and the exposed schema list in /opt/postgraphile/graphile.config.js (the schemas array and the DATABASE_URL in /opt/postgraphile/postgraphile.env), then restart the service:

sudo -u postgres createdb my_app
sudoedit /opt/postgraphile/postgraphile.env    # set DATABASE_URL to your database
sudoedit /opt/postgraphile/graphile.config.js  # set schemas: ["my_schema"]
sudo systemctl restart postgraphile

For security, keep the GraphQL connection on a dedicated least privilege PostgreSQL role that is granted only the schema you want to expose - never a superuser - exactly as the shipped postgraphile_public role is configured. PostGraphile re-introspects on restart and regenerates the API to match your schema.

Maintenance

• Password: the web password is set on first boot and stored as a bcrypt entry in /etc/nginx/.postgraphile.htpasswd. To change it, run sudo htpasswd -B /etc/nginx/.postgraphile.htpasswd admin and then sudo systemctl reload nginx.
• Your schema: point PostGraphile at your own database by editing DATABASE_URL in /opt/postgraphile/postgraphile.env and the schemas array in /opt/postgraphile/graphile.config.js, then sudo systemctl restart postgraphile.
• Least privilege: the GraphQL API connects as the postgraphile_public role, which is NOSUPERUSER and granted only the demo schema. When you expose your own schema, grant a dedicated role only what the API needs (usually USAGE on the schema plus SELECT / INSERT / UPDATE / DELETE on the specific tables) and never connect as a superuser.
• Production posture: PostGraphile runs with the amber preset and without watch mode or the EXPLAIN developer flag. Keep it that way for production; the Ruru IDE remains available behind the Basic Auth gate.
• Restrict access: PostGraphile serves plain HTTP on port 80. For production, restrict access to trusted IP ranges in your Network Security Group, and front it with TLS (for example certbot with your own domain) terminating on :443.
• Loopback binding: PostGraphile is bound to 127.0.0.1:5000 in /opt/postgraphile/graphile.config.js, so nginx is the only path in. Keep it that way - do not change the listen host to a public interface.
• Upgrades: upgrade PostGraphile in its project directory with sudo -u postgraphile npm install --prefix /opt/postgraphile postgraphile@latest and sudo systemctl restart postgraphile.
• Security patches: unattended-upgrades remains enabled so the OS continues to receive security updates automatically.

Support

cloudimg provides 24/7 expert support for this image. Contact support@cloudimg.co.uk.