For a project i'm trying to connection a cloud function to a cloud sql database setup as described in this quickstart guide.
The function is configured in the same region, the service account has the Role Cloud SQL-Client
. I called the function through my computer like this:
gcloud functions call <function-name> --region=<region> --data '{"recipient":"hello","requester":"hello","message":"test"}'
The connection to the function is working, it seems like just the authentication to the database doesn't work but i don't get where i failed.
I checked the password, user and connection name multiple times, reset the password and it still doesn't work. I found the issue here related to connecting cloud functions to cloud sql.
I tried surrounding the password in the dsn
-string with single-quotes just to be sure escaping of characters in the password isn't a problem.
I also checked the environment variables coming in and they are the ones i entered in the configuration.
The function just pings the database for test purposes:
package receiver
import (
"database/sql"
"fmt"
"net/http"
"os"
// Import Postgres SQL driver
_ "github.com/lib/pq"
)
// Receives a message and stores it
func Receive(w http.ResponseWriter, r *http.Request) {
connectionName := os.Getenv("POSTGRES_INSTANCE_CONNECTION_NAME")
dbUser := os.Getenv("POSTGRES_USER")
dbPassword := os.Getenv("POSTGRES_PASSWORD")
dsn := fmt.Sprintf("user=%s password='%s' host=/cloudsql/%s dbname=messages", dbUser, dbPassword, connectionName)
var err error
db, err := sql.Open("postgres", dsn)
if err != nil {
fmt.Fprintf(w, "Could not open db: %v \n", err)
}
// Only allow 1 connection to the database to avoid overloading
db.SetMaxIdleConns(1)
db.SetMaxOpenConns(1)
defer db.Close()
if pingerror := db.Ping(); pingerror != nil {
fmt.Fprintf(w, "Failed to ping database: %s \n", pingerror)
return
}
}
The variable POSTGRES_INSTANCE_CONNECTION_NAME
is formatted as described here as ProjectID:Region:InstanceID
.
Expected is a success message or no error and i'm actually getting this message:
pq: password authentication failed for user "postgres"
Note: I also created a function containing the demo code from here with my sql database settings and the error is the same. It seems like i missed some step while setting up the user or sql instance. But i can't find out which.
Feels strange to answer my own question but here it is: For some reason connecting with the postgres
user doesn't work. Finally i created a new database user for the function and a password containing only alphanumeric characters.