Downloads 1.4kthis week

GitHub stars 3

Extension descriptor (YAML)

Authorization for untrusted read-only SQL from tenants, LLM agents, and dashboard builders. Validate a statement without executing it, or enforce a lockable policy on the connection itself, with an audit log of every decision.

Installing and Loading

INSTALL gatekeeper FROM community;
LOAD gatekeeper;

Example

-- Something to protect, and something that must stay hidden
CREATE SCHEMA reporting;
CREATE TABLE reporting.orders (customer_id INTEGER, amount DOUBLE);
CREATE TABLE secrets (token VARCHAR);

-- Trusted setup: install the global ceiling, then freeze it.
-- An omitted catalog matches any database; add catalog: 'mydb' to pin one.
CALL gatekeeper_configure(
    allowed_tables := [{schema_path: ['reporting'], 'table': '*'}],
    blocked_functions := [{catalog:'system', schema_path:['main'], name:'md5', type:'scalar'}]
);
-- Success
-- true

SET lock_configuration = true;

-- Allowed: an allowed table with default (reviewed read-only) functions.
-- The resolved dependencies come back as binding evidence.
SELECT allowed, code, objects[1]."table" AS resolved_table
FROM gatekeeper_validate('SELECT customer_id, sum(amount) FROM reporting.orders GROUP BY customer_id');
-- allowed | code | resolved_table
-- true    | ok   | orders

-- Denied: DDL and DML are never allowed
SELECT allowed, code FROM gatekeeper_validate('DROP TABLE reporting.orders');
-- allowed | code
-- false   | unsupported

-- Denied: tables are matched by resolved identity, with structured diagnostics
SELECT allowed, code, violations[1].rule AS rule, violations[1].schema_path AS schema_path, violations[1]."table" AS "table"
FROM gatekeeper_validate('SELECT * FROM secrets');
-- allowed | code      | rule  | schema_path | table
-- false   | forbidden | table | [main] | secrets

-- Denied: a request can narrow the global policy but never widen it
SELECT allowed, code, violations[1].rule AS rule, violations[1].function_name AS function_name
FROM gatekeeper_validate('SELECT md5(''x'')', blocked_functions := []);
-- allowed | code      | rule     | function_name
-- false   | forbidden | function | md5

-- Narrowed: per-request options restrict a tenant to one table within the ceiling
SELECT allowed, code
FROM gatekeeper_validate('SELECT count(*) FROM reporting.orders',
                         allowed_tables := [{schema_path: ['reporting'], 'table': 'orders'}]);
-- allowed | code
-- true    | ok

-- Engine errors report their phase and are never 'ok'
SELECT allowed, code, error_type FROM gatekeeper_validate('SELECT * FROM missing_table');
-- allowed | code    | error_type
-- false   | binding | Catalog

About gatekeeper

Gatekeeper answers one question about SQL you did not write: does this statement stay inside the lines you drew? It is built for multi-tenant analytics, LLM agents, and embedded dashboard builders where the SQL text is untrusted but the database is yours.

• Ask with gatekeeper_validate: the decision and structured diagnostics, without executing anything.
• Draw the lines once: a lockable global policy that per-request options can narrow, never widen.
• Or let DuckDB answer for itself: CALL gatekeeper_enforce() refuses denied statements on that connection, with an audit log and a log-only rollout mode.

Validate a query

SELECT allowed, code, violations, error_message
FROM gatekeeper_validate(?, allowed_tables := ?);  -- host-bound parameters

Validation parses and binds the SQL on your connection, checks the resolved objects and functions attributable to the caller, and checks the plan for read-only operators. It returns one row; it does not execute the query.

Execute only when allowed = true and code = 'ok'. Treat exceptions and missing rows as denials, then execute the same SQL text on the same connection. Binding can perform I/O; see Security boundaries.

Functions and options

SELECT * FROM gatekeeper_validate(sql VARCHAR, option := value, ...)  -- one result row
CALL gatekeeper_configure(option := value, ...)                       -- replaces the global policy
CALL gatekeeper_enforce()                                             -- enforces the policy on this connection

The first two take the same named options and accept host-bound parameters (?, $1), so policies never need to be spliced into SQL text.

Only a whole-component '*' is a wildcard in catalog/schema components and table names: sales_*, ?, and % are literal names. Function names cannot be omitted or wildcarded; schema-wide function permission is unsupported. Wildcards also match objects created or attached later, so prefer explicit catalog names when that scope is not intended.

Configurable function grants and blocks match exact catalog-entry names, without alias canonicalization, including Parquet readers, JSON extraction aliases, and window aliases. Cover each intended entry explicitly: read_parquet and parquet_scan have separate permissions, and Parquet file shorthand selects parquet_scan. Source-defined parser/binder rewriting determines the operation or entry checked, not general semantic equivalence.

Internal table/view and function grants require exact schema components; tables/views also require an exact table name. Catalog may be omitted, NULL, or *; block namespace wildcards still match internal entries. The actual entry's internal flag controls this, not the system catalog name. Non-internal entries can use schema patterns; unknown bound-function internal origin cannot use schema-wildcard grants. Reviewed defaults already name exact identities.

Follow the policy v2 migration guide for legacy function strings, old schema fields, typed empty lists, and canonical settings.

Result columns

Violation rule values: function, table, internal_object, dynamic_sql, replacement_scan, bind_time_expression, statement, limit, unsupported_structure. Branch on code and violations[].rule, not on message text.

Known denied functions retain their catalog, schema path, name, and function_type, even though functions is empty on failure. function_type uses the same kinds as functions[].type; it is '' for unresolved kinds and nonfunction violations. Resolved catalog-object denials retain object_type = 'table' or 'view' for allowlist misses, explicit blocks, and internal-object refusals; the table rule alone does not distinguish tables from views. object_type is '' for unresolved objects and function-only or other nonobject violations, including replacement-reader refusals whose table contains a written path. All evidence lists (objects, functions, caller_objects, caller_functions) remain empty on failure. Consumers pinning the violation STRUCT must include both trailing VARCHAR kind fields. Audit decisions returned by duckdb_logs_parsed('Gatekeeper') carry the same violation shape.

functions remains combined host-facing evidence of caller-attributable functions and trusted dependencies. caller_functions is its caller-scoped authorization subset, including implied capabilities and conservative over-attribution; it is not a lexical call list or public-safe projection. caller_objects remains the conservative catalog-table/view subset of objects.

Global policy

• Global ceiling: CALL gatekeeper_configure(...) replaces the policy for the database instance. Omitted options take their defaults; the policy is not persisted.
• Per-request narrowing: options on gatekeeper_validate(...) can restrict that ceiling, never grant something it denies. Blocks win for caller-attributable references.
• Defaults: non-internal tables and views are unrestricted until allowed_tables is set. Caller functions always need a reviewed default or an explicit grant.

Trusted views and macros

Allow the outer view/table or macro to expose it as a host-controlled capability. Its dependencies are opaque to table and function policy, including blocks and the caller's never-bind list. Independent control-plane and supported-execution-scope checks still apply: Gatekeeper's control plane remains refused on every route, and private catalog authorization refuses unsupported Quack dependencies even inside trusted bodies. Deferred binding can execute remotely before that refusal; see the Quack support matrix.

• Allowing a view admits its underlying tables. Block the view itself to withdraw it.
• A caller's separate reference to an underlying table or function is still checked.
• A macro forwarding a caller argument to query_table(n) delegates table selection; caller table restrictions do not constrain that selection.
• objects and functions still report transitive dependencies as host binding evidence. For Quack, this is checked local binding, not recursively complete remote lineage. caller_objects is conservative query-wide attribution, not exact lexical dependencies: a caller-written name can attribute a matching hidden object inside a trusted body.
• Validation results and audit diagnostics, including all evidence lists, violations and engine errors, are privileged host information. caller_objects is not universally caller-safe. Applications may expose a minimal decision or a separately reviewed projection.

See the trusted-definition rules for query-wide name overlaps and bound implementation checks.

Enforced connections

To have DuckDB refuse denied statements automatically, finish trusted setup first: load extensions, attach catalogs, configure policy and host restrictions, enable logging, and lock configuration. Then, outside a transaction, run this on each LOCAL connection before handing out SQL access. On DuckDB 2.0, use a fresh local connection or DISCONNECT during trusted setup, before submitting activation, and keep the connection LOCAL:

CALL gatekeeper_enforce();

• Denials raise a Permission Error; enforcement is permanent for that connection.
• Parameters and relation queries are covered; prepared executions use the current policy.
• The result includes host-setting warnings. Keep a separate host connection for administration.
• An already-connected DuckDB 2.0 session can dispatch SQL before the local enforcement hook, including the activation call. A successful remote result does not establish local enforcement.

See the enforcement guide for setup and execution-boundary details, including parser-time side effects.

Audit and log-only mode

Enable logging during trusted setup, then inspect decisions from a host connection:

CALL enable_logging('Gatekeeper');
SELECT boundary, code, violations, statement
FROM duckdb_logs_parsed('Gatekeeper') WHERE event = 'decision' AND NOT allowed;

• Denials and policy changes: recorded at INFO.
• Allowed decisions: also recorded with SET logging_level = 'debug'.
• Trial rollout: SET gatekeeper_log_only = true records policy denials without refusing them. Set it back to false to restore policy refusals.
• Routing exception: DuckDB 2.0 CONNECT/DISCONNECT remain refused before binding, with audit mode = 'enforce', so the rollout preserves LOCAL routing.

Apart from those routing controls, log-only provides no policy protection, including for Gatekeeper's settings. Before locking configuration, plan how the host will turn it off. See the audit log and log-only guide.

What is never allowed

• Writes and administration: DDL, DML, COPY, SET, and ATTACH are unsupported. The parser-generated temporary enum for a dynamic PIVOT is the narrowly checked exception.
• Caller-written elevated operations: dynamic SQL, metadata readers, and sequence and storage functions on the never-bind list cannot be granted through options.
• Gatekeeper's control plane: policy, enforcement, and logging controls are refused even through trusted definitions.

File readers are grantable, but are not defaults. Granting a reader permits its resource access; allowed_tables does not restrict file paths. See the function policy for the exact never-bind list and trusted-definition exceptions.

Security boundaries

Gatekeeper is a statement-level sandbox. It does not filter rows or columns, isolate the filesystem, or impose memory and time limits.

• Binding can perform I/O. Prepared statements may bind before enforcement hooks run. Unsupported deferred Quack bodies and native DuckDB 1.5 preparation of constant remote SQL can execute remotely before a later refusal; see remote scope and preparation limits.
• Parsing can have side effects. DuckDB evaluates some PRAGMA arguments before enforcement; validate complete text first if your host cannot accept that residual.
• The host controls the environment. Restrict external access and autoloading where possible, set resource limits, and lock configuration before handing out connections.
• Diagnostics can reveal names. Treat engine error messages and audit logs as host data.

Read the security model for the full boundaries and setup requirements. Report suspected bypasses through SECURITY.md.

Compatibility

• Native: release binaries target DuckDB 1.5.6, source revision 069cc9f9b5be802405797faecc284961b07c70ef. Each binary requires its matching engine. Community builds can target other engines; builds and regression tests establish compatibility.
• DuckDB 2.0: the same source builds against v2.0-cyanoptera (this descriptor's ref_next), and the community repository's 2.0 builds come from it; there are no GitHub assets for 2.0. The policy model is shared, with engine-specific capabilities and refusals; Compatibility and review lists what a host on 2.0 sees differently.
• Deferred platforms: Wasm EH and Windows MinGW await matching official 1.5.6 Wasm/CRAN R hosts. Use the v0.4.0 release assets with DuckDB 1.5.5 for those hosts. MVP and threads/COI remain excluded.
• API stability: Gatekeeper is in early development (0.x); options and result schemas may change between releases.

See build-pin maintenance and Wasm setup.

Benchmarks

What each way of running Gatekeeper adds to a statement, against the same statement on a plain connection:

Median of 1000 runs per cell after 20 warm-ups, execute().fetchall() through the Python client on one connection of an in-memory database; Apple M3 Max, DuckDB 1.5.6, Gatekeeper 0.4.1. The plain row is the client round trip plus the engine's own work; in parentheses, what each mode adds to it.

The check is a second parse and bind of the statement plus the AST walk, so its cost follows the statement's size, not the data's. gatekeeper_validate runs the same check; the rest of its row is the second client round trip. A refusal never reaches the engine. The table is regenerated by scripts/benchmark.py.

Full documentation, including a Python integration example, is in the README.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings