Error connection to Google Cloud Postgres with GORM v2 after upgrade of GORM

Question

After upgrading to Gorm versions:

gorm.io/driver/postgres v1.0.2  
gorm.io/gorm v1.20.2

Not able to connect to Google Cloud SQL PostgreSQL v12

The connection worked without issues on older GORM versions with:

db, err := gorm.Open("postgres", "host=/cloudsql/project_id:us-central1:sql_instance_name port=5432 user=... dbname=... password=... sslmode=disable")   

New GORM version works on local install (mac) of localhost_golang / localhost_postgres

CODE on Google Cloud:

dsn := ""  
dsn += "user=postgres "  
dsn += "password=admin_password_here "  
dsn += "host=/cloudsql/project_id:us-central1:sql_instance_name "  
dsn += "dbname=db_name "  
dsn += "port=5432 "  
dsn += "sslmode=disable "  
fmt.Println("dsn:%v", dsn)

db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{   
    NamingStrategy: schema.NamingStrategy{  
        SingularTable: true,  
    }, 
})

ERROR on Google Cloud:
[0m[31m[error] [0mfailed to initialize database, got error failed to connect to host=/cloudsql/project_id:us-central1:sql_instance_name user=postgres database=db_name: dial error (dial unix /cloudsql/project_id:us-central1:sql_instance_name/.s.PGSQL.5432: connect: connection refused)

Thank you!

Answer 1

App Engine default service account needs proper Roles, even though the CLoud SQL Connections page indicates "App Engine authorization", All apps in this project are authorized by default. https://cloud.google.com/sql/docs/postgres/connect-app-engine-standard?_ga=2.121100104.-1170262708.1601472976

To configure App Engine standard environment to enable connections to a Cloud SQL instance using public IP:

Make sure that the instance created above has a public IP address. You can verify this on the Overview page for your instance in the Google Cloud Console. If you need to add one, see the Configuring public IP page for instructions.

Get the INSTANCE_CONNECTION_NAME for your instance. This can be found on the Overview page for your instance in the Google Cloud Console. or by running the following command: gcloud sql instances describe [INSTANCE_NAME].

Ensure that the service account your app is using to authenticate calls to Cloud SQL has the appropriate Cloud SQL role and permissions. The service account for your service needs one of the following IAM roles:

- Cloud SQL Client (preferred)  
- Cloud SQL Editor  
- Cloud SQL Admin  

Or, you can manually assign the following IAM permissions:

- cloudsql.instances.connect  
- cloudsql.instances.get   

For detailed instructions on adding IAM roles to a service account, see Granting Roles to Service Accounts. By default, your app will authorize your connections using an App Engine service account. The service account identity is in the format [email protected].

If the authorizing service account belongs to a different project than the Cloud SQL instance, the Cloud SQL Admin API and IAM permissions will need to be added for both projects.