如何在Go中获取SQL连接的客户端端口号?

huangapple go评论79阅读模式
英文:

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

问题

在PostgreSQL日志中,每个条目都会记录访问数据库的客户端地址和端口号,例如10.1.1.1(4567),其中4567是我的应用程序Pod的端口号。我想将其与我的应用程序日志进行匹配,但我找不到在Go应用程序中记录该端口号4567的方法,我正在使用标准的sql包和sql.Open

有没有办法在Go中获取它?因为应用程序对每个连接都必须知道它,但它可能是sql包的内部实现,所以我无法获取它。

英文:

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.

答案1

得分: 1

如果我理解正确,您想要记录连接到数据库的源端口,以下是我对此的理解,使用我可用的postgres数据库:

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:testpass@34.69.4.107/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)
}

然后,输出如下(一次是正常执行,一次是带有调试输出,其中包括源IP和端口以及目标IP和端口):

~/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 $
英文:

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:testpass@34.69.4.107/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 $

huangapple
  • 本文由 发表于 2021年10月16日 00:28:50
  • 转载请务必保留本文链接:https://go.coder-hub.com/69587884.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定