M1
Databases AWS

MonetDB 11.55.9 on AWS User Guide

| Product: MonetDB 11.55.9 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and using MonetDB 11.55.9 (Dec2025-SP4) on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of cloudimg's distributed and embedded SQL databases listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other databases in the listing are described on the listing's guide hub.

MonetDB is an open source column-store relational database built for analytics. A row store reads whole records even when a query mentions two columns; MonetDB stores each column separately and reads only the columns a query uses, so aggregations, grouping and window functions over large tables are fast on ordinary hardware. It speaks standard SQL and comes with mclient (an interactive SQL terminal) and msqldump; client libraries exist for Python (pymonetdb), Java (JDBC), ODBC, R and more.

What the cloudimg image adds:

  • No default login, a password unique to your instance. Upstream MonetDB gives every new database the administrator monetdb with the password monetdb. This image ships no database at all: first boot creates the database demo with a 32 character password generated on your instance and handed to the server on standard input at the moment the database is created, so the pair monetdb/monetdb never exists, not even for a second, and first boot proves it is refused. The password is written to the root only file /root/monetdb-credentials.txt, never to a command line, a log or the journal.
  • The database bound to localhost. MonetDB's daemon (monetdbd) listens on 127.0.0.1:50000 and [::1]:50000 only, and the database servers it starts listen on no network port at all. From the network, only SSH is reachable; you reach the database through an SSH tunnel (Step 8). The daemon's remote control channel (which can create and destroy databases) is off, and its passphrase is unique to your instance.
  • An optional TLS door with its own certificate. MonetDB does not implement TLS itself; upstream's guidance is to put a TLS proxy in front of it. The image has one ready: monetdb-tls.service (stunnel) serves TLS 1.2 or later on port 50443 with a certificate generated on your instance at first boot, and forwards to the local database. It is off until you switch it on (Step 9).
  • Hardened services. MonetDB runs as the unprivileged monetdb user with no capabilities, a read-only view of the system and no access to /root (and MonetDB itself refuses to run as root); the TLS door runs as the unprivileged stunnel4 user. MonetDB, its first boot service and the TLS door are blocked from the instance metadata service (MonetDB can connect to an address you name in SQL, for example with CREATE REMOTE TABLE), and the AMI requires IMDSv2. The legacy SysV monetdb-sql start-up script, which would create a second, unprotected database area, is disabled. SSH accepts keys only, and fwupd's automatic firmware-metadata refresh is switched off (EC2 has no firmware it can update).
  • Your data on its own disk. The database area (/var/monetdb5) is on a separate 20 GiB EBS volume you can grow or snapshot on its own.
  • Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on. MonetDB is installed from MonetDB's own signed package repository, with the signing key pinned by fingerprint.

What is on the image: MonetDB 11.55.9 (monetdb-server, monetdb-sql, monetdb-client from MonetDB's repository; the daemon runs as monetdbd.service), a helper monetdb-mclient that signs mclient in for you, stunnel for the optional TLS door, Ubuntu 24.04 LTS, cloud-init and the AWS Systems Manager agent. The database demo is empty, ready for your tables.

Default user: ubuntu · Architecture: x86_64 · Recommended instance type: m5.large

What this is not

MonetDB has no web interface; you work with it through SQL clients. This is a single instance MonetDB; MonetDB's distributed features (remote and replica tables across several servers) are not set up. MonetDB is designed for analytical workloads: it is at its best with large tables, bulk loads and read-mostly queries, and it uses as much memory as you give it, so choose the instance size for your working set.

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) from your admin network. MonetDB listens on the loopback address only, so no database port needs to be open; you reach it through an SSH tunnel (Step 8), or you can open the TLS door to your own network later (Step 9).

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick MonetDB 11.55.9 on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large (or a larger, memory-rich type for analytical work), your key pair and a security group as above. The AMI brings two volumes: the root volume and a 20 GiB data volume (/dev/sdf) for /var/monetdb5. Make the data volume as large as your data needs at launch.

With the AWS CLI (replace the values in angle brackets; this example grows the data volume to 100 GiB):

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> \
  --block-device-mappings 'DeviceName=/dev/sdf,Ebs={VolumeSize=100,VolumeType=gp3}' \
  --metadata-options HttpTokens=required,HttpEndpoint=enabled \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=monetdb}]'

If you make the data volume larger at launch, grow the file system once after first boot with sudo resize2fs "$(findmnt -no SOURCE /var/monetdb5)". First boot takes under a minute once the instance is running.

Step 2: Connect to your instance

Version SSH user
MonetDB 11.55.9 on Ubuntu 24.04 ubuntu 
ssh -i <your-key.pem> ubuntu@<public-ip>

Step 3: Check the services

The first boot service and the MonetDB daemon should be active; the TLS door is installed but switched off (systemctl is-enabled exits non-zero for a disabled unit, hence || true):

systemctl is-active monetdb-firstboot.service monetdbd.service
systemctl is-enabled monetdb-tls.service || true

active
active
disabled

MonetDB listens on the loopback address only; from the network, only SSH is reachable:

sudo ss -Htln

LISTEN 0      4096       127.0.0.1:50000 0.0.0.0:*
LISTEN 0      4096   127.0.0.53%lo:53    0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22    0.0.0.0:*
LISTEN 0      4096      127.0.0.54:53    0.0.0.0:*
LISTEN 0      4096           [::1]:50000    [::]:*
LISTEN 0      4096            [::]:22       [::]:*

50000 is MonetDB's SQL port (the MAPI protocol); the port 53 lines are Ubuntu's local DNS resolver, also loopback only. The daemon lists the database it manages:

sudo -u monetdb monetdb status

name  state   health                    remarks
demo  R  2m  100%  1s  mapi:monetdb://ip-172-31-89-73:50000/demo

Step 4: Get your password

Your connection details are in /root/monetdb-credentials.txt:

sudo grep -E '^MONETDB_(USER|DATABASE|HOST|PORT|TLS_PORT|TLS_CERTHASH)=' /root/monetdb-credentials.txt

MONETDB_USER=monetdb
MONETDB_DATABASE=demo
MONETDB_HOST=127.0.0.1
MONETDB_PORT=50000
MONETDB_TLS_PORT=50443
MONETDB_TLS_CERTHASH=sha256:cf43b20cc0b6a0025ccbd71d673388b1b9e8f220b28045b117ab3642d11a6ba7

Show the password itself when you need it (for a client on your own computer):

sudo grep '^MONETDB_PASSWORD=' /root/monetdb-credentials.txt

monetdb is the administrator of the database demo. MONETDB_TLS_CERTHASH is the fingerprint of this instance's TLS certificate, which clients use to trust the TLS door (Step 9).

Step 5: Connect with mclient

mclient has no password option: it reads the password from a small file. monetdb-mclient runs mclient against demo on 127.0.0.1:50000 with the password from the credentials file, handed over in a temporary root-only file that is removed afterwards:

sudo monetdb-mclient -s "SELECT value FROM sys.env() WHERE name IN ('monet_version', 'monet_release');"

+-------------+
| value       |
+=============+
| 11.55.9     |
| Dec2025-SP4 |
+-------------+
2 tuples

Run sudo monetdb-mclient with no options for an interactive session (\q quits, \? lists the commands).

Step 6: Load data and run an analytic query

Create a table, bulk-load rows with COPY INTO (here from standard input; MonetDB also loads CSV files, see the MonetDB documentation), and run an aggregation with a window function:

sudo monetdb-mclient -s "CREATE TABLE sales (region VARCHAR(8), product VARCHAR(20), amount DECIMAL(10,2));
COPY 6 RECORDS INTO sales FROM STDIN USING DELIMITERS ',',E'\n';
EMEA,keyboards,250.25
EMEA,monitors,447.80
APAC,keyboards,120.00
APAC,mice,80.50
AMER,monitors,999.99
AMER,mice,0.01
SELECT region, SUM(amount) AS revenue, RANK() OVER (ORDER BY SUM(amount) DESC) AS rank FROM sales GROUP BY region ORDER BY rank;"

operation successful
6 affected rows
+--------+---------------+------+
| region | revenue       | rank |
+========+===============+======+
| AMER   |       1000.00 |    1 |
| EMEA   |        698.05 |    2 |
| APAC   |        200.50 |    3 |
+--------+---------------+------+
3 tuples

Each column is stored on its own, which sys.storage() shows:

sudo monetdb-mclient -s "SELECT \"column\", type, count FROM sys.storage() WHERE \"table\" = 'sales';"

+---------+---------+-------+
| column  | type    | count |
+=========+=========+=======+
| region  | varchar |     6 |
| product | varchar |     6 |
| amount  | decimal |     6 |
+---------+---------+-------+
3 tuples

For applications, create a dedicated user rather than using monetdb, for example CREATE USER "app" WITH PASSWORD '<password>' NAME 'Application' SCHEMA "sys"; followed by the GRANT statements your application needs. Remove the example table with sudo monetdb-mclient -s "DROP TABLE sales;".

Step 7: Create more databases

The daemon manages every database in /var/monetdb5/dbfarm. To add one with its own administrator password, choose a password and use the same method first boot uses, which never puts the password on a command line (replace analytics with your database name):

sudo -u monetdb monetdb create analytics
read -rs NEWPW; printf '%s\n' "$NEWPW" | sudo -u monetdb mserver5 --set monet_vault_key=/var/monetdb5/dbfarm/analytics/.vaultkey --dbpath=/var/monetdb5/dbfarm/analytics --read-password-initialize-and-exit; unset NEWPW
sudo -u monetdb monetdb release analytics

Never create a database with monetdb create alone and then release it without setting a password: its administrator would be monetdb with the password monetdb. Connect to the new database with mclient -h 127.0.0.1 -p 50000 -u monetdb -d analytics, which prompts for its password (monetdb-mclient uses the password of demo).

Step 8: Connect from your own computer through an SSH tunnel

MonetDB listens on 127.0.0.1:50000 only. Forward the port over SSH from your computer, then point mclient, pymonetdb, JDBC or ODBC at your local end of the tunnel:

ssh -i <your-key.pem> -N -L 50000:127.0.0.1:50000 ubuntu@<public-ip>
mclient -h 127.0.0.1 -p 50000 -u monetdb -d demo

Enter the password from Step 4 when prompted. For applications inside your VPC, the same tunnel (or an SSH-based bastion) keeps the database port closed to the network.

Step 9: Optional: remote access over TLS

If clients must connect without SSH, switch on the TLS door. It serves TLS on port 50443 with this instance's own certificate and forwards to the local database; plaintext connections to it are refused.

sudo systemctl enable --now monetdb-tls.service
sudo ss -Htln sport = :50443

Created symlink /etc/systemd/system/multi-user.target.wants/monetdb-tls.service → /etc/systemd/system/monetdb-tls.service.
LISTEN 0      4096               *:50443            *:*

Check it on the instance. MonetDB's clients (mclient and libmapi since Dec2023, pymonetdb 1.8, JDBC 12) connect with a monetdbs:// URL and trust this self-signed certificate through its fingerprint, the certhash:

CH="$(sudo sed -n 's/^MONETDB_TLS_CERTHASH=//p' /root/monetdb-credentials.txt)"
sudo monetdb-mclient -d "monetdbs://localhost:50443/demo?certhash=${CH}" -s "SELECT 'connected over TLS' AS result;"

+--------------------+
| result             |
+====================+
| connected over TLS |
+--------------------+
1 tuple

Then allow TCP 50443 in the instance's security group from your own network only (never 0.0.0.0/0):

aws ec2 authorize-security-group-ingress --group-id <your-security-group> \
  --protocol tcp --port 50443 --cidr <your-cidr>

And connect from your computer with the fingerprint from Step 4:

mclient -u monetdb -d "monetdbs://<public-ip>:50443/demo?certhash=<MONETDB_TLS_CERTHASH>"

The certificate names the instance's addresses at first boot (localhost, the private IP, the public IP and the public DNS name), but the certhash pin works whatever name you connect to. A public IP that is not an Elastic IP changes when the instance is stopped and started. To use a certificate from your own CA instead, replace /etc/monetdb/tls/server.crt and server.key (owner root, group stunnel4, key mode 0640) and restart the door. To switch the door off again: sudo systemctl disable --now monetdb-tls.service, and remove the security group rule.

Step 10: Security checks

MonetDB, its first boot service and the TLS door cannot reach the instance metadata service:

systemctl show -p IPAddressDeny monetdbd.service monetdb-firstboot.service monetdb-tls.service

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

IPAddressDeny=fd00:ec2::254/128 169.254.169.254/32 168.63.129.16/32

The daemon listens on localhost, remote control and discovery are off, and the control passphrase is stored only as a hash:

sudo -u monetdb monetdbd get listenaddr,port,control,discovery /var/monetdb5/dbfarm
sudo grep -c '^passphrase={SHA512}' /var/monetdb5/dbfarm/.merovingian_properties

   property            value
listenaddr       localhost
port             50000
control          no
discovery        no
1

MonetDB runs unprivileged:

ps -o user:10=,comm= -u monetdb
sudo grep -E '^(CapEff|NoNewPrivs)' /proc/$(systemctl show -p MainPID --value monetdbd.service)/status

monetdb    monetdbd
monetdb    mserver5-11.55.
CapEff: 0000000000000000
NoNewPrivs: 1

The image's own round trip: the per-instance password works and an analytic query returns exact results, while monetdb/monetdb, blank, common and random passwords are refused; it also checks that port 50000 is loopback only and, when the TLS door is on, that the fingerprint is accepted while plaintext and an unpinned certificate are refused:

sudo /usr/local/sbin/monetdb-roundtrip.sh | tail -1

OK-monetdb-roundtrip

Step 11: Change the password

Sign in, set a new password, then update the credentials file so monetdb-mclient keeps working:

sudo monetdb-mclient
sql> ALTER USER SET PASSWORD '<new-password>' USING OLD PASSWORD '<current-password>';
sql> \q
sudo sed -i 's/^MONETDB_PASSWORD=.*/MONETDB_PASSWORD=<new-password>/' /root/monetdb-credentials.txt

Step 12: Backup, updates and maintenance

  • Back up by snapshotting the data volume, or by copying /var/monetdb5 with the daemon stopped (sudo systemctl stop monetdbd). For a logical SQL dump, msqldump reads the password from a dotfile the same way mclient does; keep dumps somewhere only root can read.
  • Memory: MonetDB uses the instance's memory for its working set and the operating system's file cache for the rest; give it a memory-rich instance type for large analytical tables.
  • Logs: sudo journalctl -u monetdbd.service -n 50 and /var/log/monetdb/merovingian.log.
  • Configuration: the daemon's properties live in /var/monetdb5/dbfarm/.merovingian_properties; change them with sudo -u monetdb monetdbd set <property>=<value> /var/monetdb5/dbfarm and restart the daemon. Keep listenaddr=localhost unless you deliberately expose the port, and leave control=no unless you need remote administration.
  • Updates: Ubuntu security updates install automatically. MonetDB comes from MonetDB's own repository, which is configured on the instance; upgrade it deliberately with sudo apt-get update && sudo apt-get install monetdb-server monetdb-sql monetdb-client after taking a snapshot of the data volume and reading the MonetDB release notes.

Support and licensing

MonetDB is open source software under the Mozilla Public License 2.0 (/usr/share/doc/monetdb-server/copyright). The MonetDB on this image is the unmodified upstream release from MonetDB's repository. cloudimg provides 24/7 technical support for this image by email and live chat: support@cloudimg.co.uk. MonetDB is a trademark of its owner; cloudimg is not affiliated with or endorsed by the MonetDB Foundation or MonetDB Solutions.