PostGraphile 5.2.1

In the AWS Marketplace listing, choose Continue to Subscribe, then Continue to Configuration. Pick PostGraphile 5.2.1 on Ubuntu 24.04 as the software version and pick your Region. Then choose Launch through EC2. Choose m5.large (a t3.medium is enough to try it out), your key pair, your subnet and the security group from the prerequisites.

To launch with the AWS CLI, replace the values in angle brackets and run:

aws ec2 run-instances --region <region> --image-id <ami-id-from-the-listing> \
  --instance-type m5.large --key-name <your-key-pair> \
  --security-group-ids <your-security-group> --subnet-id <your-subnet> \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=postgraphile}]'

An Elastic IP keeps the address of the server, and therefore its name and certificate, the same across stops and starts.

For PostGraphile 5.2.1 on Ubuntu 24.04, the SSH user is ubuntu. Connect with:

ssh -i <your-key.pem> ubuntu@<public-ip>

First boot takes under a minute. It creates the PostgreSQL database and the demo schema, sets the name of the server, and generates the certificate and the admin password. Only then does it start PostGraphile and nginx. Check that everything is running:

systemctl is-active postgraphile-firstboot postgraphile-egress-guard postgresql@16-main postgraphile nginx \
  && sudo test -f /var/lib/sac/data/postgraphile-firstboot.done && echo FIRSTBOOT_DONE

active active active active active FIRSTBOOT_DONE

Only SSH and nginx listen on the network. PostGraphile (a Node.js process, which names its main thread MainThread) listens on the loopback address 127.0.0.1:5000 behind nginx. PostgreSQL has no network port at all:

sudo ss -lntp | awk 'NR>1 && $4 !~ /^127\.0\.0\.5[34]/ {print $4, $6}' | sed -E 's/,pid=[0-9]+,fd=[0-9]+//g' | sort -u | tee /dev/stderr \
  | awk '$1 !~ /^127\./ {sub(/.*:/, "", $1); print $1}' | sort -un | xargs | grep -x '22 80 443' >/dev/null && echo "network listeners: 22 80 443"

0.0.0.0:22 users:(("sshd"),("systemd")) 0.0.0.0:443 users:(("nginx"),("nginx"),("nginx")) 0.0.0.0:80 users:(("nginx"),("nginx"),("nginx")) 127.0.0.1:5000 users:(("MainThread")) [::]:22 users:(("sshd"),("systemd")) [::]:443 users:(("nginx"),("nginx"),("nginx")) [::]:80 users:(("nginx"),("nginx"),("nginx")) network listeners: 22 80 443

Run sudo cat /var/lib/sac/data/postgraphile-credentials.txt to see your addresses and the admin password. Only root can read the file, and it holds these settings, all generated for this instance:

sudo sed -n 's/^\(POSTGRAPHILE_[A-Z0-9_]*\)=.*/\1/p' /var/lib/sac/data/postgraphile-credentials.txt | tee /dev/stderr \
  | grep -x POSTGRAPHILE_PASSWORD >/dev/null && echo "credentials present"

POSTGRAPHILE_URL POSTGRAPHILE_GRAPHQL_URL POSTGRAPHILE_HOST POSTGRAPHILE_USERNAME POSTGRAPHILE_PASSWORD POSTGRAPHILE_TLS_CERT POSTGRAPHILE_TLS_SHA256_FINGERPRINT POSTGRAPHILE_DATABASE credentials present

POSTGRAPHILE_URL is https://<your instance's public DNS name>/, where the Ruru IDE opens, and POSTGRAPHILE_GRAPHQL_URL is the GraphQL endpoint that your applications call. To show the settings that are not secret on their own, run:

sudo grep -E '^POSTGRAPHILE_(URL|GRAPHQL_URL|USERNAME|TLS_SHA256_FINGERPRINT)=' /var/lib/sac/data/postgraphile-credentials.txt \
  | tee /dev/stderr | grep -x 'POSTGRAPHILE_USERNAME=admin' >/dev/null

POSTGRAPHILE_URL=https://ec2-203-0-113-10.compute-1.amazonaws.com/ POSTGRAPHILE_GRAPHQL_URL=https://ec2-203-0-113-10.compute-1.amazonaws.com/graphql POSTGRAPHILE_USERNAME=admin POSTGRAPHILE_TLS_SHA256_FINGERPRINT=3A:5F:...:C2

The certificate is self-signed and generated on your instance, so your browser warns about it until you trust it (Step 5). For a certificate that everyone already trusts, use your own domain (Step 12).

Copy the certificate to your computer and check that it is the one that first boot generated. The SHA-256 of its DER form must match POSTGRAPHILE_TLS_SHA256_FINGERPRINT, ignoring the colons.

scp -i <your-key.pem> ubuntu@<public-ip>:/etc/ssl/postgraphile/postgraphile.crt postgraphile.crt openssl x509 -in postgraphile.crt -outform der | shasum -a 256

Add postgraphile.crt to the trusted certificates of your operating system or browser. On macOS, open it in Keychain Access and set it to Always Trust. Then browse to POSTGRAPHILE_URL.

The browser asks for a user name and password. Enter admin and the POSTGRAPHILE_PASSWORD from Step 4. Ruru opens with the schema already loaded. Type a query on the left and press the pink run button (or Ctrl-Enter).

The Docs button at the top of the left-hand bar shows every query and mutation that PostGraphile generated from the schema.

Applications send GraphQL as a JSON POST to /graphql with the same user name and password. On the instance, the following request asks for the demo authors and their published posts. The command reads the password from the credentials file into a curl configuration, so the password never appears on a command line:

C=/var/lib/sac/data/postgraphile-credentials.txt H="$(sudo sed -n 's/^POSTGRAPHILE_HOST=//p' "$C")" curl -s --cacert /etc/ssl/postgraphile/postgraphile.crt \
  -K <(sudo sed -n 's/^POSTGRAPHILE_PASSWORD=\(.*\)/user = "admin:\1"/p' "$C") \
  -H 'Content-Type: application/json' \
  --data-binary '{"query":"{ allAuthors { nodes { name } } searchPosts(search: \"compiler\") { nodes { title authorByAuthorId { name } } } }"}' \
  "https://$H/graphql" | python3 -m json.tool | tee /dev/stderr | grep '"name": "Ada Lovelace"' >/dev/null

{ "data": { "allAuthors": { "nodes": [ { "name": "Ada Lovelace" }, { "name": "Alan Turing" }, { "name": "Grace Hopper" } ] }, "searchPosts": { "nodes": [ { "title": "The First Compiler", "authorByAuthorId": { "name": "Grace Hopper" } } ] } } }

searchPosts is the SQL function app_public.search_posts, which PostGraphile turned into a query field by itself. From your own computer, the same request is as follows. curl -u admin asks for the password instead of taking it on the command line:

curl --cacert postgraphile.crt -u admin -H 'Content-Type: application/json' \
  --data-binary '{"query":"{ allAuthors { nodes { name } } }"}' https://<public-dns-name>/graphql

Every table also gets create, update and delete mutations. The following command creates a post for Ada Lovelace (author rowId 1) and reads it back by its new rowId:

C=/var/lib/sac/data/postgraphile-credentials.txt H="$(sudo sed -n 's/^POSTGRAPHILE_HOST=//p' "$C")" gql() { curl -s --cacert /etc/ssl/postgraphile/postgraphile.crt \
  -K <(sudo sed -n 's/^POSTGRAPHILE_PASSWORD=\(.*\)/user = "admin:\1"/p' "$C") \
  -H 'Content-Type: application/json' --data-binary "$1" "https://$H/graphql"; } NEW="$(gql '{"query":"mutation { createPost(input: {post: {authorId: 1, title: \"Hello from the guide\", body: \"Written through PostGraphile\", published: true}}) { post { rowId title } } }"}')" ID="$(python3 -c 'import json, sys; print(json.loads(sys.argv[1])["data"]["createPost"]["post"]["rowId"])' "$NEW")" \
  && echo "created post $ID" gql "{\"query\":\"{ postByRowId(rowId: $ID) { title published authorByAuthorId { name } } }\"}" | tee /dev/stderr \
  | grep '"title":"Hello from the guide","published":true,"authorByAuthorId":{"name":"Ada Lovelace"}' >/dev/null && echo && echo "read back"

created post 6 {"data":{"postByRowId":{"title":"Hello from the guide","published":true,"authorByAuthorId":{"name":"Ada Lovelace"}}}} read back

You can run the same mutation in Ruru.

Without the password, or with a wrong one, nginx answers 401 before the request reaches PostGraphile, for the IDE and for the API alike:

C=/var/lib/sac/data/postgraphile-credentials.txt H="$(sudo sed -n 's/^POSTGRAPHILE_HOST=//p' "$C")" Q='{"query":"{ allAuthors { totalCount } }"}' { curl -s -o /dev/null -w '%{http_code}\n' --cacert /etc/ssl/postgraphile/postgraphile.crt -H 'Content-Type: application/json' --data-binary "$Q" "https://$H/graphql" curl -s -o /dev/null -w '%{http_code}\n' --cacert /etc/ssl/postgraphile/postgraphile.crt -K <(printf 'user = "admin:not-the-password"\n') \
  -H 'Content-Type: application/json' --data-binary "$Q" "https://$H/graphql" curl -s -o /dev/null -w '%{http_code}\n' --cacert /etc/ssl/postgraphile/postgraphile.crt "https://$H/" } | xargs | tee /dev/stderr | grep -x '401 401 401' >/dev/null && echo "no password, wrong password and the IDE without a password: refused"

401 401 401 no password, wrong password and the IDE without a password: refused

A web page on another site cannot use your saved password either. Without the browser asking first, an HTML form can send only application/x-www-form-urlencoded, multipart/form-data or text/plain, and nginx refuses all three with 415. A JSON request that carries the Origin of another site gets 403. nginx refuses both even with the right password, and the same request from your own origin works:

C=/var/lib/sac/data/postgraphile-credentials.txt H="$(sudo sed -n 's/^POSTGRAPHILE_HOST=//p' "$C")" auth() {
sudo sed -n 's/^POSTGRAPHILE_PASSWORD=\(.*\)/user = "admin:\1"/p' "$C"; } { curl -s -o /dev/null -w '%{http_code}\n' --cacert /etc/ssl/postgraphile/postgraphile.crt -K <(auth) -H 'Origin: https://attacker.example' \
  --data 'query=mutation%20%7B%20deletePostByRowId(input%3A%20%7BrowId%3A%201%7D)%20%7B%20deletedPostId%20%7D%20%7D' "https://$H/graphql" curl -s -o /dev/null -w '%{http_code}\n' --cacert /etc/ssl/postgraphile/postgraphile.crt -K <(auth) -H 'Origin: https://attacker.example' \
  -H 'Content-Type: application/json' --data-binary '{"query":"{ allAuthors { totalCount } }"}' "https://$H/graphql" curl -s -o /dev/null -w '%{http_code}\n' --cacert /etc/ssl/postgraphile/postgraphile.crt -K <(auth) -H "Origin: https://$H" \
  -H 'Content-Type: application/json' --data-binary '{"query":"{ allAuthors { totalCount } }"}' "https://$H/graphql" } | xargs | tee /dev/stderr | grep -x '415 403 200' >/dev/null && echo "cross-site form 415, cross-site JSON 403, same origin 200"

415 403 200 cross-site form 415, cross-site JSON 403, same origin 200

nginx also answers only for the names of the server, keeps a constant health check outside the password, and redirects port 80:

H="$(sudo sed -n 's/^POSTGRAPHILE_HOST=//p' /var/lib/sac/data/postgraphile-credentials.txt)" { curl -s -w ' %{http_code}\n' --cacert /etc/ssl/postgraphile/postgraphile.crt "https://$H/healthz" | xargs curl -s -o /dev/null -w '%{http_code}\n' -k -H 'Host: someone-else.example' https://127.0.0.1/ curl -s -o /dev/null -w '%{http_code}\n' "http://$H/" } | xargs | tee /dev/stderr | grep -x 'ok 200 000 301' >/dev/null && echo "front door OK"

ok 200 000 301 front door OK

• Rate limit. Every request that reaches the password check is limited to 5 a second per client address, with bursts of 100, and at most 32 connections per address; over the limit, nginx answers 429. The limit applies to every spelling of every path (//graphql, /GRAPHQL, /%67raphql, /graphql;x and so on), because it is set for the whole server and not per path. The password is stored with bcrypt at a low cost so that each check stays cheap. It is 32 random characters, which keeps it safe from guessing.
• X-Forwarded-For. nginx replaces any X-Forwarded-For header with the address it accepted the connection from, and blanks X-Real-IP, Forwarded and similar headers. A client therefore cannot change the address that the rate limit and the logs use. If you put a load balancer in front of the instance, every client shares the address of the load balancer for the rate limit.
• What reaches PostGraphile. nginx does not pass on the Authorization header or cookies, so PostGraphile never sees your password. nginx never forwards a WebSocket upgrade.
• Sizes and types. nginx limits request bodies to 128 KiB and PostGraphile limits them to 100 kB. Compressed request bodies (Content-Encoding) get 415, and TRACE gets 405. GraphQL operations are accepted only in a JSON POST body, never in a URL.
• Headers. Responses carry Strict-Transport-Security, X-Content-Type-Options: nosniff, X-Frame-Options: DENY, a Content-Security-Policy that lets Ruru load only its own scripts, Referrer-Policy: no-referrer and Cache-Control: no-store. nginx does not advertise its version.

Logging works as follows. The nginx access log (/var/log/nginx/access.log) records the client address, request path and status, without query strings. The nginx error log records only start-up problems and not refused requests, so a password in a rejected request never reaches the disk. PostGraphile logs to the journal (journalctl -u postgraphile). It masks every database error to an id in its reply and logs the detail in the journal.

To give admin a new random password and write it to the credentials file, run the following commands. The old password stops working at once.

sudo postgraphile-rotate-password
sudo grep '^POSTGRAPHILE_PASSWORD=' /var/lib/sac/data/postgraphile-credentials.txt

The new password goes to htpasswd on standard input, never on a command line.

PostgreSQL 16 runs on the same instance and listens on its local socket only. The demo database postgraphile_demo has two schemas. app_public is the schema that PostGraphile turns into the API, and app_private is the schema that the API cannot reach. PostGraphile connects as the role postgraphile_public. PostgreSQL lets only the Linux user postgraphile sign in as that role (peer authentication with a user map), so no database password exists:

sudo -u postgres psql -X -tA -c "SELECT 'roles with a password: ' || count(*) FROM pg_authid WHERE rolpassword IS NOT NULL" \
  -c "SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolinherit, rolbypassrls FROM pg_roles WHERE rolname = 'postgraphile_public'" \
  | tee /dev/stderr | grep -x 'roles with a password: 0' >/dev/null && echo "no database password"

roles with a password: 0 postgraphile_public|f|f|f|f|f no database password

postgraphile_public can read and write the demo tables in app_public and nothing else. As that role, all of the following are refused: reading the private schema, reading the password table, reading a server file and creating a table.

for q in "SELECT count(*) FROM app_private.secrets" "SELECT count(*) FROM pg_authid" "SELECT pg_read_file('/etc/hostname')" "CREATE TABLE app_public.x (id int)"; do OUT="$(sudo setpriv --reuid=postgraphile --regid=postgraphile --clear-groups -- \
  psql -X -tA -h /var/run/postgresql -U postgraphile_public -d postgraphile_demo -c "$q" 2>&1 || true)" case "$OUT" in *"permission denied"*) echo "refused: $q" ;; *) echo "NOT refused: $q" ;; esac done | tee /dev/stderr | grep -c '^refused' | grep -x 4 >/dev/null && echo "4 of 4 refused"

refused: SELECT count(*) FROM app_private.secrets refused: SELECT count(*) FROM pg_authid refused: SELECT pg_read_file('/etc/hostname') refused: CREATE TABLE app_public.x (id int) 4 of 4 refused

To serve your own schema, create it as the postgres superuser (sudo -u postgres psql -d postgraphile_demo, or a new database), grant postgraphile_public only what the API should do (GRANT USAGE ON SCHEMA ..., GRANT SELECT, INSERT, ... ON ...), add the schema to schemas in /etc/postgraphile/graphile.config.js (and the database to DATABASE_URL in /etc/postgraphile/postgraphile.env if you use a new one), then run sudo systemctl restart postgraphile. PostGraphile shows only what the role is allowed to use.

The PostGraphile settings are in /etc/postgraphile/graphile.config.js. Before every start, postgraphile-config-check loads that file the way PostGraphile does. It refuses to start the service if PostGraphile listens on anything but 127.0.0.1, turns on watch mode, EXPLAIN output, GraphQL over GET, websockets or CORS, accepts bodies other than JSON, lifts the size, depth or time limits, or connects to the database with a password or as a superuser. Run the check yourself:

sudo /usr/local/sbin/postgraphile-config-check | tee /dev/stderr | grep '^postgraphile-config-check: OK ' >/dev/null

postgraphile-config-check: OK (127.0.0.1:5000; watch, explain, GET operations, websockets off; JSON bodies only; 100000 bytes; planning depth 50; 1 database service(s), no password)

PostGraphile refuses a deeply nested query instead of planning it (the planning depth limit is 50). Queries have 5 seconds to plan and 30 seconds to run. An error from the database reaches the caller only as An error occurred (logged with hash: ..., id: ...), with the detail in journalctl -u postgraphile.

Point a DNS name at the instance (an Elastic IP keeps it stable), then make that name the address of the server. The following command generates a new certificate for the name, pins nginx to it and updates the addresses in the credentials file:

sudo postgraphile-set-site graphql.example.com

To use a certificate from a public CA instead, for example one issued by certbot with a DNS challenge, run postgraphile-set-site first. Then replace /etc/ssl/postgraphile/postgraphile.crt (full chain) and /etc/ssl/postgraphile/postgraphile.key (mode 0600), and run sudo systemctl reload nginx.

The image ships a self-test. It checks the configuration, checks that a request with no password and a request with a wrong password are both refused, checks that Ruru opens and the demo schema answers over HTTPS with the certificate of this instance, and checks that cross-site requests are refused:

sudo postgraphile-selftest | tail -n 1 | tee /dev/stderr | grep '^POSTGRAPHILE_SELFTEST_OK ' >/dev/null

POSTGRAPHILE_SELFTEST_OK no password and a wrong one refused (401), admin opens Ruru and queries the demo schema over https://ec2-203-0-113-10.compute-1.amazonaws.com/ with this instance's certificate; cross-site form, cross-origin JSON and text/plain refused; no GraphQL over GET

Everything that the API stores is in the PostgreSQL database postgraphile_demo (or your own). Back it up with:

sudo -u postgres pg_dump -Fc postgraphile_demo > postgraphile_demo-$(date +%F).dump

Restore it on a new instance of this image with sudo -u postgres pg_restore --clean --if-exists -d postgraphile_demo postgraphile_demo-<date>.dump, then sudo systemctl restart postgraphile.

The certificate generated at first boot is a server certificate only. It cannot sign other certificates. Check the certificate constraints:

openssl x509 -in /etc/ssl/postgraphile/postgraphile.crt -noout -ext basicConstraints,keyUsage,extendedKeyUsage | tee /dev/stderr | grep 'CA:FALSE' >/dev/null

X509v3 Basic Constraints: critical CA:FALSE X509v3 Key Usage: critical Digital Signature, Key Encipherment X509v3 Extended Key Usage: TLS Web Server Authentication

The build record names every component and its checksum. Print the first lines:

sed -n '1,6p' /usr/share/doc/sac-postgraphile/BUILD-INFO.txt | tee /dev/stderr | grep '^postgraphile 5\.2\.1 ' >/dev/null

postgraphile 5.2.1 (npm, github.com/graphile/crystal, 2026-11-02), MIT (postgraphile-LICENSE.md); grafserv 1.0.3, grafast 1.2.0, ruru 2.0.3; installed with 'npm ci --omit=dev --ignore-scripts' from the embedded package-lock.json (189 packages, sha512-integrity from registry.npmjs.org; lockfile sha256 d8fa0f0283052e9b64562ab960708ae491fa2f16c1436669d385cfb311ee2aef) node 24.22.2 LTS (nodejs.org node-v24.22.2-linux-x64.tar.xz, sha256 48cc0e21e048790e54c0cf5749c2725005313d652651c40b7a21feb9697aaf75 = nodejs.org SHASUMS256.txt), MIT (node-LICENSE); binary /opt/sac-node/bin/node sha256 904a6d3484241d466989f9df9ba3aab822b1496191bdb28230eb491a55c32a52; npm/npx/corepack removed advisories npm audit, OSV and trivy over the installed tree: 0 vulnerabilities at build time

PostGraphile and Node.js are MIT licensed, and their licences are in /usr/share/doc/sac-postgraphile/. PostGraphile runs as its own unprivileged user postgraphile with no login shell, no capabilities, a read-only view of the system and no access to the credentials file or the TLS key.

PostGraphile and PostgreSQL cannot open any network connection, because an nftables rule refuses every new connection from their users. PostGraphile, nginx and PostgreSQL cannot reach the EC2 instance metadata service. PostgreSQL 16 and nginx come from the Ubuntu main archive, and the image adds no third-party package repository.

Ubuntu security updates, including PostgreSQL and nginx, install automatically. To apply all updates now, run:

sudo apt-get update && sudo apt-get upgrade -y

New PostGraphile and Node.js releases arrive as new versions of the AWS Marketplace listing. To upgrade, launch the new version and restore your database backup into it (Step 14). Then copy over your changes to /etc/postgraphile/graphile.config.js, and run sudo postgraphile-set-site with your domain if you use one.