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!