Why PostgreSQL postgres user's password is not needed with the Cloud SQL Auth Proxy

Question

When connecting directly to a PostgreSQL Cloud SQL instance the password is required:

> psql --host=12.34.56.78 --dbname=mydb --username=postgres
Password for user postgres:
psql (17.2)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: PostgreSQL)
Type "help" for help.

mydb=>

When connecting with Cloud SQL Studio from the Console it is also required.

But when connecting through the Cloud SQL Auth Proxy it is not asked:

> psql --dbname=mydb --username=postgres
psql (17.2)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

mydb=>

Why is that so?

EDIT: as requested, here are the commands:

> gcloud sql instances create --database-version POSTGRES_17 --tier db-perf-optimized-N-2 test-sql
WARNING: Starting with release 233.0.0, you will need to specify either a region or a zone to create an instance.
Creating Cloud SQL instance for POSTGRES_17...done.
Created [https://sqladmin.googleapis.com/sql/v1beta4/projects/test-gcp-123456/instances/test-sql].
NAME      DATABASE_VERSION  LOCATION       TIER                   PRIMARY_ADDRESS  PRIVATE_ADDRESS  STATUS
test-sql  POSTGRES_17       us-central1-c  db-perf-optimized-N-2  12.345.678.910   -                RUNNABLE
> gcloud sql databases create test-db --instance test-sql
Creating Cloud SQL database...done.
Created database [test-db].
instance: test-sql
name: test-db
project: test-gcp-123456
> gcloud sql users set-password postgres --instance test-sql --password postgres
Updating Cloud SQL user...done.
> gcloud sql instances describe test-sql --format='get(connectionName)'
test-gcp-123456:us-central1:test-sql
> C:\Users\orbsv\Downloads\cloud-sql-proxy.x64.exe test-gcp-123456:us-central1:test-sql
2025/01/22 20:37:59 Authorizing with Application Default Credentials
2025/01/22 20:38:01 [test-gcp-123456:us-central1:test-sql] Listening on 127.0.0.1:5432
2025/01/22 20:38:01 The proxy has started successfully and is ready for new connections!

But I was not able to reproduce from another machine, I thought some environment variable like PGPASSWORD might provide the password implicitly but I don't see it defined.
So I wonder where it could be "cached".

As per the Documentation, Cloud SQL Auth proxy provides the access to the user to connect the Cloud PostgreSQL instance without a password. However, be informed that anyone with access to that interface/port will be authorized to connect to your instance. — Sandeep Vokkareni
– Sandeep Vokkareni, Commented Jan 17 at 6:06
Note: Cloud SQL Auth proxy is used as a Cloud SQL connector that provides secure access to your instances without a need for Authorized networks or for configuring SSL. You can refer to Documentation to know how Cloud SQL Auth Proxy works. — Sandeep Vokkareni
– Sandeep Vokkareni, Commented Jan 17 at 6:06
Thanks for your comment. I'm missing some point, please check my comment under Jack's answer. — Pragmateek
– Pragmateek, Commented Jan 17 at 16:56

Jack Wotherspoon · Accepted Answer · 2025-01-17 14:50:01Z

1

What you are describing is one of the benefits of the Cloud SQL Auth Proxy which is Automatic IAM Database Authentication.

The Cloud SQL Auth Proxy and other Cloud SQL Connectors have the following benefits:

Secure connections: The Cloud SQL Auth Proxy automatically encrypts traffic to and from the database using TLS 1.3 with the cipher selection determined by Go's rules. SSL certificates are used to verify client and server identities, and are independent of database protocols; you won't need to manage SSL certificates.

Easier connection authorization: The Cloud SQL Auth Proxy uses IAM permissions to control who and what can connect to your Cloud SQL instances. Thus, the Cloud SQL Auth Proxy handles authentication with Cloud SQL, removing the need to provide static IP addresses.

IAM database authentication: Optionally, the Cloud SQL Auth Proxy supports an automatic refresh of OAuth 2.0 access tokens. For information about this functionality, see Cloud SQL IAM database authentication.

Essentially with Automatic IAM Database Authentication, the Cloud SQL Proxy behind the scenes fetches an OAuth2 token for the IAM Principal the Proxy is running as and uses it as the database password, without you the customer needing to explicitly pass it.

You can always disable IAM database authentication and use regular built-in user/password authentication by omitting the --auto-iam-authn flag when starting the Proxy. Then you will be prompted for a password like the other example you showcased.

answered Jan 17 at 14:50

Jack Wotherspoon

2,0591 gold badge5 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Pragmateek Jan 17 at 16:45

Thanks for your answer. But I thought the IAM authentication was acting at the GCP infrastructure level, simpler than configuring an authorized network. AFAIK the database has its own authentication mechanism, that is applied independently of the hosting mechanism. Moreover, why is it not used when connecting from the Web Console where we are IAM authenticated? And I'm not using the same user at the database level: "postgres" is not my IAM user. I must be missing something.

Jack Wotherspoon Jan 21 at 21:13

You should require a password for the postgres user, are you saying the postgres user is not requiring a password for you?

Pragmateek Jan 21 at 21:23

I have setup a password for the postgres user, and as expected it is requested by Cloud SQL Studio, and also by the psql client when using an Authorized Network, but not when using an Auth Proxy which is surprising.

Jack Wotherspoon Jan 22 at 14:59

That is surprising, can you update the description of the question with the command you are using to start the Auth Proxy with?

Pragmateek Jan 22 at 21:10

I've added all the commands from instance creation to proxy execution. But I was not able to reproduce on another machine, whereas on the first one still the same behavior, even after dropping and recreating the Cloud SQL Instance.

|

Collectives™ on Stack Overflow

Why PostgreSQL postgres user's password is not needed with the Cloud SQL Auth Proxy

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related