Databases Azure

PostgreSQL 18 with pg_graphql on Ubuntu 24.04 on Azure User Guide

| Product: PostgreSQL 18 with pg_graphql on Ubuntu 24.04 on Azure

Overview

This guide covers the deployment and use of PostgreSQL 18 with pg_graphql on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It is a PostgreSQL database that answers GraphQL queries itself.

pg_graphql is a GraphQL engine that runs inside the database server. It inspects the tables, columns, primary keys and foreign keys that the connecting account is allowed to see, builds a GraphQL schema from them, and answers queries through an ordinary SQL function call, graphql.resolve(). There is no second process, no extra service to supervise and no additional network port.

There is no HTTP GraphQL endpoint on this VM, and that is deliberate. This is the point most worth being clear about, because the category invites the opposite assumption. There is no /graphql URL to open in a browser. A client authenticates to PostgreSQL exactly as it always did, and calls graphql.resolve(). If you want an HTTP surface, you put your own application or API gateway in front of the database and have it make that one call — but the resolution, the planning and the authorisation all still happen in the database.

Why that placement matters. A GraphQL query becomes a single SQL statement planned by PostgreSQL, rather than a fan out of resolver round trips. And because the resolver runs as the connecting role, a GraphQL read is subject to precisely the same grants and the same row level security policies as the equivalent SELECT. There is no middle tier in which your access rules have to be re-implemented, and no way for a GraphQL query to read a row that the same account could not read with SQL.

Access control is row level security. With no gateway process in front of the database, PostgreSQL's own row level security is the access layer for GraphQL. So this image ships that posture rather than describing it. The worked example table has row level security enabled and forced — meaning it applies even to the table's owner — with a fail closed policy: a session that has not declared which tenant it is acting for sees nothing at all. Two accounts running the identical GraphQL document get different results, and you can read the policy that causes it.

Every VM gets its own database. The captured image contains the PostgreSQL binaries, pg_graphql and this appliance's configuration, but no database cluster at all. On first boot each VM runs its own initdb, so no two deployments share a cluster identity, a certificate or a password. There is no default, blank or shared credential at any point, not even for a moment.

What is included:

  • PostgreSQL 18 from the official PGDG repository, running under systemd as postgresql@18-main.service

  • pg_graphql 1.6.2, installed from the official upstream release artifact pinned by release tag and verified by SHA-256 checksum, and enabled with CREATE EXTENSION in the default graphqldb database

  • A worked example table, public.note, with row level security enabled and forced and a fail closed tenant policy, so the access model is live and demonstrable on first boot

  • An application role, graphqlapp, already granted the two privileges pg_graphql needs (USAGE on the graphql schema and EXECUTE on graphql.resolve)

  • A per VM database cluster, a per VM TLS certificate and two per VM passwords (the postgres superuser and the graphqlapp application role) generated on first boot into a root only credentials file

  • Loopback only networking by default: the only port reachable from the network is TCP 22

  • pg_hba.conf requiring TLS and scram-sha-256 on every TCP connection, so opening the database later is a single safe step

  • No compiler on the image. pg_graphql is written in Rust, but this image installs the official prebuilt release package rather than compiling it, so no toolchain is ever present

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

Prerequisites

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

  • Subscription to this listing on Azure Marketplace

  • A Network Security Group allowing TCP 22 from your administration network. Nothing else needs to be opened: PostgreSQL is bound to loopback in the shipped image.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for evaluation and light workloads. Because GraphQL resolution happens inside the server, a GraphQL query costs roughly what the equivalent SQL query costs, so size for your query load: Standard_D4s_v5 or larger is a sensible production starting point.

Deploy the virtual machine

Create the VM from the image, opening only SSH to your own network:

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

First boot creates the database cluster, mints a TLS certificate and generates the passwords, so give it a minute before connecting. You can watch it complete with systemctl status pg-graphql-firstboot.service.

Retrieve your per VM credentials

On the first boot the VM initialises its own cluster and generates its own passwords and TLS certificate. SSH in and read the root only credentials file. The command below prints everything except the two passwords, so it is safe to paste into a ticket:

sudo stat -c '%a %U %G %n' /root/pg-graphql-credentials.txt
sudo grep -E '^(postgres\.(host|port|role|database|sslmode)|graphqlapp\.role|pg_graphql\.)' /root/pg-graphql-credentials.txt

You will see the file is mode 600 owned by root, and the keys it reports look like this:

600 root root /root/pg-graphql-credentials.txt
postgres.host=127.0.0.1
postgres.port=5432
postgres.role=postgres
postgres.database=graphqldb
postgres.sslmode=require
graphqlapp.role=graphqlapp
pg_graphql.version=1.6.2
pg_graphql.schema=graphql
pg_graphql.entrypoint=graphql.resolve(text)
pg_graphql.example_table=public.note

Run sudo cat /root/pg-graphql-credentials.txt to see the whole file including the two passwords, together with a written summary of how to query and how the access model works. Nothing is baked into the image: every deployed VM generates its own.

Terminal showing the per VM credentials file at /root/pg-graphql-credentials.txt with mode 600 owned by root, listing the postgres host 127.0.0.1, port 5432, the graphqldb database name, sslmode require, the graphqlapp application role, the pg_graphql version 1.6.2, the graphql schema name and the graphql.resolve entrypoint, with both per VM passwords masked, followed by a note that there is no public GraphQL endpoint because pg_graphql runs inside PostgreSQL

Confirm the service is healthy and pg_graphql is enabled

Three things are worth checking: the database is running, the extension is enabled at the expected version, and the GraphQL entrypoint really exists as a callable function.

sudo systemctl is-active postgresql@18-main.service
sudo -u postgres psql -w -d graphqldb -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
sudo -u postgres psql -w -d graphqldb -tAc "SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'graphql' AND p.proname = 'resolve';"
sudo ss -tlnH | awk '{print $4}' | sort -u

You should see the unit active, the extension at version 1.6.2, exactly one graphql.resolve function, and a listening set in which PostgreSQL appears only on 127.0.0.1:5432:

active
  extname   | extversion
------------+------------
 pg_graphql | 1.6.2
 plpgsql    | 1.0
(2 rows)

1
0.0.0.0:22
127.0.0.1:5432
127.0.0.53%lo:53
127.0.0.54:53
[::]:22

Note what is not in that listing: there is no GraphQL port, because there is no GraphQL server. PostgreSQL binds 127.0.0.1 only. The addresses on port 53 are the local systemd resolver stub, also on loopback. The only entries reachable from outside the VM are the two on port 22.

Terminal showing the postgresql at 18-main service reported active, a pg_extension query listing pg_graphql version 1.6.2 alongside plpgsql, a count confirming one graphql.resolve entrypoint is live so GraphQL is answered inside PostgreSQL, the PostgreSQL 18.6 version string, and a socket listing showing PostgreSQL bound only to 127.0.0.1 port 5432 with SSH on port 22 as the only externally reachable listener and no GraphQL listener at all

Run your first GraphQL query

graphql.resolve() takes a GraphQL document as text and returns a JSON response. The image ships a small example table, public.note, so there is something real to query immediately. Wrapping the call in jsonb_pretty() just makes the response readable in a terminal:

sudo -u postgres psql -w -d graphqldb -tAXc "SELECT jsonb_pretty(graphql.resolve('{ noteCollection(first: 1) { edges { node { id tenant title } } } }'));"

{
    "data": {
        "noteCollection": {
            "edges": [
                {
                    "node": {
                        "id": "1",
                        "title": "Quarterly close",
                        "tenant": "acme"
                    }
                }
            ]
        }
    }
}

The shape follows the GraphQL cursor connections convention: a table note becomes a noteCollection field, whose edges each wrap a node carrying the columns you asked for. first: limits the page; pg_graphql also supports after:, filter: and orderBy: arguments on a collection.

Your application does not need psql for this. Any PostgreSQL driver can run the same statement:

SELECT graphql.resolve($1);

passing the GraphQL document as a bind parameter and reading back a single JSON value.

Ask for something that is not there

The schema is derived from your real tables, so a field that does not correspond to anything is rejected rather than quietly returning nothing. That distinction is worth seeing once, because it is how you will diagnose a typo:

sudo -u postgres psql -w -d graphqldb -tAXc "SELECT graphql.resolve('{ customerCollection { edges { node { id } } } }');" | jq -r '.errors[0].message'

Unknown field "customerCollection" on type Query

A GraphQL response always carries data or errors. Here data is null and the message names the field the schema does not contain.

Row level security is the access control

This is the part to read carefully, because it is what replaces the authorisation layer a separate GraphQL server would have given you.

pg_graphql resolves as the connecting role. So the way you decide what a GraphQL client may read is the way you decide what any database account may read: grants, and row level security policies. The shipped example makes that concrete. public.note has row level security enabled and forced — forced means the policy applies to the table's owner as well, so there is no account that quietly bypasses it except a superuser — and carries one policy:

sudo -u postgres psql -w -d graphqldb -c "SELECT polname, pg_get_expr(polqual, polrelid) AS using_expression FROM pg_policy WHERE polrelid = 'public.note'::regclass;"
sudo -u postgres psql -w -d graphqldb -tAc "SELECT relrowsecurity, relforcerowsecurity FROM pg_class WHERE oid = 'public.note'::regclass;"

        polname        |                   using_expression
-----------------------+------------------------------------------------------
 note_tenant_isolation | (tenant = current_setting('app.tenant'::text, true))
(1 row)

t|t

Now run the identical GraphQL document as the graphqlapp application role, under two different tenants, and then with no tenant declared at all:

for TENANT in acme globex; do
  sudo -u postgres psql -w -d graphqldb -qtAXc "SET ROLE graphqlapp; SET app.tenant = '$TENANT'; SELECT '$TENANT -> ' || coalesce(string_agg(e->'node'->>'title', ', '), '(nothing)') FROM jsonb_array_elements(graphql.resolve('{ noteCollection { edges { node { title } } } }')->'data'->'noteCollection'->'edges') e;"
done
sudo -u postgres psql -w -d graphqldb -qtAXc "SET ROLE graphqlapp; SELECT 'no tenant declared -> ' || coalesce(string_agg(e->'node'->>'title', ', '), '(nothing)') FROM jsonb_array_elements(graphql.resolve('{ noteCollection { edges { node { title } } } }')->'data'->'noteCollection'->'edges') e;"

acme -> Quarterly close, Renewal due
globex -> Security review, Onboarding
no tenant declared -> (nothing)

Three things to take from that output:

  • The query did not change. The same document returned a different set of rows for each tenant. The policy is doing the filtering, not the query, which is exactly the property you want: a client cannot ask its way past the rule.

  • The default is to see nothing. current_setting('app.tenant', true) returns NULL in a session that has not set it, and tenant = NULL is NULL, so an undeclared session matches no rows. The policy fails closed. A policy that failed open would be far worse than no policy at all, because it would look like it was working.

  • A superuser bypasses row level security. Connect as graphqlapp, not as postgres, for tenant scoped access. The superuser sees all four rows, which is correct PostgreSQL behaviour and is why administrative and application accounts should not be the same account.

Terminal showing the note_tenant_isolation policy with its using expression comparing the tenant column to current_setting of app.tenant, a confirmation that row security is enabled and forced even for the owner, and then the same GraphQL document run as the graphqlapp role returning Quarterly close and Renewal due for tenant acme, Security review and Onboarding for tenant globex, and nothing at all when no tenant is declared

To apply the same pattern to a table of your own, the recipe is three statements:

ALTER TABLE invoice ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice FORCE ROW LEVEL SECURITY;
CREATE POLICY invoice_tenant_isolation ON invoice
    USING      (tenant = current_setting('app.tenant', true))
    WITH CHECK (tenant = current_setting('app.tenant', true));

The WITH CHECK half matters as much as the USING half: it stops a session writing a row it would not then be allowed to read. Your application sets app.tenant once per connection or per transaction, from whatever it has already authenticated — a JWT claim, a session cookie, a service identity — and every GraphQL query on that connection is scoped automatically.

Add a table of your own and watch the schema pick it up

The GraphQL schema is reflected from the live catalogue, not from a configuration file. Create a table with a primary key and it is queryable immediately, with no restart and nothing to regenerate:

sudo -u postgres psql -w -d graphqldb -c "CREATE TABLE IF NOT EXISTS product (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sku text NOT NULL, price numeric(10,2) NOT NULL);"
sudo -u postgres psql -w -d graphqldb -c "INSERT INTO product (sku, price) VALUES ('CLOUD-001', 49.99);"
sudo -u postgres psql -w -d graphqldb -tAXc "SELECT graphql.resolve('{ productCollection { edges { node { id sku price } } } }');"
sudo -u postgres psql -w -d graphqldb -c "DROP TABLE product;"

CREATE TABLE
INSERT 0 1
{"data": {"productCollection": {"edges": [{"node": {"id": "1", "sku": "CLOUD-001", "price": "49.99"}}]}}}
DROP TABLE

The last line drops the table again so the example is repeatable; leave it out when you are building something real.

Two rules govern what appears in the schema. A table needs a primary key to be exposed as a collection — that is how pg_graphql derives the global nodeId — and a role only ever sees what it has been granted, so an account with no privileges on a table will not find that table in the schema at all. Foreign keys are reflected as nested fields, so a note with a foreign key to author gains an author field you can traverse in a single query.

Terminal showing a GraphQL document resolved by graphql.resolve returning a note row as JSON with its id, title and tenant, then a new table being created and queried immediately through its own generated collection field without any restart, and finally a query for a collection that does not exist returning the message Unknown field customerCollection on type Query

Connect over TLS with the per VM password

On the VM itself you can connect as the postgres superuser with no password, through the local unix socket:

sudo -u postgres psql -w -d graphqldb -c "SELECT version();"

To connect over TCP, use the per VM password from the credentials file. Every TCP connection, loopback included, is required to be TLS encrypted, so sslmode=require is not optional. Reading the password out of the file rather than typing it keeps it out of your shell history:

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

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

The pg_stat_ssl view confirms the connection is encrypted and reports the negotiated TLS version and cipher. Use the graphqlapp role for your application and keep the postgres superuser for administration — which, as the previous section showed, is not just tidiness: the superuser is the one account row level security does not constrain.

Open the database to your application network

The shipped image binds PostgreSQL to loopback only, so the database is not reachable from the network until you decide otherwise. Exposing it is a deliberate two step change, and pg_hba.conf already requires TLS and the per VM password, so no third step is needed to make it safe.

# 1. on the VM: bind to all interfaces and restart
sudo sed -i "s/^listen_addresses.*/listen_addresses = '*'/" /etc/postgresql/18/main/postgresql.conf
sudo systemctl restart postgresql@18-main.service

# 2. from your workstation: open 5432 to YOUR subnet only, never to the internet
az network nsg rule create --resource-group my-rg --nsg-name <your-nsg> \
  --name allow-postgres --priority 900 --access Allow --protocol Tcp \
  --destination-port-ranges 5432 --source-address-prefixes <your-app-subnet-cidr>

Then tighten the 0.0.0.0/0 line in /etc/postgresql/18/main/pg_hba.conf to the same CIDR. Remote clients must still present the per VM password over TLS; there is no plaintext TCP path on this image, on loopback or off it.

If what you actually want is a GraphQL API reachable over HTTPS, do not expose 5432 to do it. Put your own application in front of the database, let it authenticate the caller, have it SET app.tenant from whatever it authenticated, and then make the single graphql.resolve() call. The database keeps enforcing the policy either way, and 5432 stays private.

Security posture

  • No known credential, ever. The image ships no database cluster, so there is no role, no password and no certificate to discover. Both passwords are generated on the customer VM's first boot, set through psql standard input so they never appear on a command line or in the journal, and written to /root/pg-graphql-credentials.txt at mode 0600. Neither carries an expiry, so nothing locks you out on a date you did not choose.

  • Per VM cluster identity. Each VM runs its own initdb, so two VMs from this image have different cluster system identifiers, different TLS certificates, different machine IDs, different SSH host keys and different passwords.

  • Authorisation is in the database. GraphQL reads obey the same grants and row level security policies as SQL, because they are resolved as the connecting role. There is no second place for access rules to drift out of step.

  • The shipped example fails closed. public.note has row level security enabled and forced, and its policy returns nothing to a session that has not declared a tenant.

  • TLS on every TCP connection. pg_hba.conf carries only hostssl ... scram-sha-256 rules. Local administration stays password less through the unix socket.

  • Loopback only by default. The only port this image exposes to the network is TCP 22. PostgreSQL 5432 is bound to 127.0.0.1 until you change it, and there is no GraphQL listener to expose at all.

  • No compiler on the image. pg_graphql is a Rust extension, but this build installs the official prebuilt release package, pinned by release tag and verified by SHA-256 checksum, so no toolchain is ever installed.

  • SSH hardening. Root login is disabled outright (PermitRootLogin no, not prohibit-password, which still permits key based root login) and password authentication is off.

  • Kernel module baseline. The image ships a /etc/modprobe.d/dirtyfrag.conf baseline that disables the esp4, esp6, ipcomp, ipcomp4, ipcomp6 and rxrpc modules. If you intend to terminate IPsec on this VM, remove that file.

To rotate a password later, run sudo -u postgres psql -c "ALTER ROLE graphqlapp PASSWORD 'your-new-password'" and update the credentials file to match.

Operations

Manage the service through systemd. Note that postgresql.service on Debian and Ubuntu is a wrapper: the unit that actually runs your database is postgresql@18-main.service, and that is the one to check:

sudo systemctl show postgresql@18-main.service -p ActiveState,SubState,MainPID
sudo journalctl -u postgresql@18-main.service -p warning --no-pager -n 20

MainPID=1545
ActiveState=active
SubState=running
-- No entries --

The first boot log is at /var/log/cloudimg-firstboot.log, the data directory lives under /var/lib/postgresql/18/main, and the PostgreSQL server log is at /var/log/postgresql/postgresql-18-main.log, rotated by the postgresql-common logrotate configuration that ships with the distribution.

To upgrade pg_graphql later, install a newer upstream release package and run ALTER EXTENSION pg_graphql UPDATE; in each database that uses it. Check the upstream release notes first: the GraphQL schema is derived from your tables, so an extension upgrade can change generated field names.

Trademarks

PostgreSQL and the PostgreSQL elephant logo are trademarks of the PostgreSQL Community Association of Canada. pg_graphql is the name of the open source project at github.com/supabase/pg_graphql. GraphQL is a trademark of the GraphQL Foundation. Ubuntu is a registered trademark of Canonical Ltd. All other trademarks are the property of their respective owners, and are used here only to identify the software contained in this image. cloudimg is not affiliated with, endorsed by or sponsored by any of them.

Support

Every cloudimg image includes 24/7 support. If you have any questions about this PostgreSQL with pg_graphql image, contact us at support@cloudimg.co.uk.