How to get client port numbers for SQL connections in Go?-CodePudding

In postgres log each entry logs the client address accessing the DB as well as the port number, such as 10.1.1.1(4567), where 4567 is a port number for my application pod. I want to match that with my application logs but I couldn't find a way to log that port number 4567 in the Go application and I'm using the standard sql package and sql.Open.

Anyway to get that in Go? As the application must knows it for each connection, but it can be internal implementation of sql package so I cannot get it.

CodePudding user response：

If I understand correctly, you are looking to log the source port of your connection to the database, here is my take on this with the postgres db I had available:

package main

import (
 "database/sql"
 "database/sql/driver"
 "net"
 "os"
 "time"

 pq "github.com/lib/pq"
 log "github.com/sirupsen/logrus"
)

type customDriver struct {
}

func (d customDriver) Open(name string) (driver.Conn, error) {
 return pq.DialOpen(&customDriver{}, name)
}

func (d *customDriver) Dial(network, address string) (net.Conn, error) {
 c, err := net.Dial(network, address)
 if err != nil {
  return nil, err
 }
 log.Debugf("Connecting from %s to %s", c.LocalAddr(), c.RemoteAddr())
 return c, nil
}

func (d *customDriver) DialTimeout(network, address string, timeout time.Duration) (net.Conn, error) {
 c, err := net.DialTimeout(network, address, timeout)
 if err != nil {
  return nil, err
 }
 log.Debugf("Connecting from %s to %s", c.LocalAddr(), c.RemoteAddr())
 return c, nil
}

func init() {
 sql.Register("custom_postgres", customDriver{})

 logLevel, logLevelExists := os.LookupEnv("LOG_LEVEL")
 if !logLevelExists {
  logLevel = "info"
 }

 logrusLogLevel, err := log.ParseLevel(logLevel)
 if err != nil {
  logrusLogLevel = log.InfoLevel
 }

 log.SetLevel(logrusLogLevel)
}

func main() {
 connStr := "postgres://testuser:[email protected]/testdb?sslmode=disable"
 db, err := sql.Open("custom_postgres", connStr)
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()

 row := db.QueryRow("SELECT NOW()")

 var result string
 row.Scan(&result)

 log.Infof("DATABASE TIME: %s", result)
}

Then the output of this is as follows (one for regular execution and one with debug output, which includes source ip and port and destination ip and port)

~/Projects/go/q69587884 $ go run .                
INFO[0000] DATABASE TIME: 2021-10-16T02:39:36.710168Z   

~/Projects/go/q69587884 $ LOG_LEVEL=debug go run .
DEBU[0000] Connecting from 10.100.0.135:63616 to 34.169.24.137:5432 
INFO[0000] DATABASE TIME: 2021-10-16T02:39:39.291173Z   
~/Projects/go/q69587884 $