We are new to Google Cloud SQL and have been trying to integrate pgbouncer with Google Cloud SQl Postgres and authenticate database users with SECURITY DEFINER function (which queries pg_shadow)

Our Configuration:

• Server -> Pgbouncer + Cloud sql proxy (side car) -> Cloud SQL Postgres

Problem:

• But as cloud sql postgres actually does not allow to read pg_shadow from a privileged user (i.e postgres user is not a superuser). This makes it impossible to setup pgbouncer with SECURITY DEFINER function.
• Cloud SQL doesn't provide customers to use superuser (cloudsqladmin)

We've read through many articles (mostly cloud-proxy issues) where they have suggested to use pgbouncer but have not elaborated on the above problem.

Options not applicable:

• Application level pooling (not feasible right now for us)
• Authenticating using auth_file eg. users_list.txt (not recomended, needs manual management of database users)

What we are looking for:

• We intend to run a single instance of cloudsql-proxy and pgbouncer which proxies and pools connections to cloudsql postgres database.

We would appreciate your help guys!