Seeking help with Golang SDK Postgres demo

Goal

To integrate the Ziti network dialer into a standard Golang script that connects to a Postgres database

Current progress

The following is what I have worked out to date… but is not complete nor working.

package main

import (
    "context"
    "fmt"
    "os"
   // "net/http"
    "net"
    "time"
    "github.com/openziti/sdk-golang/ziti"
    "github.com/openziti/sdk-golang/ziti/config"
    "github.com/jackc/pgx/v4"
    //"github.com/jackc/pgconn"
)

var zitiContext ziti.Context


func Dial(_ context.Context, _ string, addr string) (net.Conn, error) {
    service := "private-postgres" //os.Args[2]
    defaultOptions := &ziti.DialOptions{ConnectTimeout: 5 * time.Minute}
    //zc, err := zitiContext.Dial(service)
    zc, err := zitiContext.DialWithOptions(service, defaultOptions)
    return zc, err
}

func main() {

    cfg, err := config.NewFromFile("/mnt/v/temp/tunneler-id.json")
    if err != nil {
        panic(err)
    }


    zitiContext := ziti.NewContextWithConfig(cfg) 
    identity, err := zitiContext.GetCurrentIdentity()
    fmt.Println(identity.Name) 

    connString := "postgres://postgres:postgres@zitified-postgres:5432/simpledb"
    //conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    //conn, err := pgx.Connect(context.Background(), urlExample)

    config, err := pgx.ParseConfig(connString)
    ctx := context.Background()

    // e.g. net.Dialer.DialContext
    config.DialFunc = func(ctx context.Context, network string, addr string) (net.Conn, error) {
	fmt.Println("dialing")
	zitiConn, err := Dial (ctx, "","")
	return zitiConn, err

     }


    conn, err := pgx.ConnectConfig(ctx, config)  
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    defer conn.Close(ctx)



    rows, err := conn.Query(ctx,`SELECT chardata, somenumber FROM simpletable`)

    defer rows.Close()

    for rows.Next() {
        var chardata string
        var somenumber int

        err = rows.Scan(&chardata, &somenumber)

        fmt.Println(chardata, somenumber)
    }

}

The problem

The DialFunc function is not being called to redirect the network connection to the Ziti overlay.

The DialFunc is a part of the lower level libraries that pgx uses. It is found in the pgconn library.

Current understanding

What I believe is happening is that I am using the wrong golang library.

I will update my script to use the pgconn library… which will also require a bit of a rewrite for the query to work

Tips

Am I heading in the right / wrong direction?
Is there an easier way?

I am quite a novice with this… so any tips are greatly appreciated

I am working through this to set up a demo… and need to find a way for this demo to work

PS. I believe this would be a valuable example to add into the Golang SDK examples.

@dovholuknf

Ahh… every time I write up my problem… I find something that I can use to keep myself going :slight_smile:

I finally found a custom dialer example… hopefully I am not far away now

func TestConnectCustomDialer(t *testing.T) {
	t.Parallel()

	config, err := pgconn.ParseConfig(os.Getenv("PGX_TEST_CONN_STRING"))
	require.NoError(t, err)

	dialed := false
	config.DialFunc = func(ctx context.Context, network, address string) (net.Conn, error) {
		dialed = true
		return net.Dial(network, address)
	}

	conn, err := pgconn.ConnectConfig(context.Background(), config)
	require.NoError(t, err)
	require.True(t, dialed)
	closeConn(t, conn)
}

After my initial hope… I modified the code to use the other package… but I still experience the same issue.

The dialer is not being called.

Let me know if you can help point me in the right direction :slight_smile:

package main

import (
    "context"
    "fmt"
    "os"
    "net"
    "time"
    "github.com/openziti/sdk-golang/ziti"
    "github.com/openziti/sdk-golang/ziti/config"
    "github.com/jackc/pgconn"
)

var zitiContext ziti.Context


func Dial(_ context.Context, _ string, addr string) (net.Conn, error) {
    service := "private-postgres" //os.Args[2]
    defaultOptions := &ziti.DialOptions{ConnectTimeout: 5 * time.Minute}
    //zc, err := zitiContext.Dial(service)
    zc, err := zitiContext.DialWithOptions(service, defaultOptions)
    return zc, err
}

func main() {

    cfg, err := config.NewFromFile("/mnt/v/temp/tunneler-id.json")
    if err != nil {
        panic(err)
    }


    zitiContext := ziti.NewContextWithConfig(cfg) 
    identity, err := zitiContext.GetCurrentIdentity()
    fmt.Println(identity.Name) 

    connString := "postgres://postgres:postgres@zitified-postgres:5432/simpledb"
    //conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
    //conn, err := pgx.Connect(context.Background(), urlExample)

    //config, err := pgx.ParseConfig(connString)
    config, err := pgconn.ParseConfig(connString)
    ctx := context.Background()

    // e.g. net.Dialer.DialContext
    config.DialFunc = func(ctx context.Context, network string, addr string) (net.Conn, error) {
	fmt.Println("dialing")
	zitiConn, err := Dial (ctx, "","")
	return zitiConn, err

     }


    conn, err := pgconn.ConnectConfig(ctx, config)  
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        os.Exit(1)
    }
    defer conn.Close(ctx)

    mrr := conn.Exec(ctx,`SELECT chardata, somenumber FROM simpletable`)

    defer mrr.Close()

    results, err := mrr.ReadAll()

    for r := range results {
        fmt.Println("test", r)
    }

}

I don’t have time to write it up and push it out at the moment but perhaps this will get you started. I searched the internet and the main postgres library doesn’t seem to have a custom dialer option but they do show you how to write your own db driver and just wrap their postgres driver. It seems pretty trivial.

The problem is that the postgres driver doesn’t allow you to simply set the dialer. Apparently msql does but I didn’t look into that. See the pg driver issue listed here Register dialer · Issue #470 · lib/pq · GitHub.

I pushed the start of an exercise out to the sdk-golang repo right now. it should get you what you need. I used the ‘simpletable’ that I used from the jdbc postgres cheatsheet

If you have postgres running on ‘localhost:5432’ - this example should run for you and produce the expected output of. (I grabbed the first postgres tutorial that looked clean/clear and used that as the basis of this code. find that tutorial here)

Connected!
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9
j 0

Here’s the branchI put up. I would expect you would want to hack on the zitifiedpq.go file. If it were me I might do all the ziti setup in the Open function, and then in the Dial I would just dial the overlay the way I wanted.

I hope that gives you enough direction. Good luck, I think this should get you going.

1 Like

Very helpful. Thanks so much. This will keep me moving forward for sure. :slight_smile:

After a long trip down many dead ends… I can say for sure… I was able to use your template to get it all to work

This is probably not the best of code… as its just a mock up for a demo… but highlights how simple it was.

func (d drvWrapper) Dial(network, address string) (net.Conn, error) {
        cfg, err := config.NewFromFile("/mnt/v/temp/tunneler-id.json")
        if err != nil {
                panic(err)
        }

        zitiContext := ziti.NewContextWithConfig(cfg) 
        identity, err := zitiContext.GetCurrentIdentity()
        fmt.Println(identity.Name) 

        services, err := zitiContext.GetServices()
        fmt.Println(services) 

        service := "private-postgres" //os.Args[2]
        defaultOptions := &ziti.DialOptions{ConnectTimeout: 5 * time.Minute}
        zc, err := zitiContext.DialWithOptions(service, defaultOptions)

        fmt.Println("dialing")
        return zc, err 
}
2 Likes

You probably don’t want to make that new context every dial but since database connections are often persistent, it probably isn’t a huge deal.

Awesome to see you got it working!

1 Like

Good pick up. I am also very excited.

As I see it, there was a day before I could do this, and the immediate moment after I had this working. It was a huge change, especially in how the Gloang packages connect together. This was something I found very confusing, of which, this has helped fill in a large centre piece of my jigsaw puzzle.

I have now also started to explore how to do the same with other Databases… I am exploring Oracle… but its probably going to be easier to do MySQL. Then there are all of the others to work towards.

I don’t know how far I will get… but this has certainly been a memorable achievement. Thanks so much for your help. It was greatly appreciated.