Skip to main content
Version: Next

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 and checks every statement against roles, table grants, row policies and column masks before it reaches a node.

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​

Set QOD_ACL_ENABLED=true and 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. A personal access token runs as its owner, narrowed by the token's own scope; no user name, including superuser, bypasses the check. 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(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​