postgres_fdw: use_scram_passthrough on user mapping is ignored when also set on server

First seen: 2026-05-14 17:57:18+00:00 · Messages: 6 · Participants: 2

Latest Update

2026-05-20 · claude-opus-4-6

Incremental Analysis: Review Discussion on Backpatching and Test Placement

Summary of New Activity

Fujii Masao reviewed the patch set and provided feedback on two key areas: (1) test placement for the dblink FDW-level validation fix, and (2) backpatching strategy for v18. Matheus Alcantara responded with a revised patch version addressing the test feedback.

Key Technical Discussions

Test Placement (Patch 0003)

Fujii argued that adding a TAP test for the ALTER FOREIGN DATA WRAPPER ... (use_scram_passthrough ...) validation is overkill, since neither dblink nor postgres_fdw currently has tests verifying option-level restrictions. He suggested that if such a test is added, it belongs in sql/dblink.sql (a standard SQL regression test) rather than as a TAP test. Matheus agreed and moved the test to sql/dblink.sql in a new patch version. An open question remains: whether a similar test should be added for postgres_fdw.

Backpatching to v18

Fujii raised the backpatching question explicitly: since v18 shipped with the current (buggy) behavior and no documentation stating user-mapping should take precedence, changing the behavior in a minor release could affect users relying on the current semantics. Despite this concern, Fujii expressed a preference for backpatching to v18, reasoning that having only v18 behave differently from later versions "seems odd and potentially confusing."

Matheus supported backpatching by citing existing precedent in the postgres_fdw documentation for sslkey and sslcert, which explicitly states: "If both are present, the user mapping setting overrides the connection setting." This establishes an existing documented pattern that use_scram_passthrough should follow.

Patch 0003 Backpatching

Both participants agreed that the FDW-level validation fix (patch 0003) is safe to backpatch to v18 regardless, since use_scram_passthrough set at the FDW level never had any effect — so even if users set it there, it wasn't working, making the restriction a clarification rather than a behavior change.

History (1 prior analysis)
2026-05-18 · claude-opus-4-6

Technical Analysis: SCRAM Passthrough Option Precedence in postgres_fdw and dblink

Core Problem

PostgreSQL's foreign data wrapper infrastructure (postgres_fdw and dblink) supports the use_scram_passthrough option, which enables SCRAM authentication credential forwarding when connecting to remote servers. This option can be specified at two levels:

  1. Foreign server level — applies as a shared default for all connections through that server
  2. User mapping level — intended to provide per-user overrides

The discovered bug is that when use_scram_passthrough is specified at both levels, the foreign server setting silently overrides the user mapping setting. This violates the fundamental PostgreSQL FDW design principle where user mapping options serve as per-user overrides of server-level defaults.

Architectural Context

In PostgreSQL's FDW architecture, the option hierarchy is designed to flow from general to specific:

Foreign Data Wrapper → Foreign Server → User Mapping

Each more-specific level should be able to override options set at a less-specific level. This allows DBAs to set organization-wide defaults at the server level while granting individual users the ability to have customized connection behavior. The use_scram_passthrough option not respecting this hierarchy means administrators cannot selectively enable or disable SCRAM passthrough for individual users — a significant security configuration limitation.

The SCRAM Passthrough Feature

SCRAM passthrough (introduced in commit 3642df265d0) allows a PostgreSQL server acting as an FDW client to forward the client's SCRAM credentials to the remote server, avoiding the need to store plaintext passwords in user mappings. The option controls whether this credential forwarding is active. Having incorrect precedence means:

  • You cannot disable passthrough for a specific user if it's enabled server-wide
  • You cannot enable passthrough for a specific user if it's disabled server-wide

Both scenarios represent real security administration needs.

Proposed Solution

The fix is straightforward in the UseScramPassthrough() function in both postgres_fdw/connection.c and dblink.c: when resolving the option value, check the user mapping options first (or last, depending on implementation — the key is that user mapping wins). If the option is found in the user mapping, use that value; otherwise fall back to the foreign server setting.

Patch Set Structure

  • 0001: Fix option precedence in postgres_fdw — user mapping takes precedence over foreign server
  • 0002: Fix option precedence in dblink — same fix for dblink's implementation
  • 0003: Separate bug fix — disallow use_scram_passthrough at the FOREIGN DATA WRAPPER level in dblink

The dblink FDW-Level Bug (0003)

A secondary bug was discovered during review: dblink_fdw_validator() was modified in commit 3642df265d0 to call is_valid_dblink_fdw_option() to support CREATE SERVER ... OPTIONS (use_scram_passthrough '...'). However, this validator is also invoked for ALTER FOREIGN DATA WRAPPER contexts, inadvertently allowing the option to be set at the FDW level where it has no effect (the code never reads it from that level). The fix restricts the FDW-specific option check to only fire when the validator context indicates a foreign server or user mapping operation.

Design Decisions and Tradeoffs

  1. Backport consideration: The precedence fix (0001/0002) could be viewed as a behavior change rather than a pure bugfix, making backporting debatable. The FDW-level validation fix (0003) is a cleaner bugfix candidate for backporting.

  2. Comment documentation: Adding explicit comments in the UseScramPassthrough() function body documenting the precedence rule makes the intentional behavior clear for future maintainers.

  3. Consistency across FDW implementations: Both postgres_fdw and dblink implement SCRAM passthrough, and both need the same fix, reinforcing that this is a design principle violation rather than an implementation quirk.