> 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.
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.