PostgreSQL search_path Fallback Applies Only to a Single Pooled Connection, Leaving Other Connections Unconfigured

Summary

When search_path cannot be set via DSN parameters, the fallback path in the PostgreSQL, GaussDB, and Kingbase driver implementations issues a session-level SET search_path = ... against whichever physical connection is acquired at that moment. All other physical connections in the pool remain at the database default search_path, causing subsequent queries routed to those connections to resolve schema objects incorrectly or fail entirely. The practical impact is non-deterministic query behavior: unqualified table or function references may silently resolve to a wrong same-named object in a different schema, or raise relation not found errors depending on which pooled connection services the query.

Root-Cause Analysis

Confirmed evidence (code review):

• internal/db/postgres_impl.go:559–623, internal/db/gaussdb_impl.go:212–261, and internal/db/kingbase_impl.go:184–216 each implement a search_path fallback that executes SET search_path = <schema> at the session level on a single connection.
• internal/db/sql_pool.go:38–57 manages a pool of physical connections. The pool vends connections to callers without guaranteeing that every physical connection has previously executed the session-level SET.

Reasonable inference:

The fallback is triggered when DSN-level search_path configuration fails (e.g., the driver or server version does not support the search_path DSN parameter, or the parameter is rejected). The code falls back to a runtime SET issued against one connection obtained from the pool at initialization time. Because database/sql (and compatible pool implementations) maintain multiple idle connections, subsequent db.Query / db.Exec calls may be dispatched to any physical connection that has never received the SET command.

Root cause:

PostgreSQL search_path is a session-level parameter. A SET search_path issued on connection A has no effect on connection B. Without a connection-initialization hook that runs the SET for every new physical connection the pool opens, the setting is applied to at most one connection — whichever was checked out at the moment the fallback ran.

Alternative or contributing causes:

• Connection churn: even the one correctly configured connection may be closed and replaced by the pool, and the replacement connection will also lack the SET.
• Pool MaxOpenConns = 1 would mask the bug in testing but leave it latent.

Impact classification: P1 — schema object resolution is non-deterministic in production; data written to or read from a wrong same-named object in the default schema is a data-integrity issue.

Resolution Steps

Choose one of the following strategies. Strategy 1 is the safest and most idiomatic for database/sql-based pools.

Strategy 1 — Use a Connection-Initialization Hook (Recommended)

database/sql exposes (*DB).Conn and driver-level hooks. For pgx-based pools, use AfterConnect; for database/sql, wrap the driver with a DriverConnector that executes the SET for every new physical connection.

1. Remove the one-shot SET search_path call from the fallback path in all three impl files.
2. Register an AfterConnect / ConnectConfig hook (pgx) or implement driver.SessionResetter / a custom driver.Connector that issues SET search_path = <schema> immediately after every physical connection is established.
3. Ensure the hook runs before the connection is added to the pool's idle queue.
4. Apply the same pattern to all three drivers (PostgreSQL, GaussDB, Kingbase) to keep behavior consistent.

Strategy 2 — Restrict the Pool to a Single Physical Connection

If the workload permits, set MaxOpenConns(1) and MaxIdleConns(1) so only one physical connection exists. Issue the session-level SET on that connection. This is a stopgap — it eliminates concurrency and does not survive connection loss.

> Do not use Strategy 2 in high-throughput production environments. It eliminates connection-level parallelism and masks the underlying defect.

Strategy 3 — Reject the Fallback Entirely

If search_path cannot be configured via DSN, treat it as a fatal initialization error and refuse to return a usable database handle. This prevents silent wrong-schema access at the cost of failing fast.

1. In the DSN-parameter failure path, return an error rather than falling back to a session-level SET.
2. Document that callers must provide a DSN that supports search_path or use per-query qualified identifiers.

CLI Commands

Identify the current search_path on each physical connection (useful during diagnosis):

-- Run on each backend PID to check effective search_path
SELECT pid, usename, application_name, query, current_setting('search_path') AS search_path
FROM pg_stat_activity
WHERE datname = current_database();

Verify that a new connection inherits the correct search_path without a SET:

# Connect without specifying search_path in session; check default
psql "host=<HOST> port=<PORT> dbname=<DBNAME> user=<USER>" \
  -c "SHOW search_path;"

Check per-role or per-database default search_path (alternative to runtime SET):

# Set search_path permanently for the role — survives all new connections
psql "host=<HOST> port=<PORT> dbname=<DBNAME> user=<SUPERUSER>" \
  -c "ALTER ROLE <APP_ROLE> IN DATABASE <DBNAME> SET search_path = <TARGET_SCHEMA>, public;"

> Note: The ALTER ROLE ... SET approach is the most robust server-side fix and complements Strategy 1. It does not require application-level changes and ensures every connection — regardless of pool behavior — inherits the correct search_path.

Configuration Snippets

pgx v5 — AfterConnect hook (Strategy 1)

config, err := pgxpool.ParseConfig(dsn)
if err != nil {
    return nil, fmt.Errorf("parse dsn: %w", err)
}

targetSchema := "<TARGET_SCHEMA>"

config.AfterConnect = func(ctx context.Context, conn *pgx.Conn) error {
    _, err := conn.Exec(ctx,
        fmt.Sprintf("SET search_path = %s, public", pgx.Identifier{targetSchema}.Sanitize()),
    )
    if err != nil {
        return fmt.Errorf("set search_path on new connection: %w", err)
    }
    return nil
}

pool, err := pgxpool.NewWithConfig(ctx, config)

database/sql — Custom driver.Connector wrapper (Strategy 1)

type searchPathConnector struct {
    driver.Connector
    schema string
}

func (c *searchPathConnector) Connect(ctx context.Context) (driver.Conn, error) {
    conn, err := c.Connector.Connect(ctx)
    if err != nil {
        return nil, err
    }
    // Issue SET search_path before the connection is pooled
    stmt, err := conn.Prepare(fmt.Sprintf("SET search_path = %q, public", c.schema))
    if err != nil {
        conn.Close()
        return nil, fmt.Errorf("prepare search_path on connect: %w", err)
    }
    defer stmt.Close()
    if _, err = stmt.(driver.StmtExecContext).ExecContext(ctx, nil); err != nil {
        conn.Close()
        return nil, fmt.Errorf("exec search_path on connect: %w", err)
    }
    return conn, nil
}

// Usage
db := sql.OpenDB(&searchPathConnector{Connector: baseConnector, schema: "<TARGET_SCHEMA>"})

PostgreSQL server-side default (complements any strategy)

-- Persistent; survives connection replacement and pool churn
ALTER ROLE <APP_ROLE> IN DATABASE <DBNAME>
    SET search_path = <TARGET_SCHEMA>, public;

Verification

Functional verification with a fake two-connection pool

The acceptance criteria from the issue specifies a fake pool with two physical connections that forces a DSN-parameter failure:

1. Open the pool with MaxOpenConns(2), MinConns(2) (or equivalent for the pool library) to pre-warm both connections.
2. Trigger the DSN-parameter failure path (inject a DSN without search_path support).
3. Create two same-named tables in different schemas (<TARGET_SCHEMA>.foo and public.foo with different columns).
4. Execute SELECT * FROM foo on both connections concurrently.
5. Expected: both queries return rows from <TARGET_SCHEMA>.foo. Any result from public.foo is a regression.

Live verification commands

# Confirm search_path on all active backends after pool warm-up
psql "host=<HOST> port=<PORT> dbname=<DBNAME> user=<USER>" -c \
  "SELECT pid, current_setting('search_path') AS search_path FROM pg_stat_activity WHERE datname = current_database();"

Healthy output: every row shows <TARGET_SCHEMA>, public (or your configured value). Regression indicator: any row showing "$user", public (PostgreSQL default) or a different schema.

# Verify the AfterConnect hook fires on connection replacement
# Force connection replacement by killing a backend and re-querying
psql "host=<HOST> port=<PORT> dbname=<DBNAME> user=<SUPERUSER>" -c \
  "SELECT pg_terminate_backend(<PID_OF_APP_BACKEND>);"
# Re-run the live verification command above after the pool reconnects

Rollback indicator

If after applying the fix any query returns ERROR: relation "<table>" does not exist that previously succeeded, the schema identifier in the hook may be incorrect. Revert the AfterConnect hook registration and fall back to the ALTER ROLE ... SET search_path server-side fix while investigating.

Prevention

1. Server-side default as the primary safeguard: Always configure ALTER ROLE <APP_ROLE> IN DATABASE <DBNAME> SET search_path = ... in database provisioning scripts (Terraform, Flyway, Liquibase). This ensures every connection — regardless of driver or pool behavior — inherits the correct search_path without application-level intervention.

1. Connection-initialization hook as defense-in-depth: Enforce the AfterConnect / Connector pattern across all three drivers (PostgreSQL, GaussDB, Kingbase) and add a unit test using a fake pool that verifies the SET is executed once per physical connection, not once per pool initialization.

1. Remove the one-shot fallback pattern: Statically audit all three impl files to eliminate the pattern "acquire one connection, SET, return pool." Introduce a linter rule or code-review checklist item: session-level SET on a single connection is always wrong in a pooled context.

1. Integration test with MaxOpenConns > 1: Add a CI test that opens MaxOpenConns(5), pre-warms all connections, then asserts current_setting('search_path') on each backend PID visible in pg_stat_activity. Fail the test if any connection has the wrong value.

1. Alerting: Emit a structured log line (e.g., search_path_set_on_connect=true connection_id=<ID>) from the initialization hook. Alert if the ratio of connections lacking this log line exceeds zero in a time window — indicating the hook did not fire on a new physical connection.

1. Schema-qualify critical identifiers: As an additional layer, ensure that DDL and high-risk DML in application code fully qualifies table names (<schema>.<table>). This does not replace correct search_path management but bounds the blast radius of any future misconfiguration.