SQLPage 0.47.0 on AWS User Guide

Overview

This guide covers launching and using the SQLPage 0.47.0 on Ubuntu 24.04 AMI from cloudimg on Amazon EC2. It is one version of the cloudimg developer and API tools listing on AWS Marketplace; you pick it as the software version when you subscribe and launch.

SQLPage builds data-driven web applications entirely in SQL. Each page of your site is a .sql file: a SELECT whose first column is called component picks a ready-made component (a table, a chart, a form, a card, a map, a JSON response) and the rows that follow fill it in. There is no HTML, JavaScript or build step to write, so anyone who can write a query can publish a dashboard, an internal tool or a small API. The image ships an example site (a server inventory with a search form, two charts and an orientation page) on a local PostgreSQL 16 database, ready to edit.

The cloudimg image locks SQLPage down:

• HTTPS from the first boot. nginx serves SQLPage on port 443 with a certificate generated on your instance's first boot. Port 80 redirects to HTTPS.
• A password gate in front of everything. SQLPage has no login of its own: authentication is something a page author writes in SQL. So the whole site sits behind an HTTP Basic Auth prompt whose password is generated on your instance's first boot. A fresh instance is never an open, database-backed web site on the internet. Step 9 shows how to open it to the public once your own pages handle sign-in.
• No default login. The image contains no password. First boot writes the gate's user name and a random 24 character password to /var/lib/cloudimg/sqlpage-credentials.txt (root only); nginx stores only a SHA-512 crypt hash. Common guesses are refused.
• Other web sites cannot act with your login. SQLPage forms carry no anti-forgery token, and a browser re-sends the gate password to any site that asks, so nginx refuses every state changing request (POST and the like) and every WebSocket upgrade whose browser Origin is not this instance.
• No shell from SQL. allow_exec is off, so sqlpage.exec refuses to run commands, and SQLPage refuses to start on a configuration that turns it on, that lets SQLPage listen on the network itself or that shows error details to visitors. Uploads are limited to 5 MiB.
• SQLPage is not on the network. It listens on a local socket that only nginx can open, runs as its own unprivileged user that cannot change your pages or its configuration, and sees the real client address and the configured host name whatever forwarding headers a request carries. It never sees the gate password.
• Your own database, no password anywhere. The image ships an empty PostgreSQL data directory. On first boot PostgreSQL initialises it for your instance and SQLPage signs in over the local socket as the operating system user it runs as (peer authentication), so there is no database password to leak. PostgreSQL has no network listener at all.
• The application cannot reach the instance metadata service. nginx, SQLPage and PostgreSQL are blocked at the kernel level from the EC2 instance metadata service (IPv4 and IPv6), so a page that calls sqlpage.fetch cannot read the instance's metadata or role credentials.
• A pinned, checked release. SQLPage 0.47.0 is the official release binary, checksum pinned and tied to the release's dependency lock file, with the licence of every dependency checked and the source of the one LGPL library it links shipped on the image.
• Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on.

What is on the image: SQLPage 0.47.0 (/usr/local/bin/sqlpage, pages in /var/www/sqlpage, configuration in /etc/sqlpage), PostgreSQL 16, nginx, Ubuntu 24.04 LTS, cloud-init and the AWS Systems Manager agent.

Default user: ubuntu · Architecture: x86_64 · Recommended instance type: m5.large

Prerequisites

• An AWS account subscribed to the listing, an EC2 key pair and a VPC subnet.
• A security group that allows TCP 22 (SSH), TCP 443 (SQLPage) and TCP 80 (redirects to HTTPS). While the password gate is on, open them to your own networks only. SQLPage needs no other port: the database is not on the network.

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick SQLPage 0.47.0 on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large, your key pair, a security group as above and a subnet.

With the AWS CLI (replace the values in angle brackets):

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=sqlpage}]'

Step 2: Connect to your instance

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

Step 3: Check the services

First boot sets the host name and certificate, initialises the database and creates the gate password, then SQLPage and nginx start:

systemctl is-active postgresql@16-main sqlpage nginx sqlpage-firstboot
sudo test -f /var/lib/cloudimg/sqlpage-firstboot.done && echo FIRSTBOOT_DONE

All four units are active and the first boot marker is present:

active
active
active
active
FIRSTBOOT_DONE

Only SSH and nginx listen on the network. SQLPage and PostgreSQL use local sockets only:

sudo ss -lntp | awk 'NR>1 {print $4, $6}' | grep -v '127.0.0.5' | sed -E 's/,pid=[0-9]+,fd=[0-9]+//g' | sort
sudo ss -lxp | grep -E 'sqlpage|postgres' | awk '{print $5}' | sort -u

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"))
[::]:22 users:(("sshd"),("systemd"))
[::]:443 users:(("nginx"),("nginx"),("nginx"))
[::]:80 users:(("nginx"),("nginx"),("nginx"))
/run/sqlpage/sqlpage.sock
/var/run/postgresql/.s.PGSQL.5432

The built-in self-test checks the whole path through the gate, from HTTPS down to the database:

sudo sqlpage-selftest

SELFTEST-OK https 301 + healthz + gate 401 + 6 wrong gate credentials refused + example site rendered from PostgreSQL (24 rows, bound filter 6) + charts/about 200 + 403 cross-origin POST + 415 compressed body + 444 unknown host + preflight

Step 4: Get your URL and password

sudo cat /var/lib/cloudimg/sqlpage-credentials.txt

The file is readable by root only and holds the URL and the gate's user name and password for this instance:

# SQLPage 0.47.0 - generated on first boot by sqlpage-firstboot.service
# These credentials are unique to this instance; none was baked into the image. Keep this file secret.

SQLPAGE_URL=https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/
SQLPAGE_USERNAME=sqlpage
SQLPAGE_PASSWORD=<SQLPAGE_PASSWORD>
#
# Open SQLPAGE_URL in a browser (self-signed certificate minted on this instance's first boot; port 80 redirects to
# https) and pass the HTTP Basic Auth prompt with SQLPAGE_USERNAME / SQLPAGE_PASSWORD. Your pages are the .sql files
# in /var/www/sqlpage; settings in /etc/sqlpage/sqlpage.json.
# Database: PostgreSQL 'sqlpage' over the local socket only (peer authentication for the sqlpage OS user; there is no
# database password). Self-test: sudo sqlpage-selftest     Change the served name: sudo sqlpage-set-hostname <your-domain>
# User guide: https://www.cloudimg.co.uk/guides/sqlpage-0-47-0-on-ubuntu-24-04-aws/

The URL uses the instance's public DNS name from EC2 instance metadata. Opening https://<public-ip>/ sends you on to that name.

Step 5: Open the example site

Open the SQLPAGE_URL in your browser. The certificate is self signed, so your browser asks you to confirm it the first time (or put your own certificate in place, Step 9). The browser then asks for a user name and password: enter SQLPAGE_USERNAME and SQLPAGE_PASSWORD. The example site's home page appears. Every figure on it is one row of one SELECT statement in /var/www/sqlpage/index.sql:

Step 6: Explore the example

Fleet (servers.sql) is a sortable, searchable table with a search form. What you type reaches PostgreSQL only as a bound query parameter ($search in the SQL), never as SQL text, so a quote or a semicolon in the box cannot change the query:

Charts (chart.sql) draws a bar chart and a pie chart from two GROUP BY queries over the same table:

About (about.sql) says where your pages, schema, settings and credentials live on the instance:

Step 7: Write your own page

Every .sql file in /var/www/sqlpage is a page, and the URL is its file name. The files belong to root, so SQLPage itself can never change them: write them with sudo. This page answers with JSON, which makes a quick API:

sudo tee /var/www/sqlpage/regions.sql >/dev/null <<'EOF'
SELECT 'json' AS component;
SELECT region, COUNT(*) AS servers, SUM(cpu_cores) AS vcpu
FROM demo_server
GROUP BY region
ORDER BY region;
EOF

There is nothing to build or restart. Fetch it through the gate (the password is read from the credentials file by sudo, so it never appears on a command line):

H=$(sudo sed -n 's#^SQLPAGE_URL=https://\([^/]*\)/$#\1#p' /var/lib/cloudimg/sqlpage-credentials.txt)
gate() { sudo awk -F= '/^SQLPAGE_USERNAME=/{u=$2} /^SQLPAGE_PASSWORD=/{p=$2} END{printf "user = \"%s:%s\"\n", u, p}' /var/lib/cloudimg/sqlpage-credentials.txt; }
curl -sk -K <(gate) --resolve "$H:443:127.0.0.1" "https://$H/regions.sql"; echo

[
{"region":"ap-south","servers":5,"vcpu":62},
{"region":"eu-west","servers":8,"vcpu":62},
{"region":"sa-east","servers":3,"vcpu":28},
{"region":"us-east","servers":8,"vcpu":90}
]

Change 'json' to 'table' and reload https://<your-host>/regions.sql in the browser to see the same rows as a table. The SQLPage component reference lists every component and its properties.

Your own tables. SQLPage applies the numbered files in /etc/sqlpage/migrations once each, in order, when it starts; the example data came from 0001_cloudimg_demo.sql. Add 0002_<name>.sql with your CREATE TABLE statements and run sudo systemctl restart sqlpage. Your pages can also write: a form component posts to a page whose INSERT or UPDATE uses the submitted fields as bound parameters (:field_name).

Step 8: The database

PostgreSQL 16 runs on the instance with no network listener. Each role signs in only as the operating system user of the same name (peer authentication), and no role has a password. SQLPage runs as the sqlpage user and owns the sqlpage database, but it is not a superuser and holds no role that could read server files or run programs:

sudo -u postgres psql -X -A -t -c "SELECT current_setting('server_version'), current_setting('data_directory'), current_setting('listen_addresses') = '' AS no_tcp"
sudo -u postgres psql -X -A -t -c "SELECT rolname, rolsuper, rolpassword IS NULL AS no_password FROM pg_authid WHERE rolname NOT LIKE 'pg\_%' ORDER BY 1"
sudo -u postgres psql -X -A -t -d sqlpage -c "SELECT version, description FROM _sqlx_migrations ORDER BY version"
sudo grep -v -E '^#|^$' /etc/postgresql/16/main/pg_hba.conf

16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)|/var/lib/postgresql/16/main|t
postgres|t|t
sqlpage|f|t
1|cloudimg demo
local   all             postgres                                peer
local   sqlpage         sqlpage                                 peer
local   all             all                                     reject
host    all             all             0.0.0.0/0               reject
host    all             all             ::/0                    reject

To work in the database yourself, sudo -u sqlpage psql sqlpage opens a SQL prompt as the same role SQLPage uses. A backup to a directory of your choice:

sudo mkdir -p <backup-dir> && sudo -u postgres pg_dump -Fc sqlpage | sudo tee <backup-dir>/sqlpage-$(date +%F).dump >/dev/null

Keep copies off the instance too, for example in Amazon S3, or take EBS snapshots of the volume. Back up /var/www/sqlpage and /etc/sqlpage with it: they are your application.

Another database. SQLPage also works with MySQL, MariaDB, SQLite, Microsoft SQL Server and ODBC sources. To use one, set database_url in /etc/sqlpage/sqlpage.json (for example mysql://<user>:<password>@<host>/<database>; keep that file readable by root and the sqlpage group only, as it is now) and run sudo systemctl restart sqlpage. Allow outbound traffic from the instance to that database.

Step 9: Publish on your own domain

Point a DNS record for your domain at the instance (an Elastic IP keeps the address stable), then give SQLPage the name. The helper mints a new certificate for it, pins nginx and the cross-site guard to it and redirects the instance's IP addresses there:

sudo sqlpage-set-hostname <your-domain>

For a certificate browsers trust, replace /etc/ssl/sqlpage/sqlpage.crt and /etc/ssl/sqlpage/sqlpage.key with your own (for example from Let's Encrypt or your certificate authority) and run sudo systemctl reload nginx.

Opening the site to the public. Leave the gate on until your pages handle sign-in themselves: SQLPage's authentication component, sqlpage.hash_password and its OpenID Connect support (oidc_issuer_url in sqlpage.json) are the building blocks, documented at sql-page.com. Then edit /etc/nginx/sqlpage-gate.conf, put a # in front of its two lines and run sudo nginx -t && sudo systemctl reload nginx. The cross-site guard, the HTTPS settings and the metadata block stay in place.

Step 10: Security notes

nginx, SQLPage and PostgreSQL are blocked from the instance metadata service:

for u in nginx sqlpage postgresql@16-main; do printf '%s ' "$u"; systemctl show -p IPAddressDeny --value "$u" | tr ' ' '\n' | sort | tr '\n' ' '; echo; done

nginx 168.63.129.16/32 169.254.169.254/32 fd00:ec2::254/128 
sqlpage 168.63.129.16/32 169.254.169.254/32 fd00:ec2::254/128 
postgresql@16-main 168.63.129.16/32 169.254.169.254/32 fd00:ec2::254/128 

The gate answers before SQLPage does, nginx refuses a state changing request that another web site's page sends, a request body with a Content-Encoding is refused, and a request for a host name the instance does not serve gets no answer at all:

H=$(sudo sed -n 's#^SQLPAGE_URL=https://\([^/]*\)/$#\1#p' /var/lib/cloudimg/sqlpage-credentials.txt)
curl -sk -o /dev/null -w '%{http_code}\n' --resolve "$H:443:127.0.0.1" "https://$H/"
curl -sk -o /dev/null -w '%{http_code}\n' --resolve "$H:443:127.0.0.1" -X POST -H 'Origin: https://evil.example' --data 'x=1' "https://$H/servers.sql"
printf 'x' | curl -sk -o /dev/null -w '%{http_code}\n' --resolve "$H:443:127.0.0.1" -H 'Content-Encoding: gzip' --data-binary @- "https://$H/servers.sql"
curl -sk -o /dev/null -w '%{http_code}\n' --resolve evil.example:443:127.0.0.1 https://evil.example/ || true

401
403
415
000

SQLPage's own settings keep shell execution off, keep it on its local socket and keep error details away from visitors:

sudo python3 -c "import json; c=json.load(open('/etc/sqlpage/sqlpage.json')); print('allow_exec', c['allow_exec']); print('unix_socket', c['unix_socket']); print('environment', c['environment'])"
sudo ls -l /run/sqlpage/ | tail -n +2 | awk '{print $1, $3, $4, $NF}'

allow_exec False
unix_socket /run/sqlpage/sqlpage.sock
environment production
srwxrwx--- sqlpage sqlpage sqlpage.sock

Other notes:

• What your pages can do. A page runs with the sqlpage database role and the sqlpage operating system user. Functions such as sqlpage.fetch (HTTP requests from the server), sqlpage.read_file_as_text and sqlpage.run_sql are available to the pages you write; none of the example pages uses them. Never pass a URL or a file path that a visitor controls to them. sqlpage.exec stays refused: /usr/local/sbin/sqlpage-preflight stops SQLPage from starting with allow_exec set to true.
• The gate password never reaches SQLPage. nginx removes it from every request, so SQLPage's sqlpage.basic_auth_username() and sqlpage.basic_auth_password() return nothing behind the gate.
• Compressed request bodies. nginx answers 415 to any request that carries a Content-Encoding, before SQLPage sees it. Browsers never send one; it keeps SQLPage's request decompressors, which have open advisories in their zstd library, out of reach. The advisories in SQLPage's dependencies and why none is reachable on this image are listed in /usr/share/doc/cloudimg-sqlpage/rustsec-reachability.txt.
• Content Security Policy. SQLPage sends its own policy that allows only its own scripts. nginx adds one that keeps styles, images, fonts and requests on this site and forbids framing. A page that loads a script, image or font from another site (SQLPage's map component loads Leaflet from a public CDN) needs that site added to the policy in /etc/nginx/sites-available/cloudimg-sqlpage.
• Logs. SQLPage's request log, with the real client address, is in the journal (journalctl -u sqlpage); nginx logs are in /var/log/nginx.

Step 11: Keep it up to date

Ubuntu security updates install automatically. To apply all updates now:

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

New SQLPage releases arrive as new versions of this listing, built and checked the same way. To move to one, back up the database, /var/www/sqlpage and /etc/sqlpage (Step 8), launch the new version and restore them. Read the SQLPage changelog before you move.

Support and licensing

SQLPage is open source under the MIT licence (Ophir Lojkine and the SQLPage contributors); the source code is at github.com/sqlpage/SQLPage. Its Rust dependencies are under permissive open source licences, except the unixODBC driver manager it links (LGPL 2.1 or later), whose complete source is on the image. The licence texts and the full dependency licence inventory are in /usr/share/doc/cloudimg-sqlpage/. There is no per user licence fee. The AMI price covers cloudimg's build, hardening, first boot setup and support.

• Email: support@cloudimg.co.uk
• Website: https://www.cloudimg.co.uk
• Support: 24/7, with a 24 hour response SLA

The other versions on this listing are on the developer and API tools guide.