SQLPage on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and configuration of SQLPage on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. SQLPage builds data driven web applications entirely in SQL. It is a single compiled binary that reads .sql files from a directory, runs them against a database, and renders each result set using a library of pre made web components.

The idea is simple enough to describe in one sentence: a query whose first column is called component selects a component, and the remaining columns are that component's properties. Return 'table' AS component and you get a sortable, searchable table. Return 'chart' AS component and you get a chart. There is no HTML to write, no JavaScript framework to learn, no npm install, and no build step. You edit a .sql file and reload the page.

That makes SQLPage a genuinely fast way to put an interface in front of data that already lives in a database: an internal admin panel, an operational dashboard, a reporting page for a team that knows SQL but does not write front end code, or a quick prototype you can show someone the same afternoon.

The cloudimg image ships the free and open source, MIT licensed SQLPage as the official upstream release binary, pinned by content hash and tied to an exact upstream commit. It also ships a PostgreSQL database and a working four page example application, so the appliance demonstrates what it does the moment it finishes booting, with nothing to configure first.

SQLPage is an independent open source project. This image is produced by cloudimg and is not affiliated with, endorsed by, or sponsored by the SQLPage project or its authors. It ships the free and open source MIT licensed software, unmodified.

What is included:

• SQLPage v0.45.0 — the official upstream linux/amd64 release tarball, pinned by sha256 content hash, not by a moving tag
• Verified provenance: the release binary embeds the source path of every crate it compiled, and this appliance asserts that all 168 of them resolve to the Cargo.lock of upstream commit dadb3228fe7a842c7300e5e949b8e5e9a3af8033, which is the commit the signed v0.45.0 tag points at
• PostgreSQL 16, bound to the loopback interface only, with a role password generated per VM on first boot
• A working four page example application over a 24 row demo dataset, showing the dashboard, table, chart, form and card components
• An nginx access gateway on port 80, installed as a host package so unattended security upgrades keep the internet facing component patched
• A shipped dependency licence inventory at /usr/share/sqlpage/dependency-licences.txt covering all 552 crates in the pinned dependency tree
• Backed by 24/7 cloudimg support

Prerequisites

• An Azure subscription with permission to create VMs
• A Standard_B2s VM or larger. SQLPage is a single compiled binary with no JVM, no Python runtime and no container engine, so 2 vCPU and 4 GiB is genuinely sufficient alongside PostgreSQL for most internal applications
• Inbound TCP 80 open to the networks your users and administrators connect from
• An SSH key pair for administrative access
• Familiarity with SQL. That is the only language this tool uses

Step 1: Deploy from the Azure Portal

1. Sign in to the Azure Portal and search the Marketplace for SQLPage by cloudimg.
2. Select the image and choose Create.
3. Under Basics, pick your subscription, resource group and region, and set a VM name.
4. Set the VM size to Standard_B2s or larger.
5. Under Administrator account, choose SSH public key and supply your key. The username you choose here is your Linux administrator; it is unrelated to the SQLPage gate credential.
6. Under Inbound port rules, allow SSH (22) and HTTP (80). Restrict both to your own address ranges wherever you can.
7. Review and create.

Step 2: Deploy from the Azure CLI

az group create \
  --name sqlpage-rg \
  --location eastus

az vm create \
  --resource-group sqlpage-rg \
  --name sqlpage-vm \
  --image cloudimg:sqlpage-ubuntu-24-04:sqlpage-ubuntu-24-04:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

az vm open-port \
  --resource-group sqlpage-rg \
  --name sqlpage-vm \
  --port 80

Accept the image terms once per subscription if prompted:

az vm image terms accept \
  --publisher cloudimg \
  --offer sqlpage-ubuntu-24-04 \
  --plan sqlpage-ubuntu-24-04

Step 3: Connect to your VM

ssh azureuser@<vm-ip>

First boot generates this machine's credentials, creates its database role, proves the gateway rejects unauthenticated requests, and then starts the application. On a Standard_B2s it completes in a few seconds. If you connect immediately after the VM reports running, give it a moment before the next step.

Step 4: Confirm the services are running

sudo systemctl status sqlpage nginx postgresql --no-pager

You should see sqlpage.service, nginx.service and postgresql.service all active. The one shot unit that generated this machine's credentials is sqlpage-firstboot.service:

sudo systemctl status sqlpage-firstboot --no-pager
sudo journalctl -u sqlpage-firstboot --no-pager

Confirm what is listening. Only SSH and the gateway are reachable from off the machine; SQLPage and PostgreSQL are both bound to the loopback interface:

sudo ss -lntp

State   Local Address:Port   Process
LISTEN  0.0.0.0:22           sshd
LISTEN  0.0.0.0:80           nginx
LISTEN  127.0.0.1:8080       sqlpage
LISTEN  127.0.0.1:5432       postgres

Step 5: Read the per instance credentials

Nothing is baked into the image. Both passwords were generated on this machine, on its first boot, from the kernel random number generator:

sudo cat /root/sqlpage-credentials.txt

SQLPAGE_URL=http://<vm-ip>/
SQLPAGE_USERNAME=sqlpage
SQLPAGE_PASSWORD=<generated per VM>
SQLPAGE_DB_NAME=sqlpage
SQLPAGE_DB_USER=sqlpage
SQLPAGE_DB_PASSWORD=<generated per VM>

The file is 0600 root:root. SQLPAGE_PASSWORD is the gateway credential your browser will ask for. SQLPAGE_DB_PASSWORD is the PostgreSQL role password, which is already written into the SQLPage configuration for you; you only need it if you want to connect to the database yourself.

Store both somewhere safe and consider rotating them. To change the gateway password:

sudo htpasswd -B /etc/nginx/sqlpage.htpasswd sqlpage
sudo systemctl reload nginx

Step 6: Open the application

Browse to http://<vm-ip>/. Your browser will prompt for the username and password from the previous step.

What loads is the bundled example application: a dashboard of summary cards, a searchable fleet table with a filter, two charts, and an orientation page. Every one of those pages is a .sql file, and the dashboard deliberately shows you the query that produced its first card.

Step 7: Write your first page

Your pages are the .sql files in /var/www/sqlpage. Create one:

sudo tee /var/www/sqlpage/hello.sql >/dev/null <<'EOF'
SELECT 'shell' AS component, 'My first page' AS title;

SELECT 'text' AS component,
       'This whole page is one SQL file.' AS contents_md;

SELECT 'table' AS component, TRUE AS sort, TRUE AS search;
SELECT hostname AS "Host", region AS "Region", status AS "Status"
FROM demo_server
ORDER BY hostname;
EOF

Now browse to http://<vm-ip>/hello.sql. That is the entire development loop: write the file, reload the page. There is nothing to compile and no service to restart.

A few components to start with:

Component	What it renders
shell	The page frame: title, navigation, footer
text	Paragraphs, with contents_md for Markdown
table	A table, with optional sort and search
chart	Bar, line, area and pie charts
big_number	The summary cards on the example dashboard
form	An input form, submitted back to a .sql page
card	A grid of cards
list	A linked list

The full component reference, with a live example of every property, is at sql-page.com/documentation.sql.

Step 8: Accept input safely

The example fleet page takes a search term from the query string. Look at how it does it:

SELECT 'form' AS component, 'get' AS method, 'Filter the fleet' AS title;
SELECT 'search' AS name, 'Hostname, region or role contains' AS label,
       'text' AS type, $search AS value;

SELECT 'table' AS component, TRUE AS sort;
SELECT hostname, region, role FROM demo_server
WHERE COALESCE($search, '') = ''
   OR hostname ILIKE '%' || $search || '%';

$search is a SQLPage variable. SQLPage passes it to PostgreSQL as a bound parameter, never as SQL text, so there is nothing a visitor can type into that box that changes the shape of the query. The || concatenation happens inside PostgreSQL, on a value that has already been bound, which is why it is safe.

Use $name for values from the query string or a form post. Never build a query by concatenating a variable into the SQL that you then ask the database to execute.

Step 9: Add tables of your own

Numbered files in /etc/sqlpage/migrations are applied in filename order, once each, the next time SQLPage starts. The example dataset came from 0001_cloudimg_demo.sql.

sudo tee /etc/sqlpage/migrations/0002_my_schema.sql >/dev/null <<'EOF'
CREATE TABLE IF NOT EXISTS customer (
    id       integer PRIMARY KEY,
    name     text NOT NULL,
    created  date NOT NULL DEFAULT CURRENT_DATE
);
EOF

sudo systemctl restart sqlpage

You can equally work in the database directly:

sudo -u postgres psql -d sqlpage

The application role owns the sqlpage database, so it can create and modify its own tables. It is deliberately not a superuser and holds no membership of pg_read_server_files, pg_write_server_files or pg_execute_server_program, so a page cannot reach the filesystem through PostgreSQL.

Step 10: Point SQLPage at your own database

The bundled PostgreSQL exists so the appliance works out of the box. Nothing obliges you to keep it. SQLPage speaks PostgreSQL, MySQL, MariaDB, SQLite and Microsoft SQL Server. Edit the configuration:

sudo nano /etc/sqlpage/sqlpage.json

{
  "database_url": "postgres://user:password@db.example.internal:5432/mydb",
  "listen_on": "127.0.0.1:8080",
  "web_root": "/var/www/sqlpage",
  "configuration_directory": "/etc/sqlpage",
  "allow_exec": false,
  "environment": "production",
  "max_uploaded_file_size": 5242880,
  "max_database_pool_connections": 8
}

sudo systemctl restart sqlpage

Two settings are load bearing and should stay as they are:

• listen_on must remain 127.0.0.1:8080. The gateway on port 80 is the only thing that should be reachable. Binding SQLPage to 0.0.0.0 publishes your application with no authentication in front of it, and the appliance refuses to start if you do.
• allow_exec must remain false. It enables sqlpage.exec, which runs shell commands from a .sql page. The appliance refuses to start with it enabled.

If you stop using the bundled database, you can disable it:

sudo systemctl disable --now postgresql

Step 11: Understand the access model

SQLPage has no built in authentication for the pages it serves. That is a deliberate upstream design decision, not an oversight: authentication is something the page author implements in SQL, because the author is the one who knows which pages are public and which are not. A stock SQLPage answers every route to every anonymous caller.

This appliance therefore treats SQLPage as unauthenticated and puts a gateway in front of it:

• nginx on port 80 is default deny at server scope, so every location inherits the gate. That covers the example pages, any page you add later, SQLPage's own bundled assets under /sqlpage/, and every 404.
• The only unauthenticated route is nginx's own /nginx-health literal, which is answered by nginx itself and never reaches SQLPage or the database. It exists so a load balancer can probe the VM without a credential.
• SQLPage binds 127.0.0.1:8080 only, so the gateway is the sole way in.
• The configuration file holding your database password lives at /etc/sqlpage/sqlpage.json, outside the web root, and the gateway has no root, alias or autoindex directive anywhere, so it cannot serve a file from disk under any circumstances.

The gateway cannot be started unconfigured

nginx.service carries two ConditionPathExists markers. One is this machine's credential file. The other is a proof marker that first boot writes only after it has stood up a throwaway loopback listener using the real credential file and confirmed that an anonymous request is answered with exactly 401, that a one character change to the password at either end is rejected, that a wrong username is rejected, that an empty password is rejected, and that the correct credential is accepted.

Both nginx.service and sqlpage.service additionally run /usr/local/sbin/sqlpage-preflight.sh before they start, which refuses an empty, plaintext or truncated credential file and refuses a configuration that still holds the shipped placeholder, binds a routable address, enables allow_exec, or is not in production mode.

The practical consequence is that a misconfigured appliance is unreachable, never open.

Adding your own application level login

The gateway is a perimeter, not a user model. If you need per user accounts inside the application, SQLPage has first class support for it: see the authentication documentation for sqlpage.basic_auth_password, password hashing with sqlpage.hash_password, and session cookies. You can then decide whether to keep the gateway in front as defence in depth, or remove it.

Step 12: Put TLS in front

The appliance serves plain HTTP so that it works immediately on an IP address. For anything beyond a trial, terminate TLS. The simplest route is a certificate on the VM itself:

sudo apt-get update && sudo apt-get install -y certbot python3-certbot-nginx
sudo certbot --nginx -d sqlpage.example.com

Alternatively, put Azure Application Gateway or Front Door in front and restrict the VM's network security group to that service.

Step 13: What is in the image, and under what licence

SQLPage is MIT licensed. The verbatim licence, as published at the exact commit this binary was built from, ships at /usr/share/sqlpage/licences/SQLPage-LICENSE.txt.

The full dependency licence inventory is at /usr/share/sqlpage/dependency-licences.txt:

column -t -s $'\t' /usr/share/sqlpage/dependency-licences.txt | less

Every one of the 552 crates in the pinned Cargo.lock was resolved against the licence metadata published for that exact version, at build time, and the build fails if any of them is AGPL, SSPL, BUSL, Elastic, Commons Clause, PolyForm, Prosperity, RSAL or otherwise non commercial, or if any single one cannot be resolved at all. The result for this build was 552 examined, zero restrictive, zero unresolved.

One dependency carries a copyleft obligation and it is worth knowing about:

• unix-odbc 0.1.4 — LGPL-2.1-or-later. This provides the unixODBC driver manager, which SQLPage statically links so it can connect to ODBC data sources. Its complete, unmodified source is shipped on the appliance at /usr/share/sqlpage/licences/unix-odbc-0.1.4.crate, alongside the LGPL-2.1 text, which satisfies the LGPL's source availability requirement directly from the image. No GPL licensed code is compiled into the binary. The full notice is at /usr/share/sqlpage/licences/README.txt.

Day to day operation

# service control
sudo systemctl restart sqlpage
sudo systemctl restart nginx
sudo journalctl -u sqlpage -f

# your pages
ls /var/www/sqlpage/

# your schema migrations
ls /etc/sqlpage/migrations/

# the database
sudo -u postgres psql -d sqlpage

# back up the database
sudo -u postgres pg_dump sqlpage | gzip > ~/sqlpage-$(date +%F).sql.gz

# back up your pages
sudo tar czf ~/sqlpage-pages-$(date +%F).tar.gz /var/www/sqlpage /etc/sqlpage

SQLPage caches .sql files in memory in production mode. If a change to a page does not appear, restart the service.

Security hardening

• Restrict inbound 80 and 22 in the network security group to your own address ranges
• Rotate the gateway password with htpasswd -B and reload nginx
• Terminate TLS before exposing the application beyond a trial
• Leave listen_on on the loopback interface and allow_exec set to false
• Keep your own pages to SELECT statements wherever they are reachable by users who should not change data, and always take user input through $variables rather than by building SQL strings
• The image keeps Ubuntu's unattended security upgrades enabled, so nginx, PostgreSQL and the OS continue to receive patches

Troubleshooting

The browser asks for a password and my password does not work. Read it again from /root/sqlpage-credentials.txt with sudo. The value is case sensitive and has no surrounding quotes. The Linux admin username you chose at launch is not the gateway username.

I get no response at all on port 80. Check sudo systemctl status nginx. If it reports a condition failure, first boot has not completed or its credential file is missing; sudo journalctl -u sqlpage-firstboot will say why. This is the appliance failing closed rather than serving your application to the internet without a password.

A page shows an error but not what went wrong. The appliance runs in production mode, which deliberately hides error detail from visitors. The full error is in the log: sudo journalctl -u sqlpage -n 50.

My page changed but the site did not. Production mode caches pages in memory. sudo systemctl restart sqlpage.

A migration did not run. Migrations are applied at startup, in filename order, once each. Check sudo journalctl -u sqlpage | grep -i migrat. A migration that fails stops SQLPage from starting, which is intentional.

I cannot reach the database from another machine. That is by design: PostgreSQL is bound to 127.0.0.1 only. Use an SSH tunnel: ssh -L 5432:127.0.0.1:5432 azureuser@<vm-ip>.

Support

This image is supported 24/7 by cloudimg. Raise a request at cloudimg.co.uk with your VM's region, size and the output of sudo journalctl -u sqlpage -n 100.