People can make out-of-band updates to the query, like making it more efficient or migrating it, without requiring any changes to the all.
It is quite possible to test stored procedures, it's just requires some additional work/infrastructure.
That’s not a rag on stored procedures more generally. Just that specific use case scales poorly to a large number of operations as its inherently cpu bound.
A simple sha256(lower(email)) is equally secure as a complete random salt, the only requirement on a salt is to be unique.
It's not as simple, you still have to make sure public backend user can't access web logs, that may reveal session id of an backoffice admin account or other information useful for breaking in other parts of the website.
Eventually, the attacker will gain access, but this is useful to slow him down enough, so there's a chance he'll give up, or you notice something suspicious.
For example pgbouncer allows you to connect via multiple roles/credentials to a database. It's just about how you configure your pool.