Downloads n/athis week

GitHub stars 0

Extension descriptor (YAML)

Pre-execution authorization for untrusted read-only SQL from tenants, LLM agents, and dashboard builders. Checks every resolved table, view, and function against a lockable policy without executing the query.

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: 'reporting', 'table': '*'}],
    blocked_functions := ['md5']
);
-- 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 AS schema, violations[1]."table" AS "table"
FROM gatekeeper_validate('SELECT * FROM secrets');
-- allowed | code      | rule  | schema | 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: '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 before you run SQL you did not write: does this statement stay inside the lines you drew? It is built for multi-tenant analytics, LLM-generated queries, and embedded dashboard builders where the SQL text is untrusted but the database is yours. It parses the statement, binds it on your connection, and authorizes every resolved table, view, and caller-written function against a two-layer policy: a lockable global ceiling and per-request options that may narrow it but never widen it. The submitted SQL is not executed.

Gatekeeper is a pre-execution validator, not a sandbox. It does not filter rows or columns, cap memory or time, or isolate the filesystem. See Security boundaries before integrating. It is in early development (0.x): the option and result schema may still change between releases.

How a decision is made

1. Parse the SQL and require exactly one statement.
2. Inspect the AST for statement type, dynamic SQL, never-bind functions, and bind-time expressions the caller wrote.
3. Bind on your connection, using the caller's search path and transaction.
4. Authorize every resolved table and view, plus each caller-requested function, against both the global policy and the request options.
5. Return one row with named columns. Nothing is executed.

The integration contract: require both allowed = true and code = 'ok', treat exceptions and missing rows as denials, then execute the same SQL text on the same connection.

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

Functions and options

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

Both 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: sales_*, ?, and % are literal names. Wildcards also match objects created or attached later, so prefer explicit catalog names when that scope is not intended.

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.

Global policy

Tables and views are unrestricted by default, except internal objects. Configure allowed_tables during trusted setup to restrict access; [] denies all tables and views. Function policy is always on: only the reviewed defaults and the names you allow bind.

CALL gatekeeper_configure(...) replaces the global policy atomically, filling omitted options from the built-in defaults. The policy is shared by every connection of the database instance, is not persisted, and is not undone by rollback. Request options are intersected with it: either layer's block wins, and each layer must allow every resolved table or view and every caller-written function. (Functions that trusted views and macros introduce internally are normally exempt from the allowlists but always honor blocks and the never-bind list; ambiguous caller syntax such as t.x or list[i] triggers a query-wide implementation check that can reach those expansions too.) Blocks also reach bound implementations such as sum behind list_sum, and when the caller writes a name-selected dispatcher (list_aggregate, aggregate, and aliases) the aggregate it names must itself be allowed in both layers. A request that tries to widen access does not error; it simply cannot authorize anything the global policy denies.

Grant capabilities (such as file readers) in the global policy during trusted setup, then use request options to narrow per tenant.

What is never allowed

• INSERT, UPDATE, DELETE, CREATE, DROP, COPY, SET, ATTACH, and every other non-read statement return unsupported.
• Dynamic SQL (query, query_table, json_execute_serialized_sql, json_serialize_plan), metadata readers (duckdb_tables, information_schema.*, SHOW TABLES), sequence and storage functions, and gatekeeper_configure itself are on a never-bind list that no option can override, including through views or macros.
• File readers (read_parquet, read_csv_auto, read_json_auto) and catalog, session, or configuration inspection (current_schema, current_setting, getvariable) are not defaults. Grant them by name in the global policy. Admitting a reader permits its resource access; allowed_tables does not restrict file paths. The clock (current_date, now()) and the connection-local RNG (random(), uuid()) are defaults.
• Expressions in bind-time positions (LIMIT, reader arguments, type parameters) must be literals or parameters, except correlated unnest, range, and generate_series arguments, which DuckDB evaluates per row.

Security boundaries

Binding can perform I/O through trusted catalogs and explicitly admitted readers, and type resolution can autoload extensions when those settings are on. Applications must control trusted definitions, host capabilities, credentials, resource limits, and the gap between validation and execution. Run validation on a dedicated connection with autoload_known_extensions = false, autoinstall_known_extensions = false, memory and thread limits, and lock_configuration = true. Default functions are a reviewed name inventory, not a proof that every overload is harmless. Engine error messages (error_message) can name catalog objects the policy denies, so return them to trusted operators only. Read the security model before integrating, and report suspected bypasses through SECURITY.md.

Compatibility

Release binaries are built for DuckDB 1.5.5, source revision d8cdaa33fda8df955cc76ef58a280f68f4cd43fa. Each binary must match its DuckDB engine; the community repository can rebuild this source for other engine versions. Gatekeeper generates its AST grammar from the engine being compiled and records that engine's identity: each artifact refuses any other release version or dev source id even if DuckDB's footer check is disabled. There is no fixed release allowlist in the source; builds and regression tests establish source compatibility.

Default functions are an explicit list of names. New names remain excluded until classified or explicitly allowed; inventory updates improve coverage rather than gate engine upgrades. Existing DuckDB function implementations are trusted across upgrades. See the build-pin maintenance guide.

Wasm support is limited to the EH bundle, browser-tested with @duckdb/[email protected] embedding DuckDB 1.5.5 (the npm package version is independent of its embedded engine version). MVP and threads/COI remain excluded; see Wasm compatibility.

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