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)
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
"user1" "md5 password for pswd1"Additional info:
pgbouncer.ini
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_queryandauth_userfor the pgbouncer config, you’ll need access topg_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:
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!
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.
Don’t think this was what I was looking for, but I found it myself and have created a new feature request here.