Databases AWS

PostGIS 3.6.4 on AWS User Guide

| Product: PostGIS 3.6.4 on Ubuntu 24.04 on AWS

Overview

This guide covers launching and verifying PostGIS 3.6.4 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.

PostGIS is the spatial extension for PostgreSQL. It adds geometry and geography column types, spatial indexes (GiST) and several hundred spatial SQL functions such as ST_Distance, ST_DWithin, ST_Contains, ST_Buffer and ST_Transform, so you can store points, lines and polygons next to your ordinary data and ask questions such as "which customers are within 10 km of this depot?" in plain SQL. It is the storage engine behind most open-source GIS tools, including QGIS and GeoServer.

The image installs PostgreSQL 18.6 and PostGIS 3.6.4 unmodified from the official PostgreSQL (PGDG) apt repository. Nothing is held back: unattended upgrades are configured for the PGDG archive as well as Ubuntu, so PostgreSQL and PostGIS minor (security) releases keep arriving through apt.

What is on the image:

  • PostgreSQL 18.6 (postgresql@18-main.service) with the PostGIS 3.6.4 extension and its loader tools (shp2pgsql, raster2pgsql, pgsql2shp)
  • A gisdb database with the postgis and postgis_topology extensions already enabled, owned by the application role gisadmin
  • A non-superuser application role gisadmin 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 dedicated 32 GiB gp3 data volume mounted at /var/lib/postgresql, so the database grows on its own disk that you can resize independently
  • 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: 12 GiB gp3 · Data volume: 32 GiB gp3

Security posture

  • No database ships in the image. The image contains no PostgreSQL cluster at all, so it carries no role, password, data or key from the build. First boot creates a fresh cluster on the data volume, the gisdb database and two different passwords, one for postgres and one for gisadmin, and writes them to /root/postgis-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 cluster, 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/18/main/ and turns TLS on, so no two instances share a key.
  • 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 and rejects plain-text remote connections; there is 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 datasets, raster work or heavy concurrent spatial queries.

Connecting to your instance

Version Operating system SSH user
PostGIS 3.6.4 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 PostGIS 3.6.4 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, keep the 32 GiB data volume (you can make it larger), 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 postgis \
  --description "PostGIS" --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=postgis}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required. The data volume comes from the AMI; to make it larger at launch, add a --block-device-mappings entry for /dev/sdf with a bigger VolumeSize, then run sudo resize2fs on that device once the instance is up.

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/postgis-credentials.txt and keep them in your secrets manager):

sudo grep -v -e '^POSTGRES_SUPERUSER_PASSWORD=' -e '^GISADMIN_PASSWORD=' /root/postgis-credentials.txt | head -10

# PostGIS 3.6.4 on PostgreSQL 18.6 (cloudimg AWS Marketplace image)
# Generated on THIS instance's first boot by postgis-firstboot.service; these passwords exist
# nowhere else. Rotate a password (never on a command line) with:  sudo -u postgres psql
# then  \password gisadmin  and update this file to match (postgis-credguard reads it at boot).


# Spatial database 'gisdb' (owner gisadmin, PostGIS 3.6.4 + postgis_topology enabled)
# on 127.0.0.1:5432. Connect locally with TLS:
#   psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require"
# Private address of this instance: 172.31.80.192

The commands below read the gisadmin 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:gisdb:gisadmin:<GISADMIN_PASSWORD>.

Step 3: Confirm PostgreSQL and PostGIS are running

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

psql (PostgreSQL) 18.6 (Ubuntu 18.6-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 gisadmin over TLS, list the extensions in gisdb and show the library versions PostGIS uses:

export PGPASSWORD="$(sudo sed -n 's/^GISADMIN_PASSWORD=//p' /root/postgis-credentials.txt)"
psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require" \
  -c "\dx" \
  -c "SELECT postgis_lib_version() AS postgis, split_part(postgis_geos_version(), '-', 1) AS geos, split_part(postgis_proj_version(), ' ', 1) AS proj, version() ~ '^PostgreSQL 18\.6' AS postgresql_18_6;" \
  -c "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

                                              List of installed extensions
       Name       | Version | Default version |   Schema   |                        Description
------------------+---------+-----------------+------------+------------------------------------------------------------
 plpgsql          | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
 postgis          | 3.6.4   | 3.6.4           | public     | PostGIS geometry and geography spatial types and functions
 postgis_topology | 3.6.4   | 3.6.4           | topology   | PostGIS topology spatial types and functions
(3 rows)

 postgis |  geos  | proj  | postgresql_18_6
---------+--------+-------+-----------------
 3.6.4   | 3.14.1 | 9.8.1 | t
(1 row)

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

Step 4: Create a spatial table

Create a table of five UK cities with a geometry(Point, 4326) column (longitude and latitude on WGS 84) and a GiST spatial index:

export PGPASSWORD="$(sudo sed -n 's/^GISADMIN_PASSWORD=//p' /root/postgis-credentials.txt)"
psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE cities (
    id   serial PRIMARY KEY,
    name text NOT NULL,
    geom geometry(Point, 4326) NOT NULL
);
INSERT INTO cities (name, geom) VALUES
    ('London',     ST_SetSRID(ST_MakePoint(-0.1276, 51.5072), 4326)),
    ('Cardiff',    ST_SetSRID(ST_MakePoint(-3.1791, 51.4816), 4326)),
    ('Birmingham', ST_SetSRID(ST_MakePoint(-1.8904, 52.4862), 4326)),
    ('Manchester', ST_SetSRID(ST_MakePoint(-2.2426, 53.4808), 4326)),
    ('Edinburgh',  ST_SetSRID(ST_MakePoint(-3.1883, 55.9533), 4326));
CREATE INDEX cities_geom_gix ON cities USING gist (geom);
ANALYZE cities;
SELECT name, ST_AsText(geom) AS wkt FROM cities ORDER BY id;
SQL

CREATE TABLE
INSERT 0 5
CREATE INDEX
ANALYZE
    name    |          wkt
------------+------------------------
 London     | POINT(-0.1276 51.5072)
 Cardiff    | POINT(-3.1791 51.4816)
 Birmingham | POINT(-1.8904 52.4862)
 Manchester | POINT(-2.2426 53.4808)
 Edinburgh  | POINT(-3.1883 55.9533)
(5 rows)

Step 5: Ask spatial questions

Distances on the globe, a radius search, a point-in-polygon test and a reprojection to British National Grid (EPSG:27700). Casting to geography makes PostGIS measure in metres on the WGS 84 spheroid:

export PGPASSWORD="$(sudo sed -n 's/^GISADMIN_PASSWORD=//p' /root/postgis-credentials.txt)"
psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require" -v ON_ERROR_STOP=1 <<'SQL'
-- Great-circle distance from London in kilometres
SELECT b.name, round((ST_Distance(a.geom::geography, b.geom::geography) / 1000)::numeric, 1) AS km_from_london
FROM cities a, cities b
WHERE a.name = 'London' AND b.name <> 'London'
ORDER BY 2;

-- Every city within 200 km of Birmingham
SELECT b.name
FROM cities a JOIN cities b ON ST_DWithin(a.geom::geography, b.geom::geography, 200000)
WHERE a.name = 'Birmingham' AND b.name <> 'Birmingham'
ORDER BY b.name;

-- Which cities fall inside a polygon (a rough box around England and Wales)?
SELECT name FROM cities
WHERE ST_Contains(ST_MakeEnvelope(-5.8, 50.0, 1.8, 54.0, 4326), geom)
ORDER BY name;

-- Reproject to British National Grid and read easting / northing in metres
SELECT name, round(ST_X(ST_Transform(geom, 27700))) AS easting, round(ST_Y(ST_Transform(geom, 27700))) AS northing
FROM cities WHERE name IN ('London', 'Edinburgh') ORDER BY name;
SQL

    name    | km_from_london
------------+----------------
 Birmingham |          162.9
 Cardiff    |          211.9
 Manchester |          262.4
 Edinburgh  |          534.4
(4 rows)

    name
------------
 Cardiff
 London
 Manchester
(3 rows)

    name
------------
 Birmingham
 Cardiff
 London
 Manchester
(4 rows)

   name    | easting | northing
-----------+---------+----------
 Edinburgh |  325897 |   674001
 London    |  530043 |   180358
(2 rows)

Now build a 120 km buffer around Manchester and export it as GeoJSON (the format web maps such as Leaflet and OpenLayers read), confirm the planner uses the GiST index for a bounding-box search, and drop the example table:

export PGPASSWORD="$(sudo sed -n 's/^GISADMIN_PASSWORD=//p' /root/postgis-credentials.txt)"
psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require" -v ON_ERROR_STOP=1 \
  -c "SELECT round(ST_Area(ST_Buffer(geom::geography, 120000)) / 1e6) AS area_km2, left(ST_AsGeoJSON(ST_Buffer(geom::geography, 120000)::geometry, 4), 60) || '...' AS geojson FROM cities WHERE name = 'Manchester';"
printf '%s\n' "SET enable_seqscan = off;" \
  "EXPLAIN (COSTS OFF) SELECT name FROM cities WHERE geom && ST_MakeEnvelope(-3.5, 51.0, -1.0, 53.0, 4326);" \
  | psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require" -qtA | head -1
psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require" -c "DROP TABLE cities;"

 area_km2 |                             geojson
----------+-----------------------------------------------------------------
    44978 | {"type":"Polygon","coordinates":[[[-0.4356,53.4557],[-0.4827...
(1 row)

Index Scan using cities_geom_gix on cities
DROP TABLE

Loading your own data

The PostGIS loaders are installed. For example, load a shapefile into a new table with a spatial index (-I), or a GeoTIFF raster after CREATE EXTENSION postgis_raster; (as postgres):

shp2pgsql -I -s 4326 roads.shp public.roads | psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require"
raster2pgsql -I -C -s 27700 -t 256x256 elevation.tif public.elevation | psql "host=127.0.0.1 dbname=gisdb user=gisadmin sslmode=require"

QGIS, GeoServer and most GIS tools connect to gisdb directly with the PostgreSQL connection details from Step 2 (use an SSH tunnel or the VPC set-up below, with SSL mode require).

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 gisadmin with sslmode=require.
  • Open it to your VPC. In /etc/postgresql/18/main/conf.d/10-cloudimg.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/18/main/cloudimg-server.crt) and the role's password:
sudo sed -i "s/^listen_addresses = '127.0.0.1'/listen_addresses = '*'/" /etc/postgresql/18/main/conf.d/10-cloudimg.conf
sudo systemctl restart postgresql@18-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=gisdb user=gisadmin sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "gisadmin"

Step 7: Check the security controls

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

systemctl show -p IPAddressDeny postgresql@18-main.service
sudo -u postgres psql -tAc "SELECT rolname, rolsuper FROM pg_roles WHERE rolname IN ('gisadmin', '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
gisadmin|f
postgres|t
127.0.0.1
/etc/postgresql/18/main/cloudimg-server.crt

Maintenance

  • Change a password. Run sudo -u postgres psql and type \password gisadmin (or \password postgres) at the prompt; it asks for the new password twice and never puts it on a command line. Then update /root/postgis-credentials.txt (the credential guard reads it on every boot) and your secrets manager.
  • Configuration. cloudimg's settings are in /etc/postgresql/18/main/conf.d/10-cloudimg.conf (listener, port, password hashing) and 20-cloudimg-tls.conf (this instance's certificate). Apply changes with sudo systemctl restart postgresql@18-main.service.
  • Updates. sudo apt update && sudo apt upgrade installs PostgreSQL 18 and PostGIS 3.6 minor releases from PGDG and Ubuntu updates; unattended upgrades also apply them automatically. After a PostGIS minor upgrade, run ALTER EXTENSION postgis UPDATE; (and postgis_topology) in each database.
  • Storage. The cluster lives on the data volume at /var/lib/postgresql. To grow it, modify the EBS volume in the EC2 console, then run sudo resize2fs on its device (see findmnt /var/lib/postgresql).
  • Backups. Take EBS snapshots of the data volume or pg_dump -Fc gisdb backups regularly.
  • Logs. /var/log/postgresql/postgresql-18-main.log and journalctl -u postgresql@18-main.service; first boot and the credential guard log to journalctl -u postgis-firstboot.service and journalctl -u postgis-credguard.service.
  • Licences. PostGIS is GPL-2.0-or-later and PostgreSQL is under The PostgreSQL License. Both are unmodified PGDG packages; /usr/share/doc/cloudimg/POSTGIS-GPL-2-WRITTEN-OFFER.txt explains how to get the PostGIS source (apt-get source postgresql-18-postgis-3, or ask support@cloudimg.co.uk), and /usr/share/doc/cloudimg/LICENCE-AUDIT.txt records the audited licence files.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (PostGIS 3.6.4 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. PostGIS is the name of its open-source project. cloudimg is not affiliated with or endorsed by these organisations or projects.