pg DuckDB 1.1.1

Option A: AWS Marketplace console

• Open the SecureAnyCloud Hardened PostgreSQL 18, PostGIS & Postgres Extension AMIs listing on AWS Marketplace. Choose View purchase options, then Subscribe. One subscription covers every version in the listing.
• Once the subscription is active, choose Launch your software.
• 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.
• Pick an instance type (m5.large is recommended, and the software charge is $0.04 per vCPU per hour) and your key pair.
• For the security group, note that the listing's default group allows SSH (22) only. Restrict it to your own IP address, and do not open 5432 to the internet.
• 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. Replace 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, because the AMI already makes IMDSv2 required.

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 of this output. 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/sac/data/pg_duckdb-samples/orders.csv pg_duckdb.sample_parquet=/var/lib/sac/data/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 the password in ~/.pgpass (mode 0600) as 127.0.0.1:5432:analyticsdb:analyst:<analyst-password>.

Check the PostgreSQL version, the service state and the listening ports:

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. It confirms that pg_duckdb is preloaded and that both per-instance passwords work over TLS, while blank, well-known and wrong passwords are refused. It also confirms that a query really runs through DuckDB, that DuckDB reads the sample files, and that 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

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.

Check the preload setting, the extension version and the DuckDB engine version:

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

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, DuckDB executes an ordinary aggregate over the 50,000-row analytics.orders table:

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 stock PostgreSQL.

Ask PostgreSQL for the query 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 is planned on PostgreSQL's own executor. 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. You need duckdb.force_execution only to push a query that touches nothing but PostgreSQL tables onto DuckDB.

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 run these commands as postgres:

ls -lh /var/lib/sac/data/pg_duckdb-samples/
sudo -u postgres psql -d analyticsdb -c "SELECT count(*) AS parquet_rows FROM read_parquet('/var/lib/sac/data/pg_duckdb-samples/orders.parquet');" -c "SELECT count(*) AS csv_rows FROM read_csv('/var/lib/sac/data/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/sac/data/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. The next statement joins a live PostgreSQL table on one side to a file on the other:

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/sac/data/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. This example writes a summary file, reads it back and removes it:

sudo -u postgres psql -d analyticsdb -c "SELECT duckdb.raw_query(\$\$COPY (SELECT region, count(*) AS orders FROM read_parquet('/var/lib/sac/data/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)

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. There are 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, and 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/sac-server.crt) and the per-instance password.

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

OUT="$(PGPASSWORD=<set-at-first-boot> 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"

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. Run these checks to confirm each control:

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/sac/data/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

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 the following 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. That 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.