Contents

pg_durable Security Model Specification

Status: Implementation in progress
Authors: pg_durable Team
Created: 2025-12-25
Last Updated: 2026-03-11

Table of Contents

  1. Executive Summary
  2. Goals and Non-Goals
  3. Threat Model
  4. Functional Requirements
  5. Securing df.sql()
  6. Securing df.http() 6.7 Duroxide Background Worker Authentication/Authorization
  7. Data Isolation (RLS)
  8. Implementation Specification
  9. User Experience
  10. Test Specification
  11. Open Questions

1. Executive Summary

pg_durable executes user-submitted SQL durably via a background worker. This creates a security challenge: SQL submitted by User A should execute with User A’s privileges, not with elevated background worker privileges.

For a detailed architectural overview of how function graphs are built and executed, please refer to the Architecture Guide.

Implemented approach: The background worker opens a dedicated sqlx connection authenticated directly as submitted_by (current_user at df.start() time). User SQL executes on this per-user connection, inheriting standard PostgreSQL RBAC. Identity is captured at df.start() time via GetUserId() and stored in df.instances and df.nodes.

See User Isolation: Design & Implementation Guide for the full design rationale.

Alternative considered (SPI + SetUserIdAndSecContext): An earlier draft of this spec proposed executing user SQL in-process via SPI with C-level security context switching. This would provide slightly stronger escape prevention (immune to RESET ROLE at the SQL level) and avoid the pg_hba.conf trust-auth requirement. However, the sqlx connection approach was chosen for simplicity, compatibility with the async duroxide runtime, and because it provides equivalent privilege isolation in practice. SPI remains a potential future optimization — see Section 8.8 for analysis.

Key Security Properties

Property Guarantee
Privilege Isolation User SQL executes on a connection authenticated as the submitting user
Escape Prevention RESET ROLE reverts to submitted_by (the authenticated connection identity) — no escalation possible
Audit Trail All executions logged with original user identity (submitted_by)
Trusted Extension Model Extension code is trusted; installed by superuser only

Security Boundary

Important: The pg_durable background worker connects to PostgreSQL as arbitrary users via sqlx, relying on pg_hba.conf trust (or peer) authentication for local connections. This is a trusted code + trusted network model:

  • The extension can connect as ANY local PostgreSQL role (given permissive pg_hba.conf)
  • Security relies on the extension code being correct and the pg_hba.conf configuration being appropriate
  • The worker’s ambient role (e.g., duroxide_worker) is used only for control-plane operations (loading graphs, updating status); user SQL runs on separate per-user connections
  • This is similar to how pg_cron operates — trusted extension code with elevated capabilities

The security guarantee is: only superusers can install the extension, therefore the extension code is trusted.

2. Goals and Non-Goals

Goals

  1. G1: User SQL executes with the privileges of the user who called df.start()
  2. G2: Users cannot escalate privileges through durable function execution
  3. G3: SQL-level grants to the background worker role follow least privilege (while acknowledging the overall model is a trusted, SUPERUSER-installed extension)
  4. G4: Clear audit trail of who submitted what
  5. G5: Simple user experience - no complex grant management for basic use cases
  6. G6: Compatible with Azure Flexible Server managed environment

Non-Goals

  • NG1: Supporting different users for different nodes within a single function graph
  • NG2: Cross-database durable function execution
  • NG3: Supporting untrusted extension installation (pg_durable remains SUPERUSER-install)
  • NG4: Real-time privilege revocation (in-flight executions complete with original privileges)

3. Threat Model

3.1 Actors

Actor Trust Level Capabilities
DBA/Admin Trusted Installs extension, manages roles, full database access
Application User Semi-trusted Can call df.* functions, owns application tables
Attacker Untrusted Compromised application user, attempts privilege escalation

3.2 Threats and Mitigations

Implementation Priority Summary

Threat Severity Status Notes
T8: SSRF via HTTP Activity CRITICAL Implemented Dataplane protection — see http-security.md
T4: Information Disclosure via df.* Tables HIGH Implemented RLS on df.instances and df.nodes — see rls.md
T9: Unauthorized HTTP Access HIGH Not implemented REVOKE EXECUTE + admin allowlist (future spec)
T11: Secret Exfiltration HIGH Not implemented Additive feature; no table/API exists yet
T10: Cross-User Variable Injection MEDIUM-HIGH Implemented Per-user df.vars scoping via owner column + RLS — see rls.md
T5: Denial of Service MEDIUM Not implemented Rate limiting; deferred
T6: Worker Code Vulnerability MEDIUM Mitigated by design Relies on code review
T0: SECURITY DEFINER Misuse MEDIUM Documentation-only Expected PG behavior
T12: SQL Injection in Internal SPI Queries CRITICAL Implemented df.status() and df.result() now use parameterized SPI queries (Spi::get_one_with_args)
T7: Extension Trustworthiness LOW Accepted Standard PG trust model
T13: search_path Manipulation in PL/pgSQL Helpers LOW Implemented Helper function definitions set search_path = pg_catalog, df, pg_temp
T14: Extension Object Pre-creation LOW Accepted Requires operator error; superuser-only install
T1–T3: Privilege Escalation CRITICAL Implemented Per-user sqlx connections

T0: SECURITY DEFINER Invocation Captures Definer Privileges

Severity: MEDIUM | Status: Documentation-only

Threat: Calling df.start() inside a SECURITY DEFINER function captures the definer’s identity (because GetUserId()/current_user reflect the definer inside the function). Unprivileged callers could cause durable work to run with the definer’s privileges.

Mitigation (documentation-only): This is expected PostgreSQL behavior. The extension does not block this pattern. Operators must avoid invoking df.start() from SECURITY DEFINER unless they explicitly want definer-level execution. Document clearly and, if possible, emit audit logs when df is invoked from SECURITY DEFINER.

Residual Risk: High if misused. Safe if used intentionally and documented.

T1: Privilege Escalation via RESET ROLE

Severity: CRITICAL | Status: Implemented

Threat: User submits SQL containing RESET ROLE to escape back to worker’s identity.

-- Malicious durable function
SELECT df.start(
    df.sql('RESET ROLE; DROP TABLE other_users_data;'),
    'attack'
);

Mitigation (implemented): User SQL runs on a dedicated sqlx connection authenticated as submitted_by (the user’s identity at df.start() time). RESET ROLE on this connection resets the effective role back to submitted_by — which is still the user’s own authenticated identity. The connection never has the background worker’s elevated privileges, so there is nothing to escape to.

Residual Risk: None — the connection is authenticated as the user, not the worker.

T2: Privilege Escalation via SET ROLE

Severity: CRITICAL | Status: Implemented

Threat: User attempts to assume a more privileged role.

SELECT df.start(
    df.sql('SET ROLE postgres; SELECT * FROM pg_shadow;'),
    'attack'
);

Mitigation (implemented): The sqlx connection is authenticated as submitted_by. Any SET ROLE by user SQL requires role membership, checked against submitted_by. SET ROLE postgres fails unless submitted_by is a member of postgres. This is standard PostgreSQL RBAC enforced at the connection level.

Residual Risk: None — standard PostgreSQL RBAC applies.

T3: Privilege Escalation via Dynamic SQL

Severity: CRITICAL | Status: Implemented

Threat: User obfuscates malicious commands.

SELECT df.start(
    df.sql($$ DO $x$ BEGIN EXECUTE 'RES' || 'ET ROLE'; END $x$ $$),
    'attack'
);

Mitigation (implemented): Dynamic SQL runs on the same sqlx connection, which is authenticated as the user’s submitted_by role. The connection’s authenticated identity cannot be changed by any SQL command — RESET ROLE only reverts to submitted_by (the user’s own identity), and SET ROLE requires membership.

Residual Risk: None.

T4: Information Disclosure via df.* Tables

Severity: HIGH | Status: Implemented

Threat: User queries df.instances or df.nodes to see other users' durable functions.

Mitigation (implemented): RLS policies on df.instances and df.nodes enforce per-user visibility using submitted_by = current_user::regrole. Auto-grants provide SELECT+INSERT on both tables and column-level UPDATE (status, updated_at) on instances (no DELETE). Ownership checks in df.cancel() and df.signal() prevent cross-user operations via the duroxide client. Monitoring functions (df.list_instances(), df.instance_info(), etc.) also enforce ownership.

See rls.md for the full design, policy definitions, grant strategy, and decisions.

Residual Risk: Low — RLS is a well-tested PostgreSQL feature.

T5: Denial of Service via Resource Exhaustion

Severity: MEDIUM | Status: Not implemented (deferred)

Threat: User creates many long-running durable functions to exhaust worker capacity.

Mitigation: - Rate limiting via df.max_concurrent_per_user GUC - Timeout enforcement via df.execution_timeout - Queue depth limits

Residual Risk: Medium - requires monitoring; out of scope for initial implementation.

T6: Background Worker Code Vulnerability

Severity: MEDIUM | Status: Mitigated by design

Threat: Bug in extension code allows attacker to control which user the worker connects as.

Attack Vector: If submitted_by values used by connect_as_user() are derived from user-controlled data, an attacker could forge them to connect as a different user.

Mitigation (implemented): - submitted_by is captured via GetUserId() at df.start() time in the user’s backend process - It is stored as REGROLE in df.instances and propagated to df.nodes — users cannot write to these columns directly (they are set by the extension’s Rust code via SPI during df.start()) - Worker reads role names from df.nodes when loading the function graph, and passes them to the execute_sql activity - Code review checklist: verify role name provenance in all paths

Residual Risk: Medium — relies on correct implementation. Code review critical.

T7: Extension Code Trustworthiness

Severity: LOW (accepted) | Status: N/A — inherent to PG extension model

Threat: Malicious or buggy extension code abuses its ability to connect as any user.

Context: PG’s extension architecture has a full trust model - extension code must be safe and correct. Any extension can call C functions, which is stronger than “connect as any user”.

Mitigation: - Extension requires superuser to install (CREATE EXTENSION pg_durable) - pg_hba.conf must be configured to allow the worker’s local connections (trust or peer auth) - Code is open source and auditable - Standard trusted extension model (same as pg_cron, postgis, etc.)

Residual Risk: Accepted — this is the PostgreSQL trusted extension model. If you install the extension, you trust the code.

T8: Server-Side Request Forgery (SSRF) via HTTP Activity

Severity: CRITICAL | Status: Implemented

Threat: Attacker uses df.http() to access internal network services, cloud metadata endpoints, or localhost services from within the PostgreSQL VM. In a PG-as-a-service deployment, this is a dataplane escape.

Mitigation (implemented): Compile-time IP blocklist that blocks all private/reserved IP ranges, with DNS rebinding protection and IPv4-mapped IPv6 handling. The blocklist is hardcoded and cannot be bypassed by any database user, including superusers, for pg_durable’s built-in df.http() activity path. It does not restrict arbitrary SQL functions, user-defined functions, or third-party Postgres extensions that SQL nodes are permitted to execute.

See http-security.md for the full specification, blocked IP ranges, and implementation details.

Residual Risk: Low for df.http() — hardcoded blocklist cannot be bypassed in that path.

T9: Unauthorized HTTP Access

Severity: HIGH | Status: Partially implemented

Threat: User abuses df.http() to access external resources they shouldn’t (exfiltrate data, attack external services).

Mitigation: - df.http() has EXECUTE revoked from PUBLIC on fresh installs - DBA grants HTTP access explicitly, either with df.grant_usage(role, include_http => true) or a direct GRANT EXECUTE ON FUNCTION df.http(text, text, text, jsonb, integer) - The worker re-checks EXECUTE at execution time to block raw df.start() JSON injection - Audit logging records HTTP attempts

Future enhancements: - Per-customer or per-role destination controls beyond the current feature-gated allowlist - Rate limiting

Residual Risk: Medium until customer-level controls are implemented. T8 (SSRF/dataplane) protection is independent and addressed first.

T10: Cross-User Variable Injection via df.vars

Severity: MEDIUM-HIGH | Status: Implemented

Threat: df.vars is a key-value table. Without per-user scoping, users can override or read each other’s variables, potentially redirecting workflows.

Mitigation (implemented): df.vars has an owner REGROLE column with DEFAULT current_user::regrole and a composite primary key (owner, name). RLS policy vars_user_isolation restricts each user to their own variables. df.setvar() scopes ownership via the column DEFAULT and ON CONFLICT (owner, name); read/delete functions (df.getvar(), df.unsetvar(), df.clearvars()) and df.start() vars capture use explicit WHERE owner = current_user::regrole filters. This ensures correct scoping even for superusers who bypass RLS. See rls.md, Decision 5.

Residual Risk: Low — per-user scoping is enforced at both the RLS and application layers.

T11: Secret Exfiltration via df.secrets

Severity: HIGH | Status: Not implemented (additive feature)

Threat: df.secrets are intended to be admin-managed values (API keys, shared tokens) that workflows can use without hard-coding secrets into graphs. If secrets are directly readable by all users, they are not secrets. Without this feature, users must embed credentials directly in function graphs, where they are stored in df.nodes and potentially visible in logs.

Mitigation: - Secrets MUST NOT be directly selectable by non-admin users - Secrets MUST NOT be returned in results or error strings - Secrets should be resolved only inside the worker execution path and substituted into SQL/HTTP requests at execution time

Residual Risk: Medium (by design secrets are high-impact); mitigated by least-privilege, auditing, and never exposing plaintext to users.

T12: SQL Injection in Internal SPI Queries

Severity: CRITICAL | Status: Implemented

Threat: Two extension functions (df.status() and df.result()) previously interpolated user-supplied instance_id directly into SQL without escaping single quotes. That allowed SQL injection to bypass RLS and read other users' instance data.

-- Attack: bypass RLS to read any user's instance status
SELECT df.status('x'' OR 1=1--');

-- Attack: read results from other users' workflows
SELECT df.result('x'' UNION SELECT secret FROM admin_table--');

Current mitigation (src/dsl.rs): ```rust // df.status() — parameterized SPI query let status: Option

// df.result() — parameterized SPI query let result: Option

Fix implemented: Replaced string interpolation with parameterized SPI in df.status() and df.result() using Spi::get_one_with_args(). This removes quote-escaping as a correctness requirement for these call sites. Other internal SPI queries should also prefer parameterization wherever supported; manual escaping is fallback-only for cases where parameters are not available.

Note on variable substitution: The substitute_all_with_options() function in src/types.rs inserts user variables ({name}) as-is into SQL without quoting. This is by design — variables are intended to be SQL fragments (e.g., table names, expressions). Users choose what to put in their own variables, and the SQL executes with their own privileges on a per-user connection. This is analogous to psql variable substitution (:name). Result substitution ($name) does properly quote string values.

Residual Risk: Low. Remaining hardening is to continue migrating any remaining string-formatted SPI lookups to parameterized calls where possible.

See this section and Appendix A checklist for ongoing SPI query hardening work.

T13: search_path Manipulation in PL/pgSQL Helper Functions

Severity: LOW | Status: Implemented

Threat: The extension’s PL/pgSQL helper functions (df.if_then_op(), df.if_else_op(), df.ensure_durofut()) and SQL wrapper functions (df.as_op(), df.loop_prefix_op()) do not set a fixed search_path. If an attacker can place a malicious function in a schema that appears earlier in search_path, they could shadow a built-in or extension function.

Mitigations (implemented): - All function calls within the helpers are already schema-qualified: df.ensure_durofut(), df.sql(), df.if(), df.loop(), df.as() - Built-in functions used (jsonb_build_object) are in pg_catalog, which is always implicitly first in search_path - The functions are created in the df schema (owned by superuser) - The extension requires superuser to install - Helper definitions in both fresh install SQL and upgrade SQL include SET search_path = pg_catalog, df, pg_temp

Fix implemented: Added SET search_path = pg_catalog, df, pg_temp to the helper function definitions (df.if_then_op(), df.if_else_op(), df.ensure_durofut(), df.as_op(), df.loop_prefix_op()) in both install and upgrade paths.

Residual Risk: Low — current references are schema-qualified and helper search path is pinned as defense-in-depth.

T14: Extension Object Pre-creation Attack

Severity: LOW | Status: Accepted risk

Threat: The extension uses CREATE TABLE IF NOT EXISTS and CREATE OR REPLACE FUNCTION patterns. An attacker could pre-create objects with the same names to retain ownership or inject malicious implementations.

Why this is low risk: - All DDL runs inside extension_sql!() blocks during CREATE EXTENSION, which requires superuser - The df schema is created by pgrx during extension installation — it doesn’t exist beforehand - The duroxide schema is created by sql/duroxide_install.sql with SET LOCAL search_path TO duroxide - An attacker would need CREATE TABLE privilege in a schema controlled by the extension before the extension is installed — this requires the superuser to have manually created the schema and granted access (operator error) - The CREATE OR REPLACE FUNCTION for PL/pgSQL helpers is a standard pgrx convention; the #[pg_extern] macro also generates CREATE OR REPLACE

Residual Risk: Low — requires operator error (manually creating df schema and granting usage before installing the extension).

4. Functional Requirements

4.1 Overall Security Requirements

pg_durable follows PostgreSQL’s native security patterns wherever possible:

Mechanism PostgreSQL Native pg_durable Usage
Function access GRANT/REVOKE EXECUTE Control who can use df.sql(), df.http(), etc.
Data isolation Row-Level Security (RLS) Users see only their own instances
Configuration GUCs (ALTER SYSTEM SET) HTTP allowlists, rate limits, timeouts
Role membership GRANT role TO user No custom roles; use standard PostgreSQL
Audit PostgreSQL logging Log with effective user identity

Design Principle: Follow the pg_cron model — use RLS for data isolation, standard function permissions for access control, and GUCs for system-wide settings. No custom permission tables or roles.

4.2 Security by Activity Type

Activity Permission Model Additional Controls
df.sql() PostgreSQL RBAC via per-user sqlx connection None needed — native
df.http() GRANT EXECUTE on function SSRF blocking, URL allowlist GUC
df.start() GRANT EXECUTE on function RLS on df.instances
Future activities GRANT EXECUTE on function Activity-specific GUCs

4.3 Workflow Variables (df.vars)

pg_durable supports workflow variables via df.setvar()/df.getvar()/df.unsetvar()/df.clearvars(). Variables are captured at df.start() time and passed into the orchestration as an immutable vars map.

Security requirement: Variables MUST NOT be global, cross-user state.

Current status: Implemented. df.vars has per-user scoping via an owner REGROLE column with RLS. Each user has their own variable namespace. Variables are captured at df.start() time from the calling user’s namespace only. See rls.md, Decision 5.

4.4 Shared Secrets (df.secrets)

pg_durable supports shared secrets for workflows.

Intent: Provide admin-managed secrets (API keys, bearer tokens, shared credentials) that workflows can reference without embedding secrets in the function graph.

Key security property: Secrets are usable by workflows but are not directly readable by non-admin users.

API surface (proposed): - df.setsecret(name text, value text) (admin-only) - df.unsetsecret(name text) (admin-only) - df.clearsecrets() (admin-only) - No general-purpose df.getsecret() for non-admins

How users consume secrets: - Secrets are referenced by name inside node queries/config and resolved by the worker at execution time. - Example placeholder (conceptual): ${secret:stripe_api_key}.

Permissions: - Only admins are granted EXECUTE on secret mutators (df.setsecret, df.unsetsecret, df.clearsecrets). - Non-admins should not have SELECT on df.secrets. - The worker (trusted code) may read df.secrets to perform substitution.

Audit: - Log secret name usage for traceability (never log values).

Scenarios that this enables: - Any user granted EXECUTE on df.http(text, text, text, jsonb, integer) can run a workflow that calls df.http() to an allowed host and uses an Authorization header populated from df.secrets. - Any user can run a workflow that queries an external FDW/API gateway where the credential is provided by the worker.

5. Securing df.sql()

5.1 Overview

SQL execution is the core activity. It uses PostgreSQL’s native permission system via dedicated per-user sqlx connections.

┌─────────────────────────────────────────────────────────────────┐
│  df.sql() Security Model (Implemented)                          │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  User calls: df.start(df.sql('SELECT * FROM my_table'), ...)   │
│                                                                 │
│  1. df.start() captures:                                       │
│     • GetUserId() → submitted_by (current_user)                │
│     • Stored as REGROLE in df.instances and df.nodes            │
│                                                                 │
│  2. Background worker executes:                                │
│     • connect_as_user(submitted_by) via sqlx                   │
│       → Authenticates TCP connection as submitted_by           │
│       → SET df.in_workflow = 'true' (guard variable mutations) │
│     • sqlx::query(user_sql) ← runs with user's privileges     │
│     • Connection dropped after execution                       │
│                                                                 │
│  3. PostgreSQL permission checks:                              │
│     • All ACL checks use the connection's effective role        │
│     • RLS policies evaluate against the connected user         │
│     • pg_stat_activity shows the user's identity               │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

5.2 Permission Model

No additional permissions needed — PostgreSQL’s native RBAC applies:

-- User A owns their table
CREATE TABLE user_a.my_data (id int, secret text);

-- User A can query it in a durable function
SELECT df.start(df.sql('SELECT * FROM user_a.my_data'), 'my-job');
-- ✓ Succeeds: runs as user_a, who owns the table

-- User B tries to access user_a's table
SET ROLE user_b;
SELECT df.start(df.sql('SELECT * FROM user_a.my_data'), 'steal-data');
-- ✗ Fails: runs as user_b, who has no access

5.3 Escape Prevention

Attack Why It Fails
RESET ROLE Reverts to submitted_by — still the user’s own identity, not the worker’s
SET ROLE postgres Requires membership in postgres; checked against submitted_by
EXECUTE 'RESET ROLE' Dynamic SQL runs on the same connection — same identity constraints
SELECT my_escape_func() Function runs on the same connection — same identity constraints

5.4 Implementation

See Section 8: Implementation Specification for full code.

6. Securing df.http()

6.1 Overview

HTTP requests are guarded by PostgreSQL function privileges plus runtime SSRF defenses. In the current implementation, security is enforced via:

  1. Function-level permission: GRANT/REVOKE EXECUTE ON FUNCTION df.http(text, text, text, jsonb, integer)
  2. Execution-time privilege re-check: the worker validates that submitted_by still has EXECUTE before any network activity
  3. SSRF protection: Block internal IPs at the code level
  4. Compile-time endpoint allowlist: allowed destinations depend on the HTTP Cargo feature
  5. Redirect handling: redirects are disabled
┌─────────────────────────────────────────────────────────────────┐
│  df.http() Security Model                                       │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  Layer 1: Function Permission (PostgreSQL native)              │
│  ┌───────────────────────────────────────────────────────────┐ │
│  │ REVOKE EXECUTE ON FUNCTION df.http(text, text, text,      │ │
│  │     jsonb, integer) FROM PUBLIC;                          │ │
│  │ SELECT df.grant_usage('api_users', include_http => true); │ │
│  │ -- or GRANT EXECUTE ON FUNCTION df.http(text, text, text, │ │
│  │ --     jsonb, integer) TO api_users;                      │ │
│  └───────────────────────────────────────────────────────────┘ │
│                                                                 │
│  Layer 2: Execution-Time Privilege Check                       │
│  ┌───────────────────────────────────────────────────────────┐ │
│  │ submitted_by must still have EXECUTE on df.http(...)      │ │
│  │ Blocks raw df.start() JSON injection bypasses             │ │
│  └───────────────────────────────────────────────────────────┘ │
│                                                                 │
│  Layer 3: SSRF Protection + Feature Allowlist                  │
│  ┌───────────────────────────────────────────────────────────┐ │
│  │ Block: private/link-local/loopback ranges                │ │
│  │ Allow only feature-approved hostnames                    │ │
│  │ No redirects                                              │ │
│  └───────────────────────────────────────────────────────────┘ │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

6.2 Permission Model

Function-level access control (PostgreSQL native):

-- Fresh installs: HTTP disabled by default
REVOKE EXECUTE ON FUNCTION df.http(text, text, text, jsonb, integer) FROM PUBLIC;

-- DBA enables HTTP for specific roles
SELECT df.grant_usage('etl_service', include_http => true);
SELECT df.grant_usage('webhook_handler', include_http => true);

-- Regular app users cannot use HTTP
SET ROLE app_user;
SELECT df.start(df.http('https://example.com', 'GET'), 'test');
-- ERROR: permission denied for function df.http

6.3 GUC Configuration

The current implementation does not expose HTTP allowlists or rate limits via GUCs. Allowed destinations are compiled in through Cargo features and the execution-time permission model is documented in http-security.md.

6.4 SSRF Protection

SSRF protection is implemented as a compile-time IP blocklist that blocks all private/reserved IP ranges (RFC 1918, link-local, loopback, IPv6 ULA, etc.), with DNS rebinding protection and IPv4-mapped IPv6 handling.

See http-security.md for the full specification including blocked ranges, implementation architecture, and testing.

6.5 Implementation

See http-security.md for the implemented privilege check, SSRF protections, and feature-gated hostname allowlist.

6.6 User Experience

-- DBA setup (one-time)
SELECT df.grant_usage('etl_service', include_http => true);

-- User with permission
SET ROLE etl_service;
SELECT df.start(
    df.http('https://api.company.com/webhook', 'POST', '{"event": "done"}'),
    'notify-completion'
);
-- ✓ Succeeds

-- User without permission
SET ROLE app_user;
SELECT df.start(
    df.http('https://api.company.com/data', 'GET'),
    'fetch-data'
);
-- ✗ ERROR: permission denied for function df.http

-- SSRF attempt (even with permission)
SET ROLE etl_service;
SELECT df.start(
    df.http('http://169.254.169.254/latest/meta-data/', 'GET'),
    'ssrf-attempt'
);
-- ✗ ERROR: HTTP request blocked: resolves to internal IP

6.7 Duroxide Background Worker Authentication/Authorization

Purpose: Keep the background worker’s control-plane connections passwordless while constraining what the worker can do when it is not running execute_sql on behalf of a user.

  • Auth (peer over Unix socket + pg_ident mapping): Use a Unix socket host and peer auth to map the OS user postgres → a low-privilege DB role (e.g., duroxide_worker). Example:

    • pg_hba.conf: local postgres duroxide_worker peer map=duroxide_map
    • pg_ident.conf: duroxide_map postgres duroxide_worker
    • Connection string (sqlx/duroxide store): postgresql://duroxide_worker@/postgres?host=/var/run/postgresql
    • Rationale: passwordless, auditable, and scoped to local socket; avoids trust on TCP.

  • AuthZ (limit ambient role): Grant the worker role only what the control-plane needs:

    • GRANT USAGE ON SCHEMA df, duroxide TO duroxide_worker;
    • GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA df, duroxide TO duroxide_worker;
    • REVOKE ALL ON SCHEMA public FROM duroxide_worker; (and avoid grants in other schemas)
    • Future tables: ensure default privileges in those schemas keep the role scoped to df.* and duroxide.

  • Privilege boundary vs execute_sql (implemented): The execute_sql activity opens a separate sqlx connection authenticated as submitted_by (the user’s current_user at df.start() time). This connection is completely independent of the worker’s control-plane connection. All other worker SQL (loading graphs, updating status) runs on the worker’s own pooled connection as the low-privilege duroxide_worker role.

  • pg_hba.conf requirement for per-user connections: The execute_sql activity connects as arbitrary users via TCP. This requires pg_hba.conf to allow trust (or peer) auth for local connections. In development with pgrx (TCP), the typical configuration is: host all all 127.0.0.1/32 trust

  • Safety notes:

    • For control-plane connections, prefer socket path (not 127.0.0.1) for peer to apply.
    • This model still assumes trusted extension code; the worker can connect as any user for execute_sql, but only that activity should do so.
    • If sockets/peer are unavailable (managed services), fall back to client cert or AAD/Managed Identity as a “passwordless” token, while keeping the DB role scoped to df.* + duroxide.

7. Data Isolation (RLS)

Status: Implemented

RLS on df.instances and df.nodes enforces per-user data isolation. The extension does not grant privileges to PUBLIC — admins must explicitly grant appropriate permissions (schema usage, function execution, table DML) to application roles. The background worker bypasses RLS as a superuser.

See rls.md for the full design including: - RLS policy definitions and rationale - Grant strategy (column-level UPDATE, no DELETE) - Ownership checks in df.cancel(), df.signal(), and monitoring functions - Worker bypass strategy - Deferred df.vars scoping (Phase 2)

8. Implementation Specification

8.1 Schema Changes

df.instances Table Updates

-- df.instances (implemented)
-- submitted_by is created as part of the table definition in src/lib.rs
-- submitted_by: REGROLE NOT NULL — effective role (current_user) when df.start() was called

-- df.nodes (implemented)
-- submitted_by: REGROLE — nullable, set when node is linked to an instance by df.start()

Note on earlier draft: An earlier version of this spec proposed submitted_by OID and a security_context JSONB column. The implemented design uses REGROLE (which stores OIDs but displays as role names) and the current v0.2.0 line tracks a single submitted_by column. The released v0.1.1 schema also carried a second column, login_role, for the session user identity; the v0.2.0 work removes that column and simplifies the model. See user-isolation.md for the full design and upgrade caveats.

df.vars Table Updates (per-user scoping) — Deferred

Per-user scoping of df.vars (adding an owner column + RLS) is deferred to a follow-up PR. See rls.md, Decision 5 for the design.

df.secrets Table (admin-managed, workflow-usable)

df.secrets stores shared secrets that are referenced by workflows but not directly readable by non-admins.

CREATE TABLE IF NOT EXISTS df.secrets (
    name  TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    created_by REGROLE NOT NULL DEFAULT current_user::regrole
);

-- Permissions
REVOKE ALL ON TABLE df.secrets FROM PUBLIC;
-- Only the worker and admins can read to perform substitution
GRANT SELECT ON TABLE df.secrets TO duroxide;
-- Secret mutators are admin-only
REVOKE EXECUTE ON FUNCTION df.setsecret(name text, value text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION df.unsetsecret(name text) FROM PUBLIC;
REVOKE EXECUTE ON FUNCTION df.clearsecrets() FROM PUBLIC;

Additional requirements: - Secrets must never be returned in results, status, or logs (only secret names may be logged for audit). - If secrets are stored in plaintext, storage must be restricted to trusted roles as above; encrypt-at-rest may be added later but is not assumed by this spec. - Secret substitution occurs inside the worker; users cannot SELECT df.secrets.

8.2 Function Permissions (Extension Installation)

-- Called during CREATE EXTENSION pg_durable

-- Default: all df functions require explicit grant
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA df FROM PUBLIC;

-- df.sql() - available to anyone who can use df.start()
-- (actual SQL permission checked via per-user sqlx connection)

-- df.http() - disabled by default, DBA enables per-role
REVOKE EXECUTE ON FUNCTION df.http(text, text, text, jsonb, integer) FROM PUBLIC;

-- Convenience helper: grant standard df usage, excluding df.http() unless
-- include_http => true is passed.
-- Example: SELECT df.grant_usage('app_role', include_http => true);

-- df.start(), df.status() - grant to users who need durable functions
-- DBA grants these: GRANT EXECUTE ON FUNCTION df.start TO app_role;

-- df.vars - normal users may set/get their own variables (enforced by RLS)
-- df.secrets - admin-only mutators; no generic getter for non-admins

8.3 Earlier GUC Proposal (Not Implemented)

The following GUC definitions were part of an earlier proposal. The current implementation uses Cargo features plus the execution-time privilege check described in docs/http-security.md, not runtime HTTP GUCs.

// src/lib.rs

// HTTP activity controls
GucRegistry::define_string_guc(
    "df.http_allowed_hosts",
    "Comma-separated list of allowed host patterns for df.http()",
    "",  // default: empty = no hosts allowed
    GucContext::Suset,
);

GucRegistry::define_bool_guc(
    "df.http_block_internal_ips", 
    "Block HTTP requests to private/internal IP ranges (SSRF protection)",
    true,  // default: enabled
    GucContext::Suset,
);

GucRegistry::define_int_guc(
    "df.http_timeout_seconds",
    "Timeout for HTTP requests in seconds",
    30,
    1,
    300,
    GucContext::Suset,
);

GucRegistry::define_int_guc(
    "df.http_rate_limit_per_minute",
    "Maximum HTTP requests per user per minute",
    60,
    1,
    1000,
    GucContext::Suset,
);

8.4 Identity Capture (Implemented)

Identity is captured at df.start() time in the user’s backend process. DSL functions (df.sql(), df.seq(), etc.) do not capture identity — nodes are created without submitted_by, which remains NULL until linked to an instance.

// src/dsl.rs — inside df.start()

// Capture current_user identity directly from PostgreSQL (no SPI needed)
let current_user_oid = unsafe { pgrx::pg_sys::GetUserId() };      // submitted_by

// OID is stored as REGROLE in the instance and node rows:
//   INSERT INTO df.instances (..., submitted_by)
//   VALUES (..., {current_user_oid}::oid::regrole)

Why GetUserId() (i.e., current_user): This captures the effective user at df.start() time. Inside a SECURITY DEFINER function, this will be the definer’s identity — which is the expected behavior for the simplified model. The user who has current_user privileges at call time is the identity used for SQL execution. See user-isolation.md for the full design.

8.5 Secure SQL Execution (Implemented)

User SQL is executed on a dedicated sqlx connection authenticated as the submitting user, not via SPI:

// src/types.rs — connect_as_user()

pub async fn connect_as_user(
    user: &str,
    database: Option<&str>,
) -> Result<sqlx::postgres::PgConnection, String> {
    let mut options = PgConnectOptions::new()
        .username(user)              // Authenticate as submitted_by
        .database(db)
        .port(get_port());

    let mut conn = PgConnection::connect_with(&options).await?;

    // Mark connection as running inside a workflow (prevents variable mutations)
    sqlx::query("SET df.in_workflow = 'true'")
        .execute(&mut conn)
        .await?;

    Ok(conn)
}

// src/activities/execute_sql.rs

#[derive(Serialize, Deserialize)]
struct ExecuteSqlInput {
    pub query: String,
    pub submitted_by: String,       // User identity (current_user at df.start() time)
    pub database: Option<String>,   // Target database
}

pub async fn execute(
    ctx: ActivityContext,
    _pool: Arc<PgPool>,             // shared pool — not used for user SQL
    input_json: String,
) -> Result<String, String> {
    let input: ExecuteSqlInput = serde_json::from_str(&input_json)?;

    // Open a per-user connection (NOT the worker's shared pool)
    let mut conn = connect_as_user(
        &input.submitted_by,
        input.database.as_deref(),
    ).await?;

    // Execute user SQL on the per-user connection
    let result = sqlx::query(&input.query)
        .fetch_all(&mut conn)
        .await?;

    // conn is dropped here — connection closed
    Ok(/* serialized result */)
}

Key properties of the implemented approach: - User SQL runs on a connection authenticated as the user — never on the worker’s shared pool - RESET ROLE reverts to submitted_by (the user’s own identity) — no escalation possible - Each SQL node gets a fresh connection (future optimization: per-instance connection caching) - SET df.in_workflow = 'true' prevents variable mutations (setvar/unsetvar/clearvars) during workflow execution. Note: it does not currently prevent recursive df.start() calls — that is a potential future improvement

8.6 Orchestration Wiring (Implemented)

The orchestration packages the query and both identities together before scheduling the activity:

// src/orchestrations/execute_function_graph.rs

let input = serde_json::json!({
    "query": final_query,
    "submitted_by": node.submitted_by,
    "database": node.database,
});

let result = ctx
    .schedule_activity(activities::execute_sql::NAME, input.to_string())
    .into_activity()
    .await?;

Both values come from the FunctionNode loaded via the load_function_graph activity. They are stable across replays (safe for determinism).

8.7 Fault Injection Hooks (Test-Only)

To validate crash/panic behavior and restart semantics, pg_durable should include a test-only fault injection mechanism that can deterministically trigger failures inside execute_sql.

Mechanism (proposed): - A superuser-only GUC such as df.test_fault_inject = 'none'|'execute_sql_error'|'execute_sql_panic_before'|'execute_sql_panic_after_connect'. - The worker checks this setting at runtime and triggers the configured fault. - This GUC MUST NOT be enabled by default and should be documented as test-only.

8.8 SPI as a Potential Future Optimization

The current implementation executes user SQL via external sqlx connections. An alternative approach — executing via SPI with SetUserIdAndSecContext() — was considered during design and remains a potential future optimization.

What SPI + SetUserIdAndSecContext would provide

Benefit Description
Stronger escape prevention SetUserIdAndSecContext() operates at the C level. SQL commands like RESET ROLE only affect the session-level role, not the C-level security context. With the sqlx approach, RESET ROLE reverts to submitted_by — which is still the user’s identity (no escalation), but the effective role does change.
No pg_hba.conf dependency SPI runs in-process; no TCP connection is needed for user SQL. The current approach requires pg_hba.conf trust auth so the worker process can connect as arbitrary users.
Lower connection overhead SPI avoids TCP connection setup/teardown per SQL node. This could matter for workflows with many SQL nodes.
Atomic with worker transaction SPI shares the worker’s transaction context, which could simplify certain consistency scenarios.

Why the sqlx approach was chosen

Reason Description
Async compatibility SPI is synchronous and has thread-affinity requirements. The duroxide runtime is async (tokio). Mixing SPI into an async context requires careful locking and prevents concurrent SQL execution across different instances. The sqlx approach naturally composes with async.
Concurrency With SPI, only one SQL query can execute at a time per background worker (global SPI lock + single backend thread). With sqlx, multiple SQL nodes from different instances could execute concurrently on separate connections.
Simplicity The sqlx approach uses standard PostgreSQL client semantics. SPI + SetUserIdAndSecContext requires unsafe C code, RAII guards, subtransaction management, and careful interaction with the duroxide runtime.
Equivalent security Both approaches provide privilege isolation. The sqlx connection is authenticated as the user — RESET ROLE only reverts to the user’s own submitted_by, not the worker’s identity. There is no privilege escalation path.

When to reconsider SPI

SPI would be worth revisiting if: - Connection overhead becomes a bottleneck (many SQL nodes per workflow) - The pg_hba.conf trust requirement is unacceptable in a deployment environment - A need arises for tighter integration with the worker’s transaction (e.g., savepoint-based partial rollback) - The C-level escape prevention of SetUserIdAndSecContext is required for compliance

9. User Experience

9.1 End User Workflow

-- User with df function access (DBA already granted)
SET ROLE app_user;

-- Create a durable function (SQL runs as app_user)
SELECT df.start(
    df.sql('INSERT INTO my_table VALUES (now(), ''hello'')'),
    'my-job'
);
-- Returns: instance_id 'abc12345'

-- Check status
SELECT df.status('abc12345');
-- Returns: 'completed' (or 'running', 'failed')

-- View my instances only (RLS enforced)
SELECT id, label, status, submitted_at 
FROM df.instances 
ORDER BY submitted_at DESC;

9.2 Administrator Workflow

-- After CREATE EXTENSION, admins must grant privileges to application roles.
-- Table and schema grants are NOT auto-applied.

-- 1. Grant basic df access to a role
-- (see USER_GUIDE.md "Privilege Grants" for the full list of individual grants)
SELECT df.grant_usage('app_backend');

-- 2. Opt a role into HTTP access only when needed
SELECT df.grant_usage('app_backend_http', include_http => true);

-- 3. Allowed HTTP destinations depend on the build feature set.
-- See docs/http-security.md for the current allowlist.

-- 4. View all instances (superuser bypasses RLS)
SELECT submitted_by, count(*) 
FROM df.instances 
GROUP BY submitted_by;

9.3 Error Messages

Scenario Error Message
No function permission ERROR: permission denied for function df.http or execution-time Blocked: role '{role}' does not have EXECUTE privilege on df.http()...
SQL permission denied ERROR: permission denied for table secret_data (in df.status result)
HTTP host not allowed ERROR: HTTP request blocked: host 'evil.com' not in allowed list
SSRF blocked ERROR: HTTP request blocked: resolves to internal IP 169.254.169.254
RLS filtered (silent — user simply doesn’t see other users' rows)
Cross-user cancel/signal ERROR: Instance not found or access denied: <id>

10. Test Specification

10.1 Unit Tests

UT1: Security Context Capture

#[pg_test]
fn test_security_context_capture() {
    // Setup: Create test user
    Spi::run("CREATE USER test_capture_user").unwrap();
    Spi::run("GRANT EXECUTE ON FUNCTION df.start TO test_capture_user").unwrap();
    Spi::run("GRANT EXECUTE ON FUNCTION df.sql TO test_capture_user").unwrap();
    Spi::run("GRANT EXECUTE ON FUNCTION df.status TO test_capture_user").unwrap();
    Spi::run("SET ROLE test_capture_user").unwrap();

    let ctx = SecurityContext::capture();

    assert_eq!(ctx.user_name, "test_capture_user");
    assert!(!ctx.is_superuser);
    assert!(ctx.user_oid > 0);

    // Cleanup
    Spi::run("RESET ROLE").unwrap();
    Spi::run("DROP USER test_capture_user").unwrap();
}

UT2: Context Switch and Restore

Note: This unit test validates PostgreSQL’s security context APIs (GetUserIdAndSecContext / SetUserIdAndSecContext) and the general ability to switch/restore identities inside a backend. These APIs are not currently used by pg_durable’s implemented execution path (which uses sqlx connections instead), but this test documents the API’s behavior for potential future SPI-based execution (see Section 8.8).

The pg_durable-specific verification of privilege isolation is covered by the E2E tests (E2E-SEC-01..03) which assert privilege isolation against real tables via the implemented sqlx connection approach.

#[pg_test]
fn test_context_switch_and_restore() {
    // Get original context
    let original_user = Spi::get_one::<String>("SELECT current_user")
        .unwrap().unwrap();

    // Create target user
    Spi::run("CREATE USER test_switch_user").unwrap();
    let target_oid: u32 = Spi::get_one(
        "SELECT oid::int4 FROM pg_roles WHERE rolname = 'test_switch_user'"
    ).unwrap().unwrap();

    // Switch context
    unsafe {
        let mut saved_uid: pg_sys::Oid = 0;
        let mut saved_sec: i32 = 0;
        pg_sys::GetUserIdAndSecContext(&mut saved_uid, &mut saved_sec);

        pg_sys::SetUserIdAndSecContext(
            target_oid,
            saved_sec | pg_sys::SECURITY_LOCAL_USERID_CHANGE as i32
        );

        // Verify switch
        let current = Spi::get_one::<String>("SELECT current_user").unwrap().unwrap();
        assert_eq!(current, "test_switch_user");

        // Restore
        pg_sys::SetUserIdAndSecContext(saved_uid, saved_sec);
    }

    // Verify restore
    let restored_user = Spi::get_one::<String>("SELECT current_user")
        .unwrap().unwrap();
    assert_eq!(restored_user, original_user);

    // Cleanup
    Spi::run("DROP USER test_switch_user").unwrap();
}

10.2 E2E Security Tests

E2E-SEC-01: Basic Privilege Isolation

File: tests/e2e/sql/security_01_privilege_isolation.sql

-- Test: User SQL executes with submitting user's privileges
-- Expected: User can access their own tables, not others'

-- Setup: Create two users with separate tables
DROP TABLE IF EXISTS user_a_data;
DROP TABLE IF EXISTS user_b_data;
DROP USER IF EXISTS sec_test_user_a;
DROP USER IF EXISTS sec_test_user_b;

CREATE USER sec_test_user_a;
CREATE USER sec_test_user_b;
GRANT EXECUTE ON FUNCTION df.start TO sec_test_user_a, sec_test_user_b;
GRANT EXECUTE ON FUNCTION df.sql TO sec_test_user_a, sec_test_user_b;
GRANT EXECUTE ON FUNCTION df.status TO sec_test_user_a, sec_test_user_b;

-- Create tables owned by each user
SET ROLE sec_test_user_a;
CREATE TABLE user_a_data (id serial, value text);
INSERT INTO user_a_data (value) VALUES ('secret_a');
RESET ROLE;

SET ROLE sec_test_user_b;
CREATE TABLE user_b_data (id serial, value text);
INSERT INTO user_b_data (value) VALUES ('secret_b');
RESET ROLE;

-- Test 1: User A can access their own table
SET ROLE sec_test_user_a;
CREATE TEMP TABLE _test_1 (instance_id TEXT);
INSERT INTO _test_1 SELECT df.start(
    df.sql('SELECT value FROM user_a_data'),
    'sec-test-1-own-table'
);
RESET ROLE;

-- Poll until complete
DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_1;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 100;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) != 'completed' THEN
        RAISE EXCEPTION 'TEST 1 FAILED: User A could not access own table. Status: %', status;
    END IF;
END $$;

-- Test 2: User A cannot access User B's table
SET ROLE sec_test_user_a;
CREATE TEMP TABLE _test_2 (instance_id TEXT);
INSERT INTO _test_2 SELECT df.start(
    df.sql('SELECT value FROM user_b_data'),
    'sec-test-2-other-table'
);
RESET ROLE;

-- Poll until complete (should fail)
DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    result_json JSONB;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_2;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 100;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) != 'failed' THEN
        RAISE EXCEPTION 'TEST 2 FAILED: User A should NOT access User B table. Status: %', status;
    END IF;

    -- Verify error message mentions permission denied
    SELECT result::jsonb INTO result_json 
    FROM df.instances WHERE id = inst_id;

    IF NOT (result_json->>'error' ILIKE '%permission denied%') THEN
        RAISE EXCEPTION 'TEST 2 FAILED: Expected permission denied error, got: %', result_json->>'error';
    END IF;
END $$;

-- Cleanup
DROP TABLE _test_1;
DROP TABLE _test_2;
DROP TABLE user_a_data;
DROP TABLE user_b_data;
DROP USER sec_test_user_a;
DROP USER sec_test_user_b;

SELECT 'TEST PASSED: E2E-SEC-01 Privilege Isolation' AS result;

E2E-SEC-02: RESET ROLE Escape Prevention

File: tests/e2e/sql/security_02_reset_role_escape.sql

-- Test: User cannot escape to duroxide privileges via RESET ROLE
-- Expected: RESET ROLE has no effect; SQL still runs as original user

-- Setup
DROP TABLE IF EXISTS admin_only_table;
DROP USER IF EXISTS sec_test_escape_user;

CREATE USER sec_test_escape_user;
GRANT EXECUTE ON FUNCTION df.start TO sec_test_escape_user;
GRANT EXECUTE ON FUNCTION df.sql TO sec_test_escape_user;
GRANT EXECUTE ON FUNCTION df.status TO sec_test_escape_user;

-- Create a table that sec_test_escape_user cannot access
CREATE TABLE admin_only_table (secret text);
INSERT INTO admin_only_table VALUES ('admin_secret');
-- duroxide CAN access this, but sec_test_escape_user CANNOT

-- User attempts to escape via RESET ROLE
SET ROLE sec_test_escape_user;
CREATE TEMP TABLE _test_escape (instance_id TEXT);
INSERT INTO _test_escape SELECT df.start(
    df.sql('RESET ROLE; SELECT secret FROM admin_only_table;'),
    'sec-test-escape-attempt'
);
RESET ROLE;

-- Poll until complete (should fail)
DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_escape;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 100;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    -- The key assertion: even with RESET ROLE, the query should FAIL
    -- because the connection is authenticated as sec_test_escape_user,
    -- and RESET ROLE only reverts to that identity (no escalation possible)
    IF lower(status) = 'completed' THEN
        RAISE EXCEPTION 'SECURITY VULNERABILITY: RESET ROLE escape succeeded!';
    END IF;

    IF lower(status) != 'failed' THEN
        RAISE EXCEPTION 'TEST FAILED: Unexpected status: %', status;
    END IF;
END $$;

-- Cleanup
DROP TABLE _test_escape;
DROP TABLE admin_only_table;
DROP USER sec_test_escape_user;

SELECT 'TEST PASSED: E2E-SEC-02 RESET ROLE Escape Prevention' AS result;

E2E-SEC-03: SET ROLE Escalation Prevention

File: tests/e2e/sql/security_03_set_role_escalation.sql

-- Test: User cannot escalate to a role they're not a member of
-- Expected: SET ROLE to non-member role fails

-- Setup
DROP USER IF EXISTS sec_test_low_priv;
DROP USER IF EXISTS sec_test_high_priv;

CREATE USER sec_test_low_priv;
CREATE USER sec_test_high_priv WITH SUPERUSER;  -- high privilege role
GRANT EXECUTE ON FUNCTION df.start TO sec_test_low_priv;
GRANT EXECUTE ON FUNCTION df.sql TO sec_test_low_priv;
GRANT EXECUTE ON FUNCTION df.status TO sec_test_low_priv;
-- NOTE: sec_test_low_priv is NOT a member of sec_test_high_priv

-- Low-priv user attempts to escalate
SET ROLE sec_test_low_priv;
CREATE TEMP TABLE _test_escalate (instance_id TEXT);
INSERT INTO _test_escalate SELECT df.start(
    df.sql('SET ROLE sec_test_high_priv; SELECT usename FROM pg_shadow;'),
    'sec-test-escalation-attempt'
);
RESET ROLE;

-- Poll until complete (should fail)
DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_escalate;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 100;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) = 'completed' THEN
        RAISE EXCEPTION 'SECURITY VULNERABILITY: SET ROLE escalation succeeded!';
    END IF;

    IF lower(status) != 'failed' THEN
        RAISE EXCEPTION 'TEST FAILED: Unexpected status: %', status;
    END IF;
END $$;

-- Cleanup
DROP TABLE _test_escalate;
DROP USER sec_test_low_priv;
DROP USER sec_test_high_priv;

SELECT 'TEST PASSED: E2E-SEC-03 SET ROLE Escalation Prevention' AS result;

E2E-SEC-04: Row-Level Security Isolation

File: tests/e2e/sql/security_04_rls_isolation.sql

-- Test: Users can only see their own instances in df.instances
-- Expected: User A cannot see User B's instances

-- Setup
DROP USER IF EXISTS sec_test_rls_a;
DROP USER IF EXISTS sec_test_rls_b;

CREATE USER sec_test_rls_a;
CREATE USER sec_test_rls_b;
GRANT EXECUTE ON FUNCTION df.start TO sec_test_rls_a, sec_test_rls_b;
GRANT EXECUTE ON FUNCTION df.sql TO sec_test_rls_a, sec_test_rls_b;
GRANT EXECUTE ON FUNCTION df.status TO sec_test_rls_a, sec_test_rls_b;

-- User A creates an instance
SET ROLE sec_test_rls_a;
CREATE TEMP TABLE _test_rls_a (instance_id TEXT);
INSERT INTO _test_rls_a SELECT df.start(
    df.sql('SELECT 1'),
    'rls-test-user-a'
);
RESET ROLE;

-- User B creates an instance
SET ROLE sec_test_rls_b;
CREATE TEMP TABLE _test_rls_b (instance_id TEXT);
INSERT INTO _test_rls_b SELECT df.start(
    df.sql('SELECT 2'),
    'rls-test-user-b'
);
RESET ROLE;

-- Wait for both to complete
PERFORM pg_sleep(2);

-- Test: User A can see their instance
SET ROLE sec_test_rls_a;
DO $$
DECLARE
    a_instance_id TEXT;
    visible_count INT;
BEGIN
    SELECT instance_id INTO a_instance_id FROM _test_rls_a;

    -- User A should see exactly 1 instance (their own)
    SELECT count(*) INTO visible_count FROM df.instances;

    IF visible_count != 1 THEN
        RAISE EXCEPTION 'RLS FAILED: User A sees % instances, expected 1', visible_count;
    END IF;

    -- Verify it's their instance
    IF NOT EXISTS (SELECT 1 FROM df.instances WHERE id = a_instance_id) THEN
        RAISE EXCEPTION 'RLS FAILED: User A cannot see their own instance';
    END IF;
END $$;
RESET ROLE;

-- Test: User B can see their instance, not A's
SET ROLE sec_test_rls_b;
DO $$
DECLARE
    a_instance_id TEXT;
    b_instance_id TEXT;
    visible_count INT;
BEGIN
    SELECT instance_id INTO a_instance_id FROM _test_rls_a;
    SELECT instance_id INTO b_instance_id FROM _test_rls_b;

    -- User B should see exactly 1 instance (their own)
    SELECT count(*) INTO visible_count FROM df.instances;

    IF visible_count != 1 THEN
        RAISE EXCEPTION 'RLS FAILED: User B sees % instances, expected 1', visible_count;
    END IF;

    -- Verify User B cannot see User A's instance
    IF EXISTS (SELECT 1 FROM df.instances WHERE id = a_instance_id) THEN
        RAISE EXCEPTION 'RLS FAILED: User B can see User A instance!';
    END IF;
END $$;
RESET ROLE;

-- Cleanup
DROP TABLE _test_rls_a;
DROP TABLE _test_rls_b;
DROP USER sec_test_rls_a;
DROP USER sec_test_rls_b;

SELECT 'TEST PASSED: E2E-SEC-04 RLS Isolation' AS result;

E2E-SEC-05: Function Permission Requirement

File: tests/e2e/sql/security_05_function_permission_required.sql

-- Test: Users without EXECUTE on df.start cannot create durable functions
-- Expected: df.start() fails with permission denied

-- Setup
DROP USER IF EXISTS sec_test_no_permission;
CREATE USER sec_test_no_permission;
-- NOTE: NOT granting EXECUTE on df.start/df.sql

-- Attempt to create durable function without required function permissions
SET ROLE sec_test_no_permission;
DO $$
BEGIN
    -- This should fail
    PERFORM df.start(df.sql('SELECT 1'), 'should-fail');
    RAISE EXCEPTION 'SECURITY FAILURE: User without permission could call df.start()!';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLERRM NOT ILIKE '%permission denied%' AND 
           SQLERRM NOT ILIKE '%df.start%' THEN
            RAISE EXCEPTION 'Unexpected error: %', SQLERRM;
        END IF;
        -- Expected: permission denied
END $$;
RESET ROLE;

-- Cleanup
DROP USER sec_test_no_permission;

SELECT 'TEST PASSED: E2E-SEC-05 Function Permission Requirement' AS result;

10.3 Test Matrix

Test ID Description Threat Expected Result
E2E-SEC-01 Privilege Isolation T1, T2 User accesses only own tables
E2E-SEC-02 RESET ROLE Escape T1 RESET ROLE has no effect
E2E-SEC-03 SET ROLE Escalation T2 SET ROLE to non-member fails
E2E-SEC-04 RLS Isolation T4 Users see only own instances (see also 37_rls.sql)
E2E-SEC-05 Function Permission - Users without EXECUTE cannot use df.*
E2E-SEC-06 Dynamic SQL Escape T3 EXECUTE ‘RESET ROLE’ fails
E2E-SEC-07 HTTP SSRF Blocked T8 Internal IP requests denied
E2E-SEC-08 HTTP Function Permission T9 Users without GRANT cannot use df.http
E2E-SEC-09 HTTP Allowlist T9 Non-allowlisted hosts denied
E2E-SEC-10 Vars RLS Isolation T10 Users cannot read/override other users' vars
E2E-SEC-11 execute_sql Fault: Error - Instance fails cleanly; worker continues
E2E-SEC-12 execute_sql Fault: Panic + Restart - After restart, instance is failed or retried deterministically

10.4 HTTP Activity Tests

E2E-SEC-07: HTTP SSRF Protection

File: tests/e2e/sql/security_07_http_ssrf.sql

-- Test: HTTP requests to internal IPs are blocked (always-on protection)
-- Expected: SSRF attempts fail regardless of permissions

-- Setup
DROP USER IF EXISTS sec_test_ssrf_user;
CREATE USER sec_test_ssrf_user;
GRANT EXECUTE ON FUNCTION df.start TO sec_test_ssrf_user;
GRANT EXECUTE ON FUNCTION df.sql TO sec_test_ssrf_user;
GRANT EXECUTE ON FUNCTION df.http(text, text, text, jsonb, integer) TO sec_test_ssrf_user;  -- Has HTTP permission
GRANT EXECUTE ON FUNCTION df.status TO sec_test_ssrf_user;
GRANT sec_test_ssrf_user TO duroxide;

-- Build with a feature set that allows the chosen public test destination.
-- SSRF protection still applies regardless of that feature set.

-- Test 1: AWS metadata endpoint (169.254.169.254) should be blocked
SET ROLE sec_test_ssrf_user;
CREATE TEMP TABLE _test_ssrf_1 (instance_id TEXT);
INSERT INTO _test_ssrf_1 SELECT df.start(
    df.http('http://169.254.169.254/latest/meta-data/', 'GET'),
    'ssrf-test-aws-metadata'
);
RESET ROLE;

DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    error_msg TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_ssrf_1;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 50;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) = 'completed' THEN
        RAISE EXCEPTION 'SSRF VULNERABILITY: AWS metadata request succeeded!';
    END IF;

    -- Verify error message mentions blocked/internal
    SELECT result->>'error' INTO error_msg FROM df.instances WHERE id = inst_id;
    IF error_msg NOT ILIKE '%internal%' AND error_msg NOT ILIKE '%blocked%' THEN
        RAISE EXCEPTION 'Expected SSRF block error, got: %', error_msg;
    END IF;
END $$;

-- Test 2: localhost should be blocked
SET ROLE sec_test_ssrf_user;
CREATE TEMP TABLE _test_ssrf_2 (instance_id TEXT);
INSERT INTO _test_ssrf_2 SELECT df.start(
    df.http('http://127.0.0.1:8080/', 'GET'),
    'ssrf-test-localhost'
);
RESET ROLE;

DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_ssrf_2;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 50;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) = 'completed' THEN
        RAISE EXCEPTION 'SSRF VULNERABILITY: localhost request succeeded!';
    END IF;
END $$;

-- Cleanup
DROP TABLE _test_ssrf_1;
DROP TABLE _test_ssrf_2;
REVOKE sec_test_ssrf_user FROM duroxide;
DROP USER sec_test_ssrf_user;
SELECT 'TEST PASSED: E2E-SEC-07 HTTP SSRF Protection' AS result;

E2E-SEC-08: HTTP Function Permission

File: tests/e2e/sql/security_08_http_permission.sql

-- Test: Users need EXECUTE permission on df.http to use it
-- Expected: Users without permission get function permission error

-- Setup
DROP USER IF EXISTS sec_test_http_denied;
DROP USER IF EXISTS sec_test_http_allowed;

CREATE USER sec_test_http_denied;
CREATE USER sec_test_http_allowed;

-- Both users can use df.start and df.sql
GRANT EXECUTE ON FUNCTION df.start TO sec_test_http_denied, sec_test_http_allowed;
GRANT EXECUTE ON FUNCTION df.sql TO sec_test_http_denied, sec_test_http_allowed;
GRANT EXECUTE ON FUNCTION df.status TO sec_test_http_denied, sec_test_http_allowed;
GRANT SELECT ON df.instances TO sec_test_http_denied, sec_test_http_allowed;

-- Only sec_test_http_allowed gets HTTP permission
GRANT EXECUTE ON FUNCTION df.http(text, text, text, jsonb, integer) TO sec_test_http_allowed;
-- NOTE: sec_test_http_denied does NOT get df.http permission

GRANT sec_test_http_denied TO duroxide;
GRANT sec_test_http_allowed TO duroxide;

-- Build with a feature set that allows httpbingo.org.

-- Test 1: User WITHOUT df.http permission should fail
SET ROLE sec_test_http_denied;
DO $$
BEGIN
    PERFORM df.start(
        df.http('https://httpbingo.org/get', 'GET'),
        'http-test-denied'
    );
    RAISE EXCEPTION 'SECURITY FAILURE: User without df.http permission could use it!';
EXCEPTION
    WHEN insufficient_privilege THEN
        -- Expected: permission denied for function df.http
        NULL;
    WHEN OTHERS THEN
        IF SQLERRM NOT ILIKE '%permission denied%' THEN
            RAISE EXCEPTION 'Unexpected error: %', SQLERRM;
        END IF;
END $$;
RESET ROLE;

-- Test 2: User WITH df.http permission should succeed
SET ROLE sec_test_http_allowed;
CREATE TEMP TABLE _test_http_allowed (instance_id TEXT);
INSERT INTO _test_http_allowed SELECT df.start(
    df.http('https://httpbingo.org/get', 'GET'),
    'http-test-allowed'
);
RESET ROLE;

DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_http_allowed;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 100;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) != 'completed' THEN
        RAISE EXCEPTION 'TEST FAILED: Allowed user HTTP should succeed. Status: %', status;
    END IF;
END $$;

-- Cleanup
DROP TABLE _test_http_allowed;
REVOKE sec_test_http_denied FROM duroxide;
REVOKE sec_test_http_allowed FROM duroxide;
DROP USER sec_test_http_denied;
DROP USER sec_test_http_allowed;

SELECT 'TEST PASSED: E2E-SEC-08 HTTP Function Permission' AS result;

E2E-SEC-09: HTTP URL Allowlist

File: tests/e2e/sql/security_09_http_allowlist.sql

-- Test: HTTP requests only allowed to the destinations permitted by the
-- build-time feature allowlist
-- Expected: Requests to non-allowlisted hosts are denied

-- Setup
DROP USER IF EXISTS sec_test_allowlist_user;
CREATE USER sec_test_allowlist_user;
GRANT EXECUTE ON FUNCTION df.start TO sec_test_allowlist_user;
GRANT EXECUTE ON FUNCTION df.sql TO sec_test_allowlist_user;
GRANT EXECUTE ON FUNCTION df.http(text, text, text, jsonb, integer) TO sec_test_allowlist_user;
GRANT EXECUTE ON FUNCTION df.status TO sec_test_allowlist_user;
GRANT SELECT ON df.instances TO sec_test_allowlist_user;
GRANT sec_test_allowlist_user TO duroxide;

-- Build with a feature set that allows httpbingo.org but not evil.com.

-- Test 1: Allowlisted host should succeed
SET ROLE sec_test_allowlist_user;
CREATE TEMP TABLE _test_allowed (instance_id TEXT);
INSERT INTO _test_allowed SELECT df.start(
    df.http('https://httpbingo.org/get', 'GET'),
    'allowlist-test-allowed'
);
RESET ROLE;

DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_allowed;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 100;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) != 'completed' THEN
        RAISE EXCEPTION 'TEST FAILED: Allowlisted host should succeed. Status: %', status;
    END IF;
END $$;

-- Test 2: Non-allowlisted host should fail
SET ROLE sec_test_allowlist_user;
CREATE TEMP TABLE _test_denied (instance_id TEXT);
INSERT INTO _test_denied SELECT df.start(
    df.http('https://evil.com/steal-data', 'GET'),
    'allowlist-test-denied'
);
RESET ROLE;

DO $$
DECLARE
    inst_id TEXT;
    status TEXT;
    error_msg TEXT;
    attempts INT := 0;
BEGIN
    SELECT instance_id INTO inst_id FROM _test_denied;
    LOOP
        SELECT s INTO status FROM df.status(inst_id) s;
        EXIT WHEN lower(status) IN ('completed', 'failed') OR attempts > 50;
        PERFORM pg_sleep(0.1);
        attempts := attempts + 1;
    END LOOP;

    IF lower(status) = 'completed' THEN
        RAISE EXCEPTION 'SECURITY FAILURE: Non-allowlisted host succeeded!';
    END IF;

    SELECT result->>'error' INTO error_msg FROM df.instances WHERE id = inst_id;
    IF error_msg NOT ILIKE '%allowed%' AND error_msg NOT ILIKE '%blocked%' THEN
        RAISE EXCEPTION 'Expected allowlist error, got: %', error_msg;
    END IF;
END $$;

-- Cleanup
DROP TABLE _test_allowed;
DROP TABLE _test_denied;
REVOKE sec_test_allowlist_user FROM duroxide;
DROP USER sec_test_allowlist_user;

SELECT 'TEST PASSED: E2E-SEC-09 HTTP URL Allowlist' AS result;

10.5 Fault Injection Tests (execute_sql)

These tests validate behavior when execute_sql fails due to expected errors and unexpected panics.

E2E-SEC-11: execute_sql error path

  • Configure df.test_fault_inject = 'execute_sql_error' (superuser)
  • Start a simple workflow with a SQL node
  • Assert:
    • Instance transitions to failed
    • Error is recorded without leaking secrets/other-user data
    • Worker keeps processing subsequent instances

E2E-SEC-12: execute_sql panic + restart

  • Configure df.test_fault_inject = 'execute_sql_panic_after_connect' (superuser)
  • Start a workflow that will hit SQL execution
  • Assert:
    • The worker process crashes/restarts (or is forcibly restarted)
    • On restart, the runtime resumes from durable state
    • The instance eventually becomes failed (or is retried if the orchestration semantics retry)
    • No stale per-user connections remain after restart

Restart validation strategy: - Use the existing E2E harness to stop/start Postgres (or restart the bgworker) between polling loops. - After restart, poll df.status(instance_id) until terminal state.

10.6 Vars/Secrets Security Tests

E2E-SEC-10: Vars RLS isolation

  • User A sets df.setvar('k','a')
  • User B sets df.setvar('k','b')
  • User A starts a workflow that reads $k and asserts it sees a
  • User B starts a workflow that reads $k and asserts it sees b
  • Assert User A cannot read/overwrite User B’s var rows

Secrets tests (design-level)

  • Verify non-admin cannot EXECUTE df.setsecret/df.unsetsecret/df.clearsecrets
  • Verify non-admin cannot SELECT from df.secrets
  • Verify workflows can reference a secret by name and the secret value is not returned/logged

11. Open Questions

OQ1: Azure Managed Identity Integration

Question: Should duroxide support Azure AD authentication as an alternative to pg_hba.conf local auth?

Considerations: - Useful for multi-instance deployments - Requires duroxide-pg changes - May need Azure SDK dependency

Recommendation: Defer to Phase 2; pg_hba.conf approach works for Azure Flexible Server.

OQ2: Cross-Database Execution

Question: Should durable functions support cross-database queries (via postgres_fdw)?

Considerations: - Per-user sqlx connections are per-database (the database field in ExecuteSqlInput supports targeting a specific database) - FDW connections are separate sessions - Security model becomes complex

Recommendation: Out of scope; document as unsupported.

OQ3: SECURITY DEFINER Functions

Question: If user SQL calls a SECURITY DEFINER function, which user’s context applies?

Answer: PostgreSQL’s standard behavior applies: - SECURITY DEFINER function runs as its owner - After function returns, context reverts - This is expected and documented PostgreSQL behavior

Recommendation: Document this; no special handling needed.

OQ4: Superuser Durable Functions

Status: Resolved — implemented via pg_durable.enable_superuser_instances.

Question: Should superusers be able to create durable functions that run as superuser?

Considerations: - Useful for administrative tasks - Higher risk if function graph is compromised - BYPASSRLS roles can forge submitted_by to a superuser OID, making this a privilege escalation vector in multi-tenant environments

Resolution: Gated behind pg_durable.enable_superuser_instances (default off). When off, df.start() immediately rejects any submission whose current_user is a superuser, and the background worker rejects any instance whose submitted_by resolves to a superuser at execution time (closing the BYPASSRLS forgery path). When on, superuser submissions are allowed as before. The GUC is SUSET / SUPERUSER_ONLY and hidden from SHOW ALL. See superuser_guc.md for full design rationale.

Appendix A: Security Checklist for Code Review

  • [ ] All user SQL goes through connect_as_user() → per-user sqlx connection (never the worker’s shared pool)
  • [ ] submitted_by is captured from GetUserId() at df.start() time, not from user input
  • [ ] Role names are properly quoted where needed
  • [ ] Access control uses PostgreSQL-native function permissions (EXECUTE on df.start/df.sql/df.http)
  • [x] RLS policies use current_user, not user-supplied values
  • [ ] Error messages don’t leak other users' data
  • [ ] Logging includes effective user for audit trail
  • [ ] SET df.in_workflow = 'true' is set on user connections to prevent variable mutation during execution (future: could also guard against recursive df.start())
  • [ ] All SPI queries that accept user-supplied parameters prefer parameterized APIs (Spi::get_one_with_args() / Spi::run_with_args()). Use manual .replace('\'', "''") escaping only as fallback where parameters cannot be used. Cross-check: df.status(), df.result(), df.cancel(), df.signal(), monitoring functions
  • [ ] PL/pgSQL and SQL helper functions include SET search_path = pg_catalog, df, pg_temp
  • [ ] All function/table references in dynamic SQL are schema-qualified (df.instances, not instances)

Appendix B: Related PostgreSQL Internals

Note: These internals are relevant to the potential future SPI-based execution path described in Section 8.8. The current implementation uses sqlx connections and does not directly call these APIs for user SQL execution.

SetUserIdAndSecContext Flags

Flag Value Meaning
SECURITY_LOCAL_USERID_CHANGE 1 Temporary userid change (like SECURITY DEFINER)
SECURITY_RESTRICTED_OPERATION 2 Block certain operations
SECURITY_NOFORCE_RLS 4 Don’t force RLS for this operation

Relevant PostgreSQL Source

  • src/backend/utils/init/miscinit.c: SetUserIdAndSecContext implementation
  • src/backend/utils/adt/acl.c: Permission checking functions
  • src/backend/executor/spi.c: SPI implementation

End of Specification