PostgreSQL 18 pg GraphQL

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 from your administration network. Nothing else needs to be opened, because 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.

GraphQL resolution happens inside the server, so a GraphQL query costs roughly what the equivalent SQL query costs. 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 and open only SSH to your own network:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:pg-graphql-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-rg \
  --name pggraphql-1 \
  --image sac1647283583153:pg-graphql-ubuntu-24-04:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

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

Retrieve your per-VM credentials

On 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

The file is mode 600 and owned by root. 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. It includes the two passwords and 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 credentials.

Confirm the service is healthy and pg_graphql is enabled

Check three things: the database is running, the extension is enabled at the expected version, and the GraphQL entrypoint 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 PostgreSQL listening 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.

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 that you have something to query immediately. Wrapping the call in jsonb_pretty() 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. The first: argument 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);

Pass the GraphQL document as a bind parameter and read 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 instead of quietly returning nothing. This 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

Row level security replaces the authorisation layer that a separate GraphQL server would otherwise give you.

pg_graphql resolves as the connecting role. You decide what a GraphQL client may read the same way you decide what any database account may read: with 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 no account bypasses it except a superuser. The table 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. Run it 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 does the filtering, not the query, so 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 worse than no policy, 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. This is correct PostgreSQL behaviour, and it is why administrative and application accounts should not be the same account.

To apply the same pattern to a table of your own, run 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 from writing a row that 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, such as a JWT claim, a session cookie or a service identity. Every GraphQL query on that connection is then 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. There is 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 that 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. This is how pg_graphql derives the global nodeId.
• A role only sees what it has been granted. 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. A note with a foreign key to author gains an author field that you can traverse in a single 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, must be TLS-encrypted, so sslmode=require is mandatory. Read the password out of the file instead of typing it, to keep 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 that 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. As the previous section showed, this matters: the superuser is the one account that 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. 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 you want a GraphQL API reachable over HTTPS, do not expose 5432 to do it. Put your own application in front of the database and let it authenticate the caller. Have it SET app.tenant from whatever it authenticated, and then make the single graphql.resolve() call. The database enforces 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. They are set through psql standard input, so they never appear on a command line or in the journal, and they are 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, TLS certificates, machine IDs, SSH host keys and 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 passwordless 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. On Debian and Ubuntu, postgresql.service is a wrapper. The unit that runs your database is postgresql@18-main.service, so check that one:

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/sac/sac-firstboot.log.
• The data directory lives under /var/lib/postgresql/18/main.
• The PostgreSQL server log is at /var/log/postgresql/postgresql-18-main.log. It is 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. SecureAnyCloud is not affiliated with, endorsed by or sponsored by any of them.

Support

Every SecureAnyCloud image includes 24/7 support. If you have any questions about this PostgreSQL with pg_graphql image, contact us at sales@secureanycloud.com.

Deploy on Azure

Launch PostgreSQL 18 with pg_graphql 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