Both connection pooling and using the superuser with SET SESSION
AUTHORIZATION both have their uses. You might have an application
that processes some type of transaction and inserts data into a users
schema or table, but where there are no user credentials available.
Then you might have a web interface for users to access that data
where user credentials are available. We have this type of setup and
we use a superuser with SET SESSION AUTHORIZATION for the incoming
transactions. But we also have extensive security requirements that
demand we do things most people don't do. Full security/code audits
every quarter, peer review and full testing for any new code,
hardware encryption for sensitive data and keys stored on tokens,
client certificate authentication for all web access, restrictive
firewall, etc..
Bottom line is that I'm paranoid about using SET SESSION
AUTHORIZATION, but it does have it's uses and can be used safely.