sslmode from --db-url is dropped when the connection config is round-tripped through ToPostgresURL()

[evanvolgas]

Describe the bug

--db-url values are parsed correctly by flags.ParseDatabaseConfig (pgconn.ParseConfig resolves sslmode into the TLS config), but the resulting pgconn.Config is then re-serialized back into a URL to make the actual connection:

// internal/utils/connect.go
func ConnectByConfigStream(ctx context.Context, config pgconn.Config, w io.Writer, options ...func(*pgx.ConnConfig)) (*pgx.Conn, error) {
	...
	return ConnectByUrl(ctx, ToPostgresURL(config), opts...)
}

ToPostgresURL() serializes only user, host, port, database, connect_timeout, and RuntimeParams. sslmode is not a runtime param (pgconn consumes it into TLSConfig/Fallbacks at parse time), so it is silently dropped in the round-trip, and the rebuilt URL is re-parsed with default sslmode semantics (prefer + env fallback). Verified unchanged at v2.101.0 and v2.109.1 (toPostgresURL after the refactor).

Impact

• A user's explicit --db-url "...?sslmode=require" does not actually mandate TLS on the final connection. ConnectByUrl's fallback-stripping ("No fallback from TLS to unsecure connection") only helps when the re-parse happens to produce a TLS config; the user's stated requirement is lost.
• Combined with --debug silently disables TLS on database connections (SetupPGX sets TLSConfig = nil), overriding sslmode=require and PGSSLMODE #5872 (--debug nulls TLSConfig), there is no URL-level way to guarantee TLS at all.
• Workaround we're using in CI: set PGSSLMODE=require in the environment — env vars are consulted by the re-parse since the rebuilt URL lacks sslmode, so the mandate survives the round-trip. This works but is non-obvious; users reasonably expect the URL parameter to be authoritative.

To Reproduce

Observe that PGSSLMODE=disable supabase db push --db-url "postgresql://...:5432/postgres" --dry-run against an SSL-enforcing (hostssl-only) Supabase project fails with no pg_hba.conf entry ... no encryption even though nothing in the URL requested plaintext, while PGSSLMODE=require with the identical URL connects over TLS — demonstrating the final connection takes its sslmode from the environment, not from --db-url.

Expected behavior

ToPostgresURL() (or the config plumbing around it) should preserve the parsed TLS requirement — e.g., serialize an explicit sslmode derived from the parsed config, or pass the pgconn.Config through to the connector without a lossy string round-trip.

Version

2.101.0 and 2.109.1.