---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: gatekeeper
excerpt: |
DuckDB Community Extensions
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.
extension:
name: gatekeeper
description: 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.
version: 0.4.4
language: C++
build: cmake
license: MIT
maintainers:
- derekperkins
requires_toolchains: python3
excluded_platforms: 'wasm_mvp;wasm_threads;wasm_eh'
opt_in_platforms: 'osx_amd64;linux_amd64_musl;linux_arm64_musl;windows_amd64_mingw;windows_arm64'
repo:
github: nozzle/duckdb-gatekeeper
ref: fabb0894997a4a15b180e7f50aff2bb89b5400f7
variegata: c4b9b3cf2c71ac527fb3f8ae7310066384e397f1
docs:
hello_world: |
-- 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
extended_description: |
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
```sql
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](#security-boundaries).
### Functions and options
```text
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.
| Option | Type | Default | Notes |
|--------|------|---------|-------|
| `allowed_tables` | STRUCT[] | unrestricted (non-internal) | `{catalog?, schema_path: VARCHAR[], table}`. Nonempty path, outermost first; `'*'` matches one component at exactly that depth, never recursively. Omitted or NULL catalog matches any. `[]` denies all tables and views. Until this is set, every non-internal table and view is readable. |
| `blocked_tables` | STRUCT[] | `[]` | Same identity rules, including exact depth: `['*']` does not block nested schemas on 2.0. A match always denies what the caller names; does not reach inside trusted views, macros, or attached tables. |
| `use_default_functions` | BOOLEAN | `true` | `true`: 919 reviewed qualified defaults (913 distinct names) **plus** `allowed_functions`. `false`: only `allowed_functions`. |
| `allowed_functions` | STRUCT[] | `[]` | Resolved `{catalog?, schema_path, name, type?}` grants. Required exact leaf; `'*'` means multiplication, not all functions. Defaults cover reviewed system.main identities. |
| `blocked_functions` | STRUCT[] | `[]` | Same qualified identity rules as grants, including optional kind and exact schema depth. A matching block always wins for caller-attributable functions. Does not reach inside trusted views, macros, or attached tables. |
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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/policy-migration.md)
for legacy function strings, old `schema` fields, typed empty lists, and canonical settings.
### Result columns
| Column | Type | Meaning |
|--------|------|---------|
| `allowed` | BOOLEAN | True exactly when `code = 'ok'`. |
| `code` | VARCHAR | `ok`, `forbidden`, `unsupported`, `parser`, `binding`, `invalid_input`. |
| `violations` | STRUCT[] | `rule`, `message`, `catalog`, `schema_path VARCHAR[]`, `table`, `function_name`, `position BIGINT`, `function_type`, `object_type` (other fields VARCHAR, in this order). Nonempty only for `forbidden` and `unsupported`. |
| `error_type` | VARCHAR | DuckDB exception category (`parser`, `Catalog`, `Binder`, ...) for engine errors. |
| `error_message` | VARCHAR | The engine's message; empty for policy denials. |
| `position` | BIGINT | Zero-based parser byte offset, or NULL. |
| `objects` | STRUCT[] | Resolved `catalog`, `schema_path VARCHAR[]`, `table`, `type` (`table`, `view`, `replacement`), including trusted dependencies. See `caller_objects` for the caller-attributable catalog subset. Empty unless `ok`. |
| `functions` | STRUCT[] | Resolved `catalog`, `schema_path VARCHAR[]`, `name`, `type` the query bound to. Empty unless `ok`. |
| `caller_objects` | STRUCT[] | Caller-attributable catalog tables/views, with the same fields as `objects`. Sorted, deduplicated subset of `objects`; empty unless `ok`. |
| `caller_functions` | STRUCT[] | Identities in `functions` checked by caller-scoped policy at any authorization point. Same fields; sorted, deduplicated, empty unless `ok`. |
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.
| Operation | SQL |
|-----------|-----|
| Inspect | `SELECT current_setting('gatekeeper_policy')` |
| Reset to built-ins | `RESET gatekeeper_policy` or `CALL gatekeeper_configure()` |
| Freeze | `SET lock_configuration = true` after trusted setup |
| Allow later changes while locked | `SET allowed_configs = ['gatekeeper_policy']` before locking |
### 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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/quack.md).
- 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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/security.md#function-enforcement-and-trusted-expansion)
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:
```sql
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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/security.md#enforced-connections)
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:
```sql
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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/security.md#audit-log)
and [log-only guide](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/security.md#log-only-mode).
### What is never allowed
- **Writes and administration:** DDL, DML, `COPY`, `SET`, and `ATTACH` are unsupported.
Parser-shaped temporary PIVOT enums are the narrowly checked exception on both engines.
Dynamic PIVOT checks each enum query and the final SELECT before binding and execution.
- **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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/security.md#never-bind-functions)
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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/quack.md).
- **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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/security.md)
for the full boundaries and setup requirements. Report suspected bypasses through
[SECURITY.md](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/SECURITY.md).
### Compatibility
- **Community binaries:** the community repository builds this release for DuckDB 2.0. It no
longer builds DuckDB 1.5; its 1.5.6 binaries remain Gatekeeper v0.4.1. The policy model is
shared, with engine-specific capabilities and refusals;
[Compatibility and review](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/docs/security.md#compatibility-and-review)
lists what a host on 2.0 sees differently.
- **Primary build / GitHub release v0.4.4:** target DuckDB 2.0.0, source revision
`d54cbbe3df17e8e5ce68312f483b4155836112aa`. Published v0.4.3 assets target 1.5.6.
Source compatibility with 1.5.6 is maintained until March 1, 2027. Each binary requires its
matching engine. Builds and regression tests, not an engine allowlist, establish compatibility.
- **Platforms:** the 2.0 distribution restores Windows MinGW with CRAN R. Wasm EH source
builds are browser-tested with a matching development-labeled 2.0 runtime; release Wasm
assets await a release-labeled host. 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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/inventories/README.md#repinning-the-engine)
and [Wasm setup](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/test/wasm/README.md).
### Benchmarks
What each way of running Gatekeeper adds to a statement, against the same statement on a
plain connection:
| | point lookup (1 K rows) | aggregate (10 M rows) | large statement (11 KB) |
| --- | ---: | ---: | ---: |
| plain connection | 103 µs | 4.0 ms | 9.4 ms |
| enforced connection | 164 µs (+61 µs) | 4.2 ms (+221 µs) | 20.7 ms (+11.3 ms) |
| enforced, audit log at debug | 227 µs (+124 µs) | 4.4 ms (+370 µs) | 20.8 ms (+11.4 ms) |
| validate, then execute | 278 µs (+175 µs) | 4.4 ms (+405 µs) | 19.2 ms (+9.8 ms) |
| denied on an enforced connection | 334 µs | 529 µs | 11.1 ms |
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 2.0.0, Gatekeeper
0.4.4. 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](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/scripts/benchmark.py).
Full documentation, including a Python integration example, is in the
[README](https://github.com/nozzle/duckdb-gatekeeper/blob/v0.4.4/README.md).
extension_star_count: 5
extension_star_count_pretty: 5
extension_download_count: 1076
extension_download_count_pretty: 1.1k
image: '/images/community_extensions/social_preview/preview_community_extension_gatekeeper.png'
layout: community_extension_doc
---
### Compatibility Matrix
This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Linux ARM64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| macOS AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| macOS ARM64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Windows AMD64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| Windows ARM64 | ❌ | ❌ | ✅ | ✅ | ✅ |
| WebAssembly | ❌ | ❌ | ✅ | ✅ | ❌ |
### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```
{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}
{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions
| function_name | function_type | description | comment | examples |
|----------------------|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------|---------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| gatekeeper_configure | table | Replaces the global Gatekeeper policy atomically; omitted options revert to the built-in defaults. | NULL | [CALL gatekeeper_configure(allowed_tables := [{schema_path: ['reporting'], 'table': '*'}], blocked_functions := [{catalog:'system', schema_path:['main'], name:'md5'}])] |
| gatekeeper_enforce | table | Irreversibly makes this connection execute only statements the global Gatekeeper policy allows and reports host settings that weaken the sandbox. | NULL | [CALL gatekeeper_enforce()] |
| gatekeeper_validate | table | Validates one untrusted read-only SQL statement against the global policy and the request options without executing it. | NULL | [SELECT allowed, code FROM gatekeeper_validate('SELECT sum(amount) FROM reporting.orders', allowed_tables := [{schema_path: ['reporting'], 'table': 'orders'}])] |
### Overloaded Functions
This extension does not add any function overloads.
### Added Types
This extension does not add any types.
### Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---------------------|--------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------|---------|---------------------------------------------------------------------------------------------------------------------------------------------------------|
| gatekeeper_log_only | Whether enforced connections record policy decisions without refusing them; CONNECT/DISCONNECT routing controls remain refused | BOOLEAN | GLOBAL | [] | false |
| gatekeeper_policy | Global Gatekeeper authorization ceiling | STRUCT(use_default_functions BOOLEAN, allowed_functions STRUCT("catalog" VARCHAR, schema_path VARCHAR[], "name" VARCHAR, "type" VARCHAR)[], blocked_functions STRUCT("catalog" VARCHAR, schema_path VARCHAR[], "name" VARCHAR, "type" VARCHAR)[], allowed_tables STRUCT("catalog" VARCHAR, schema_path VARCHAR[], "table" VARCHAR)[], blocked_tables STRUCT("catalog" VARCHAR, schema_path VARCHAR[], "table" VARCHAR)[], restrict_tables BOOLEAN) | GLOBAL | [] | {'allowed_functions': [], 'allowed_tables': [], 'blocked_functions': [], 'blocked_tables': [], 'restrict_tables': false, 'use_default_functions': true} |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |