pg_duckdb 1.1.1 on AWS User Guide

Overview

This guide covers launching and verifying pg_duckdb 1.1.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.

pg_duckdb is the official PostgreSQL extension for DuckDB (MIT licensed). It embeds DuckDB's vectorised, columnar execution engine inside the PostgreSQL backend, so an analytical query over an ordinary PostgreSQL table can be executed by DuckDB instead of PostgreSQL's row-at-a-time executor, with no change to your SQL and no data moved anywhere. read_parquet() and read_csv() make Parquet and CSV files queryable as tables, and you can join them against your PostgreSQL tables in one statement.

The image installs PostgreSQL 17.11 from the official PostgreSQL (PGDG) apt repository and builds pg_duckdb 1.1.1 from its release tag together with the DuckDB 1.4.3 engine it vendors (with the json, icu and httpfs extensions compiled in). The PostgreSQL packages are not held, so apt upgrade keeps delivering PGDG minor (security) releases, and unattended security upgrades stay on for the operating system.

pg_duckdb has no web interface. You reach it with psql or any PostgreSQL client driver, exactly like any other PostgreSQL database.

What is on the image:

• PostgreSQL 17.11 (postgresql@17-main.service) with pg_duckdb preloaded through shared_preload_libraries (the extension reports extversion 1.1.0, upstream's script version for the 1.1.1 release)
• A database analyticsdb with an analytics schema and a 50,000-row orders table, plus the same rows as a Parquet file and a CSV file under /var/lib/cloudimg/pg_duckdb-samples/, so the first query works with no cloud account
• The duckdb_users role, which controls who may run queries through DuckDB, and the non-superuser application role analyst (a member) that owns the analytics schema
• Per-instance passwords for postgres and analyst and a per-instance TLS certificate, all generated on your instance's first boot
• pg-duckdb-selfcheck, which verifies the whole appliance end to end

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

Security posture

• No default password. The image ships with no password on any database role. First boot generates unique passwords for the postgres superuser and the analyst role and writes them to /root/pg-duckdb-credentials.txt, readable by root only. Locally, the postgres system user still connects over the unix socket with no password (peer authentication).
• Per-instance TLS certificate. First boot creates this instance's own private key and self-signed certificate 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 8.
• 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 server-side fetching of URLs. duckdb.disabled_filesystems = 'HTTPFileSystem' stops every role, superusers included, from making the database read an http:// or https:// URL, and only a superuser can change that setting. DuckDB's local file access is off for non-superusers such as analyst.
• No metadata access for the database. Every PostgreSQL unit is blocked from reaching the instance metadata service (169.254.169.254 and fd00:ec2::254), so no query can obtain the instance role's credentials. Amazon Time Sync (169.254.169.123) still works. Read S3 with an explicit DuckDB secret (Step 10), not 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 (certificate or passwords), it stops PostgreSQL rather than leave it half configured, and 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. DuckDB is memory hungry by design: the image caps it at 1 GB per connection (duckdb.max_memory), which fits m5.large. For heavier analytics choose m5.xlarge or a memory-optimised type and raise the cap (see Tuning).

Connecting to your instance

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 pg_duckdb 1.1.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 pg-duckdb \
  --description "pg_duckdb" --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=pg-duckdb}]'

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. It creates this instance's TLS key and certificate, turns TLS on, sets the postgres and analyst passwords and writes the credentials file. Show the connection details (the password lines are left out here; read them with sudo cat /root/pg-duckdb-credentials.txt and keep them in your secrets manager):

sudo grep -v '\.password=' /root/pg-duckdb-credentials.txt

# pg_duckdb 1.1.1 on PostgreSQL 17 - generated on first boot by pg-duckdb-firstboot.service.
# These passwords are unique to this instance. Store them somewhere safe.

PG_DUCKDB_URL=postgresql://127.0.0.1:5432/analyticsdb?sslmode=require

# --- PostgreSQL superuser (local peer login needs no password: sudo -u postgres psql) ---
postgres.host=127.0.0.1
postgres.port=5432
postgres.role=postgres
postgres.database=analyticsdb
postgres.sslmode=require

# --- Application role (owns the analytics schema; may drive DuckDB execution) ---
analyst.role=analyst

# --- pg_duckdb ---
pg_duckdb.version=1.1.0
pg_duckdb.analytics_schema=analytics
pg_duckdb.sample_csv=/var/lib/cloudimg/pg_duckdb-samples/orders.csv
pg_duckdb.sample_parquet=/var/lib/cloudimg/pg_duckdb-samples/orders.parquet
public_ip=<public-ip>
...

The commands below read a 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:analyticsdb:analyst:<analyst-password>.

Step 3: Confirm PostgreSQL is running and verify the appliance

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

psql (PostgreSQL) 17.11 (Ubuntu 17.11-1.pgdg24.04+2)
active
LISTEN 0      4096      127.0.0.54: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.53%lo: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. The bundled self-check proves the whole appliance in one command: pg_duckdb is preloaded, both per-instance passwords work over TLS while blank, well-known and wrong ones are refused, a query really runs through DuckDB, DuckDB reads the sample files, and an http:// read is refused:

sudo pg-duckdb-selfcheck

self-check OK: pg_duckdb 1.1.0 preloaded and live; postgres and analyst authenticated over TLS with their per-instance passwords while blank, well-known and wrong passwords were refused; a 50,000-row aggregate over a PostgreSQL table ran through a DuckDBScan node and agreed with PostgreSQL on sum(amount) = 3568661.22; DuckDB read 50000 Parquet and 50000 CSV rows from local disk; analyst ran through DuckDB and its http:// read of the metadata address was refused

Step 4: Confirm the extension is live

The shared_preload_libraries line is the one that matters: without it the extension's planner hook is never installed and no query reaches DuckDB.

sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;'
sudo -u postgres psql -d analyticsdb -tAc "SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_duckdb';"
sudo -u postgres psql -d analyticsdb -tAc "SELECT r['v'] FROM duckdb.query(\$\$SELECT version() AS v\$\$) r;"

pg_duckdb
pg_duckdb|1.1.0
v1.4.3

Step 5: Run an analytical query through DuckDB

On the instance the unix socket needs no password, so sudo -u postgres psql -d analyticsdb opens an interactive session on the sample database. With duckdb.force_execution on, an ordinary aggregate over the 50,000-row analytics.orders table is executed by DuckDB:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = true;" -c "SELECT region, count(*) AS orders, round(sum(amount), 2) AS revenue FROM analytics.orders GROUP BY region ORDER BY region;"

SET
 region | orders |  revenue
--------+--------+-----------
 amer   |  12500 | 892173.48
 apac   |  12500 | 892144.77
 emea   |  12500 | 892226.91
 latam  |  12500 | 892116.06
(4 rows)

The SQL is completely ordinary; the same statement runs unchanged on a stock PostgreSQL.

Step 6: Prove the query really ran on DuckDB

Ask for the plan. When DuckDB executes a query, the plan contains a Custom Scan (DuckDBScan) node:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = true;" -c "EXPLAIN (COSTS FALSE) SELECT region, count(*), sum(amount) FROM analytics.orders GROUP BY region;"

SET
          QUERY PLAN
-------------------------------
 Custom Scan (DuckDBScan)
   DuckDB Execution Plan:

 ┌───────────────────────────┐
 │       HASH_GROUP_BY       │
 │    ────────────────────   │
 │         Groups: #0        │
 │                           │
 │        Aggregates:        │
 │        count_star()       │
 │          sum(#1)          │
 │                           │
 │        ~31,606 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │         PROJECTION        │
 │    ────────────────────   │
 │           region          │
 │           amount          │
 │                           │
 │        ~50,000 rows       │
 └─────────────┬─────────────┘
 ┌─────────────┴─────────────┐
 │  PGDUCKDB_POSTGRES_SCAN   │
 │    ────────────────────   │
 │       Table: orders       │
 │                           │
 │        Projections:       │
 │           region          │
 │           amount          │
 │                           │
 │        ~50,000 rows       │
 └───────────────────────────┘


(35 rows)

With DuckDB execution off, the same query plans on PostgreSQL's own executor, and both engines return the same total:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = false;" -c "EXPLAIN (COSTS FALSE) SELECT region, count(*), sum(amount) FROM analytics.orders GROUP BY region;"
sudo -u postgres psql -d analyticsdb -tA -c "SET duckdb.force_execution = true;" -c "SELECT round(sum(amount), 2) FROM analytics.orders;"
sudo -u postgres psql -d analyticsdb -tA -c "SET duckdb.force_execution = false;" -c "SELECT round(sum(amount), 2) FROM analytics.orders;"

SET
        QUERY PLAN
--------------------------
 HashAggregate
   Group Key: region
   ->  Seq Scan on orders
(3 rows)

SET
3568661.22
SET
3568661.22

As soon as a query uses a DuckDB-only feature such as read_parquet(), DuckDB execution is selected automatically; duckdb.force_execution is only needed to push a query that touches nothing but PostgreSQL tables onto DuckDB.

Step 7: Query Parquet and CSV files, and join them to a table

The image ships a Parquet file and a CSV file holding the same 50,000 rows, so the data-lake path works immediately with no object storage and no credentials. Reading files from the server's disk is a superuser action, so these run as postgres:

ls -lh /var/lib/cloudimg/pg_duckdb-samples/
sudo -u postgres psql -d analyticsdb -c "SELECT count(*) AS parquet_rows FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet');" -c "SELECT count(*) AS csv_rows FROM read_csv('/var/lib/cloudimg/pg_duckdb-samples/orders.csv');"
sudo -u postgres psql -d analyticsdb -c "SELECT r['region'] AS region, count(*) AS orders, round(sum(r['amount']::numeric), 2) AS revenue FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet') r GROUP BY r['region'] ORDER BY 1;"

total 2.1M
-rw-r--r-- 1 root root  158 Oct 21 13:56 SHA256SUMS
-rw-r--r-- 1 root root 1.9M Oct 21 13:56 orders.csv
-rw-r--r-- 1 root root 230K Oct 21 13:56 orders.parquet
 parquet_rows
--------------
        50000
(1 row)

 csv_rows
----------
    50000
(1 row)

 region | orders |  revenue
--------+--------+-----------
 amer   |  12500 | 892173.48
 apac   |  12500 | 892144.77
 emea   |  12500 | 892226.91
 latam  |  12500 | 892116.06
(4 rows)

Select columns out of a file with the r['column'] syntax. A live PostgreSQL table on one side and a file on the other, joined in one statement:

sudo -u postgres psql -d analyticsdb -c "SELECT p.region, p.db_orders, f.file_orders FROM (SELECT region, count(*) AS db_orders FROM analytics.orders GROUP BY region) p JOIN (SELECT r['region'] AS region, count(*) AS file_orders FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet') r GROUP BY r['region']) f ON f.region = p.region ORDER BY 1;"

 region | db_orders | file_orders
--------+-----------+-------------
 amer   |     12500 |       12500
 apac   |     12500 |       12500
 emea   |     12500 |       12500
 latam  |     12500 |       12500
(4 rows)

The engine writes files too, which is how the bundled Parquet file was produced:

sudo -u postgres psql -d analyticsdb -c "SELECT duckdb.raw_query(\$\$COPY (SELECT region, count(*) AS orders FROM read_parquet('/var/lib/cloudimg/pg_duckdb-samples/orders.parquet') GROUP BY region ORDER BY region) TO '/tmp/region_summary.parquet' (FORMAT parquet)\$\$);" -c "SELECT * FROM read_parquet('/tmp/region_summary.parquet') r ORDER BY r['region'];"
sudo rm -f /tmp/region_summary.parquet

 raw_query
-----------

(1 row)

NOTICE:  result: Count
BIGINT
[ Rows: 1]
4

 region | orders
--------+--------
 amer   |  12500
 apac   |  12500
 emea   |  12500
 latam  |  12500
(4 rows)

Step 8: Connect from your application

For applications use the analyst role rather than the superuser. It owns the analytics schema and is a member of duckdb_users, so it can run queries through DuckDB. Connect over TLS with its per-instance password:

export PGPASSWORD="$(sudo sed -n 's/^analyst\.password=//p' /root/pg-duckdb-credentials.txt)"
psql "host=127.0.0.1 port=5432 dbname=analyticsdb user=analyst sslmode=require" -c "SELECT current_user, ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid();" -c "SET duckdb.force_execution = true;" -c "SELECT region, count(*) FROM analytics.orders GROUP BY region ORDER BY 1;"
unset PGPASSWORD

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

SET
 region | count
--------+-------
 amer   | 12500
 apac   | 12500
 emea   | 12500
 latam  | 12500
(4 rows)

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

• 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 analyst with sslmode=require.
• Open it to your VPC. Change listen_addresses = '127.0.0.1' to listen_addresses = '*' in /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf, run sudo systemctl restart postgresql@17-main.service, 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 per-instance password.

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 port=5432 dbname=analyticsdb user=analyst sslmode=require" -c "SELECT 1" 2>&1 || true)"
echo "${OUT#*failed: }"

FATAL:  password authentication failed for user "analyst"

Step 9: Check the security controls

The PostgreSQL units carry the metadata block, an http:// read is refused for every role, and analyst (not a superuser) cannot read files off the server's disk:

systemctl show -p IPAddressDeny postgresql@17-main.service
export PGPASSWORD="$(sudo sed -n 's/^analyst\.password=//p' /root/pg-duckdb-credentials.txt)"
OUT="$(psql "host=127.0.0.1 port=5432 dbname=analyticsdb user=analyst sslmode=require" -tAc "SELECT count(*) FROM read_csv('http://169.254.169.254/latest/meta-data/');" 2>&1 || true)"; echo "${OUT}" | grep -o 'File system [A-Za-z]* has been disabled by configuration'
OUT="$(psql "host=127.0.0.1 port=5432 dbname=analyticsdb user=analyst sslmode=require" -tAc "SELECT count(*) FROM read_csv('/var/lib/cloudimg/pg_duckdb-samples/orders.csv');" 2>&1 || true)"; echo "${OUT}" | grep -o 'File system [A-Za-z]* has been disabled by configuration'
unset PGPASSWORD
sudo -u postgres psql -tAc "SELECT rolname, rolsuper, pg_has_role(rolname, 'duckdb_users', 'member') FROM pg_roles WHERE rolname IN ('analyst', 'postgres') ORDER BY 1;"

IPAddressDeny=169.254.169.254/32 168.63.129.16/32 fd00:ec2::254/128
File system HTTPFileSystem has been disabled by configuration
File system LocalFileSystem has been disabled by configuration
analyst|f|t
postgres|t|t

Step 10: Connect object storage

Everything above needs no cloud account. To read your own data lake, create a DuckDB secret with explicit credentials, then read remote paths the same way as local ones. Run this as postgres or another member of duckdb_users. Use an IAM user or role key scoped to the buckets you need; the instance role is not available to the database because the metadata service is blocked for it:

SELECT duckdb.create_simple_secret(
    type := 'S3', key_id := 'your_key', secret := 'your_secret', region := 'eu-west-2'
);

SELECT count(*) FROM read_parquet('s3://your-bucket/your-data.parquet');

Google Cloud Storage and Cloudflare R2 work the same way (gs://, r2://). Plain http:// and https:// URLs stay disabled; to allow them, a superuser can remove HTTPFileSystem from duckdb.disabled_filesystems in the configuration file, which also lets any role in duckdb_users make the server fetch any URL it can reach. Secrets created this way are visible to members of duckdb_users, so grant that role deliberately.

Tuning

The shipped configuration lives in /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf; this instance's TLS settings are in 20-cloudimg-tls.conf next to it. Apply changes with sudo systemctl restart postgresql@17-main.service.

To make DuckDB execution the default for one role rather than typing SET every session, run as postgres:

ALTER ROLE analyst IN DATABASE analyticsdb SET duckdb.force_execution = true;

Maintenance

• Change a password. Run sudo -u postgres psql and type \password analyst (or \password postgres) at the prompt; it asks for the new password twice and never puts it on a command line. Then update /root/pg-duckdb-credentials.txt and your secrets manager.
• Updates. sudo apt update && sudo apt upgrade installs PostgreSQL 17 minor releases from PGDG and Ubuntu security updates. pg_duckdb is built from source for PostgreSQL 17; a newer pg_duckdb release needs a rebuild against a matching PostgreSQL major.
• Backups. Take EBS snapshots or pg_dump backups of analyticsdb regularly.
• Logs. /var/log/postgresql/postgresql-17-main.log and journalctl -u postgresql@17-main.service; first boot logs to journalctl -u pg-duckdb-firstboot.service.

Troubleshooting

• EXPLAIN shows no DuckDBScan node. Either duckdb.force_execution is not set in that session, or the connected role is not a member of duckdb_users. Check with SELECT pg_has_role(current_user, 'duckdb_users', 'member');.
• read_parquet() on a local path says LocalFileSystem has been disabled. DuckDB's local file access is off for roles that are not superusers and not members of both pg_read_server_files and pg_write_server_files. Read local files as postgres, or grant those roles deliberately if an application genuinely needs server-side file access.
• Queries fail with an out-of-memory error. The cap is per connection: lower duckdb.max_memory, or move to a larger instance and raise it.
• The database is unreachable from outside the instance. It listens on localhost by default: use the SSH tunnel, or follow "Open it to your VPC" in Step 8, and connect with sslmode=require.

Support

cloudimg supports this image by email at support@cloudimg.co.uk. Include the instance ID, the version (pg_duckdb 1.1.1 on Ubuntu 24.04) and what you were doing. For pg_duckdb itself, the upstream documentation at github.com/duckdb/pg_duckdb covers the function reference, settings and data-lake integration.

DuckDB is a trademark of the DuckDB Foundation. PostgreSQL is a trademark of the PostgreSQL Community Association of Canada. Ubuntu is a trademark of Canonical Ltd. cloudimg is not affiliated with or endorsed by these organisations.