SecureAnyCloud
Deployment guide

Mathesar Ubuntu 24.04

Overview

Mathesar is an open-source web application that gives your team a spreadsheet-like way to work with the data in PostgreSQL databases. People browse, filter, sort and edit records in a data grid, and follow relationships between tables. They also build explorations (saved queries) and forms, and manage schemas, tables, columns and constraints from the browser. The data stays in standard PostgreSQL tables underneath. Access is controlled per database through PostgreSQL roles and Mathesar collaborators.

This guide covers deploying and using the SecureAnyCloud Mathesar 0.12.0 on Ubuntu 24.04 LTS image on Microsoft Azure.

What you get:

  • HTTPS from the first boot. nginx serves Mathesar on port 443 with a certificate generated on your VM's first boot. Port 80 redirects to HTTPS, and requests for a host name the VM does not serve get no answer.
  • An administrator unique to your VM. First boot creates the admin account with a password generated on the VM and written to /root/mathesar-credentials.txt (readable by root only). Mathesar stores only a salted PBKDF2 hash of it. The Django secret key that signs sessions is generated on the VM too. Nothing secret is baked into the image, and Mathesar's "the first visitor creates the administrator" page is never reachable.
  • Anonymous sign-up closed. Upstream Mathesar 0.12.0 leaves its local sign-up page open, so anyone who can reach the server could create an account and sign in. This image closes it in Mathesar itself and in nginx. An administrator creates accounts (or single sign-on does, if you configure it).
  • Cross-site API requests refused. Mathesar's JSON-RPC API does not check CSRF tokens upstream. Here, nginx refuses every request that changes something when a web browser sends it on behalf of another site, so a malicious page cannot use a signed-in user's session.
  • PostgreSQL created on your VM, on its own disk. No database ships in the image. First boot creates the PostgreSQL 16 cluster on the dedicated 32 GiB data disk, together with Mathesar's metadata database and its role. PostgreSQL listens on 127.0.0.1:5432 and the unix socket only. Mathesar's role signs in by peer authentication, so it has no password at all.
  • Closed until first boot, and fail closed. Mathesar cannot start without the settings first boot writes, and nginx ships switched off. First boot opens the public listener only after Mathesar works on loopback, and then proves a full sign-in round trip. If anything fails, the listener is closed again.
  • Hardened defaults. Mathesar runs as its own unprivileged account in a systemd sandbox that can write only to its upload directory. Mathesar, nginx and PostgreSQL are all blocked from the Azure instance metadata service and the Azure wire server, and never write a core dump. nginx passes Mathesar a fixed host name and the real client address. Sign-in attempts are rate limited, and SSH refuses root logins.
  • Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on.

What is on the image: Mathesar 0.12.0 (the official release, checksum pinned) in /opt/mathesar, with its Python dependencies in a virtual environment at /opt/mathesar/venv. Every package is pinned by version and checksum, with the Django 4.2.30, Pillow 12.3.0 and CairoSVG 2.9.1 security fixes. The image also holds gunicorn, nginx, PostgreSQL 16, Ubuntu 24.04 LTS, cloud-init and the Azure Linux Agent. No database and no account ship in the image.

Recommended size: Standard_B2s or larger.

Prerequisites

  • An Azure subscription with permission to create virtual machines, and an SSH key pair (or a password) for the VM's administrator account
  • A network security group that allows TCP 443 (Mathesar) and TCP 80 (redirects to HTTPS) from the people who will use it, and TCP 22 (SSH) from your own networks only. Never open 5432, because PostgreSQL is reachable on the VM only
1

Deploy from the Azure Portal

  • Find Mathesar on Ubuntu 24.04 LTS by SecureAnyCloud in the Azure Marketplace, and select Create.
  • Choose your subscription, resource group, region and a size (Standard_B2s or larger).
  • Set the administrator user name and your SSH public key (or a password).
  • On the Disks tab, find the 32 GiB data disk that holds PostgreSQL and your uploads. You can make it larger now or grow it later (Step 14).
  • On the Networking tab, allow 443 and 80 from your users, and 22 from your own networks only.
  • Select Review + create, then Create.
2

Deploy from the Azure CLI

Replace the values in angle brackets with your own.

az vm image terms accept --urn sac1647283583153:mathesar-ubuntu-24-04:default:latest
az vm create --resource-group <your-resource-group> --name mathesar \
  --image sac1647283583153:mathesar-ubuntu-24-04:default:latest \
  --size Standard_B2s --admin-username azureuser --ssh-key-values ~/.ssh/id_rsa.pub \
  --public-ip-sku Standard
az vm open-port --resource-group <your-resource-group> --name mathesar --port 443 --priority 1001
az vm open-port --resource-group <your-resource-group> --name mathesar --port 80 --priority 1002

Then connect over SSH as the administrator account you chose:

ssh azureuser@<public-ip>
3

Check the services

First boot creates the database cluster, the secret key, the administrator and the certificate. It starts Mathesar, opens nginx, and proves a full sign-in round trip before it finishes. This takes about a minute after the VM starts:

systemctl is-active postgresql@16-main mathesar nginx mathesar-firstboot
sudo test -f /var/lib/sac/data/mathesar-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 public addresses. gunicorn (Mathesar) and PostgreSQL listen on loopback:

sudo ss -lntp | awk 'NR>1 {print $4, $6}' | grep -v '127.0.0.5' | sort
0.0.0.0:22 users:(("sshd",...)) 0.0.0.0:443 users:(("nginx",...)) 0.0.0.0:80 users:(("nginx",...)) 127.0.0.1:5432 users:(("postgres",...)) 127.0.0.1:8000 users:(("gunicorn",...)) [::]:22 users:(("sshd",...)) [::]:443 users:(("nginx",...)) [::]:80 users:(("nginx",...))

PostgreSQL's data lives on the data disk:

sudo -u postgres psql -X -tA -c 'SHOW data_directory' findmnt -no TARGET,FSTYPE,SIZE /var/lib/mathesar-data
/var/lib/mathesar-data/postgresql/16/main /var/lib/mathesar-data ext4 31.2G
4

Get your URL and password

Read the credentials file:

sudo grep -E '^MATHESAR_' /root/mathesar-credentials.txt

Only root can read the file. It holds the URL, the administrator's user name and this VM's password:

MATHESAR_URL=https://<public-ip>/ MATHESAR_ADMIN_USER=admin MATHESAR_ADMIN_PASSWORD=<MATHESAR_ADMIN_PASSWORD>

The URL uses the VM's public IP address from the Azure instance metadata service, or its private address when the VM has no public IP. The certificate also names the private IP address, so Mathesar answers on both.

To serve Mathesar under your own DNS name, see Step 9.

5

Sign in

Open the MATHESAR_URL in your browser. The certificate is self-signed, so your browser asks you to confirm it the first time. You can also install your own certificate (Step 9). Sign in as admin with the password from Step 4.

The home page lists the databases Mathesar is connected to. The image starts with none. Choose Connect Database to create a new PostgreSQL database on this VM, optionally loaded with Mathesar's sample data. You can also connect to a database that already exists elsewhere.

A database shows its schemas. A table opens in the data grid, where you filter, sort, group and edit records and follow relationships. The inspector is on the right.

6

Check it from the command line

Run the commands below. Port 80 redirects to HTTPS, an anonymous visitor is sent to the sign-in page, and the sign-up page does not exist. The health check stays open for load balancers. nginx answers it, and it reveals nothing:

H=$(sudo grep '^MATHESAR_URL=' /root/mathesar-credentials.txt | sed -E 's#^MATHESAR_URL=https://([^/]+)/.*#\1#')
curl -s -o /dev/null -w 'http:// -> %{http_code} %{redirect_url}\n' --connect-to "$H:80:127.0.0.1:80" "http://$H/"
curl -sk -o /dev/null -w 'no login -> %{http_code} %{redirect_url}\n' --connect-to "$H:443:127.0.0.1:443" "https://$H/"
curl -sk -o /dev/null -w 'signup -> HTTP %{http_code}\n' --connect-to "$H:443:127.0.0.1:443" -X POST "https://$H/auth/signup/"
curl -sk -o /dev/null -w 'healthz -> HTTP %{http_code}\n' --connect-to "$H:443:127.0.0.1:443" "https://$H/healthz"
http:// -> 301 https://<public-ip>/ no login -> 302 https://<public-ip>/auth/login/?next=/ signup -> HTTP 404 healthz -> HTTP 200
7

Run the built-in sign-in check

The image ships an end-to-end check you can run at any time. First boot runs it too.

The check reads the credentials file itself and drives the real web flow over HTTPS through nginx, with the VM's certificate pinned. It confirms that:

  • An anonymous visitor is refused, and sign-up is closed.
  • A sign-in without the CSRF token is refused.
  • A wrong password gets no session, and the right password gets a secure session.
  • The JSON-RPC API answers that session.
  • A request from another web site riding that session is refused.

The check signs out afterwards. Run it:

sudo mathesar-verify.sh
port 80 -> 301 https://<public-ip>/ ; an unknown Host is dropped on 80 and 443 ; certificate verifies for <public-ip> anonymous: / -> sign-in, the RPC API refuses, /auth/signup/ is 404, the setup page is closed sign-in: CSRF-less POST 403, wrong password no session, right password 302 + Secure HttpOnly session, RPC users.list lists admin, cross-origin RPC 403 / same-origin RPC answered MATHESAR_ROUNDTRIP_OK
8

Run the security checks

The files that matter are locked down. The settings file holds this VM's Django secret key:

sudo stat -c '%a %U:%G %n' /etc/mathesar/mathesar.env /root/mathesar-credentials.txt /var/lib/mathesar-data/media /var/lib/mathesar-data/postgresql/16/main /etc/ssl/mathesar/mathesar.key
640 root:mathesar /etc/mathesar/mathesar.env 600 root:root /root/mathesar-credentials.txt 750 mathesar:mathesar /var/lib/mathesar-data/media 700 postgres:postgres /var/lib/mathesar-data/postgresql/16/main 600 root:root /etc/ssl/mathesar/mathesar.key

No PostgreSQL role has a password. postgres and Mathesar's own mathesar role sign in over the unix socket by peer authentication only, and mathesar is not a superuser. Roles you create later need a password:

sudo -u postgres psql -X -tA -c "SELECT rolname, rolsuper, rolpassword IS NOT NULL AS has_password FROM pg_authid WHERE rolname IN ('postgres','mathesar') ORDER BY 1"
sudo grep -vE '^\s*(#|$)' /etc/postgresql/16/main/pg_hba.conf
mathesar|f|f postgres|t|f local all postgres peer local all mathesar peer local all all scram-sha-256 host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256

The certificate was generated on this VM and names its own addresses. Check it:

H=$(sudo grep '^MATHESAR_URL=' /root/mathesar-credentials.txt | sed -E 's#^MATHESAR_URL=https://([^/]+)/.*#\1#')
echo | openssl s_client -connect 127.0.0.1:443 -servername "$H" 2>/dev/null | openssl x509 -noout -subject -ext subjectAltName
subject=CN = <public-ip>, O = sac X509v3 Subject Alternative Name: IP Address:<public-ip>, IP Address:<private-ip>, IP Address:127.0.0.1, DNS:localhost

Mathesar runs unprivileged in a sandbox that can write only to its upload directory. None of Mathesar, nginx or PostgreSQL can reach the Azure instance metadata service or the wire server, or write a core dump. A database address or import URL a user types therefore cannot be pointed at them:

systemctl show mathesar -p User -p ProtectSystem -p ReadWritePaths -p NoNewPrivileges -p IPAddressDeny -p LimitCORE
systemctl show nginx -p IPAddressDeny -p LimitCORE
systemctl show postgresql@16-main -p IPAddressDeny -p LimitCORE
IPAddressDeny=168.63.129.16/32 169.254.169.254/32 LimitCORE=0 User=mathesar ReadWritePaths=/var/lib/mathesar /var/lib/mathesar-data/media ProtectSystem=strict NoNewPrivileges=yes IPAddressDeny=168.63.129.16/32 169.254.169.254/32 LimitCORE=0 IPAddressDeny=168.63.129.16/32 169.254.169.254/32 LimitCORE=0

nginx passes Mathesar a fixed host name and the real client address from the connection. It never passes values a client sends, and it refuses cross-site requests that change something. Mathesar accepts only that host name:

grep -E 'proxy_set_header (Host|X-Forwarded-For|X-Real-IP)' /etc/nginx/mathesar-proxy.conf
grep -E 'sac_csrf = "MO"' /etc/nginx/mathesar-origin-guard.conf
sudo grep -E '^(ALLOWED_HOSTS|POSTGRES_HOST)=' /etc/mathesar/mathesar.env
proxy_set_header Host $mathesar_host; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $remote_addr; if ($sac_csrf = "MO") { return 403; } POSTGRES_HOST=/var/run/postgresql ALLOWED_HOSTS=<public-ip>

SSH refuses root logins, empty passwords and X11 forwarding. Password sign-in follows the choice you made when you deployed the VM:

sudo sshd -T | grep -E '^(permitrootlogin|permitemptypasswords|x11forwarding) '
permitrootlogin no x11forwarding no permitemptypasswords no

No per-VM secret reached a log, the journal, shell history, backups, /etc (other than the settings file), the application or the database files. Confirm it:

sudo mathesar-secret-leak-check.sh
LEAKCHECK_OK no per-instance secret in the journal, auth.log, syslog, cloud-init, nginx or PostgreSQL logs, shell history, MOTD, /var/backups, /etc (SECRET_KEY only in mathesar.env), the app and media trees, the PostgreSQL datadir (plaintext) or /tmp
9

Use your own DNS name and certificate

Point a DNS record (or the Azure public IP's DNS name label) at the VM, then set the name Mathesar is served as. This command mints a new self-signed certificate for the name. It updates nginx's host rule, Mathesar's allowed host and trusted origins, and the URL in /root/mathesar-credentials.txt. It then restarts Mathesar. The name is kept across reboots:

sudo mathesar-set-hostname <your-domain>

To use a certificate from your own certificate authority instead, copy it over the generated pair and reload nginx. Running mathesar-set-hostname again replaces it with a self-signed one:

sudo install -m 0644 <your-domain>.crt /etc/ssl/mathesar/mathesar.crt
sudo install -m 0600 <your-domain>.key /etc/ssl/mathesar/mathesar.key
sudo nginx -t && sudo systemctl reload nginx

Behind an Azure Application Gateway, use HTTPS to the backend on port 443, with /healthz as the health probe path. Set the VM's name to your domain with mathesar-set-hostname, because a request for a name the VM does not serve is dropped.

10

Manage accounts and passwords

Add people in Mathesar under Administration > Users (the gear menu). Each user gets a password you share with them, and either the administrator role or access to the databases you grant as a collaborator. People cannot create their own accounts.

Change the admin password on your profile page in Mathesar (/profile/), or from the shell. The tool prompts twice without echoing. The password never appears on a command line, in the logs or in your history:

sudo mathesar-set-admin-password admin

Then update or remove MATHESAR_ADMIN_PASSWORD in /root/mathesar-credentials.txt so it does not hold an old password.

Given a new user name, the same tool creates another administrator.

11

Connect databases

A new database on this VM. In Mathesar, choose Connect Database > Create a new PostgreSQL database on Mathesar's internal server. It is created in the VM's own PostgreSQL 16 on the data disk, owned by Mathesar's mathesar role. Mathesar can load its sample data sets into it.

An existing database elsewhere (Azure Database for PostgreSQL or another server). Choose Connect Database > Connect to an Existing Database, and give its host, port, database, role and password. Allow the VM's address in the database server's firewall on port 5432. Mathesar keeps the role password encrypted with this VM's secret key, so a database connection only works on the VM that saved it.

Your own tools on the local PostgreSQL. Connect through an SSH tunnel, because port 5432 is never opened to the network. Create a role with a password for the tool first (sudo -u postgres createuser --pwprompt <role>). Then run this from your computer:

ssh -L 5432:127.0.0.1:5432 azureuser@<public-ip>
12

Change the configuration

Mathesar reads /etc/mathesar/mathesar.env (mode 0640, owned by root, readable by the mathesar account). First boot writes it:

Setting Meaning Image default

SECRET_KEY Django secret key (signs sessions, encrypts saved database passwords) generated on first boot

ALLOWED_HOSTS / CSRF_TRUSTED_ORIGINS The served host name and this server's own HTTPS origins set by mathesar-set-hostname

POSTGRES_DB / POSTGRES_USER / POSTGRES_HOST Mathesar's metadata database mathesar_django / mathesar / /var/run/postgresql (peer authentication)

MEDIA_ROOT Uploaded import files /var/lib/mathesar-data/media/

EMAIL_BACKEND and EMAIL_HOST, EMAIL_PORT, EMAIL_HOST_USER, EMAIL_HOST_PASSWORD, EMAIL_USE_TLS, DEFAULT_FROM_EMAIL Outgoing mail not set: mail is discarded

To send password-reset mail, add EMAIL_BACKEND=django.core.mail.backends.smtp.EmailBackend and your SMTP values to the file. After a change, run sudo systemctl restart mathesar.

Never change SECRET_KEY on a running server. Sessions end, and saved connections to other databases stop working.

nginx accepts uploads of up to 512 MB (client_max_body_size in /etc/nginx/sites-available/sac-mathesar). It allows ten sign-in attempts a minute per address, with a short burst. Usage statistics to the Mathesar project are off, as upstream ships them.

13

Server components

Component Version Path

Mathesar 0.12.0 /opt/mathesar

Django 4.2.30 /opt/mathesar/venv

gunicorn 23.0.0 /opt/mathesar/venv/bin/gunicorn (127.0.0.1:8000, 3 workers)

PostgreSQL 16.15 /etc/postgresql/16/main (127.0.0.1:5432), data in /var/lib/mathesar-data/postgresql/16/main

nginx 1.24.0 /etc/nginx/sites-available/sac-mathesar

Python 3.12.3 /opt/mathesar/venv/bin/python

Ubuntu 24.04 LTS

Path Purpose

/etc/mathesar/mathesar.env Settings and secret key (Step 12)

/var/lib/mathesar-data The 32 GiB data disk: PostgreSQL data and uploads

/etc/ssl/mathesar/ Certificate and key

/root/mathesar-credentials.txt URL, user and password (mode 0600)

/usr/local/sbin/mathesar-firstboot.sh First boot

/usr/local/sbin/mathesar-set-hostname Host name and certificate

/usr/local/sbin/mathesar-set-admin-password Administrator passwords

/usr/local/sbin/mathesar-manage Runs Mathesar's manage.py with this VM's settings

/usr/local/sbin/mathesar-verify.sh End to end check (Step 7)

/usr/local/sbin/mathesar-secret-leak-check.sh Proves no secret reached a log

/opt/mathesar/venv/bin/gunicorn --version psql --version sudo journalctl -u mathesar-firstboot --no-pager -o cat | grep -E 'Serving as|First boot complete'
14

Grow the data disk

Stop the VM and resize the data disk. In the Azure Portal, select Disks, select the data disk, then select Size + performance. Or use the CLI. Then start the VM and grow the file system:

az vm deallocate --resource-group <your-resource-group> --name mathesar
az disk update --resource-group <your-resource-group> --name <data-disk-name> --size-gb 128
az vm start --resource-group <your-resource-group> --name mathesar
sudo resize2fs "$(findmnt -no SOURCE /var/lib/mathesar-data)"
15

Back up

Back up the PostgreSQL databases and the settings file into a directory that only root can read. The databases are Mathesar's metadata database and every database you created on the VM:

sudo install -d -o root -g root -m 0700 /var/backups/mathesar
sudo bash -c 'umask 077; sudo -u postgres pg_dumpall --clean > /var/backups/mathesar/pg-$(date -u +%Y%m%d).sql'
sudo bash -c 'umask 077; tar -czf /var/backups/mathesar/files-$(date -u +%Y%m%d).tar.gz /etc/mathesar /var/lib/mathesar-data/media'

The settings file holds the secret key that encrypts saved database passwords, so keep the backup as private as the server.

Copy the files off the VM, for example to Azure Blob Storage. Or snapshot the data disk with Azure Backup or disk snapshots.

Troubleshooting

  • The browser closes the connection. nginx drops requests for host names it does not serve. Use the address in MATHESAR_URL or the VM's private address, or set your own name with mathesar-set-hostname (Step 9).
  • "CSRF verification failed" when signing in. Open Mathesar by one of the names the server serves (see CSRF_TRUSTED_ORIGINS in /etc/mathesar/mathesar.env). After adding a DNS name, run mathesar-set-hostname with it.
  • HTTP 403 from an API call made by another web application. nginx refuses requests that change something when a browser sends them from another site. Call the API from a server-side client, which sends no Origin, or serve the calling page from the same name.
  • HTTP 429 when signing in. There were too many sign-in attempts from your address. Wait a minute.
  • Cannot connect an external database. Check that the database server's firewall allows this VM on 5432, and that it accepts TLS or password connections from the VM's address.
  • Nothing answers after deployment. Check systemctl status mathesar-firstboot and journalctl -u mathesar-firstboot. Until first boot completes, the server is deliberately closed.

Security notes

  • Allow ports 22, 80 and 443 in the network security group from your own networks only.
  • Keep the administrator password secret, change it if it may have been shared, and give each person their own account with only the databases they need (Step 10).
  • Use your own DNS name and a certificate your users trust (Step 9).
  • Keep the operating system patched. Unattended security upgrades are on, and sudo apt-get update && sudo apt-get upgrade applies everything else.
  • Mathesar 0.12.0 pins Django 4.2, and the 4.2 long-term support line has ended. The image runs the last 4.2 release, 4.2.30. The Django advisories published since then affect features this image does not use: the cache middleware, GeoDjango, signed cookies and the domain-name validator, which 4.2 does not have. Likewise, the Pillow and fsspec advisories without a fix in their installed lines are reachable only through Mathesar's file-attachment storage backends, which are not configured. The image's build checks each of these and fails if one becomes reachable. Move to a newer Mathesar release when one is published.
  • Back up regularly (Step 15).

Support and licensing

Mathesar is open source under the GNU General Public License v3. There is no per-user or per-CPU license fee. Its source code is at https://github.com/mathesar-foundation/mathesar (release 0.12.0). The SecureAnyCloud charge covers hardening, first-boot setup, image maintenance and support.

Mathesar is a trademark of its respective owners. SecureAnyCloud is not affiliated with or endorsed by the Mathesar Foundation.

  • Email: sales@secureanycloud.com
  • Website: https://secureanycloud.com

Deploy on Azure

Launch Mathesar on Ubuntu 24.04 LTS on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com

Deploy on Azure