MonetDB

Search the Azure Marketplace for MonetDB on Ubuntu 24.04 LTS by SecureAnyCloud. Choose Standard_B2ms or larger, and allow inbound ports 22 and 50000. After deployment, restrict both ports to your own address range (Step 2 shows the command).

MonetDB is developed from a main-memory perspective, so RAM is the resource that matters most. Standard_B2ms (2 vCPU, 8 GiB) is a sensible starting size. Move up through the memory-optimised sizes as your working set grows.

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:monetdb-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-resource-group \
  --name my-monetdb \
  --image sac1647283583153:monetdb-ubuntu-24-04:default:latest \
  --size Standard_B2ms \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard

Then open the SQL port only to the address range you will connect from. Replace <your-cidr> with your own network, for example 203.0.113.0/24.

az network nsg rule create \
  --resource-group my-resource-group \
  --nsg-name my-monetdbNSG \
  --name allow-monetdb-tls \
  --priority 1001 \
  --protocol Tcp \
  --destination-port-ranges 50000 \
  --source-address-prefixes <your-cidr> \
  --access Allow

Every VM launched from this image generates its own credentials on first boot and writes them to a single root-only file. Nothing is shared between deployments, and no other copy of the password exists on the VM. Read the file with sudo:

sudo cat /root/monetdb-credentials.txt

The file shows the database user, its password, the database name, this VM's public address, and the TLS certificate fingerprint (MONETDB_TLS_CERTHASH). You need the fingerprint to connect from another machine, because the certificate is generated on your VM and is not signed by a public certificate authority.

The monetdbd daemon also has an administrative passphrase for its control channel, which can create and destroy databases. Remote control is disabled on this image (control=no). First boot generates the passphrase per VM and stores it only as a {SHA512} hash in the database area. The plain text is discarded, so it is not in the credentials file.

systemctl is-active monetdbd monetdb-tls monetdb-firstboot mserver5 --version | head -1 sudo -u monetdb monetdb -p 50001 status

All three units report active, the server reports 11.55.9 (Dec2025-SP4), and the demo database shows state R for running.

Next, check what is reachable from outside the VM:

sudo ss -Hltn | awk '{print $4}' | sort -u

Only ports 22 and 50000 are bound to a public address. MonetDB itself listens on 127.0.0.1:50001 and [::1]:50001, behind the TLS proxy.

mclient has no --password option. The only non-interactive way to supply a password is a small configuration file that MonetDB calls a dotfile (.monetdb, two lines: user= and password=).

On the VM, monetdb-mclient builds the dotfile for you from the credentials file in a temporary root-only location, runs mclient, and deletes the dotfile afterwards:

sudo monetdb-mclient -s "SELECT 'connected' AS status, current_user AS whoami;"

To go through the TLS front door on port 50000 instead of the loopback port, as a remote client does, point mclient at a monetdbs:// URL pinned to this VM's certificate. The password still travels only through a 0600 dotfile:

sudo bash -c ' set -eu CREDS=/root/monetdb-credentials.txt T=$(mktemp -d /run/monetdb-guide.XXXXXX); chmod 700 "$T"; umask 077 sed -n "s/^MONETDB_PASSWORD=\(.*\)$/user=monetdb\npassword=\1/p" "$CREDS" > "$T/.monetdb" CH=$(sed -n "s/^MONETDB_TLS_CERTHASH=//p" "$CREDS") DOTMONETDBFILE="$T/.monetdb" mclient \
  -d "monetdbs://127.0.0.1:50000/demo?certhash=sha256:$CH" \
  -s "SELECT 11.55 AS version, current_user AS whoami;" rm -rf "$T" '

MonetDB uses standard SQL for schema definition. Create a table:

sudo monetdb-mclient -s " DROP TABLE IF EXISTS quarterly_sales; CREATE TABLE quarterly_sales ( id INTEGER, sold_at DATE, region VARCHAR(32), product VARCHAR(64), qty INTEGER, amount DECIMAL(12,2) );"

COPY INTO is MonetDB's bulk loader and is much faster than individual INSERT statements. Reading from STDIN keeps everything in one statement:

sudo monetdb-mclient -s " COPY 6 RECORDS INTO quarterly_sales FROM STDIN USING DELIMITERS ',', E'\n'; 1,2026-01-15,EMEA,widget,10,199.50 2,2026-01-16,AMER,widget,4,79.80 3,2026-01-16,EMEA,gadget,7,349.00 4,2026-02-02,APAC,widget,12,239.40 5,2026-02-11,EMEA,gadget,3,149.55 6,2026-02-19,AMER,sprocket,20,880.00 "

Confirm that the rows landed:

sudo monetdb-mclient -s "SELECT COUNT(*) AS rows_loaded FROM quarterly_sales;"

To load from a file on the VM instead, give an absolute path that the monetdb service account can read, and use COPY INTO ... FROM '/path/to/data.csv'. To load a file that lives on your machine rather than the server, add ON CLIENT, and mclient reads the file for you.

This query is what a column store is for: an aggregation with a window function over it.

sudo monetdb-mclient -s " SELECT region, SUM(amount) AS revenue, SUM(qty) AS units, RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk FROM quarterly_sales GROUP BY region ORDER BY revenue DESC;"

AMER totals 959.80, EMEA 698.05 and APAC 239.40. Because each column is stored separately, this query reads only region, qty and amount, never the whole row.

MonetDB can also show how long the server spent on a query, which is useful when tuning:

sudo monetdb-mclient -t performance -s "SELECT region, SUM(amount) FROM quarterly_sales GROUP BY region;"

On your own machine, put the user and password from Step 3 in a .monetdb dotfile that only you can read. Then connect with the monetdbs:// URL and the certificate fingerprint. Replace <vm-ip> and <MONETDB_TLS_CERTHASH> with the values from your VM's credentials file:

umask 077 printf 'user=monetdb\npassword=%s\n' "$(read -rsp 'MonetDB password: ' p; echo "$p")" > ~/.monetdb chmod 600 ~/.monetdb mclient -d "monetdbs://<vm-ip>:50000/demo?certhash=sha256:<MONETDB_TLS_CERTHASH>" \
  -s "SELECT COUNT(*) FROM quarterly_sales;"

mclient reads ~/.monetdb automatically, or the file named by $DOTMONETDBFILE, so the password never appears on your command line or in your shell history.

The fingerprint is needed because the certificate is generated on your VM. If you install a certificate from a public certificate authority (Step 11), drop the ?certhash= parameter, and the ordinary verified TLS path applies.

From Python

The pymonetdb driver accepts the same TLS URL. Read the password from your environment or a secrets store instead of writing it into code:

import os, pymonetdb conn = pymonetdb.connect( "monetdbs://<MONETDB_PUBLIC_IP>:50000/demo?certhash=sha256:<MONETDB_TLS_CERTHASH>", username="monetdb", password=os.environ["MONETDB_PASSWORD"]) cur = conn.cursor() cur.execute("SELECT region, SUM(amount) FROM quarterly_sales GROUP BY region") print(cur.fetchall())

The image ships self-tests that you can re-run at any time. Each individual check is first proven against a known-bad input, so a check that always passes cannot go unnoticed.

sudo /usr/local/sbin/monetdb-selftest.sh

Individually:

sudo /usr/local/sbin/monetdb-port-check.sh
sudo /usr/local/sbin/monetdb-daemon-check.sh
sudo /usr/local/sbin/monetdb-verify-auth.sh /root/monetdb-credentials.txt
sudo /usr/local/sbin/monetdb-gate-selftest.sh

On the TLS front door, monetdb-verify-auth.sh confirms that the published monetdb/monetdb pair is refused, that blank and weak passwords are refused, that this VM's own password returns real rows, and that both a plaintext connection and an untrusted certificate are rejected.

MonetDB and the TLS proxy are blocked from the Azure Instance Metadata Service (169.254.169.254) and the WireServer (168.63.129.16), so a SQL statement that makes the server open a connection (such as a remote table) can never reach them. The Azure agent and cloud-init keep their access. Inspect the rule and prove it from inside the service's own control group, where a loopback connection still works:

systemctl show -p IPAddressDeny monetdbd.service monetdb-tls.service
sudo bash -c 'echo $$ > /sys/fs/cgroup$(systemctl show -p ControlGroup --value monetdbd.service)/cgroup.procs; exec /usr/local/sbin/sac-egress-probe monetdbd.service'

Both services run as unprivileged accounts with no Linux capabilities and a read-only view of the system:

systemctl show -p User -p NoNewPrivileges -p ProtectSystem -p ProtectHome monetdbd.service monetdb-tls.service

You can also confirm that your password has never been written to the authentication log or the system journal:

sudo bash -c ' P=$(mktemp /run/monetdb-check.XXXXXX); J=$(mktemp /run/monetdb-check.XXXXXX); chmod 600 "$P" "$J" sed -n "s/^MONETDB_PASSWORD=//p" /root/monetdb-credentials.txt > "$P" journalctl -o cat --no-pager > "$J" echo "journal lines searched: $(wc -l < "$J")" echo "password found in /var/log/auth.log: $(grep -cF -f "$P" /var/log/auth.log) lines" echo "password found in the journal: $(grep -cF -f "$P" "$J") lines" rm -f "$P" "$J" '

The daemon's administrative control channel is disabled over the network and exists only as a local socket:

sudo -u monetdb monetdbd get control,discovery,listenaddr,port /var/monetdb5/dbfarm

msqldump writes a complete SQL dump of a database. Like mclient, it reads the password from a dotfile. Create a dump:

sudo bash -c ' set -eu T=$(mktemp -d /run/monetdb-guide.XXXXXX); chmod 700 "$T"; umask 077 sed -n "s/^MONETDB_PASSWORD=\(.*\)$/user=monetdb\npassword=\1/p" /root/monetdb-credentials.txt > "$T/.monetdb" DOTMONETDBFILE="$T/.monetdb" msqldump -h 127.0.0.1 -p 50001 -d demo > /var/backups/demo-$(date +%F).sql rm -rf "$T" ls -lh /var/backups/demo-*.sql '

To restore, feed a dump back to mclient. Point it at the dump you want to restore:

sudo monetdb-mclient <backup-dir>/demo-2026-10-25.sql

For a physical backup, stop the database and copy its directory out of the database area:

sudo bash -c 'du -sh /var/monetdb5/dbfarm/demo; echo "stop the database first with: sudo -u monetdb monetdb -p 50001 stop demo"'

The image generates a self-signed certificate for your VM at first boot, which is why remote clients need the fingerprint. If you have a certificate from a public certificate authority, install it and restart the TLS front door. Clients then connect with a plain monetdbs:// URL and no fingerprint at all.

# Replace the per-VM pair with your own certificate and key, then restart.
sudo install -m 0644 -o root -g stunnel4 \
  /etc/letsencrypt/live/<your-domain>/fullchain.pem /etc/monetdb/tls/server.crt
sudo install -m 0640 -o root -g stunnel4 \
  /etc/letsencrypt/live/<your-domain>/privkey.pem /etc/monetdb/tls/server.key
sudo systemctl restart monetdb-tls.service

monetdb-rotate-password generates a new 32-character password and writes the ALTER USER statement to a root-only temporary file that mclient reads by path. It then rewrites the credentials file and proves that the new password works and the old one is refused.

Neither password ever appears on a command line, so neither reaches auth.log or the journal.

sudo monetdb-rotate-password
sudo monetdb-mclient -s "SELECT 'rotated' AS status;"

Clients that keep their own copy of the password, such as your workstation's ~/.monetdb or application settings, need the new value from /root/monetdb-credentials.txt.