Databases Azure

MonetDB on Ubuntu 24.04 on Azure User Guide

Last updated: 2026-10-25 | Product: MonetDB 11.55.9 on Ubuntu 24.04 LTS on Azure

Overview

MonetDB is an open source relational database built for analytics. A traditional row store reads whole records off disk even when your query only mentions two columns; MonetDB stores each column separately and touches only the ones you ask for. That is why aggregations, grouping and window functions over large tables run so much faster on the same hardware.

It speaks standard SQL, so your existing queries, tools and skills carry straight over, and it ships with mclient (an interactive SQL terminal), msqldump (a SQL dump utility), and client libraries for Python, Java, R, PHP, Ruby, ODBC and JDBC.

The cloudimg image delivers MonetDB 11.55.9 (Dec2025-SP4) on Ubuntu 24.04, served over TLS on port 50000, with a unique database password generated on the first boot of your VM. Backed by 24/7 cloudimg support.

What is included:

MonetDB 11.55.9 (Dec2025-SP4) from the official MonetDB apt repository, pinned and verified against the vendor's signing key
The MAPI/SQL port served over TLS on port 50000, with a certificate generated for your VM at first boot
A durable database area at /var/monetdb5/dbfarm, owned by the unprivileged monetdb service account
A per-VM database password in one file, /root/monetdb-credentials.txt, readable only by root
monetdb-mclient and monetdb-rotate-password helpers that never put the password on a command line
MonetDB and the TLS proxy run sandboxed and cannot reach the Azure Instance Metadata Service or the WireServer
On-VM self-tests that prove the security posture at any time

A note on TLS

MonetDB does not implement TLS itself; upstream's own guidance is to put a TLS termination proxy in front of it, and that is exactly what this image does. The database listens only on 127.0.0.1:50001 (and [::1]:50001), and stunnel terminates TLS on port 50000, the port MonetDB clients expect. You connect with a monetdbs:// URL rather than monetdb://, and pin this VM's certificate with its fingerprint, as the examples below show.

A note on the default credential

MonetDB documents that every newly created database gets the user monetdb with the password monetdb. This image never creates that pair. No database exists in the image at all. On your VM's first boot the demo database is created in maintenance mode and initialised with a password generated for that VM, handed to the server on standard input, before the database ever serves a client.

A note on secrets and command lines

A password typed on a command line is visible to other processes and is written by sudo into /var/log/auth.log and the system journal. Nothing in this image does that: first boot passes the password to MonetDB on standard input, monetdb-mclient hands it to mclient in a temporary root-only file that is deleted when mclient exits, and the monetdbd administrative passphrase is stored only as a hash. Step 9 shows how to check this yourself.

Prerequisites

An Azure subscription
SSH access to the VM (port 22), plus port 50000 from the address range you will run SQL from
The MonetDB client (mclient) on your workstation if you want to connect remotely, or just use the one already on the VM

Step 1: Deploy from the Azure Marketplace

Search the Azure Marketplace for MonetDB on Ubuntu 24.04 LTS by cloudimg, choose Standard_B2ms or larger, and allow inbound 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 point; move up the memory-optimised sizes as your working set grows.

Step 2: Deploy from the Azure CLI

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn cloudimg1647283583153:monetdb-ubuntu-24-04:default:latest

az vm create \
  --resource-group my-resource-group \
  --name my-monetdb \
  --image cloudimg1647283583153: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

Step 3: Retrieve this VM's database password

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.

sudo cat /root/monetdb-credentials.txt

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

MonetDB per-VM credentials file

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), and the passphrase is generated per VM and stored only as a {SHA512} hash in the database area; its plain text is discarded at first boot, so it is not in the credentials file.

Step 4: Confirm the database is running

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.

Check what is reachable from outside the VM:

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

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

MonetDB service status and listening sockets

Step 5: Connect with mclient

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 one for you from the credentials file in a temporary root-only location, runs mclient, and deletes it 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, which is exactly what a remote client does, point mclient at a monetdbs:// URL pinned to this VM's certificate. The password still only travels 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"
'

Step 6: Create a table and load data

MonetDB uses standard SQL for schema definition:

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 dramatically 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 the rows really 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 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 will read it for you.

Step 7: Run an analytic query

This is what the 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 columnar analytics query

MonetDB can also show you 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;"

Step 8: Connect from your workstation

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 speaks the same TLS URL. Read the password from your environment or a secrets store rather than 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())

Step 9: Verify the security posture

The image ships self-tests you can re-run at any time. Each individual check is itself proven against a known-bad input first, 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

monetdb-verify-auth.sh confirms on the TLS front door 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 security posture self-tests

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. You can see 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/cloudimg-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

And you can 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

Step 10: Back up and restore

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

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
'

Restore by feeding a dump back to mclient. Point it at whichever 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"'

Step 11: Use your own TLS certificate

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

Step 12: Rotate the database password

monetdb-rotate-password generates a new 32-character password, writes the ALTER USER statement to a root-only temporary file that mclient reads by path, rewrites the credentials file, and then 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 (your workstation's ~/.monetdb, application settings) need the new value from /root/monetdb-credentials.txt.

Support

cloudimg provides 24/7 support for this image: deployment, upgrades, TLS certificates and custom domains, bulk data loading, schema design, query tuning and scaling.

MonetDB is a trademark of its respective owner. All product and company names are trademarks or registered trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.