MobilityDB

Prerequisites

You need:

• An active Azure subscription, an SSH public key, and a VNet with a subnet in the target region
• A subscription to this listing on Azure Marketplace
• A network security group (NSG) that allows TCP 22 (administration) and TCP 5432 (the PostgreSQL wire protocol) from your client network. In production, restrict 5432 to your application subnet.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development, evaluation and light workloads. For larger trajectory histories and higher query rates, choose a memory-rich size such as Standard_E2s_v5 or larger.

Deploy the virtual machine

Create the VM from the image with the Azure CLI. Open only SSH and the PostgreSQL port, and only to your own network.

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:mobilitydb:default:latest
az vm create \
  --resource-group my-rg \
  --name mobilitydb-1 \
  --image sac1647283583153:mobilitydb:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name mobilitydb-1 --port 5432 --priority 900

Retrieve your per-VM credentials

On first boot, the VM mints its own passwords and TLS certificate. SSH in and read the root-only credentials file:

sudo cat /root/mobilitydb-credentials.txt

The file contains the postgres superuser password, the fleet_app application role password and ready-to-paste connection strings. The file is mode 0600 and owned by root, and the values in it exist only on this VM.

The postgres.host line is filled in from the VM's own network metadata. Azure's instance metadata service returns an empty value for Standard SKU public IPs, so the image falls back to the load-balancer metadata and then to the VM's private address. When you connect from outside Azure, use the VM's public IP address from the portal or from az vm list-ip-addresses.

Confirm the service is healthy

Check that PostgreSQL and the credential guard are active and that both extensions are loaded:

systemctl is-active postgresql.service mobilitydb-credguard.service
sudo -u postgres psql -tAc "SELECT version();"
sudo -u postgres psql -d mobility -tAc "SELECT extname || ' ' || extversion FROM pg_extension WHERE extname IN ('mobilitydb','postgis') ORDER BY extname;"
sudo -u postgres psql -d mobility -tAc "SELECT a.amname FROM pg_class c JOIN pg_am a ON a.oid = c.relam WHERE c.relname = 'trips_trip_gist';"

The last query must return gist. This confirms that the demo trajectory index uses the GiST access method, so spatiotemporal predicates against it are index-assisted rather than sequential scans.

Query trajectories: position, length and time slices

This is the core of MobilityDB. The mobility database ships a trips table. Each row is a vehicle and its whole path over the interval 08:00 to 08:20, stored in a single tgeompoint (temporal geometry point) column. Because the movement is stored as data, you can query it directly.

To ask where a vehicle was at a specific instant, use valueAtTimestamp(). It interpolates the position along the trajectory:

sudo -u postgres psql -d mobility -c "SELECT vehicle, ST_AsText(valueAtTimestamp(trip, timestamptz '2000-01-01 08:10')) AS position_at_0810 FROM trips WHERE vehicle = 'bus-A';"

To measure how far each vehicle travelled, use length(). It returns the spatial length of the whole trajectory:

sudo -u postgres psql -d mobility -c "SELECT vehicle, round(length(trip)::numeric, 3) AS trajectory_length FROM trips ORDER BY vehicle;"

To restrict a trajectory to a time window and get the path it took, use atTime() to slice the movement. Then use trajectory() to project it to a geometry:

sudo -u postgres psql -d mobility -c "SELECT ST_AsText(trajectory(atTime(trip, tstzspan '[2000-01-01 08:05, 2000-01-01 08:15]'))) AS path_0805_to_0815 FROM trips WHERE vehicle = 'bus-A';"

Spatial windows and nearest approach, over TLS

MobilityDB combines the time dimension with PostGIS space. Connect as the least-privilege application role over TLS, then ask which vehicles ever pass through a map window and how close any two vehicles ever came.

atGeometry() restricts a trajectory to the part that falls inside a geometry. An IS NOT NULL test then tells you which trajectories ever entered a spatial window:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "SELECT vehicle FROM trips WHERE atGeometry(trip, ST_MakeEnvelope(3,-1,5,1)) IS NOT NULL ORDER BY vehicle;"

The |=| operator returns the nearest approach distance, the closest two moving objects ever came to each other, taking time into account. A distance of 0 means their paths crossed the same point at the same instant:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "SELECT a.vehicle AS v1, b.vehicle AS v2, round((a.trip |=| b.trip)::numeric, 3) AS nearest_approach FROM trips a JOIN trips b ON a.id < b.id ORDER BY nearest_approach;"

To confirm that the connection uses TLS, run:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -tAc "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

Store your own trajectories

Adding your own moving objects is ordinary SQL. Create a table with a tgeompoint column and a GiST index. Then insert trajectories in MobilityDB's temporal literal form [Point(x y)@timestamp, ...].

Create the table and index as the postgres owner, because schema DDL is an owner operation. The least-privilege fleet_app role then reads and writes the rows:

sudo -u postgres psql -d mobility -c "CREATE TABLE IF NOT EXISTS my_trips (id int PRIMARY KEY, name text, trip tgeompoint);"
sudo -u postgres psql -d mobility -c "CREATE INDEX IF NOT EXISTS my_trips_gist ON my_trips USING gist (trip);"
sudo -u postgres psql -d mobility -c "GRANT SELECT, INSERT, UPDATE, DELETE ON my_trips TO fleet_app;" PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "INSERT INTO my_trips (id, name, trip) VALUES (1, 'van-1', tgeompoint '[Point(0 0)@2000-01-01 10:00, Point(5 5)@2000-01-01 10:30]') ON CONFLICT (id) DO NOTHING;"

Now query your own trajectory, for example its length and where it was halfway through:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=mobility user=fleet_app sslmode=require" -c "SELECT name, round(length(trip)::numeric, 3) AS len, ST_AsText(valueAtTimestamp(trip, timestamptz '2000-01-01 10:15')) AS at_1015 FROM my_trips WHERE id = 1;"

MobilityDB also provides temporal types for floats, integers, text and geographies (tfloat, tint, tgeogpoint, ...). Set-of-time types (tstzspan, tstzspanset) let you reason about when things happened. See the upstream reference for the full type and function set.

Security posture

• No baked credential. Every role ships with no password at all. Both role passwords are minted on first boot from a cryptographic random source and are unique per VM.
• No shared certificate. TLS is off in the image, and the certificate is generated per VM on first boot, so no two VMs share a server key.
• TLS enforced for remote clients. pg_hba.conf accepts remote connections only through hostssl with scram-sha-256. There is no trust rule anywhere. On-box administration stays password-less through the local Unix socket.
• A credential guard that fails closed. On every boot, mobilitydb-credguard.service proves that the recorded per-VM credentials authenticate. It then tries a list of published and example values. If any of them succeeds, the guard stops PostgreSQL rather than serve a database with a known credential.
• Least privilege for applications. The fleet_app role has DML on the demo schema but is not a superuser. Point your application at it rather than at postgres.

You can rotate a password at any time. Generate a fresh random secret and record it in the credentials file in the same step, so the credential guard keeps agreeing with reality:

NEWPW="$(openssl rand -base64 40 | tr -dc 'A-Za-z0-9' | cut -c1-28)" sudo -u postgres psql -c "ALTER ROLE fleet_app PASSWORD '${NEWPW}';"
sudo sed -i "s|^fleet_app.password=.*|fleet_app.password=${NEWPW}|" /root/mobilitydb-credentials.txt echo "fleet_app password rotated and recorded in the credentials file"

Always update /root/mobilitydb-credentials.txt when you rotate. On every boot, the credential guard verifies that the recorded password authenticates, and it stops PostgreSQL if it does not. This check makes a stale or mismatched credential impossible to ignore.

Never set a password to a value published in documentation. The guard tries a list of such values and refuses to serve if one of them works.

Operations

Check the service status and the first-boot log:

systemctl status postgresql.service --no-pager sudo journalctl -u mobilitydb-firstboot.service --no-pager | tail -20

Back up the demo database to a SQL file:

sudo -u postgres pg_dump -d mobility -f /tmp/mobility-backup.sql && ls -la /tmp/mobility-backup.sql

The image includes third-party licence attribution for MobilityDB, PostGIS and PostgreSQL. Read it with:

cat /usr/share/doc/mobilitydb-sac/ATTRIBUTION.txt

Support

SecureAnyCloud images include 24/7 support. Raise an issue through the Azure Marketplace listing, or contact SecureAnyCloud support with the VM name, the region and the output of systemctl status postgresql.service.

MobilityDB is developed at the Université libre de Bruxelles and distributed under The PostgreSQL License. PostGIS is GPL-2.0+, and PostgreSQL is under The PostgreSQL License. Upstream documentation is at mobilitydb.com.

Deploy on Azure

Launch PostgreSQL 17 with PostGIS and MobilityDB on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com