PgBouncer cannot query pg_shadow in Cloud SQL to authenticate client

[d3vesh-bo]

We intend to use the Postgres database of Cloud SQL for our application and connect it using cloud-sql-proxy. After reading documentation and other related articles (github issue: #88 ), we decided to run a single instance of pgbouncer and cloudsql-proxy, as a side-car, which proxies and pools connections to cloudsql postgres database. Our Configuration: App/service -> (PgBouncer + cloudsql-proxy) -> cloud sql database

Pgbouncer Authentication: We want to authenticate the client by querying the database (auth_user and auth_query) using Postgres SECURITY DEFINER function which basically queries pg_shadow view Example function:

CREATE OR REPLACE FUNCTION public.user_lookup(INOUT p_user     name, OUT   p_password text)
RETURNS record AS $$
BEGIN
SELECT usename, passwd FROM pg_catalog.pg_shadow 
WHERE usename = p_user;
RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER

But as pg_shadow can only be read by the superuser and no user in cloud SQL is granted complete superuser privileges (not even postgres user). So no customer-created user/postgres user has access to read pg_shadow. This makes it impossible to setup pgbouncer with the SECURITY DEFINER function (preferred method in pgbouncer).

We've read through many articles where they have suggested using pgbouncer but have not acknowledged the above problem.

How can pgbouncer authenticate the client by querying the pg_shadow to pool connections for Cloud SQL (Postgres)?

Could you please help us?

[enocom]

I saw this same question on Stack Overflow. I'll respond here if that's alright.

To summarize, if you define auth_query and auth_user for the pgbouncer config, you'll need access to pg_shadow, which requires admin rights. By default, any user you create in a Cloud SQL postgres instance won't have access. This is a known limitation that the Cloud SQL team is aware of, by the way.

Before we get into pgbouncer specifies, let me briefly add that we recommend using client-side connection pooling first in addition to sizing the pool correctly. Often times, this will eliminate the need for running pgbouncer. I'll assume you've already been through that exercise and have determined that pgbouncer is necessary.

Let's go over the connection path:

Application -> pgbouncer + Cloud SQL Auth Proxy -> Cloud SQL instance

So the way we recommend setting up pgbouncer is to use one deployment of it per application. This avoids a single point of failure, such that if another application begins to misbehave, it won't affect any other deployed applications. From there, you might find it more convenient to use Automatic IAM AuthN with the Proxy. This way, you don't have to be keeping database passwords within pgbouncer and the Cloud SQL Auth Proxy will use whatever IAM Principal you have configured.

From there, you'll want to ensure the connection between your application and pgbouncer is encrypted. You can either provision certificates to use with pgbouncer, or rely on a service mesh if you have one.

Lastly, let's talk about passwords. Since you'll want to be certain that your application can connect to only the associated pgbouncer instance, you should configure a password for your applications using md5 auth or similar. This way you'll be able to benefit from encryption of the password, and won't have to manage a bunch of database passwords.

[d3vesh-bo]

Thank you for your prompt reply.

From what I understood

• user1 and pswd1 is the credential of user that will be used by the app to connect to database
• auth_file: users_list.txt using md5 auth "user1" "md5 password for pswd1"
• cloud proxy: proxying connection for IAM principal (eg. service account) through automatic IAM authentication.

Additional info:

pgbouncer.ini

  [databases]
  cloud_sql_proxy = host=127.0.0.1 port=5431 dbname=postgres

  [pgbouncer]
  auth_type = md5
  auth_file = /etc/pgbouncer/userlist.txt

Please correct me if I am wrong!

[enocom]

Yep, that's the idea. Thanks for the nice diagram.

I'm going to close this (but we'll always have it for future reference).

[mgreenw]

@enocom This feature would be extremely helpful to have integrated directly Google Cloud SQL directly so there are no additional hoops to jump through, and I suspect that that many firms would pay extra to use it. Are there plans to integrate pgbouncer or similar directly with Google Cloud SQL? Thanks!

[enocom]

Completely agree on this being a useful feature for Cloud SQL to add.

I can't comment on the upcoming Cloud SQL roadmap, but will update this issue if the pg_shadow support story changes. Meanwhile, I'll make sure the product team sees the interest in this.

[enocom]

This is now a supported flag. :wink:

https://cloud.google.com/sql/docs/postgres/users#pg_shadow

[Shahor]

@enocom Thanks for that update!

Do you know if supporting pgBouncer directly from Google Cloud SQL (ala Crunchy bridge) is something in the roadmap? That would surely make this whole thing much easier for customers.

[enocom]

It's a question many people are wondering about. And unfortunately, I can't comment on the upcoming roadmap. :smile:

[Shahor]

What's your threshold for considering customer demands and how many demands did you receive 😬 ? (Technically not commenting on the upcoming roadmap 😬)

[enocom]

Off-loading the burden of running pgbouncer to Cloud SQL would be a big win, I agree, but I don't own the roadmap and so can only speak to what's already publicly known. :-)

[rushilsrivastava]

We run PGBouncer in a similar setup, but would really love to have it managed directly through Google Cloud SQL. Is there a feature request already open where we can track this?

[enocom]

Watching the Release Notes would be best.

[rushilsrivastava]

Watching the Release Notes would be best.

Don't think this was what I was looking for, but I found it myself and have created a new feature request here.

[AnatoliiPerfun]

Application -> pgbouncer + Cloud SQL Auth Proxy -> Cloud SQL instance is not a good idea... don't waste your time on PgBouncer for connection pooling with Cloud SQL in GCP. The docs (https://cloud.google.com/sql/docs/mysql/manage-connections) don't mention it for a reason - it's not supported! Cloud SQL Auth Proxy already handles secure connections and basic pooling. Keep it simple and manage pooling in your code.

[enocom]

FYI https://cloud.google.com/sql/docs/postgres/users#pg_shadow

pgbouncer + Cloud SQL Proxy does work, but it does require you to deploy and run pgbouncer yourself. We have an example of how to do that here: https://github.com/GoogleCloudPlatform/cloud-sql-proxy/tree/main/examples/k8s-service.

[AnatoliiPerfun]

Yes, you are right, but I assumed the original requirement was to set up pgbouncer + cloud-auth-proxy on a VM instance and make it work with Cloud SQL. On Kubernetes, it can surely run.