Skip to content

Instantly share code, notes, and snippets.

@x-yuri

x-yuri/Overview of Cloud SQL (PostgreSQL) authentication.md

Last active March 20, 2024 14:23
Show Gist options
  • Save x-yuri/d2a257ecd151d8efef32193f46db398e to your computer and use it in GitHub Desktop.
Save x-yuri/d2a257ecd151d8efef32193f46db398e to your computer and use it in GitHub Desktop.
Download ZIP
Overview of Cloud SQL (PostgreSQL) authentication
Raw
Overview of Cloud SQL (PostgreSQL) authentication.md

Overview of Cloud SQL (PostgreSQL) authentication

There are 2 ways to connect to a Cloud SQL database: directly, or via a connector. 2 types of connectors are supported: standalone (Cloud SQL Auth Proxy), and in-process (Cloud SQL Language Connector, available for Java, Python, Go, and Node.js).

There are also 2 ways to authenticate to a database:

  • The PostgreSQL's built-in authentication (password).
  • IAM database authentication. In this case you need an IAM service account and a matching PostgreSQL user.

But before you can do that, you need to give yourself access to the database (authorize the connection):

  • If you're connecting directly, you need to add the client's IP to authorized networks. In this case you also most likely want to make use of SSL/TLS.

  • If you're connecting via a connector, you need to provide it with a service account that has the cloudsql.instances.connect permission. One way to do that is to grant the Cloud SQL Client role (roles/cloudsql.client) to the service account. In this case you don't need to bother with SSL/TLS, the connector handles this for you (encrypts the connection). Additionally, if you're using a standalone connector (Cloud SQL Auth Proxy), the connection between it and a client is not encrypted, as such you most likely want to keep the connector on the same machine.

To use IAM database authentication you need to:

To use SSL/TLS you need a server certificate (which is created automatically when you create an instance) and client certificates. You're notified when a server certificate expires, but not when a client certificate does. When connecting directly using SSL/TLS is strongly recommended. When using a connector configuring SSL/TLS is not needed.

To view Cloud SQL logs:

$ gcloud logging read "resource.type=cloudsql_database" --project=PROJECT_ID --limit=10

Examples of filter expressions:

  • resource.type=cloudsql_database
  • logName=projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
  • projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
  • resource.type=cloudsql_database AND logName=projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log
  • resource.type=cloudsql_database AND projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres.log

Other useful arguments: --freshness (defaults to 1d), --order (defaults to desc).

Additionally:

  • When you create a Cloud SQL instance the postgres user is created with no password. You need to set the password to be able to authenticate.
  • You can't have superusers on Cloud SQL.
  • Users created with gcloud initially have the same permissions as postgres.
  • authorized networks can contain both IP addresses and IP ranges.
  • How the proxy works is described here.
  • Automatic instance discovery is not recommended in production.
  • Where the proxy looks for credentials is described here.
  • It seems like PostgreSQL connections used to hang w/o sslmode=disable, which doesn't seem to be an issue these days.
  • Using the Cloud SQL Auth Proxy's --port flag reduces startup time.

IAM database authentication + Cloud SQL Auth Proxy (ruby)
IAM database authentication + Cloud SQL Auth Proxy
built-in authentication + SSL/TLS

How to:

The docs:

Logging:

Troubleshooting:

Quickstarts:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment