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.