Connect to Google Cloud SQL for PostgreSQL from your Mac

What you need

• A database user and password (the built-in user is postgres), or an IAM user set up for IAM database authentication.
• One of two ways in from your Mac:
  • Public IP: the instance has a public IP address and your IP is in its authorized networks.
  • Cloud SQL Auth Proxy: a small program you run on your Mac. It needs no authorized networks; your Google account (or a service account) needs the Cloud SQL Client role.

An instance with only a private IP can’t be reached from a laptop outside Google Cloud unless you have access to its VPC network.

Find your connection details

In the Google Cloud console, open Cloud SQL Instances and click the instance to open its Overview page. In the Connect to this instance section, copy the Connection name, in the form <project>:<region>:<instance>; the Auth Proxy needs it. The instance’s public IP address is shown in the console too.

Authorized networks. Open the instance, select Connections from the SQL navigation menu, and click the Networking tab. With Public IP selected, expand New IP range in the Authorized networks section, enter a name and your address in CIDR notation (203.0.113.7/32 for one address), or click Use my IP; then Done and Save. Cloud SQL doesn’t support IPv6 authorized networks, and behind a VPN or proxy Use my IP doesn’t show your Mac’s real address.

Managed connection pooling (Enterprise Plus edition only) listens on port 6432 and defaults to transaction mode; direct connections use 5432.

Connection string

Over the public IP, with certificate checks:

postgresql://postgres:<password>@<public-ip>:5432/<database>?sslmode=verify-ca&sslrootcert=/path/to/server-ca.pem

Through the Auth Proxy, running on your Mac:

postgresql://postgres:<password>@127.0.0.1:5432/<database>?sslmode=disable

sslmode=disable is right here: the proxy itself encrypts the traffic to Google Cloud. See PostgreSQL connection strings.

TLS

Each instance has an SSL mode. In the console, open the instance, click Connections, and select the Security tab:

• Allow unencrypted network traffic (ALLOW_UNENCRYPTED_AND_ENCRYPTED, the default) accepts connections with and without TLS.
• Allow only SSL connections (ENCRYPTED_ONLY) requires TLS.
• Require trusted client certificates (TRUSTED_CLIENT_CERTIFICATE_REQUIRED) requires TLS and a valid client certificate.

To verify the server, you need its CA certificate. With a per-instance CA, Cloud SQL creates a self-signed CA for each instance; the Security tab lists the server certificates, and gcloud can save the CA as server-ca.pem:

gcloud sql ssl server-ca-certs list --format="value(cert)" --instance=<instance> > server-ca.pem

Which sslmode verifies the server depends on the instance’s server CA mode:

• Per-instance CA (“Google managed internal certificate authority” in the console; the default when you create an instance with gcloud, the API or Terraform): each instance has its own CA, so sslmode=verify-ca with server-ca.pem already proves you reached your instance.
• Shared CA (“Google managed CAS certificate authority”) or a customer-managed CA: the CA is shared, so use the regional CA bundle Google publishes and also check the host name with sslmode=verify-full, connecting to the instance’s DNS name (from gcloud sql instances describe, or the console).

If you create a client certificate, Cloud SQL gives you client-cert.pem and client-key.pem to go with server-ca.pem.

Connect with Inlet

Over the public IP:

1. Choose New Connection, enter the public IP (or DNS name), port 5432, user and database. Or paste a postgresql:// URL and Inlet fills in the form.
2. Under TLS, choose verify-ca (per-instance CA) or verify-full (shared CA, with the DNS name as host), and select server-ca.pem (or the regional bundle) as the CA file. If the instance requires client certificates, add client-cert.pem and client-key.pem as the client certificate and key.

Through the Auth Proxy: start the proxy (see below), then connect Inlet to host 127.0.0.1, port 5432 (or the --port you gave), with TLS off.

Either way, keep the password in the Keychain or have Inlet ask each time, and tag the connection’s environment. Production connections open read-only (the server refuses writes) until you unlock them for ten minutes.

If you use managed connection pooling on port 6432 in transaction mode, Inlet’s read-only mode, which is a session setting, isn’t guaranteed to hold, because each transaction can run on a different server connection. Use port 5432 for production, or make the role read-only on the server with ALTER ROLE <role> SET default_transaction_read_only = on (a default a session can still change; grant only SELECT for a hard limit).

IAM users: Google notes the login token is longer than password fields allow. Start the proxy with --auto-iam-authn instead; it signs in for you.

Connect from the command line

Over the public IP (Google’s examples):

psql "sslmode=verify-ca sslrootcert=server-ca.pem hostaddr=<public-ip> user=postgres dbname=<database>"

Add sslcert=client-cert.pem sslkey=client-key.pem when client certificates are required.

Through the Auth Proxy, in one terminal:

./cloud-sql-proxy <project>:<region>:<instance>

It prints Listening on 127.0.0.1:5432 for <connection name>. In another:

psql "host=127.0.0.1 port=5432 sslmode=disable dbname=<database> user=postgres"

With IAM database authentication and psql directly:

PGPASSWORD="$(gcloud sql generate-login-token)" psql "sslmode=require hostaddr=<public-ip> user=<you@example.com> dbname=<database>" --no-password

IAM users sign in with their full email address; service accounts drop the .gserviceaccount.com part.

Troubleshooting

• Connection timed out or refused on the public IP: your address isn’t in the authorized networks (or it changed). Add it, or use the Auth Proxy.
• Refused without TLS, or without a client certificate: check the instance’s SSL mode. With ENCRYPTED_ONLY, turn TLS on; with TRUSTED_CLIENT_CERTIFICATE_REQUIRED, add client-cert.pem and client-key.pem too.
• verify-full fails on an instance with a per-instance CA: use verify-ca with server-ca.pem. Google’s docs say that checking the CA verifies the server for that CA mode, because no other instance shares it.
• The proxy can’t connect: check your account has the Cloud SQL Client role and the connection name is right.
• password authentication failed: check the user and password; for IAM users, the user name is the email address.