Question 188212 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Connecting to Postgres Cloud SQL from App Engine Standard with IAM is timing out?

Upon trying the sample code here: https://cloud.google.com/sql/docs/postgres/connect-app-engine-standard#connect-connectors … And here: https://cloud.google.com/sql/docs/postgres/iam-logins

… The connection times out when using the private IP (but correctly resolves the private IP using the instance name), and the same code with the public IP (by adjusting the dialer) says:

failed SASL auth (FATAL: password authentication failed for user "<IAM SERVICE ACCOUNT AUTHORIZED FOR DB>" (SQLSTATE 28P01)

Connecting with IAM does work through the Cloud SQL Proxy on my client machine. Since neither of these options are working for App Engine Standard, and the logs show no helpful information, is there a known solution or suggestion on a workaround?

I’m using Go, "edited down" version of the code here…

import (
    "database/sql"
    "fmt"
    "net"

    "cloud.google.com/go/cloudsqlconn"
    pgx "github.com/jackc/pgx/v4"
    pgxStdlib "github.com/jackc/pgx/v4/stdlib"
)

func test() {
    var dialOptions []cloudsqlconn.DialOption

    if usePrivateIP {
        dialOptions = append(dialOptions, cloudsqlconn.WithPrivateIP())
    }

    var dialer *cloudsqlconn.Dialer
    dialer, err := cloudsqlconn.NewDialer(
        context.Background(),
        cloudsqlconn.WithIAMAuthN(),
        cloudsqlconn.WithDefaultDialOptions(dialOptions...),
    )

    if err != nil {
        return
    }

    config, err =: pgx.ParseConfig(fmt.Sprintf(
        "user=%s password=%s database=%s",
        user,
        password,
        databaseName,
    ))

    if err != nil {
        return
    }

     config.DialFunc = func(ctx context.Context, _ string, _ string) (net.Conn, error) {
        return dialer.Dial(ctx, instanceName)
    }

    var db *sql.DB
    db, err := sql.Open("pgx", pgxStdlib.RegisterConnConfig(config))

    if err != nil {
        return
    }

    _ = db // should be live
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    You need to use XX@appspot rather than the full IAM Service Account ([email protected]) address.

    Thanks to @enocom for highlighting this in his comment when we were troubleshooting, as it's not clear the values aren't interchangeable from the documentation, and the Cloud SQL Proxy for client machines does this for you.


  2. 0

    Private IP is probably timing out because you’ll need to configure a Serverless VPC Access Connector to ensure a network path to your database.

    When you tried public IP, you did connect to your database but authentication failed. This is probably because you’re running your App Engine app as a separate IAM principal than your database user.

    Here’s how it looks all together:

        // password is intentionally blank
    dsn := fmt.Sprintf("user=%s password="" dbname=%s sslmode=disable", postgresUserIAM, postgresDB)
    config, err := pgx.ParseConfig(dsn)
    if err != nil {
        t.Fatalf("failed to parse pgx config: %v", err)
    }
    d, err := cloudsqlconn.NewDialer(ctx, cloudsqlconn.WithIAMAuthN())
    if err != nil {
        t.Fatalf("failed to initiate Dialer: %v", err)
    }
    defer d.Close()
    config.DialFunc = func(ctx context.Context, network string, instance string) (net.Conn, error) {
        return d.Dial(ctx, postgresConnName)
    }

    conn, connErr := pgx.ConnectConfig(ctx, config)
    if connErr != nil {
        t.Fatalf("failed to connect: %s", connErr)
    }
    defer conn.Close(ctx)

    var now time.Time
    err = conn.QueryRow(context.Background(), "SELECT NOW()").Scan(&now)
    if err != nil {
        t.Fatalf("QueryRow failed: %s", err)
    }

    See how we wire it up here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search