Golang zitified postgres db query

Goal

The current golang code can connect to a postgres database and return a set of records.

It assumes that a tunneller is running on the host to resolve the zitified endpoint on the overlay network

I now want to integrate the Golang Ziti SDK so that the tunneller is not required.

package main
 
import (
        "database/sql"
        "fmt"
         _ "github.com/jackc/pgx/v4/stdlib"
)
 
const (
    host     = "zitified-postgres"
    port     = 5432
    user     = "postgres"
    password = "postgres"
    dbname   = "simpledb"
)

 
func main() {
        // connection string
    dsn := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
    
    fmt.Println("dsn")
    fmt.Println(dsn)

    dbPool, err := sql.Open("pgx", dsn)
    if err != nil {
     fmt.Println(err)
    }

    rows, err := dbPool.Query(`SELECT * FROM simpletable`)
    CheckError(err)
 
    defer rows.Close()
    
    for rows.Next() {
        var chardata string
        var somenumber int
     
        err = rows.Scan(&chardata, &somenumber)
        CheckError(err)
     
        fmt.Println(chardata, somenumber)
    }
     
    CheckError(err)


        // close database
    defer dbPool.Close()
 
        // check db
    err = dbPool.Ping()
    CheckError(err)
 
    fmt.Println("Connected!")
}
 
func CheckError(err error) {
    if err != nil {
        panic(err)
    }
}

Areas where assistance is required

I have spent some time to explore where to override the network dialer so that the driver can connect to the overlay network.

However… I am quite lost and don’t have much of an idea on how to approach this.

I looked at the Influx db example, but this is not very helpful in this context as it uses the HTTP APIs… whereas this example needs to have the some of the postgres driver configurations changed.

I am thinking that there needs to be some network configuration changed earlier before I make the database open connection… but I don’t really know where to start… I have not seen any examples to provide guidance on this

I looked at other Golang SDK examples, but they either relied on a HTTP or SSH client

Let me know if anyone has any tips on where best to find answers to help me work through this

@dovholuknf

PS. I did find this sample as a reference point but was not able to adapt it to suit what I was wanting to do.

I found also found the following code in the conn.go file of the pgx library.

This is what performs the dialing as I understand… so somehow… I need to integrate the open ziti config here. but not 100% sure if this is right / possible …


	c.pgConn, err = pgconn.ConnectConfig(ctx, &config.Config)
	if err != nil {
		if c.shouldLog(LogLevelError) {
			c.log(ctx, LogLevelError, "connect failed", map[string]interface{}{"err": err})
		}
		return nil, err
	}

As a quick update… something I noticed in both the Java Postgres demo and the Ansible demo is the following.

Ziti.init

This appears to establish the connection to the overlay network, though I really don’t know much more than that.

I believe this to be a part of the solution, but I don’t really know what else needs to happen to make the db connection work.

All tips / suggestions welcome.

Getting a database connection to work in go should work fine. If our samples don’t have an example for what you need yet, we’ll look to add them in the future but probably not in the immediate future. you need to learn about and find where the net.Conn is used by the db driver and figure out (or if) you can replace the Conn with a ziti connection.

The Ziti.init is only relevant to java/python right now - and since this is a post about golang it doesn’t fit in on this thread. It just initializes the sdk to work with intercepts.

1 Like