Extensions providing OAuth and OpenID primitives for authentication and authorization for the DuckDB quack server.
Maintainer(s):
jrosskopf
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, 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
INSTALL quack_oauth FROM community;
LOAD quack_oauth;
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| quack_oauth_acquire | scalar | One-stop client-side OAuth orchestrator (R-C-2 + R-C-4). Reads the named TYPE=quack_oauth SECRET, decides the right flow from what's available – a fresh cached access_token short-circuits; otherwise tries refresh_token grant, then client_credentials, then RFC 8628 device_code – runs it, persists rotated tokens back onto the SECRET, and returns the access_token. Designed to be threaded into ATTACH: ATTACH 'quack:host:port' AS rs (TYPE quack, token quack_oauth_acquire('cli')). Honours quack_oauth_renew_skew_s (default 60 s) – ATs within the skew window of expiry are treated as stale and re-minted. |
NULL | [SELECT quack_oauth_acquire('my_client'), ATTACH 'quack:rs.example.com:9494' AS rs (TYPE quack, token quack_oauth_acquire('cli'))] |
| quack_oauth_audit_log | table | Returns the in-memory audit ring (last N auth decisions) as a typed table. Columns: timestamp_unix_s BIGINT, event_type VARCHAR, subject VARCHAR, issuer VARCHAR, kid VARCHAR, token_hash VARCHAR, action VARCHAR, reason VARCHAR. token_hash is the 8-hex-char SHA-256 prefix of the raw token; the raw token is never exposed. For persistent audit, set audit_table on the server SECRET. |
NULL | [SELECT * FROM quack_oauth_audit_log() ORDER BY timestamp_unix_s DESC LIMIT 20] |
| quack_oauth_check_authorization | scalar | Authorize a query for a session whose Principal was previously cached by quack_oauth_check_token(). Parses the SQL with DuckDB's parser, classifies the action (Attach / Scan / Insert / Update / Delete / Ddl / Pragma / CopyTo / CopyFrom / ServeAdmin) and enumerates the referenced objects + columns, then evaluates the policy: either the SQL-native rules in the table named by policy_table on the active quack_oauth_server SECRET (rules can target subject / scope / action / object_pattern / column_pattern), or the default scope-based policy (quack:read → Attach + Scan; quack:write → also Insert/Update/Delete/CopyTo/CopyFrom; admin actions always denied). Returns false for unknown session_id, policy_table load failure, parser failure, or any policy deny. Wired into quack via SET quack_authorization_function = 'quack_oauth_check_authorization'. |
NULL | [SELECT quack_oauth_check_authorization('sess-1', 'SELECT * FROM t'), SELECT quack_oauth_check_authorization('sess-1', 'COPY t TO ''out.csv''')] |
| quack_oauth_check_token | scalar | 3-argument form matching quack's quack_check_token callback signature. Validates client token in auth_string (arg 2) after stripping optional Bearer prefix (ignoring quack's internal PSK in arg 3) AND caches the extracted Principal keyed by session_id (arg 1) so a subsequent quack_oauth_check_authorization() call can apply policies. Wired into quack via SET quack_authentication_function = 'quack_oauth_check_token'. |
NULL | [SELECT quack_oauth_check_token('sess-1', 'Bearer eyJhbGciOi…', '')] |
| quack_oauth_check_token | scalar | Validate an OAuth 2.1 / OIDC access token against the active quack_oauth_server SECRET. Returns true if the token verifies (JWKS-mode signature check, RFC 7662 introspection, or Google-style tokeninfo per global setting quack_oauth_validation_mode or provider preset). | NULL | [SELECT quack_oauth_check_token('eyJhbGciOi…')] |
| quack_oauth_current_principal | table | Returns the per-session Principal cache as a typed table (R-S-6): one row per active session_id with subject, issuer, scopes (VARCHAR[]), and exp (BIGINT unix seconds). Populated by the 3-arg form of quack_oauth_check_token. Useful for ops introspection – e.g. SELECT * FROM quack_oauth_current_principal() WHERE exp < epoch(now()) shows stale entries that should be expired. |
NULL | [SELECT * FROM quack_oauth_current_principal()] |
| quack_oauth_device_login | scalar | Run an RFC 8628 device authorization flow against the named quack_oauth SECRET. Requests a device + user code, prints the verification URL + user_code to stderr for the operator to visit on a second device, then polls the token endpoint with RFC 8628 §3.5 error handling (pending / slow_down back-off / access_denied / expired_token). On success persists access_token + refresh_token + expires_at back onto the SECRET and returns the ISO-8601 expires_at timestamp. Use for interactive auth on input-constrained devices. | NULL | [SELECT quack_oauth_device_login('my_client_secret')] |
| quack_oauth_diagnose | table | Health and configuration snapshot for the quack_oauth extension. Returns one row per component (extension, jwks_cache, decision_cache, session_principals, recent_decisions) with a status and a free-form detail string of key=value pairs. Use to verify that a freshly-loaded extension is configured and that the caches behave (R-N-13). |
NULL | [SELECT * FROM quack_oauth_diagnose()] |
| quack_oauth_inspect_sql | table | Parses a SQL string and returns what the authorization layer would gate on: action VARCHAR, objects VARCHAR[], columns VARCHAR[], unsafe BOOLEAN, error VARCHAR. Objects are schema.table for base tables and views, or fn:<name> for table functions (fn:read_csv). Catalog metadata – information_schema., pg_catalog., and the duckdb_* family – is not gated, except duckdb_secrets / duckdb_settings, which carry credentials. unsafe marks SQL that fails closed. The statement is only parsed, never executed, and no policy is consulted – use it to see why a query was denied, or which object_pattern a rule needs. |
NULL | [SELECT * FROM quack_oauth_inspect_sql('SELECT a FROM t')] |
| quack_oauth_login | scalar | Run an RFC 6749 §4.4 client_credentials flow against the token endpoint of the named quack_oauth SECRET. POSTs the SECRET's token_endpoint with grant_type=client_credentials, persists the resulting access_token, refresh_token (if any), and expires_at back onto the SECRET, and returns the ISO-8601 expires_at timestamp. Use for machine-to-machine (service account) flows. | NULL | [SELECT quack_oauth_login('my_client_secret')] |
| quack_oauth_logout | scalar | Clears access_token, refresh_token, and expires_at on the named TYPE=quack_oauth SECRET (R-C-8). Returns true. The RFC 7009 revocation-endpoint call is a SHOULD in the spec and is deferred – this version only does the local field clear. Use after a user explicitly logs out so a stolen refresh_token cannot be reused. | NULL | [SELECT quack_oauth_logout('my_client_secret')] |
| quack_oauth_refresh | scalar | Run an RFC 6749 §6 refresh_token grant against the token endpoint of the named quack_oauth SECRET. Reads token_endpoint + client_id [+ client_secret] + refresh_token from the SECRET, POSTs grant_type=refresh_token, and persists the rotated access_token + refresh_token (if returned) + expires_at back onto the SECRET, and returns the ISO-8601 expires_at timestamp. Supports both public and confidential clients. | NULL | [SELECT quack_oauth_refresh('my_client_secret')] |
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 |
|---|---|---|---|---|---|
| datazoo_banner | Show the DataZoo feedback banner when an extension is loaded in an interactive terminal (at most once a day per extension). | BOOLEAN | GLOBAL | [] | true |
| quack_oauth_clock_skew_s | Allowable clock skew (seconds) when verifying JWT exp/nbf/iat (R-S-3). | INTEGER | GLOBAL | [] | 60 |
| quack_oauth_enabled | Swap quack's auth callbacks for the OAuth implementation (R-S-1). | BOOLEAN | GLOBAL | [] | false |
| quack_oauth_introspect_cache_s | Cache lifetime (seconds) for introspect-mode decisions, capped at token exp (R-S-5). | INTEGER | GLOBAL | [] | 30 |
| quack_oauth_jwks_min_refresh_s | Minimum seconds between JWKS refreshes per kid (R-S-4). Must be between the startup floor and 3600. | INTEGER | GLOBAL | [] | 30 |
| quack_oauth_policy_default | Default decision when no policy_table rule matches: 'allow' or 'deny' (R-S-7). | VARCHAR | GLOBAL | [] | deny |
| quack_oauth_provider | First-class IdP preset: entra|google|keycloak|okta|github|generic (R-S-12). | VARCHAR | GLOBAL | [] | generic |
| quack_oauth_renew_skew_s | Client refreshes the access token this many seconds before expires_at (R-C-2). | INTEGER | GLOBAL | [] | 60 |
| quack_oauth_server_secret_name | Name of the quack_oauth_server SECRET that check_token reads. | VARCHAR | GLOBAL | [] | |
| quack_oauth_telemetry_enabled | Enable anonymous usage telemetry, see https://erpl.io/telemetry for details. | BOOLEAN | GLOBAL | [] | true |
| quack_oauth_telemetry_key | PostHog API key for telemetry, see https://erpl.io/telemetry for details. | VARCHAR | GLOBAL | [] | phc_t3wwRLtpyEmLHYaZCSszG0MqVr74J6wnCrj9D41zk2t |
| quack_oauth_trust_plaintext | Allow LOAD with enabled=true even when no TLS terminator is detected (R-N-4). | BOOLEAN | GLOBAL | [] | false |
| quack_oauth_validation_mode | Token validation strategy: 'jwks', 'introspect', 'tokeninfo', or 'github_check' (R-S-2). | VARCHAR | GLOBAL | [] | jwks |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |