CODEWITHSUNDEEP
postgresqlgoogle-compute-enginegoogle-cloud-sql
amita00
amita00

Reputation: 189

Connecting GCP Compute engine to GCP Cloud SQL with PostgreSQL

I'm trying to get my GCP Compute Engine instance, Ubuntu 16.04, connected to a GCP Cloud SQL PostgreSQL database.

I've followed all the instructions in the documentation, but when I enter the command to connect to the database:

psql -h [CLOUD_SQL_PUBLIC_IP_ADDR] -U postgres

The result is:

psql: FATAL:  Peer authentication failed for user "postgres"

I've done the authentication on both the CloudSQL side and the ComputeEngine side, so I'm not sure why this is going wrong.

The database I'm trying to connect to is in the same project, and the command

gcloud sql instances list

shows the database in the listings. However, the command

sudo -u postgres psql my-db

returns

psql: FATAL:  database "my-db" does not exist

The expected result is that a psql connection opens, but instead I get a psql: FATAL: Peer authentication failed for user "postgres".

Upvotes: 1

Views: 1081

Answers (1)

Sandro G
Sandro G

Reputation: 150

I've followed the instructions from the documentation you posted and I was able to connect successfully from my Compute Engine instance (Ubuntu 16.04) using a public IP address.

The steps I've followed are documented in "Connecting using a public IP address":

1- Added a static IPv4 IP address to the Compute Engine instance. To do this, navigate to the Cloud console > VPC Network > External IP addresses and click in the button "Reserve static address".

2- Authorize the static IP address of the Compute Engine instance as a network that can connect to the Cloud SQL instance.

3- Connect via SSH button to your Compute Engine instance.

4- Install the psql client:

$ sudo apt-get update

$ sudo apt-get install postgresql-client

5- find the CLOUD_SQL_PUBLIC_IP_ADDR

$ gcloud sql instances list:

6- And connect to the Cloud SQL instance with the psql client making sure both user and database do exist:

$ psql -h [CLOUD_SQL_PUBLIC_IP_ADDR] -U [USER] -d [DATABASE]

Also, the command below worked fine for me:

$ psql [USER] -h [CLOUD_SQL_PUBLIC_IP_ADDR] -d [DATABASE]

Then you will be asked for the user's password and voilà.

Could you please try following the instructions above to verify if it works fine for you? Did you configure an encrypted connection before using SSL?

Upvotes: 1

Related Questions