Retain client_address on Postgres connection when using Private IP

[fatmcgav]

Question

We're currently rewriting a number of our Golang services to use cloudsqlconn and overall things are working great.

However one thing that we've spotted is that there's no client_address reported on the connections within Postgres, even if we're using the instance private IP to connect.
However if we connect using psql from the same pod using the same details then the client_address is recorded.

So is there a specific reason why the client_address isn't being reported when connecting via cloudsqlconn using the private IP?

Code

No response

Additional Details

No response

[enocom]

That's an artifact of how the Proxy path works -- it goes from the Go Connector to the Proxy server to the database server. The connection between the Proxy server and the database server is over a local unix socket. So when the database sees the connection, it's from localhost. The team is aware of the limitation, but probably won't be fixing it anytime soon.

Out of curiosity are you using Auto IAM AuthN with the Go Connector?

[fatmcgav]

That's an artifact of how the Proxy path works -- it goes from the Go Connector to the Proxy server to the database server. The connection between the Proxy server and the database server is over a local unix socket. So when the database sees the connection, it's from localhost. The team is aware of the limitation, but probably won't be fixing it anytime soon.

Out of curiosity are you using Auto IAM AuthN with the Go Connector?

Thanks for the quick reply @enocom .

Yeh, so we're using Auto IAM AuthN with GKE service accounts and Workload Identity.
All working great apart from the lack of client_addr.

Am I right in assuming that if we enable the private-ip setting that Go Connector is talking to the proxy server on the private ip? As you mentioned that the proxy is collocated with the database and uses a local socket.

[enocom]

Am I right in assuming that if we enable the private-ip setting that Go Connector is talking to the proxy server on the private ip? As you mentioned that the proxy is collocated with the database and uses a local socket.

Yes, that's correct.

[enocom]

FWIW, it's possible to connect directly and still use Auto IAM AuthN (with a little know-how). In that scenario, you'd get the correct IP in the client logs.

Also, a caveat: if you've enabled "Allow only SSL connections" on your instance, you'll have to configure client certificates on the direct path. Doable, but a bit of a nuisance.

[fatmcgav]

FWIW, it's possible to connect directly and still use Auto IAM AuthN (with a little know-how). In that scenario, you'd get the correct IP in the client logs.

Ooh, interesting... Are you able to provide an example?

I could see a working scenario using a manually generated sql access token, but that would require coding a refresh wouldn't it?

[enocom]

If you're using database/sql, you could write a driver like this:

import (
	"context"
	"database/sql"
	"database/sql/driver"

	"github.com/jackc/pgx/v4"
	"github.com/jackc/pgx/v4/stdlib"
	"golang.org/x/oauth2"
	"golang.org/x/oauth2/google"
)

func init() {
	sql.Register("gcp-postgres", &pgDriver{})
}

type pgDriver struct{}

func authToken() (*oauth2.Token, error) {
	ts, err := google.DefaultTokenSource(context.Background())
	if err != nil {
		return nil, err
	}
	return ts.Token()
}

func (p *pgDriver) Open(name string) (driver.Conn, error) {
	config, err := pgx.ParseConfig(name)
	if err != nil {
		return nil, err
	}
	tok, err := authToken()
	if err != nil {
		return nil, err
	}
	config.Password = tok.AccessToken

	dbURI := stdlib.RegisterConnConfig(config)
	if err != nil {
		return nil, err
	}
	return stdlib.GetDefaultDriver().Open(dbURI)
}

And then usage looks like:

package main

import (
	"database/sql"
	"fmt"
	"time"

	_ "enocom.dev/driver/postgres"
)

func main() {
	db, err := sql.Open("gcp-postgres", "host=localhost dbname=postgres user=mysa@project.iam sslmode=require")
	if err != nil {
		panic(err)
	}
	row := db.QueryRow("SELECT NOW()")
	var t time.Time
	if err := row.Scan(&t); err != nil {
		panic(err)
	}
	fmt.Println(t)
}

If you were using pgx, it would look like this:

import (
        "context"
        "fmt"
        "time"

        "github.com/jackc/pgx/v5"
        "github.com/jackc/pgx/v5/pgxpool"
        "golang.org/x/oauth2"
        "golang.org/x/oauth2/google"
)

func authToken() (*oauth2.Token, error) {
        ts, err := google.DefaultTokenSource(context.Background())
        if err != nil {
                return nil, err
        }
        return ts.Token()
}

func main() {
        config, err := pgxpool.ParseConfig(
                "host=<INSTANCE_IP> user=postgres password=empty sslmode=require",
        )
        if err != nil {
                /* ... */
        }
        // This function is called before every connection
        config.BeforeConnect = func(ctx context.Context, c *pgx.ConnConfig) error {
                tok, err := authToken()
                if err != nil {
                        /* ... */
                }
                c.Password = tok.AccessToken
                return nil
        }

        pool, err := pgxpool.NewWithConfig(context.Background(), config)
        if err != nil {
                /* ... */
        }

        conn, err := pool.Acquire(context.Background())
        if err != nil {
                /* ... */
        }
        defer conn.Release()

        row := conn.QueryRow(context.Background(), "SELECT NOW()")
        var t time.Time
        if err := row.Scan(&t); err != nil {
                /* ... */
        }

        fmt.Println(t)
}

[fatmcgav]

Thanks for the pointers @enocom ...

We're currently using go-pg, but might be looking to migrate soon as the project has been archived.

[enocom]

Looks like Bun (the rewrite of go-pg) makes this easy at least.

[enocom]

Marking this as a feature request. The change would be on the backend, but might be feasible.