CrateDB 6.5.1 on AWS User Guide

Overview

This guide covers launching and using the CrateDB 6.5.1 on Ubuntu 24.04 AMI from cloudimg on Amazon EC2. It is one version of the cloudimg Search, OLAP and Time-Series database listing on AWS Marketplace; you pick it as the software version when you subscribe and launch.

CrateDB is an open source (Apache-2.0) distributed SQL database for real-time analytics. It stores JSON-like objects, arrays, full-text indexes, time series and geospatial data in tables you query with standard SQL, and it answers over an HTTP endpoint (/_sql), the PostgreSQL wire protocol (so psql and PostgreSQL drivers work) and a built in web Admin UI with a SQL console.

The cloudimg image adds:

• No default login, no trust entry. Upstream CrateDB trusts its built in crate superuser from localhost with no password. This image turns host-based authentication on with password authentication for HTTP and the PostgreSQL wire protocol and no trust entry at all, so the crate superuser (which cannot hold a password) cannot log in. First boot creates an admin user with all privileges and a 40-character password generated on your instance; none is baked into the image.
• HTTPS from the first boot. nginx serves the SQL endpoint and the Admin UI on port 443 with a certificate generated on your instance. There is no port 80. CrateDB itself listens on 127.0.0.1 only (HTTP 4200, PostgreSQL wire 5432, transport 4300). Requests for a host name the instance does not serve get no answer, and cross-site browser requests are refused.
• An SSRF firewall for COPY FROM. CrateDB fetches any URL a SQL user names (COPY FROM over http(s):// or s3://, snapshot repositories). On this image the CrateDB process cannot connect to private, link-local, metadata or reserved addresses, nor to your VPC's own ranges: a firewall owned by the crate user and systemd's address filter both refuse them, and CrateDB's own copy_from.http.blocked_hosts is set too. Imports from public HTTPS URLs and from Amazon S3 work.
• Metadata access blocked for CrateDB, nginx and the first-boot service, and the AMI requires IMDSv2.
• Anonymous usage reporting and JavaScript functions off (udc.enabled: false, lang.js.enabled: false; upstream defaults on).
• Fails closed. CrateDB and nginx refuse to start until first boot has created the password, the certificate and the credentials note, and CrateDB refuses to start without its egress firewall.
• Fully patched Ubuntu 24.04 LTS at build time, with unattended security upgrades switched on.

What is on the image: the official CrateDB 6.5.1 release (/opt/crate-6.5.1, with its own bundled Java runtime; no system Java), nginx, Ubuntu 24.04 LTS, the PostgreSQL client psql, cloud-init and the AWS Systems Manager agent.

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

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 443 (Admin UI and SQL over HTTPS) from the people and applications that use CrateDB, and TCP 22 (SSH) from your own networks only. CrateDB's own ports are not exposed and need no rule.

CrateDB gives half of the instance's memory to its Java heap (re-read on every start, so a larger instance type needs no change). Use m5.large (8 GiB) or larger; data is stored on the root volume under /var/lib/cratedb, so give the root volume the space your data needs.

Step 1: Launch the instance

In the AWS Marketplace listing choose Continue to Subscribe, then Continue to Configuration, pick CrateDB 6.5.1 on Ubuntu 24.04 as the software version and your Region, then Launch through EC2. Choose m5.large or larger, your key pair and a security group as above. The software charge is $0.04 per vCPU per hour.

With the AWS CLI (replace the values in angle brackets):

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/sda1,Ebs={VolumeSize=100,VolumeType=gp3}' \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=cratedb}]'

You do not need --metadata-options: the AMI already makes IMDSv2 required. The root file system grows to fill the volume on first boot.

Step 2: Connect to your instance

ssh -i <your-key.pem> ubuntu@<public-ip>

Step 3: Check the services

First boot takes about a minute. Then CrateDB, nginx, first boot and the egress firewall are active:

systemctl is-active crate nginx cratedb-firstboot cloudimg-cratedb-egress

active
active
active
active

nginx (443) and SSH (22) are the only listeners on the network; every CrateDB port is on loopback:

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

0.0.0.0:22
0.0.0.0:443
127.0.0.53%lo:53
127.0.0.54:53
[::]:22
[::]:443
[::ffff:127.0.0.1]:4200
[::ffff:127.0.0.1]:4300
[::ffff:127.0.0.1]:5432

4200 is the HTTP endpoint and Admin UI (served to you through nginx), 5432 the PostgreSQL wire protocol and 4300 the node's transport port. The two port 53 entries are the system DNS resolver.

Step 4: Get your URL and password

sudo grep -v '^CRATEDB_ADMIN_PASSWORD=' /root/cratedb-credentials.txt

The file holds the address CrateDB is served as (your instance's public DNS name by default), the user, the fingerprint of this instance's certificate and, on the line left out above, the password:

# CrateDB 6.5.1 - generated on first boot by cratedb-firstboot.service
# This password is unique to this instance; none was baked into the image. Keep this file secret.
# Every request needs it (HTTP basic auth over HTTPS, or the PostgreSQL wire protocol on loopback); /healthz is open.

CRATEDB_URL=https://<public-dns-name>/
CRATEDB_HOST=<public-dns-name>
CRATEDB_USER=admin
CRATEDB_CERT_SHA256=<64 hex digits, colon separated>
#
# Admin UI:      https://<CRATEDB_HOST>/  (user admin; compare the certificate fingerprint above on first connect)
...

Read the password with sudo cat /root/cratedb-credentials.txt and keep it in your secrets manager. The same summary is printed when you log in over SSH.

Step 5: Open the Admin UI

Open CRATEDB_URL in your browser. The certificate is self signed (generated on your instance), so your browser warns once: compare the fingerprint it shows with CRATEDB_CERT_SHA256, or use your own name and certificate (Step 10). Sign in as admin with the password from Step 4. The overview shows the cluster health, the node and its load:

The Console runs SQL in the browser:

Tables lists your tables with their shards, replicas and health:

Step 6: Run SQL over HTTPS

Applications send SQL to https://<CRATEDB_HOST>/_sql as JSON with HTTP basic authentication. This example creates a table, inserts rows with bulk arguments and queries them. The password is written into a curl configuration file readable only by you, so it never appears on a command line, and --resolve sends the request to this instance under its served name:

H="$(sudo sed -n 's/^CRATEDB_HOST=//p' /root/cratedb-credentials.txt)"
CFG="$(mktemp)"; chmod 600 "$CFG"
sudo sed -n 's/^CRATEDB_ADMIN_PASSWORD=\(.*\)$/user = "admin:\1"/p' /root/cratedb-credentials.txt > "$CFG"
sql() { curl -s --cacert /etc/nginx/tls/cratedb.crt --resolve "$H:443:127.0.0.1" -K "$CFG" \
  -H 'Content-Type: application/json' --data-binary @- "https://$H/_sql"; }
sql <<'JSON' | jq -c '.rowcount'
{"stmt": "CREATE TABLE IF NOT EXISTS doc.readings (sensor TEXT, ts TIMESTAMP WITH TIME ZONE, temp DOUBLE PRECISION, location GEO_POINT)"}
JSON
sql <<'JSON' | jq -c '[.results[].rowcount]'
{"stmt": "INSERT INTO doc.readings (sensor, ts, temp, location) VALUES (?, ?, ?, ?)",
 "bulk_args": [["s1", "2026-11-10T09:00:00Z", 21.5, [-0.1276, 51.5072]],
               ["s1", "2026-11-10T10:00:00Z", 22.0, [-0.1276, 51.5072]],
               ["s2", "2026-11-10T09:00:00Z", 18.25, [2.3522, 48.8566]]]}
JSON
sql <<'JSON' >/dev/null
{"stmt": "REFRESH TABLE doc.readings"}
JSON
sql <<'JSON' | jq -c '.rows[]'
{"stmt": "SELECT sensor, count(*), avg(temp) FROM doc.readings GROUP BY sensor ORDER BY sensor"}
JSON
rm -f "$CFG"

1
[1,1,1]
["s1",2,21.75]
["s2",1,18.25]

From another machine, use https://<CRATEDB_HOST>/_sql with the user admin and the password, and copy /etc/nginx/tls/cratedb.crt from the instance to trust the certificate (--cacert for curl), or use your own certificate (Step 10). CrateDB's client libraries for Python (crate), Java (JDBC) and Node.js, SQLAlchemy and Grafana all take an HTTPS URL with a user and password.

Step 7: Use psql over the PostgreSQL wire protocol

CrateDB speaks the PostgreSQL wire protocol on 127.0.0.1:5432. On the instance, psql connects with the admin user; the password is read from a password file, not the command line:

PGF="$(mktemp)"; chmod 600 "$PGF"
sudo sed -n 's/^CRATEDB_ADMIN_PASSWORD=\(.*\)$/127.0.0.1:5432:doc:admin:\1/p' /root/cratedb-credentials.txt > "$PGF"
PGPASSFILE="$PGF" psql -h 127.0.0.1 -p 5432 -U admin -d doc -At -c "SELECT sensor, max(temp) FROM doc.readings GROUP BY sensor ORDER BY sensor"
rm -f "$PGF"

s1|22.0
s2|18.25

Port 5432 is not open to the network. From your own machine, reach it through an SSH tunnel (ssh -L 5432:127.0.0.1:5432 ubuntu@<public-ip>) and connect your PostgreSQL client or driver to localhost:5432, database doc, user admin.

Step 8: Check the security controls

Requests without a password, with a wrong one, or as the crate superuser are refused; only /healthz answers without credentials:

H="$(sudo sed -n 's/^CRATEDB_HOST=//p' /root/cratedb-credentials.txt)"
for u in "" "-u admin:wrong" "-u crate:"; do
  curl -s -o /dev/null -w '%{http_code}\n' --cacert /etc/nginx/tls/cratedb.crt --resolve "$H:443:127.0.0.1" $u "https://$H/_sql" \
    -H 'Content-Type: application/json' -d '{"stmt":"SELECT 1"}'
done
curl -s --cacert /etc/nginx/tls/cratedb.crt --resolve "$H:443:127.0.0.1" "https://$H/healthz"

401
401
401
ok

Authentication is password-only, there is no trust entry, and anonymous usage reporting and JavaScript user-defined functions are off:

sudo grep -E '^(network.host|auth.host_based.enabled|udc.enabled|lang.js.enabled):|method:' /etc/crate/crate.yml

network.host: 127.0.0.1
udc.enabled: false
lang.js.enabled: false
auth.host_based.enabled: true
        method: password
        method: password

CrateDB, nginx and first boot cannot reach the instance metadata service:

for u in crate nginx cratedb-firstboot; do
  printf '%s: ' "$u"
  systemctl show -p IPAddressDeny --value "$u" | tr ' ' '\n' | grep -E '^169\.254\.' | sort | paste -sd' '
done

crate: 169.254.0.0/16
nginx: 169.254.169.254/32
cratedb-firstboot: 169.254.0.0/16 169.254.169.254/32

(crate and first boot block the whole link-local range, which includes the metadata address.)

A COPY FROM that names the metadata address or a private address imports nothing, and the readings table is unchanged:

H="$(sudo sed -n 's/^CRATEDB_HOST=//p' /root/cratedb-credentials.txt)"
CFG="$(mktemp)"; chmod 600 "$CFG"
sudo sed -n 's/^CRATEDB_ADMIN_PASSWORD=\(.*\)$/user = "admin:\1"/p' /root/cratedb-credentials.txt > "$CFG"
sql() { curl -s --cacert /etc/nginx/tls/cratedb.crt --resolve "$H:443:127.0.0.1" -K "$CFG" \
  -H 'Content-Type: application/json' --data-binary @- "https://$H/_sql"; }
for src in http://169.254.169.254/latest/meta-data/ http://10.0.0.10/data.json; do
  printf '{"stmt": "COPY doc.readings FROM ? RETURN SUMMARY", "args": ["%s"]}' "$src" | sql | jq -c '.error.message'
done
sql <<'JSON' | jq -c '.rows'
{"stmt": "SELECT count(*) FROM doc.readings"}
JSON
rm -f "$CFG"

"SQLParseException[Host `169.254.169.254` is blocked]"
"SQLParseException[Host `10.0.0.10` is blocked]"
[[3]]

CrateDB refuses these hosts itself, and the egress firewall is a second layer: the crate user cannot connect to internal, link-local, metadata or this VPC's own addresses even if a URL gets past CrateDB's check (for example through a DNS name that resolves to a private address).

The full self test the image build runs is available too:

sudo cratedb-selftest

CRATEDB_SELFTEST_OK https://<CRATEDB_HOST>/ (TLS ok, 401 x3 without valid credentials, admin SQL over HTTPS and PostgreSQL wire ok, 4200/5432/4300 loopback only)

Step 9: Users, privileges and backups

• Application users: create a user for each application with its own password and only the privileges it needs, for example CREATE USER app WITH (password = '<a long random password>') then GRANT DQL ON SCHEMA doc TO app. Run these in the Admin UI console or over /_sql. Keep admin for administration.
• Backups: CrateDB backs up with snapshots. Create an S3 repository with CREATE REPOSITORY backups TYPE s3 WITH (bucket = '<your-bucket>', access_key = '<key>', secret_key = '<secret>') and then CREATE SNAPSHOT backups.snap1 ALL WITH (wait_for_completion = true). The CrateDB process cannot use the instance role (metadata access is blocked), so give the repository an access key for an IAM user limited to that bucket. EBS snapshots of the root volume are an alternative.
• Imports: COPY doc.readings FROM 'https://<public-host>/data.json' and COPY ... FROM 's3://<bucket>/<path>' work for public and S3 sources; private and metadata addresses are refused as shown in Step 8. COPY ... FROM 'file:///<path>' reads files on the instance as the crate service account, and CrateDB asks only for the DML privilege on the target table, so grant DML only to users you would trust with the files that account can read.
• JavaScript functions: CREATE FUNCTION ... LANGUAGE JAVASCRIPT is refused on this image. To allow it, set lang.js.enabled: true in /etc/crate/crate.yml and run sudo systemctl restart crate; any user with DDL on a schema can then run JavaScript inside the database process.

Step 10: Your own DNS name and certificate

Point a DNS name at the instance, then make it the served name. This writes a new self signed certificate for that name (and the instance's IP addresses), updates the credentials note and reloads nginx:

sudo cratedb-set-hostname cratedb.example.com

To use a certificate from your own CA or from Let's Encrypt, replace /etc/nginx/tls/cratedb.crt and /etc/nginx/tls/cratedb.key (keep the key mode 0600) and run sudo systemctl reload nginx. Clients then trust it without --cacert.

Step 11: Change the admin password

sudo cratedb-rotate-password

This gives admin a new random password, checks that the new one works and the old one is refused, and updates CRATEDB_ADMIN_PASSWORD in /root/cratedb-credentials.txt. Update your applications with the new password.

Step 12: Server components

Logs: sudo tail /var/log/cratedb/crate.log and /var/log/nginx/.

Step 13: Security recommendations

• Allow port 22 in the security group from your own networks only, and open 443 only to the people and applications that need CrateDB.
• Give each application its own user with only the privileges it needs, and keep admin for administration.
• Use your own DNS name and a certificate your clients trust (Step 10).
• Keep the operating system patched; unattended security upgrades are on, and sudo apt-get update && sudo apt-get upgrade applies everything else.

Support and licensing

CrateDB 6.5.1 is open source software under the Apache License 2.0 from https://github.com/crate/crate; its LICENSE and NOTICE and the notices of the components it bundles (its Java runtime, libraries and the Admin UI's code) are in /usr/share/doc/cloudimg/cratedb/. The image's price covers the cloudimg packaging, hardening and per-instance setup described above; there is no CrateDB licence key or node limit.

• Email: support@cloudimg.co.uk
• Website: https://www.cloudimg.co.uk
• Support: 24/7, with a 24 hour response SLA