pgpass doesn't work with builder methods

[YgorSouza]

Bug Description

PgConnectionOptions tries to get the password from the pgpass file when the struct is created, but the caller can then use the builder API to change options that affect which password is taken from the file, so we can end up with the wrong password.

sqlx/sqlx-postgres/src/options/mod.rs

Lines 136 to 138 in 7e7dded

	pub fn new() -> Self {
	Self::new_without_pgpass().apply_pgpass()
	}

sqlx/sqlx-postgres/src/options/mod.rs

Lines 174 to 185 in 7e7dded

	pub(crate) fn apply_pgpass(mut self) -> Self {
	if self.password.is_none() {
	self.password = pgpass::load_password(
	&self.host,
	self.port,
	&self.username,
	self.database.as_deref(),
	);
	}
	self
	}

For example, we can select a different host here and it will not read the pgpass file again (and even if it did, it might already have gotten the wrong password and would not change it):

sqlx/sqlx-postgres/src/options/mod.rs

Lines 203 to 206 in 7e7dded

	pub fn host(mut self, host: &str) -> Self {
	self.host = host.to_owned();
	self
	}

Info

• SQLx version: 0.7.1
• SQLx features enabled: postgres
• Database server and version: Postgres

[YgorSouza]

I think the pgpass password should be fetched when connecting, not when creating the options. But the connect() method takes an immutable reference to the options, so I am not sure of where this call could be made.

The password is only used in these 3 match cases:

sqlx/sqlx-postgres/src/connection/establish.rs

Lines 73 to 101 in 7e7dded

	Authentication::CleartextPassword => {
	// The frontend must now send a [PasswordMessage] containing the
	// password in clear-text form.
	stream
	.send(Password::Cleartext(
	options.password.as_deref().unwrap_or_default(),
	))
	.await?;
	}
	Authentication::Md5Password(body) => {
	// The frontend must now send a [PasswordMessage] containing the
	// password (with user name) encrypted via MD5, then encrypted again
	// using the 4-byte random salt specified in the
	// [AuthenticationMD5Password] message.
	stream
	.send(Password::Md5 {
	username: &options.username,
	password: options.password.as_deref().unwrap_or_default(),
	salt: body.salt,
	})
	.await?;
	}
	Authentication::Sasl(body) => {
	sasl::authenticate(&mut stream, options, body).await?;
	}

So I suppose each of them could have a call to the method that reads the pgpass file if the pgpass option is enabled and the password is empty. And I guess the Pool would have to be able to call a method to fill this password only once, so the file isn't read again every time a new connection is made.

Another possibility would be to make the apply_pgpass() method public and add a clear_password() method to the options, so the user could call it manually when using the builder methods.

Let me know whether a pull request for either of these options would be accepted, or if there are any better alternatives.

[demhadais]

Running into this issue myself, and it took me awhile to figure out why. I would be a big fan of exposing the apply_pgpass method publicly!

Just leaving this comment so as to indicate interest in the issue.