Exfiltration of password databases, for offline hash cracking, is a well known attack.

Guarding against this by using a pepper is actually quite a good mitigation. It protects in situations where the attacker has obtained the password DB, but has not achieved access to the application secret (the pepper). Breaches of DB are quite a common attack.

I prefer the approach where they use the pepper to symmetrically encrypt & decrypt the hash, which potentially allows the pepper to be rotated.

Operationally in case of pwd DB being exfiltrated, it's pretty necessary to do something regardless of a strong hash algorithm being used. Pepper, if you can strongly establish it hasn't been breached, can give you a potential option short of resetting everyone's passwords.

> I prefer the approach where they use the pepper to symmetrically encrypt & decrypt the hash, which potentially allows the pepper to be rotated.

Isn't that field level encryption rather than pepper?

It does functionally result in not being able to extract the entirety of the password from the database though (think SQLI SELECT back), correct me if I'm wrong

That depends, you can symmetrically encrypt and decrypt in postgres using functions.

For user input like this, you could pass it in as in array that you unnest in a sub-query, and you can then join to that for your pepper for each user ID/password combination which can be passed into the decrypt function.

Brute-forcing the pepper is computationally infeasible if it was generated with enough entropy. This means the passwords are safe unless both the DB and the pepper is leaked. Even if the user chooses the passowrd "123", the attacker cannot see this with purely the DB. A decent hashing algorithm doesn't help in that way.

Argon protects only against naive brute force attack, not against dictionary attack or known passwords.

But isn't brute forcing a long pepper very expensive?