# Audit this PostgreSQL 17 queue implementation for safe concurrent claiming, idempotency, lease recovery, bounded retries, tenant isolation, and current PostgreSQL practices. Verify transaction and locking claims precisely. Ground every high-priority finding in the supplied SQL with file and line locations. Use exact PostgreSQL 17 documentation sections for SELECT locking clauses and SKIP LOCKED, explicit locking, partial indexes, INSERT ON CONFLICT, row security policies, PL/pgSQL transaction behavior, and function security. Distinguish database guarantees from application-level delivery guarantees, and do not attach generic or unrelated sources. Return a concise structured report with no more than four production-impacting findings so synthesis can complete within its time budget.

**Mode:** default  
**Runtime:** PostgreSQL 17  
**Language:** SQL

## Direct answer

Tenant isolation is not enforced inside privileged SECURITY DEFINER APIs: callers control p_tenant_id. Claiming uses FOR UPDATE SKIP LOCKED correctly for concurrent queue consumption, but delivery is at-least-once, so external handlers must be idempotent. Retry delay and several lease/limit inputs are not fully bounded.

## Findings

### Tenant authorization is caller-controlled

**Priority:** high  
**Location:** durable-queue.sql:115  
**Evidence:** `where tenant_id = p_tenant_id`

Each privileged function uses the supplied tenant identifier without comparing it with app.current_tenant_id(). RLS does not automatically constrain SECURITY DEFINER execution when the owner bypasses RLS.

**Recommendation:** Derive tenant identity from trusted session context or require p_tenant_id = app.current_tenant_id(); use a controlled owner with NOBYPASSRLS where feasible. Sources: WEB-3, WEB-4.

### Delivery is not exactly-once

**Priority:** high  
**Location:** durable-queue.sql:179  
**Evidence:** `and lease_expires_at > v_now`

Lease ownership prevents stale completion, but a worker can perform an external side effect and crash before complete_job commits. Database transactions cannot roll back that external effect.

**Recommendation:** Treat delivery as at-least-once and make handlers idempotent using tenant_id plus operation_key. Sources: WEB-5.

### SKIP LOCKED trades fairness for throughput

**Priority:** medium  
**Location:** durable-queue.sql:119  
**Evidence:** `for update skip locked`

Concurrent workers may temporarily skip locked rows; PostgreSQL documents this as suitable for queue-like consumers but not a consistent view.

**Recommendation:** Accept the tradeoff for throughput and monitor starvation, lease expiry, and retry metrics. Sources: WEB-1.

### Retry delay is unbounded

**Priority:** medium  
**Location:** durable-queue.sql:249  
**Evidence:** `available_at = case when attempts >= max_attempts then available_at else v_now + p_retry_delay end`

p_retry_delay is not validated, so callers can schedule arbitrarily long delays. This is an operational bound issue rather than a locking defect.

**Recommendation:** Reject nonpositive or excessive delays and apply a documented backoff/jitter policy. Sources: citation undiscovered.

## Upgrade path

### Close the tenant authorization gap

- Add a trusted tenant check or derive the tenant inside all five privileged functions.
- Review function ownership and BYPASSRLS attributes.
- Add cross-tenant authorization tests. Sources: WEB-3, WEB-4.

### Bound operational inputs

- Validate p_lease in renew_job_lease.
- Validate p_limit in release_expired_jobs.
- Set an explicit maximum for p_retry_delay and test interval edge cases. Sources: WEB-4.

### Operationalize at-least-once delivery

- Require idempotent handlers keyed by tenant_id and operation_key.
- Monitor skipped rows, lease expiry, retries, and dead-letter transitions. Sources: WEB-1, WEB-5.

## Sources

- [WEB-1: PostgreSQL 17 SELECT](https://www.postgresql.org/docs/17/sql-select.html) — postgresql.org
- [WEB-2: PostgreSQL 17 Explicit Locking](https://www.postgresql.org/docs/17/explicit-locking.html) — postgresql.org
- [WEB-3: PostgreSQL 17 Row Security Policies](https://www.postgresql.org/docs/17/ddl-rowsecurity.html) — postgresql.org
- [WEB-4: PostgreSQL 17 CREATE FUNCTION](https://www.postgresql.org/docs/17/sql-createfunction.html) — postgresql.org
- [WEB-5: PostgreSQL 17 PL/pgSQL Transaction Management](https://www.postgresql.org/docs/17/plpgsql-transactions.html) — postgresql.org