Pg
Databases AWS

pg_graphql 1.6.2 on AWS User Guide

| Product: pg_graphql 1.6.2 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying pg_graphql 1.6.2 on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other PostgreSQL builds and extensions in the listing are described on the listing's guide hub.

pg_graphql is a PostgreSQL extension, created by Supabase, that adds GraphQL to PostgreSQL itself. It reads your tables, columns, primary keys and foreign keys and builds a GraphQL schema from them, then answers GraphQL queries and mutations through one SQL function, graphql.resolve(). Because the resolver runs inside the database as the role that is connected, every GraphQL request obeys the same grants and row-level security policies as plain SQL.

What this image serves, exactly. There is no web server, no /graphql URL and no HTTP endpoint on this image. The only network listeners are SSH on port 22 and PostgreSQL on 127.0.0.1:5432. Your application (or your own API gateway, such as PostgREST or a serverless function) connects to PostgreSQL and calls graphql.resolve(). If you want GraphQL over HTTP, put your own authenticated service in front of the database; nothing on the image accepts unauthenticated web requests.

The image installs PostgreSQL 18.6 from the official PostgreSQL (PGDG) apt repository and pg_graphql 1.6.2 from the upstream project's prebuilt package for PostgreSQL 18, checked against a pinned SHA-256 digest. No compiler or Rust toolchain is installed. The PostgreSQL packages are not held, so apt upgrade keeps delivering PGDG minor (security) releases, and unattended security upgrades stay on for the operating system.

What is on the image:

  • PostgreSQL 18.6 (postgresql@18-main.service)
  • A graphqldb database with the pg_graphql 1.6.2 extension and a small example table, note, with four rows belonging to two tenants (acme and globex), protected by row-level security
  • A non-superuser application role graphqlapp that can read and write the example table and call graphql.resolve(), and the postgres superuser; both passwords are generated on your instance's first boot
  • TLS with a certificate generated on your instance's first boot (TLS 1.2 minimum)
  • A credential guard that runs on every boot and refuses to serve if a published or example password ever authenticates

Default user: ubuntu · Architecture: x86_64 · Root volume: 20 GiB gp3

Security posture

  • No default password. The image ships with no password on any database role. First boot generates two different passwords, one for postgres and one for graphqlapp, and writes them to /root/pg-graphql-credentials.txt, readable by root only. Locally, postgres is also reachable as the postgres system user (peer authentication).
  • Nothing is served before first boot finishes. PostgreSQL will not start until first boot has created the certificate and the passwords. While it sets them, the server listens on its local socket only, with no TCP port.
  • Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate under /etc/postgresql/18/main/ and turns TLS on. No key ships in the image, so no two instances share one.
  • Localhost only. PostgreSQL listens on 127.0.0.1:5432, and the listing's security group opens SSH (22) only. Reach the database through an SSH tunnel, or open it to your VPC as described in Step 7.
  • TLS on every TCP connection. pg_hba.conf accepts TCP clients, loopback included, only over hostssl with scram-sha-256; there is no plain-text TCP rule and no trust rule.
  • Row-level security in the example. The note table is owned by postgres, has row-level security enabled and forced, and its policy fails closed: a session that has not declared a tenant sees no rows. graphqlapp cannot turn the policy off.
  • No metadata access for the database. A database user can make PostgreSQL open outbound connections (for example COPY ... FROM PROGRAM as a superuser), so every PostgreSQL unit and both first-boot services are blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
  • Fails closed. If first boot cannot finish, PostgreSQL stays stopped and first boot tries again on the next boot.
  • Host hardening. SSH refuses root logins and password logins. The kernel modules affected by the Dirty Frag vulnerabilities (CVE-2026-43284 and CVE-2026-43500: esp4, esp6, ipcomp, ipcomp4, ipcomp6, rxrpc) are blocked in /etc/modprobe.d/cloudimg-dirtyfrag.conf; this database image uses no IPsec or AFS. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large. Size up for large schemas or many concurrent GraphQL clients.

Connecting to your instance

Version Operating system SSH user
pg_graphql 1.6.2 on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose pg_graphql 1.6.2 on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 5432 to the internet.
  6. Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name pg-graphql \
  --description "pg_graphql" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pg-graphql}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. Show the connection details (the two password lines are left out here; read them with sudo cat /root/pg-graphql-credentials.txt and keep them in your secrets manager):

sudo grep -v -e '^postgres.password=' -e '^graphqlapp.password=' /root/pg-graphql-credentials.txt | head -16

# pg_graphql 1.6.2 on PostgreSQL 18.6 (cloudimg AWS Marketplace image)
# Generated on THIS instance's first boot by pg-graphql-firstboot.service; these passwords
# exist nowhere else and are shown in plain text only here. Rotate a password (never on a
# command line) with:  sudo -u postgres psql   then   \password graphqlapp
# and update this file to match.

postgres.host=127.0.0.1
postgres.port=5432
postgres.role=postgres
postgres.database=graphqldb
postgres.sslmode=require
postgres.private_ip=172.31.88.33

# The application role: least-privilege login for your application (DML on public.note,
# EXECUTE on graphql.resolve; not a superuser, so row-level security applies to it).
graphqlapp.role=graphqlapp

The commands below read the graphqlapp password from that file at run time, so it never appears on screen. In your own sessions you can instead put it in ~/.pgpass (mode 0600) as 127.0.0.1:5432:graphqldb:graphqlapp:<GRAPHQLAPP_PASSWORD>.

Step 3: Confirm PostgreSQL and pg_graphql are running

psql --version
systemctl is-active postgresql@18-main.service pg-graphql-firstboot.service pg-graphql-credguard.service
sudo ss -Hltn

psql (PostgreSQL) 18.6 (Ubuntu 18.6-1.pgdg24.04+2)
active
active
active
LISTEN 0      200        127.0.0.1:5432 0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22   0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      4096      127.0.0.54:53   0.0.0.0:*
LISTEN 0      4096            [::]:22      [::]:*

PostgreSQL listens on 127.0.0.1:5432 only and there is no HTTP listener; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub. Now connect as graphqlapp over TLS and list the extension:

export PGPASSWORD="$(sudo sed -n 's/^graphqlapp.password=//p' /root/pg-graphql-credentials.txt)"
psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" \
  -c "\dx pg_graphql" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

                          List of installed extensions
    Name    | Version | Default version | Schema  |         Description
------------+---------+-----------------+---------+-----------------------------
 pg_graphql | 1.6.2   | 1.6.2           | graphql | pg_graphql: GraphQL support
(1 row)

 ssl | tls_version
-----+-------------
 t   | TLSv1.3
(1 row)

Step 4: Run your first GraphQL query

pg_graphql exposes every table you can read as a collection named after the table, so the note table becomes noteCollection. Declare which tenant this session works for, then ask for the notes:

export PGPASSWORD="$(sudo sed -n 's/^graphqlapp.password=//p' /root/pg-graphql-credentials.txt)"
psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" -qtA \
  -c "SET app.tenant = 'acme';" \
  -c "SELECT jsonb_pretty(graphql.resolve('{ noteCollection { edges { node { id title body } } } }')::jsonb);"

{
    "data": {
        "noteCollection": {
            "edges": [
                {
                    "node": {
                        "id": "1",
                        "body": "Acme closed the Q3 books on the fourth.",
                        "title": "Quarterly close"
                    }
                },
                {
                    "node": {
                        "id": "2",
                        "body": "Acme renews in March.",
                        "title": "Renewal due"
                    }
                }
            ]
        }
    }
}

The answer is the standard GraphQL response shape: a data object, plus an errors list when something is wrong. Collections take filters, ordering and cursor pagination. Find one note by title, then page through acme's notes newest first, one at a time:

export PGPASSWORD="$(sudo sed -n 's/^graphqlapp.password=//p' /root/pg-graphql-credentials.txt)"
psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" -qtA \
  -c "SET app.tenant = 'acme';" \
  -c "SELECT graphql.resolve('{ noteCollection(filter: {title: {eq: \"Renewal due\"}}) { edges { node { id title body } } } }');" \
  -c "SELECT graphql.resolve('{ noteCollection(first: 1, orderBy: [{id: DescNullsLast}]) { pageInfo { hasNextPage endCursor } edges { node { id title } } } }');"

{"data": {"noteCollection": {"edges": [{"node": {"id": "2", "body": "Acme renews in March.", "title": "Renewal due"}}]}}}
{"data": {"noteCollection": {"edges": [{"node": {"id": "2", "title": "Renewal due"}}], "pageInfo": {"endCursor": "WzIsIDJd", "hasNextPage": true}}}}

Pass the endCursor value back as after: "..." to fetch the next page. In an application, send the GraphQL document and its variables as parameters, for example SELECT graphql.resolve($1, $2::jsonb) from your PostgreSQL driver, rather than building the SQL string by hand.

Step 5: See row-level security decide what GraphQL returns

The same query as the same role returns different rows for each tenant, and nothing at all when no tenant is declared, because the table's policy compares each row's tenant column with the session setting app.tenant:

export PGPASSWORD="$(sudo sed -n 's/^graphqlapp.password=//p' /root/pg-graphql-credentials.txt)"
for t in acme globex; do
  echo "tenant $t:"
  psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" -qtA \
    -c "SET app.tenant = '$t';" \
    -c "SELECT graphql.resolve('{ noteCollection { edges { node { title } } } }');"
done
echo "no tenant declared:"
psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" -qtA \
  -c "SELECT graphql.resolve('{ noteCollection { edges { node { title } } } }');"

tenant acme:
{"data": {"noteCollection": {"edges": [{"node": {"title": "Quarterly close"}}, {"node": {"title": "Renewal due"}}]}}}
tenant globex:
{"data": {"noteCollection": {"edges": [{"node": {"title": "Security review"}}, {"node": {"title": "Onboarding"}}]}}}
no tenant declared:
{"data": {"noteCollection": {"edges": []}}}

A superuser bypasses row-level security, so postgres sees every tenant's rows. Use postgres for administration only, and give applications a non-superuser role like graphqlapp:

sudo -u postgres psql -d graphqldb -qtA \
  -c "SELECT graphql.resolve('{ noteCollection { edges { node { tenant title } } } }');"

{"data": {"noteCollection": {"edges": [{"node": {"title": "Quarterly close", "tenant": "acme"}}, {"node": {"title": "Renewal due", "tenant": "acme"}}, {"node": {"title": "Security review", "tenant": "globex"}}, {"node": {"title": "Onboarding", "tenant": "globex"}}]}}}

Row-level security protects tenants from each other only if your application sets app.tenant from the user it has authenticated, never from a value the end user can choose. Set it at the start of each transaction (for example SET LOCAL app.tenant = ... inside the transaction that calls graphql.resolve()).

Step 6: Write data with GraphQL mutations

pg_graphql generates insertInto...Collection, update...Collection and deleteFrom...Collection mutations for every table the role can write. As tenant acme, insert a note, then try to insert a note for globex (the policy's WITH CHECK clause refuses it), then delete the first note again:

export PGPASSWORD="$(sudo sed -n 's/^graphqlapp.password=//p' /root/pg-graphql-credentials.txt)"
psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" -qtA \
  -c "SET app.tenant = 'acme';" \
  -c "SELECT graphql.resolve('mutation { insertIntonoteCollection(objects: [{tenant: \"acme\", title: \"Kick-off\", body: \"Project kick-off on Monday.\"}]) { affectedCount records { id title } } }');" \
  -c "SELECT graphql.resolve('mutation { insertIntonoteCollection(objects: [{tenant: \"globex\", title: \"Planted\", body: \"Written as acme.\"}]) { affectedCount } }');" \
  -c "SELECT graphql.resolve('mutation { deleteFromnoteCollection(filter: {title: {eq: \"Kick-off\"}}, atMost: 1) { affectedCount } }');"

{"data": {"insertIntonoteCollection": {"records": [{"id": "5", "title": "Kick-off"}], "affectedCount": 1}}}
{"data": null, "errors": [{"message": "new row violates row-level security policy for table \"note\""}]}
{"data": {"deleteFromnoteCollection": {"affectedCount": 1}}}

Expose your own table

Create tables as postgres, enable row-level security on them, and grant the application role what it needs. pg_graphql picks up a new table straight away; a table needs a primary key to appear as a collection. This example adds a project table whose tenant column defaults to the session's tenant, inserts a row through GraphQL, reads it back, and then drops the table again:

sudo -u postgres psql -d graphqldb -v ON_ERROR_STOP=1 -q <<'SQL'
CREATE TABLE public.project (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant text NOT NULL DEFAULT current_setting('app.tenant', true),
    name   text NOT NULL
);
ALTER TABLE public.project ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.project FORCE ROW LEVEL SECURITY;
CREATE POLICY project_tenant ON public.project
    USING (tenant = current_setting('app.tenant', true))
    WITH CHECK (tenant = current_setting('app.tenant', true));
GRANT SELECT, INSERT, UPDATE, DELETE ON public.project TO graphqlapp;
SQL
export PGPASSWORD="$(sudo sed -n 's/^graphqlapp.password=//p' /root/pg-graphql-credentials.txt)"
psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" -qtA \
  -c "SET app.tenant = 'acme';" \
  -c "SELECT graphql.resolve('mutation { insertIntoprojectCollection(objects: [{name: \"Website relaunch\"}]) { records { id tenant name } } }');" \
  -c "SELECT graphql.resolve('{ projectCollection { edges { node { id tenant name } } } }');"
sudo -u postgres psql -d graphqldb -qc "DROP TABLE public.project;"

{"data": {"insertIntoprojectCollection": {"records": [{"id": "1", "name": "Website relaunch", "tenant": "acme"}]}}}
{"data": {"projectCollection": {"edges": [{"node": {"id": "1", "name": "Website relaunch", "tenant": "acme"}}]}}}

Foreign keys between tables become nested fields you can query in one request. pg_graphql 1.6 turns GraphQL introspection (__schema, __type) off by default; if a tool such as GraphiQL needs it, enable it for a schema with a comment, as described in the pg_graphql documentation, and keep in mind that it describes every table the connected role can see.

Step 7: Connect from your application

PostgreSQL accepts connections on localhost only. Two ways to reach it:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 5432:127.0.0.1:5432 ubuntu@<public-ip>, then connect your client to 127.0.0.1:5432 as graphqlapp with sslmode=require.
  • Open it to your VPC. In /etc/postgresql/18/main/conf.d/10-cloudimg-pg-graphql.conf change listen_addresses = '127.0.0.1' to listen_addresses = '*', restart PostgreSQL, then add an inbound rule for 5432 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0). Clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/18/main/cloudimg-server.crt) and the role's password:
sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-cloudimg-pg-graphql.conf
sudo systemctl restart postgresql@18-main.service

A wrong password is refused by scram-sha-256, and a connection without TLS is refused by pg_hba.conf, even on loopback:

OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"
OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 dbname=graphqldb user=graphqlapp sslmode=disable" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "graphqlapp"
FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "graphqlapp", database "graphqldb", no encryption

Step 8: Check the security controls

The PostgreSQL unit carries the metadata block, graphqlapp is not a superuser, the example table has forced row-level security and is owned by postgres, and PostgreSQL listens on loopback with this instance's own certificate:

systemctl show -p IPAddressDeny postgresql@18-main.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper FROM pg_roles WHERE rolname IN ('graphqlapp', 'postgres') ORDER BY 1;"
sudo -u postgres psql -d graphqldb -tAc "SELECT relname, relrowsecurity, relforcerowsecurity, pg_get_userbyid(relowner) FROM pg_class WHERE relname = 'note';"
sudo -u postgres psql -tAc "SHOW listen_addresses;" -tAc "SHOW ssl_cert_file;"

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
graphqlapp|f
postgres|t
note|t|t|postgres
127.0.0.1
/etc/postgresql/18/main/cloudimg-server.crt

Maintenance

  • Change a password. Run sudo -u postgres psql and type \password graphqlapp (or \password postgres) at the prompt; it asks for the new password twice and never puts it on a command line. Then update /root/pg-graphql-credentials.txt (the credential guard reads it on every boot) and your secrets manager.
  • Configuration. cloudimg's settings are in /etc/postgresql/18/main/conf.d/10-cloudimg-pg-graphql.conf (listener, password hashing, connection logging) and 20-cloudimg-tls.conf (this instance's certificate). Apply changes with sudo systemctl restart postgresql@18-main.service.
  • Updates. sudo apt update && sudo apt upgrade installs PostgreSQL 18 minor releases from PGDG and Ubuntu security updates. To move to a newer pg_graphql release, install the upstream package for PostgreSQL 18 and run ALTER EXTENSION pg_graphql UPDATE; in each database.
  • The example table. public.note and its policy are defined in /usr/share/doc/pg-graphql-cloudimg/demo-schema.sql. Drop it with sudo -u postgres psql -d graphqldb -c "DROP TABLE public.note;" when you no longer need it.
  • Backups. Take EBS snapshots or regular pg_dump backups of graphqldb.
  • Logs. /var/log/postgresql/postgresql-18-main.log (connections are logged) and journalctl -u postgresql@18-main.service; first boot and the credential guard log to journalctl -u pg-graphql-firstboot.service and journalctl -u pg-graphql-credguard.service.
  • Licences. pg_graphql is licensed under the Apache License 2.0 (Copyright 2021 Supabase); the full text is in /usr/share/doc/pg-graphql-cloudimg/LICENSE, and ATTRIBUTION.txt beside it names the exact upstream release. PostgreSQL is under The PostgreSQL License.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (pg_graphql 1.6.2 on Ubuntu 24.04) and what you were doing.

PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. Ubuntu is a trademark of Canonical Ltd. GraphQL is a trademark of the GraphQL Foundation. Supabase is a trademark of Supabase, Inc.; pg_graphql is the name of its open-source project. cloudimg is not affiliated with or endorsed by these organisations or projects.