SQLPage 0.47.0

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, select your Region, and choose Launch through EC2.

Choose m5.large, your key pair, a security group as described above and a subnet.

Alternatively, launch 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}]'

Connect as the ubuntu user, the SSH user for SQLPage 0.47.0 on Ubuntu 24.04:

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

The first boot sets the host name and certificate, initializes the database and creates the gate password. Then SQLPage and nginx start. Confirm the services and the first-boot marker:

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

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

active active active active FIRSTBOOT_DONE

Check the listeners. Only SSH and nginx listen on the network, while 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

Run the built-in self-test. It 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

Read the credentials file as root:

sudo cat /var/lib/sac/data/sqlpage-credentials.txt

Only root can read the file. It 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://secureanycloud.com/stacks/sqlpage-0-47-0/

The URL uses the instance's public DNS name from EC2 instance metadata. If you open https://<public-ip>/, you are redirected to that name.

Open the SQLPAGE_URL in your browser. The certificate is self-signed, so your browser asks you to confirm it the first time. Alternatively, 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.

Open each page of the example site:

• 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. 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.

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 the page through the gate. sudo reads the password from the credentials file, so it never appears on a command line:

H=$(sudo sed -n 's#^SQLPAGE_URL=https://\([^/]*\)/$#\1#p' /var/lib/sac/data/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/sac/data/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} ]

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

Your own tables. When it starts, SQLPage applies the numbered files in /etc/sqlpage/migrations once each, in order. The example data came from 0001_sac_demo.sql. Add 0002_<name>.sql with your CREATE TABLE statements, then run sudo systemctl restart sqlpage.

Your pages can also write data. A form component posts to a page whose INSERT or UPDATE uses the submitted fields as bound parameters (:field_name).

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. It is not a superuser and holds no role that could read server files or run programs. Verify the setup:

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|sac 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, run sudo -u sqlpage psql sqlpage. It opens a SQL prompt as the same role SQLPage uses.

Back up the database 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 as well, because 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.

Then run sudo systemctl restart sqlpage, and allow outbound traffic from the instance to that database.

Point a DNS record for your domain at the instance. An Elastic IP keeps the address stable.

Then give SQLPage the name. The helper generates 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 that 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. Then run sudo systemctl reload nginx.

Opening the site to the public. Leave the gate on until your pages handle sign-in themselves. The building blocks are SQLPage's authentication component, sqlpage.hash_password and its OpenID Connect support (oidc_issuer_url in sqlpage.json), all documented at sql-page.com.

Then edit /etc/nginx/sqlpage-gate.conf and put a # in front of its two lines. Run sudo nginx -t && sudo systemctl reload nginx. The cross-site guard, the HTTPS settings and the metadata block stay in place.

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 sent by another web site's page, refuses a request body with a Content-Encoding, and gives no answer at all to a request for a host name the instance does not serve:

H=$(sudo sed -n 's#^SQLPAGE_URL=https://\([^/]*\)/$#\1#p' /var/lib/sac/data/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 SQLPage 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

Keep these points in mind:

• 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, and none of the example pages uses them. Never pass them a URL or a file path that a visitor controls. sqlpage.exec stays refused, because /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. The rule 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/sac-sqlpage/rustsec-reachability.txt.
• Content Security Policy. SQLPage sends its own policy, which 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 needs that site added to the policy in /etc/nginx/sites-available/sac-sqlpage. For example, SQLPage's map component loads Leaflet from a public CDN.
• Logs. SQLPage's request log, with the real client address, is in the journal (journalctl -u sqlpage). The nginx logs are in /var/log/nginx.

Ubuntu security updates install automatically. To apply all updates immediately, run:

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 a new version, back up the database, /var/www/sqlpage and /etc/sqlpage (Step 8). Launch the new version and restore them. Read the SQLPage changelog first.