Securing a DuckDB Server in Production

Abstract​

DuckDB is an in-process database. Its defaults assume that whoever writes the SQL is the person who owns the process: they can read the files it can read, use the cloud keys it can use, and change any setting. That assumption is right on a laptop. It is wrong the moment DuckDB sits behind a server (Quack, Arrow Flight SQL, an HTTP endpoint) and several users send it SQL.

When we moved Quack on Demand to DuckDB 2.0, we audited every way a SQL user of a shared DuckDB node could reach something the operator did not mean to share. This post is the result: eight classes of exposure, a concrete example for each, and what to do about it. We close with how Quack on Demand handles all eight by default, on the DuckDB nodes and in the gateway that checks every statement before it reaches them.

A SQL user of a DuckDB server is not the operating system user that runs it, but by default DuckDB treats them as if they were.

Everything below follows from that. A shared DuckDB process has one filesystem, one environment, one set of secrets, one set of mostly global settings and one extension directory. Any SQL user who can name one of those can usually use it.

A DuckDB node needs credentials: an object-store key, the password of the Postgres database that holds a DuckLake catalog, tokens for remote catalogs. The easy ways to hand them over all leave them readable from SQL.

What we do instead. Secrets reach the node as files in an owner-only directory that the launcher deletes right after reading. Databases are attached through a named temporary secret, which is dropped as soon as the ATTACH returns:

CREATE TEMPORARY SECRET crm_cred (TYPE postgres, HOST 'pg.internal', PORT 5432, DATABASE 'crm', USER 'reporting', PASSWORD '...'); ATTACH '' AS crm (TYPE postgres, SECRET crm_cred, READ_ONLY); DROP SECRET crm_cred;

The attached catalog keeps working after the drop. We checked that it still reconnects after every one of its Postgres backends is terminated from outside. The init script reaches the CLI through -init (a FIFO), which the CLI does not record, and never through stdin.

DuckDB loads every persistent secret it finds under ~/.duckdb/stored_secrets the first time a secret is needed. On a developer machine this is a feature. On a server, it means the operator's own cloud keys, saved months ago for some unrelated notebook, are now usable by every SQL user of every node started from that account.

The obvious fix, SET allow_persistent_secrets = false, has a cost on DuckDB 2.0: every Postgres ATTACH that does not name a secret then fails, including a DuckLake catalog on Postgres:

Invalid Input Error: Unknown secret storage found: "local_file"

What works is to keep persistent secrets enabled but give them nowhere to come from and nowhere to go:

-- before any secret, ATTACH or extension use SET secret_directory = '/run/node-1234/secrets/none/s'; SELECT count(*) FROM duckdb_secrets(); -- 0, and the setting is now locked

Here secrets is an empty directory with mode 0500, and none is a read-only regular file inside it. Nothing can be loaded from below a file, and nothing can be created there either: CREATE PERSISTENT SECRET fails, even for root, which ignores the directory's own permissions. Once the secret manager has been used, DuckDB refuses to change this for every session:

Invalid Input Error: Changing Secret Manager settings after the secret manager is used is not allowed!

3. The filesystem is part of the SQL surface​

DuckDB reads files by path in many places: read_parquet, read_csv, glob, COPY ... TO, ATTACH, EXPORT DATABASE, a string literal in a FROM clause. DuckDB 2.0 adds a less obvious one: a table name it cannot resolve is tried as a file. With no schema named config, this reads ./config.json from the process's working directory:

SELECT * FROM config.json;

Quoted names count too. FROM "s3://bucket/data/*.parquet" and FROM file:///etc/hosts look like identifiers to anything that only checks table names, and read an object store or a local file with the node's own credentials.

In a shared process, many SET statements are not about your session. threads, memory_limit, temp_directory, checkpoint and WAL settings, the HTTP client, logging and profiling all change the process for every session. One user running SET threads = 1 slows down everyone on the node.

What to do: set what the node needs at startup, and refuse every node-wide setting to ordinary users. Session settings (TimeZone, search_path, http_timeout, SET VARIABLE) stay available.

DuckDB is generous with metadata, and on a shared server that metadata belongs to other people:

SELECT * FROM duckdb_secrets(); -- names, key ids, hosts, users SELECT current_setting('s3_secret_access_key'); -- an unredacted key SELECT * FROM duckdb_logs; -- other sessions' statements SELECT path FROM duckdb_databases(); -- connection strings, file paths SELECT pg_get_viewdef(oid) FROM range(1, 5000) t(oid); -- every view's SQL

On a Quack server, quack_active_connections() lists every session's running query. If the server holds a DuckLake catalog with encryption on, its metadata tables hold every Parquet file's key.

What to do: reserve these functions to administrators. Filtering them is not enough: a refused duckdb_settings() is pointless if current_setting() with a computed name still answers.

Several DuckDB functions execute a string or a plan chosen at run time:

A check that reads statement text sees the call but not what it will run, so query_table('other_tenant.main.' || 'salaries') passes a table-level grant check untouched.

What to do: treat these as their own class. Refuse them, or at least refuse them whenever their argument is not a constant, to anyone whose access you check table by table. Judge built-in macros by their body, not their name, and pin the list so a new DuckDB release cannot add one silently.

Attaching another database READ_ONLY is engine-level protection, but it has a side door. postgres_execute (and mysql_execute) send raw SQL to the remote server, and ending DuckDB's remote transaction first lets a write through:

BEGIN; CALL postgres_execute('crm', 'COMMIT'); CALL postgres_execute('crm', 'DELETE FROM invoices'); -- lands on the remote server COMMIT;

Building the function name at run time (query('FROM postgres_' || 'execute(...)')) hides it from a text screen, and a macro created earlier hides it completely.

What to do: refuse passthrough calls aimed at a read-only catalog, and run-time-computed SQL on any node that has one. But the only complete guarantee is on the other side: connect with a remote role that has no write privileges.

Running the DuckDB CLI as a long-lived server process is convenient, and it brings habits from interactive use that do not belong on a server.

Every check above depends on reading a statement exactly as DuckDB does, and DuckDB 2.0 moved several goalposts:

We ended up porting DuckDB's tokenizer into the gateway so that the comment stripper, the statement splitter, the classifier and the access check all see the same tokens DuckDB does. Whatever a check cannot parse, it refuses.

Quack on Demand serves DuckDB and DuckLake to many users through Arrow Flight SQL, the native Quack protocol and MCP. Every item above is enforced by default on the DuckDB 2.0 release: on the nodes themselves (secret handling, working directory, settings), and in the gateway, which reads each statement with DuckDB's own tokenizer before checking it against the user's grants. Per-pool lockdown applies the same rules to administrators. The Quack on Demand documentation covers how to turn each of them on and what they refuse.