Configuration¶

This page shows where DuckDB's settings live on the image, how to change the memory and spill limits, and how to read data from your own S3 bucket.

Where configuration lives¶

Path	What it holds
`/etc/duckdb/duckdb.env`	Memory limit and spill cap for `duckdb-sql`. Created on first boot.
`/etc/duckdb/customer-s3.sql.example`	Commented S3 example. It runs no S3 query as shipped.
`/etc/duckdb/secrets/`	Your S3 SQL file. `root:duckdb`, mode 0750. `ubuntu` cannot read it.
`/opt/sushi/share/duckdb/extensions/`	Pinned `httpfs` and `aws` extensions
`/opt/sushi/bin/duckdb-sql`	Query wrapper

How `duckdb-sql` runs a query¶

duckdb-sql takes exactly one SQL file:

/opt/sushi/bin/duckdb-sql <file.sql>

For each run it:

loads parquet, httpfs and aws from the pinned extension folder, and turns off automatic extension install and load;
refuses persistent secrets, so a secret lives only for that one run;
applies the memory limit and spill cap from /etc/duckdb/duckdb.env;
spills to /tmp/sushi-duckdb-<uid>/spill;
prints results as CSV without a header, and stops at the first error.

Each run opens a new in-memory database. Anything you create, including an S3 secret, is gone when the run ends, so keep related statements in the same file.

Change the memory and spill limits¶

The defaults are:

DUCKDB_MEMORY_LIMIT=2GB
DUCKDB_MAX_TEMP_DIRECTORY_SIZE=4GB

To change them, edit the file as root:

sudoedit /etc/duckdb/duckdb.env

The file accepts only these two settings, plus blank lines and # comments. Each value must be a whole number followed by MB or GB, for example 6GB. Any other line makes duckdb-sql stop with an error.

The change applies to the next duckdb-sql run. Keep the memory limit below the instance's RAM, and keep enough free disk on the root volume for the spill cap. A query that needs more than either limit fails.

Read from S3¶

Reading from S3 is optional. It uses the instance's IAM role, never access keys. Nothing calls AWS until you run the SQL file you create here.

1. Attach a role to the instance¶

Create a dedicated, least-privilege instance role for the bucket prefix you want to read:

s3:ListBucket on the bucket;
s3:GetObject on the prefix;
only if you write results back with COPY: s3:PutObject, s3:AbortMultipartUpload and s3:ListMultipartUploadParts on the prefix.

The role does not need s3:CreateBucket. The instance also needs outbound HTTPS to S3 and access to the instance metadata service.

2. Write the SQL file¶

Start from the example:

cat /etc/duckdb/customer-s3.sql.example

Create your own file with the secret and the query together. Replace the bucket, prefix, file name and Region:

CREATE OR REPLACE SECRET customer_s3 (
    TYPE S3,
    PROVIDER credential_chain,
    CHAIN 'instance',
    REGION 'us-east-1',
    SCOPE 's3://<your-bucket>/<your-prefix>/'
);
SELECT count(*) FROM read_parquet('s3://<your-bucket>/<your-prefix>/orders.parquet');

CHAIN 'instance' reads the instance role's credentials. SCOPE only picks which URLs use this secret; your IAM policy is what grants or limits access.

Do not put access keys on the instance

Do not add KEY_ID or SECRET values to this file, and do not call load_aws_credentials(). Use the instance role.

3. Install the file and run it as `duckdb`¶

Install the file into the protected folder, then run it as the duckdb service user, which can read that folder:

sudo install -m 0640 -o root -g duckdb ~/customer-s3.sql /etc/duckdb/secrets/customer-s3.sql
sudo -u duckdb /opt/sushi/bin/duckdb-sql /etc/duckdb/secrets/customer-s3.sql

Keep the file root:duckdb, mode 0640. Do not add ubuntu to the duckdb group, and never run SQL as root.

See the DuckDB AWS extension reference for more options.