Mathesar 0.12.0

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 select your Region. Then choose 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.

To launch with the AWS CLI instead, 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}]'

Connect over SSH. The login user for Mathesar 0.12.0 on Ubuntu 24.04 is ubuntu:

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

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. Check the units:

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",...))

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 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. It uses 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.

Open the MATHESAR_URL in your browser. The certificate is self-signed, so your browser asks you to confirm it the first time. Alternatively, install your own certificate (see Step 9).

Sign in as admin with the password from Step 4.

The home page lists the databases that 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. You can also connect to a database that already exists elsewhere. The database in the screenshots was created with the library sample data.

A database shows its schemas.

A table opens in the data grid, with the inspector on the right. In the grid, you filter, sort, group and edit records and follow relationships.

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" "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

The image ships an end-to-end check that 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.

The check confirms that:

• 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, and the JSON-RPC API answers it.
• A request from another web site riding that session is refused.

The check signs out afterwards. Run:

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

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 = sac 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. None of Mathesar, nginx or PostgreSQL can reach the instance metadata service, so a database address or import URL that 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 that a client sends. 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

Point a DNS record at the instance, then set the name that 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 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

To run 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.

Add people in Mathesar under Administration > Users (the gear menu). Each user gets a password that 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 that the file does not hold an old password.

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

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. A database connection therefore only works on the instance that saved it.

Your own tools on the local PostgreSQL. Connect 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, run:

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

Mathesar reads /etc/mathesar/mathesar.env (mode 0640, owned by root, readable by the mathesar account). First boot writes it. The settings and their image defaults are:

• SECRET_KEY: the Django secret key, which signs sessions and 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. Defaults are mathesar_django / mathesar / /var/run/postgresql (peer authentication).
• MEDIA_ROOT: uploaded import files. Default is /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, so 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/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. An administrator can turn them on under Administration > Settings (Usage data).

Components, with their versions and paths:

• 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/sac-mathesar
• Python 3.12.3: /opt/mathesar/venv/bin/python
• Ubuntu 24.04 LTS

Paths and their purpose:

• /etc/mathesar/mathesar.env: settings and secret key (see 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 (see Step 7)
• /usr/local/sbin/mathesar-secret-leak-check.sh: proves that 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'

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.