PostgreSQL 12 High Availability Cookbook - Third Edition

There's more...

It may be desirable to make our function even more secure. Perhaps we want to avoid allowing superuser access through PgBouncer. Maybe we want to be even more draconian and only allow users assigned to a certain group. We can accomplish both of these by modifying the verification query to something like this:

SELECT u.rolname::TEXT, u.rolpassword::TEXT 
  FROM pg_authid g 
  JOIN pg_auth_members m ON (m.roleid = g.oid) 
  JOIN pg_authid u ON (u.oid = m.member) 
 WHERE NOT u.rolsuper 
   AND g.rolname = 'use_proxy' 
   AND u.rolname = p_usename; 

Not only do we explicitly disallow superusers from connecting through the proxy, but now users need a supplementary GRANT statement before they're allowed to connect. If we want a new user recent_hire ...