cloud-sql-proxy: PgBouncer cannot query pg_shadow in Cloud SQL to authenticate client

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?

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Comments: 17 (8 by maintainers)

Most upvoted comments

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.

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. 😃

Thank you for your prompt reply.

From what I understood

Pgbouncer-CloudSQL configuration

  • 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!

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.

@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!

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.

FYI: The README recommends using pgbouncer.

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.

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.

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.