Skip to main content
Version: 0.12.1

DuckDB access control: roles, grants, row and column security

DuckDB has no users and no GRANT: whoever can open the file reads every table. Quack on Demand puts a gateway in front of DuckDB that authenticates each connection, applies row policies and column masks, and runs each statement on a node that checks it against the caller's roles and table grants before it executes.

The problem​

An embedded engine trusts its process. That is fine for one analyst and breaks the moment two teams share a DuckDB or DuckLake dataset: there is no way to say that finance may read orders but not salaries, that a regional manager sees only their region, or that an email column must be hashed for everyone except support. Copying files per audience or hiding tables behind views does not hold up, because the engine still trusts whoever holds the file.

How Quack on Demand does it​

Every statement, whether it arrives over Arrow Flight SQL, DuckDB's native Quack protocol, the MCP server or an admin endpoint that previews, diffs, restores or undrops a table, is matched against the caller's effective permission set; there is no switch to turn this off. The check runs inside the node, on the plan DuckDB bound: the tables, views and columns the statement reads or writes, the functions and settings it calls and the files it names, so a view, a macro or a query() string cannot hide a table from it. Files are reachable only under S3 keys the user registered or folders the tenant authorized. A personal access token runs as its owner, narrowed by the token's own scope. Only a real superuser (an account with no tenant, signed in through the system realm) skips the check; a tenant user named superuser gets no special treatment. Roles bundle table verbs (RO, RW, DDL, ALL) on catalog.schema.table triples with wildcards. Groups collect roles and pool grants. A user reaches permissions directly or through their groups, and admission to a pool is what admits them to that pool's database. Row policies rewrite a matching SELECT with a filter, and column policies mask or deny a column before the SQL reaches DuckDB.

Every tenant also carries four protected built-ins: the roles qod_all_tables and qod_no_tables and the groups qod_all_pools and qod_no_pools. A user created without naming roles and groups joins qod_all_tables and qod_all_pools, which give full access to the tenant; name the roles and groups at creation (CREATE USER bob PASSWORD '...' ROLES analyst GROUPS finance) to start narrower.

You can manage all of it from the admin UI, the qod CLI, or plain SQL from any connected client:

CREATE ROLE analyst;
GRANT SELECT ON acme_tpch.tpch1.customer TO ROLE analyst;
GRANT ROLE analyst TO GROUP finance;
GRANT CONNECT ON POOL bi TO GROUP finance;

CREATE OR REPLACE ROW POLICY ON acme_tpch.tpch1.orders FOR ROLE analyst
  USING (region = ${tenantId} OR owner = ${user});

CREATE OR REPLACE COLUMN POLICY ON acme_tpch.tpch1.customer COLUMN c_email
  FOR ROLE analyst MASK USING (SHA256('<long-random-secret>' || CAST(c_email AS VARCHAR)));

Every grant, denial and policy change lands in the audit log. Quack on Demand runs as a single Docker container on one node, or on Kubernetes for multi-host fleets.

Go deeper​