postgres_cdc: heartbeat connection never refreshes IAM auth token, fails every tick after ~15min

Summary

The postgres_cdc input's heartbeat mechanism opens its own connection with a one-time snapshot of the IAM auth token, and never refreshes it. Once the token expires (15 min, per AWS), every subsequent heartbeat write fails with a PAM authentication error — indefinitely, on every tick, for the life of the pipeline.

Root cause

newHeartbeat() in internal/impl/postgresql/pglogicalstream/heartbeat.go calls openPgConnectionFromConfig(config):

func openPgConnectionFromConfig(cfg *Config) (*sql.DB, error) {
	parsedCfg, err := pgxpool.ParseConfig(cfg.DBRawDSN)
	if err != nil {
		return nil, err
	}
	parsedCfg.ConnConfig.Password = cfg.DBConfig.Password
	parsedCfg.ConnConfig.TLSConfig = cfg.TLSConfig
	return stdlib.OpenDB(*parsedCfg.ConnConfig), nil
}

parsedCfg.ConnConfig.Password = cfg.DBConfig.Password copies whatever token is in cfg.DBConfig.Password at the moment newHeartbeat() is called (pipeline startup) into a brand-new, independent pgconn.Config. This is a value copy, not a live reference to the shared config. The resulting *sql.DB backs every future periodic heartbeat ExecContext call (heartbeat.go's run() method), for the entire lifetime of the pipeline, using that frozen token.

Compare this to the main replication connection's tokenBuilder in internal/impl/postgresql/aws/aws.go:

// tokenBuilder will be called upon component connection to refresh token/password and reconnect.
// Tokens last ~15 minutes and will only need refreshing after a connection is lost.
tokenBuilder := func(ctx context.Context) error {
	...
	password, err := auth.BuildAuthToken(ctx, endpoint, cfg.Region, dbConf.User, cfg.Credentials)
	...
	dbConf.Password = password
	...
}

This is explicitly re-invoked on every reconnect of the main connection, generating a fresh token each time. The heartbeat's connection has no equivalent refresh path — it never calls tokenBuilder, and its *sql.DB has no mechanism to regenerate or re-fetch a token when its underlying physical connection needs to reopen.

Impact

For any postgres_cdc pipeline using AWS IAM authentication (aws.enabled: true) with a heartbeat_interval longer than the IAM token lifetime (15 min — so effectively any non-trivial interval), every heartbeat write after the first ~15 minutes fails:

level=warning msg="unable to write heartbeat message: failed to connect to `user=... database=...`: ...: server error: FATAL: PAM authentication failed for user \"...\" (SQLSTATE 28000)"

This recurs on every tick, indefinitely, even while the main replication stream stays healthy. We reproduced this reliably: heartbeat failure at pipeline startup + 1h, again at +2h, etc., on a fresh pipeline with a 1h heartbeat_interval, with the main connection fully healthy throughout.

We don't yet know if this is purely cosmetic (heartbeats are best-effort keep-alives for low-traffic tables per the docs) or could eventually affect replication slot liveness / standby_timeout handling if it prevents the slot from being acknowledged forward — but it's a clear, 100%-reproducible bug independent of that question.

Suggested fix direction

newHeartbeat's connection needs to participate in the same token-refresh mechanism as the main connection — either by sharing/calling the same tokenBuilder before each ExecContext, or before opening a new physical connection when the pool needs one, rather than baking in a value snapshot once at construction time.

Environment

• Redpanda Connect (benthos) 4.98.0
• postgres_cdc input, aws.enabled: true, IAM auth via assumed cross-account role
• Amazon RDS for PostgreSQL 12.22
• heartbeat_interval set to a value well beyond the 15-minute token lifetime

Start in internal/impl/postgresql/pglogicalstream/heartbeat.go, tracing newHeartbeat, openPgConnectionFromConfig, and run; then compare the heartbeat connection with tokenBuilder in internal/impl/postgresql/aws/aws.go. Determine how the heartbeat connection can refresh IAM credentials, and verify that periodic writes continue succeeding after the 15-minute token lifetime.