PostgreSQL 12 High Availability Cookbook - Third Edition

How it works...

The first thing we need to do is create a secure method for PgBouncer to check passwords. PgBouncer knows how PostgreSQL stores passwords, so it can compare any value from a client authentication request with the encrypted information in the database. We merely need a way to retrieve it.

The pgbouncer.get_auth() function does this by retrieving a requested user and encrypted password from where PostgreSQL itself stores it. Normally it's not possible for regular users to view the pg_shadow table, but since we created the function as SECURITY DEFINER, it will execute as the user that created it. That means the pgbouncer user has indirect access to a secure resource since we ran this SQL as the postgres user.

We also secure this ...