Databases AWS

MobilityDB 1.3.1 on AWS User Guide

| Product: MobilityDB 1.3.1 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying MobilityDB 1.3.1 on Ubuntu 24.04 from cloudimg on Amazon EC2. It is one version of the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace; you pick it as the software version when you subscribe and launch. The other PostgreSQL builds and extensions in the listing are described on the listing's guide hub.

MobilityDB is a PostgreSQL and PostGIS extension for moving objects: vehicles, vessels, aircraft, people and sensors whose position changes over time. It adds temporal types such as tgeompoint (a point that moves) and tfloat (a number that changes), and hundreds of functions to ask questions such as "where was this bus at 08:10?", "how far did it travel?", "which vehicles passed through this area?" and "how close did these two trajectories come?".

The image installs PostgreSQL 17.11 and PostGIS 3.6.4 from the official PostgreSQL (PGDG) apt repository and compiles MobilityDB 1.3.1 from the upstream release tag. MobilityDB 1.3.1 fixes CVE-2026-102639, an out-of-bounds read in the binary (WKB) input of temporal values that let a database user crash a backend; the PGDG package for Ubuntu 24.04 is still 1.3.0, so cloudimg builds 1.3.1 from source and removes the compiler, headers and source tree before the image is captured. The PostgreSQL and PostGIS packages are not held, so apt upgrade keeps delivering PGDG minor (security) releases, and unattended security upgrades stay on for the operating system.

What is on the image:

  • PostgreSQL 17.11 (postgresql@17-main.service) with PostGIS preloaded, as MobilityDB requires
  • A mobility database with the mobilitydb 1.3.1 and postgis 3.6.4 extensions and a small trips table of three example trajectories with a GiST index, so queries return data straight away
  • A non-superuser application role fleet_app with read and write access to the example table, and the postgres superuser; both passwords are generated on your instance's first boot
  • TLS with a certificate generated on your instance's first boot (TLS 1.2 minimum)
  • A credential guard that runs on every boot and refuses to serve if a published or example password ever authenticates

Default user: ubuntu · Architecture: x86_64 · Root volume: 20 GiB gp3

Security posture

  • No default password. The image ships with no password on any database role. First boot generates two different passwords, one for postgres and one for fleet_app, and writes them to /root/mobilitydb-credentials.txt, readable by root only. Locally, postgres is also reachable as the postgres system user (peer authentication).
  • Nothing is served before first boot finishes. PostgreSQL will not start until first boot has created the certificate and the passwords. While it sets them, the server listens on its local socket only, with no TCP port.
  • Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate under /etc/postgresql/17/main/ and turns TLS on. No key ships in the image, so no two instances share one.
  • Localhost only. PostgreSQL listens on 127.0.0.1:5432, and the listing's security group opens SSH (22) only. Reach the database through an SSH tunnel, or open it to your VPC as described in Step 6.
  • Remote connections require TLS. pg_hba.conf accepts non-loopback clients only over hostssl with scram-sha-256; there is no plain-text remote rule and no trust rule.
  • No metadata access for the database. A database user can make PostgreSQL open outbound connections (for example COPY ... FROM PROGRAM as a superuser), so every PostgreSQL unit and both first-boot services are blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254). Amazon Time Sync (169.254.169.123) still works. If you add backups to S3 from inside the database, give them explicit credentials; they cannot use the instance role.
  • IMDSv2 required. The AMI is registered with ImdsSupport v2.0, so instances launched from it refuse token-less (IMDSv1) metadata requests.
  • Fails closed. If first boot cannot finish, PostgreSQL stays stopped and first boot tries again on the next boot.
  • Per-instance identity. SSH host keys and the machine ID are generated fresh on every instance.

Prerequisites

  1. An AWS account and permission to launch EC2 instances and create security groups
  2. An EC2 key pair in the Region you launch in
  3. A VPC subnet, with a public IP on the instance if you connect over the internet

Recommended instance type: m5.large. Size up for large trajectory sets or heavy concurrent analytics.

Connecting to your instance

Version Operating system SSH user
MobilityDB 1.3.1 on Ubuntu 24.04 Ubuntu 24.04 LTS ubuntu

Step 1: Launch the instance

Option A: AWS Marketplace console

  1. Open the cloudimg Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace and choose View purchase options, then Subscribe. One subscription covers every version in the listing.
  2. Once the subscription is active, choose Launch your software.
  3. Choose One-click launch from AWS Marketplace (or Launch from EC2 Console for the full launch wizard). In Version, choose MobilityDB 1.3.1 on Ubuntu 24.04, then choose your Region.
  4. Pick an instance type (m5.large recommended; the software charge is $0.04 per vCPU per hour) and your key pair.
  5. Security group: the listing's default group allows SSH (22) only. Restrict it to your own IP address. Do not open 5432 to the internet.
  6. Choose your VPC and subnet, leave the metadata defaults (the image already requires IMDSv2), then choose Launch.

Option B: AWS CLI

Run this on your own workstation. Replace the AMI ID with the one shown for your Region on the Marketplace launch page, and the VPC, subnet, key pair and CIDR with your own:

SG=$(aws ec2 create-security-group --group-name mobilitydb \
  --description "MobilityDB" --vpc-id vpc-0123456789abcdef0 \
  --query GroupId --output text)
aws ec2 authorize-security-group-ingress --group-id "$SG" --protocol tcp --port 22 --cidr <your-mgmt-cidr>
aws ec2 run-instances \
  --image-id ami-0123456789abcdef0 \
  --instance-type m5.large \
  --key-name my-key-pair \
  --security-group-ids "$SG" \
  --subnet-id subnet-0123456789abcdef0 \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=mobilitydb}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required.

Step 2: Connect and read your credentials

Connect as ubuntu with the key pair you chose:

ssh -i ~/.ssh/my-key-pair.pem ubuntu@<public-ip>

First boot finishes within a minute of launch. Show the connection details (the two password lines are left out here; read them with sudo cat /root/mobilitydb-credentials.txt and keep them in your secrets manager):

sudo grep -v -e '^postgres.password=' -e '^fleet_app.password=' /root/mobilitydb-credentials.txt | head -16

# MobilityDB 1.3.1 on PostgreSQL 17.11 + PostGIS 3.6.4 (cloudimg AWS Marketplace image)
# Generated on THIS instance's first boot by mobilitydb-firstboot.service; these passwords
# exist nowhere else and are shown in plain text only here. Rotate a password (never on a
# command line) with:  sudo -u postgres psql   then   \password fleet_app
# and update this file to match.

postgres.host=127.0.0.1
postgres.port=5432
postgres.role=postgres
postgres.database=mobility
postgres.sslmode=require
postgres.private_ip=172.31.83.13

# The application role: least-privilege login for your application (DML on the demo schema, not a superuser).
fleet_app.role=fleet_app

The commands below read the fleet_app password from that file at run time, so it never appears on screen. In your own sessions you can instead put it in ~/.pgpass (mode 0600) as 127.0.0.1:5432:mobility:fleet_app:<APP_PASSWORD>.

Step 3: Confirm PostgreSQL and MobilityDB are running

psql --version
systemctl is-active postgresql@17-main.service mobilitydb-firstboot.service mobilitydb-credguard.service
sudo ss -Hltn

psql (PostgreSQL) 17.11 (Ubuntu 17.11-1.pgdg24.04+2)
active
active
active
LISTEN 0      4096   127.0.0.53%lo:53   0.0.0.0:*
LISTEN 0      200        127.0.0.1:5432 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            [::]:22      [::]:*

PostgreSQL listens on 127.0.0.1:5432 only; port 53 on 127.0.0.53/127.0.0.54 is the operating system's local DNS stub. Now connect as fleet_app over TLS, list the extension and show the versions it was built with:

export PGPASSWORD="$(sudo sed -n 's/^fleet_app.password=//p' /root/mobilitydb-credentials.txt)"
psql "host=127.0.0.1 dbname=mobility user=fleet_app sslmode=require" \
  -c "\dx mobilitydb" \
  -c "SELECT mobilitydb_full_version();" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

                                     List of installed extensions
    Name    | Version | Schema |                             Description
------------+---------+--------+----------------------------------------------------------------------
 mobilitydb | 1.3.1   | public | MobilityDB geospatial trajectory data management & analysis platform
(1 row)

                                                           mobilitydb_full_version
---------------------------------------------------------------------------------------------------------------------------------------------
 MobilityDB 1.3.1, PostgreSQL 17.11 (Ubuntu 17.11-1.pgdg24.04+2), PostGIS 3.6.4, GEOS 3.14.1-CAPI-1.20.5, PROJ 9.8.1, JSON-C 0.17, GSL 2.7.1
(1 row)

 ssl | tls_version
-----+-------------
 t   | TLSv1.3
(1 row)

Step 4: Query the example trajectories

The trips table holds three vehicles moving between 08:00 and 08:20 on 1 January 2000. bus-A goes from (0,0) to (2,2) to (4,0), bus-B goes north from (1,0) to (1,4), and tram-C runs far away along y = 10. List them, ask where each one was at 08:10, and how far each travelled:

export PGPASSWORD="$(sudo sed -n 's/^fleet_app.password=//p' /root/mobilitydb-credentials.txt)"
psql "host=127.0.0.1 dbname=mobility user=fleet_app sslmode=require" \
  -c "SELECT id, vehicle, numInstants(trip) AS points, startTimestamp(trip) AS starts, endTimestamp(trip) AS ends FROM trips ORDER BY id;" \
  -c "SELECT vehicle, ST_AsText(valueAtTimestamp(trip, timestamptz '2000-01-01 08:10')) AS position_at_0810 FROM trips ORDER BY id;" \
  -c "SELECT vehicle, round(length(trip)::numeric, 3) AS distance FROM trips ORDER BY id;"

 id | vehicle | points |         starts         |          ends
----+---------+--------+------------------------+------------------------
  1 | bus-A   |      3 | 2000-01-01 08:00:00+00 | 2000-01-01 08:20:00+00
  2 | bus-B   |      2 | 2000-01-01 08:00:00+00 | 2000-01-01 08:20:00+00
  3 | tram-C  |      2 | 2000-01-01 08:00:00+00 | 2000-01-01 08:20:00+00
(3 rows)

 vehicle | position_at_0810
---------+------------------
 bus-A   | POINT(2 2)
 bus-B   | POINT(1 2)
 tram-C  | POINT(12 10)
(3 rows)

 vehicle | distance
---------+----------
 bus-A   |    5.657
 bus-B   |    4.000
 tram-C  |    4.000
(3 rows)

MobilityDB interpolates between the recorded points, so you can ask for a position at any moment, not only at the instants you stored. Now cut bus-A's journey to the 08:05 to 08:15 window, find how close the other vehicles came to bus-A, and find which vehicles ever entered the box from (3,-1) to (5,1):

export PGPASSWORD="$(sudo sed -n 's/^fleet_app.password=//p' /root/mobilitydb-credentials.txt)"
psql "host=127.0.0.1 dbname=mobility user=fleet_app sslmode=require" \
  -c "SELECT ST_AsText(trajectory(atTime(trip, tstzspan '[2000-01-01 08:05, 2000-01-01 08:15]'))) AS bus_a_0805_0815 FROM trips WHERE vehicle = 'bus-A';" \
  -c "SELECT b.vehicle, round((a.trip |=| b.trip)::numeric, 3) AS closest_approach FROM trips a, trips b WHERE a.vehicle = 'bus-A' AND b.vehicle <> 'bus-A' ORDER BY 2;" \
  -c "SELECT vehicle FROM trips WHERE atGeometry(trip, ST_MakeEnvelope(3,-1,5,1)) IS NOT NULL;"

     bus_a_0805_0815
-------------------------
 LINESTRING(1 1,2 2,3 1)
(1 row)

 vehicle | closest_approach
---------+------------------
 bus-B   |            0.000
 tram-C  |           12.806
(2 rows)

 vehicle
---------
 bus-A
(1 row)

|=| is MobilityDB's nearest-approach distance: bus-A and bus-B meet at (1,1) at 08:05, so their distance is 0, while tram-C never comes closer than 12.8 units.

Step 5: Add your own trajectory

The fleet_app role can write to the table. Insert a van that drives north along x = 3 for 30 minutes, read its top speed (units per hour), find every vehicle that ever entered a small area around (3,1), then delete the van again:

export PGPASSWORD="$(sudo sed -n 's/^fleet_app.password=//p' /root/mobilitydb-credentials.txt)"
psql "host=127.0.0.1 dbname=mobility user=fleet_app sslmode=require" \
  -c "INSERT INTO trips (id, vehicle, trip) VALUES (4, 'van-D', tgeompoint '[Point(3 0)@2000-01-01 08:00, Point(3 3)@2000-01-01 08:30]');" \
  -c "SELECT vehicle, round(maxValue(speed(trip))::numeric * 3600, 1) AS max_units_per_hour FROM trips WHERE vehicle = 'van-D';" \
  -c "SELECT vehicle FROM trips WHERE eIntersects(trip, ST_MakeEnvelope(2.5,0.5,3.5,1.5)) ORDER BY vehicle;" \
  -c "DELETE FROM trips WHERE id = 4;"

INSERT 0 1
 vehicle | max_units_per_hour
---------+--------------------
 van-D   |                6.0
(1 row)

 vehicle
---------
 bus-A
 van-D
(2 rows)

DELETE 1

For real data, store GPS fixes with a spatial reference system (for example tgeompoint 'SRID=4326;[Point(-0.1278 51.5074)@2026-10-20 08:00, ...]') and use tgeogpoint when you want distances in metres on the globe. The MobilityDB documentation lists every type and function.

Step 6: Connect from your application

PostgreSQL accepts connections on localhost only. Two ways to reach it:

  • SSH tunnel (no change to the instance). On your workstation run ssh -N -L 5432:127.0.0.1:5432 ubuntu@<public-ip>, then connect your client to 127.0.0.1:5432 as fleet_app with sslmode=require.
  • Open it to your VPC. In /etc/postgresql/17/main/conf.d/10-cloudimg-mobilitydb.conf change listen_addresses = '127.0.0.1' to listen_addresses = '*', restart PostgreSQL, then add an inbound rule for 5432 to the instance's security group from your application subnet or VPC CIDR only (never 0.0.0.0/0). Remote clients must use TLS (sslmode=require, or verify-full with this instance's certificate /etc/postgresql/17/main/cloudimg-server.crt) and the role's password:
sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/17/main/conf.d/10-cloudimg-mobilitydb.conf
sudo systemctl restart postgresql@17-main.service

A plain-text (non-TLS) connection from another host is refused by pg_hba.conf, and a wrong password is refused by scram-sha-256:

OUT="$(PGPASSWORD=wrong-password psql "host=127.0.0.1 dbname=mobility user=fleet_app sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "fleet_app"

Step 7: Check the security controls

The PostgreSQL unit carries the metadata block, fleet_app is not a superuser, PostgreSQL listens on loopback and uses this instance's own certificate:

systemctl show -p IPAddressDeny postgresql@17-main.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper FROM pg_roles WHERE rolname IN ('fleet_app', 'postgres') ORDER BY 1;"
sudo -u postgres psql -tAc "SHOW listen_addresses;"
sudo -u postgres psql -tAc "SHOW ssl_cert_file;"

IPAddressDeny=168.63.129.16/32 fd00:ec2::254/128 169.254.169.254/32
fleet_app|f
postgres|t
127.0.0.1
/etc/postgresql/17/main/cloudimg-server.crt

Maintenance

  • Change a password. Run sudo -u postgres psql and type \password fleet_app (or \password postgres) at the prompt; it asks for the new password twice and never puts it on a command line. Then update /root/mobilitydb-credentials.txt (the credential guard reads it on every boot) and your secrets manager.
  • Configuration. cloudimg's settings are in /etc/postgresql/17/main/conf.d/10-cloudimg-mobilitydb.conf (listener, PostGIS preload, max_locks_per_transaction) and 20-cloudimg-tls.conf (this instance's certificate). Apply changes with sudo systemctl restart postgresql@17-main.service.
  • Updates. sudo apt update && sudo apt upgrade installs PostgreSQL 17 and PostGIS 3.6 minor releases from PGDG and Ubuntu security updates. MobilityDB 1.3.1 is compiled for PostgreSQL 17; when a newer MobilityDB release is available from cloudimg or PGDG, install it and run ALTER EXTENSION mobilitydb UPDATE; in each database.
  • Backups. Take EBS snapshots or pg_dump -Fc mobility backups regularly.
  • Logs. /var/log/postgresql/postgresql-17-main.log and journalctl -u postgresql@17-main.service; first boot and the credential guard log to journalctl -u mobilitydb-firstboot.service and journalctl -u mobilitydb-credguard.service.
  • Licences. MobilityDB is under The PostgreSQL License and includes PostGIS's liblwgeom (GPL-2.0-or-later); PostGIS is GPL-2.0-or-later. /usr/share/doc/mobilitydb-cloudimg/ATTRIBUTION.txt names the exact source release.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (MobilityDB 1.3.1 on Ubuntu 24.04) and what you were doing.

PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. Ubuntu is a trademark of Canonical Ltd. MobilityDB and PostGIS are the names of their respective open-source projects. cloudimg is not affiliated with or endorsed by these organisations or projects.