Analytics AWS

Mathesar 0.12.0 on AWS User Guide

Last updated: 2026-10-23 | Product: Mathesar 0.12.0 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and using the Mathesar 0.12.0 on Ubuntu 24.04 AMI from cloudimg on Amazon EC2. It is one version of the cloudimg BI and web analytics listing on AWS Marketplace; you pick it as the software version when you subscribe and launch.

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, follow relationships between tables, build explorations (saved queries) and forms, and manage schemas, tables, columns and constraints from the browser, while the data stays in standard PostgreSQL tables underneath. Access is controlled per database through PostgreSQL roles and Mathesar collaborators.

The cloudimg image ships it the secure way:

HTTPS from the first boot. nginx serves Mathesar on port 443 with a certificate generated on your instance's first boot. Port 80 redirects to HTTPS.
An administrator unique to your instance. First boot creates the admin account with a password generated on the instance and written to /root/mathesar-credentials.txt; Mathesar stores only a salted PBKDF2 hash of it. The Django secret key that signs sessions is generated on the instance 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. This image closes it in Mathesar itself and in nginx; accounts are created by an administrator (or by single sign-on, if you configure it).
PostgreSQL created on your instance. No database ships in the image. First boot creates the PostgreSQL 16 cluster, Mathesar's metadata database and its role. PostgreSQL listens on 127.0.0.1:5432 and the unix socket only, and 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, 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 instance metadata service; the AMI requires IMDSv2; requests for any host name the instance does not serve are dropped; nginx refuses state-changing requests a web browser sends on behalf of another site; sign-in attempts are rate limited.
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 pinned by version and checksum), gunicorn, nginx, PostgreSQL 16, Ubuntu 24.04 LTS, cloud-init and the AWS Systems Manager agent. No database and no account ship in the image.

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 with a route to the internet (or a private route you use for management).
A security group that allows TCP 22 (SSH), TCP 443 (Mathesar) and TCP 80 (redirects to HTTPS) from your own networks only. Never open 5432: PostgreSQL is reachable on the instance only.

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick Mathesar 0.12.0 on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large, your key pair and a security group as above. Size the root EBS volume for the data you plan to keep: Mathesar's metadata database and every database you create from the web interface live in PostgreSQL on the root volume (you can grow it later).

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 \
  --block-device-mappings 'DeviceName=/dev/sda1,Ebs={VolumeSize=30,VolumeType=gp3}' \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=mathesar}]'

Step 2: Connect to your instance

Version	SSH user
Mathesar 0.12.0 on Ubuntu 24.04	`ubuntu`

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

Step 3: Check the services

First boot creates the database cluster, the secret key, the administrator and the certificate, starts Mathesar, opens nginx and proves a full sign-in round trip before it finishes (about a minute):

systemctl is-active postgresql@16-main mathesar nginx mathesar-firstboot
sudo test -f /var/lib/cloudimg/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",...))

Step 4: Get your URL and password

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

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

MATHESAR_URL=https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/
MATHESAR_ADMIN_USER=admin
MATHESAR_ADMIN_PASSWORD=<MATHESAR_ADMIN_PASSWORD>

The URL uses the instance's public DNS name from EC2 instance metadata (the public IP address when the subnet has no public DNS names). The certificate also names the public and private IP addresses, so https://<public-ip>/ works too. To serve Mathesar under your own DNS name, see Step 9.

Step 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 (or install your own certificate, Step 9). Sign in as admin with the password from Step 4:

The Mathesar sign-in page

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 instance (optionally loaded with Mathesar's sample data) or to connect to a database that already exists elsewhere. The database below was created with the library sample data for the screenshots:

The Mathesar home page with one database, library_demo, on the instance's own PostgreSQL socket

A database shows its schemas:

The library_demo database with the Library Management schema of seven tables and the public schema

and a table opens in the data grid, where you filter, sort, group, edit records and follow relationships, with the inspector on the right:

The Books table in the Mathesar data grid with the table inspector showing its relationships to Authors, Publishers and Items

Step 6: Check it from the command line

Port 80 redirects to HTTPS, an anonymous visitor is sent to the sign-in page, the sign-up page does not exist, and the health check stays open for load balancers (it is answered by nginx and 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" "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://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/
no login -> 302 https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/auth/login/?next=/
signup   -> HTTP 404
healthz  -> HTTP 200

Step 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. It reads the credentials file itself and drives the real web flow over HTTPS through nginx, with the instance's certificate pinned: an anonymous visitor is refused, a sign-in without the CSRF token is refused, a wrong password gets no session, the right password gets a secure session, the JSON-RPC API answers it, and a request from another web site riding that session is refused. It signs out afterwards:

sudo mathesar-verify.sh

port 80 -> 301 https://ec2-<public-ip-dashed>.<region>.compute.amazonaws.com/ ; an unknown Host is dropped on 80 and 443 ; certificate verifies for ec2-<public-ip-dashed>.<region>.compute.amazonaws.com
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

Step 8: Security checks

The files that matter are locked down; the settings file holds this instance's Django secret key:

sudo stat -c '%a %U:%G %n' /etc/mathesar/mathesar.env /root/mathesar-credentials.txt /var/lib/mathesar/media /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/media
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 instance and names its own addresses:

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 = ec2-<public-ip-dashed>.<region>.compute.amazonaws.com, O = cloudimg
X509v3 Subject Alternative Name:
    DNS:ec2-<public-ip-dashed>.<region>.compute.amazonaws.com, 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, and none of Mathesar, nginx or PostgreSQL can reach the instance metadata service (so a database address or import URL a user types cannot be pointed at it):

systemctl show mathesar -p User -p ProtectSystem -p ReadWritePaths -p NoNewPrivileges -p IPAddressDeny
systemctl show nginx -p IPAddressDeny
systemctl show postgresql@16-main -p IPAddressDeny

IPAddressDeny=fd00:ec2::254/128 169.254.169.254/32 168.63.129.16/32
User=mathesar
ReadWritePaths=/var/lib/mathesar
ProtectSystem=strict
NoNewPrivileges=yes
IPAddressDeny=fd00:ec2::254/128 169.254.169.254/32 168.63.129.16/32
IPAddressDeny=fd00:ec2::254/128 169.254.169.254/32 168.63.129.16/32

nginx passes Mathesar a fixed host name and the real client address from the connection, never values a client sends, and Mathesar accepts only that host name:

grep -E 'proxy_set_header (Host|X-Forwarded-For|X-Real-IP)' /etc/nginx/mathesar-proxy.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;
POSTGRES_HOST=/var/run/postgresql
ALLOWED_HOSTS=ec2-<public-ip-dashed>.<region>.compute.amazonaws.com

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

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

Step 9: Your own DNS name and certificate

Point a DNS record at the instance, then set the name Mathesar is served as. This mints a new self signed certificate for the name, updates nginx's host rule, Mathesar's allowed host and trusted origins and the URL in /root/mathesar-credentials.txt, restarts Mathesar and keeps the name 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 Application Load Balancer with a certificate from AWS Certificate Manager, register the instance on port 443 (HTTPS), use /healthz as the health check path, and set the instance's name to your domain with mathesar-set-hostname; a request for a name the instance does not serve is dropped.

Step 10: 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.

Step 11: Databases

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

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

Connect your own tools to the local PostgreSQL through an SSH tunnel; 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 from your computer:

ssh -i <your-key.pem> -L 5432:127.0.0.1:5432 ubuntu@<public-ip>

Step 12: 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/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 (for example Amazon SES SMTP credentials) 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/cloudimg-mathesar) and 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; an administrator can turn them on under Administration > Settings (Usage data).

Step 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)
nginx	1.24.0	`/etc/nginx/sites-available/cloudimg-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/media`	Uploaded import files
`/var/lib/postgresql/16/main`	PostgreSQL data (metadata database and your databases)
`/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 instance'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'

Step 14: Back up

Back up the PostgreSQL databases (Mathesar's metadata database and every database you created on the instance) and the settings file, into a root only directory:

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/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 instance (for example to Amazon S3 with server-side encryption), or snapshot the root volume.

Troubleshooting

The browser closes the connection. nginx drops requests for host names it does not serve. Use the name in MATHESAR_URL, the instance's IP addresses, 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 429 when signing in. Too many sign-in attempts from your address; wait a minute.
Cannot connect an external database. Check that the database's security group allows this instance on 5432 and that the database accepts TLS or password connections from the instance's address.
Nothing answers after launch. Check systemctl status mathesar-firstboot and journalctl -u mathesar-firstboot. Until first boot completes the server is deliberately closed.

Security recommendations

Allow ports 22, 80 and 443 in the 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 (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 14).

Support and licensing

Mathesar is open source under the GNU General Public License v3; there is no per user or per CPU licence fee. Its source code is at https://github.com/mathesar-foundation/mathesar (release 0.12.0). The AMI price covers cloudimg's hardening, first boot setup and support. Mathesar is a trademark of its respective owners; cloudimg is not affiliated with or endorsed by the Mathesar Foundation.

Email: support@cloudimg.co.uk
Website: https://www.cloudimg.co.uk