Unable to connect to postgresql using Go and pq

I'm trying to connect a Go application with postgresql.

The app import postgresql driver:

"crypto/tls"
"database/sql"
"fmt"
"log"
"os"
"os/signal"
 ...
_ "github.com/go-sql-driver/mysql"
_ "github.com/lib/pq"
_ "github.com/mattn/go-sqlite3"

and uses like it to connect to the database:

driver, cnxn := dbFromURI(dbURI)
db, err := sql.Open(driver, cnxn)
if err != nil {
    panic(err)
}

and the dbFromUri method just split the info

func dbFromURI(uri string) (string, string) {
    parts := strings.Split(uri, "://")
    return parts[0], parts[1]
}

My URI works locally when i run the command : psql postgresql://user:[email protected]:5432/lcp

But in go i Go, I got

./lcpserver
2021/03/07 02:00:42 Reading config /root/lcp-server-install/lcp-home/config/config.yaml
panic: pq: SSL is not enabled on the server

I tried this URI for Go without success : psql postgresql://user:[email protected]:5432/lcp?sslmode=disable

Do you have any idea why i can't connect ? I tried with my aws rds postgres database, and got same result. Thnaks for the help.

complete code of the server https://github.com/readium/readium-lcp-server/blob/master/lcpserver/lcpserver.go

I change the typo and the demo provided i succeed the connexion. But in the lcp server i style got the same issue.

postgres://user:[email protected]:5432/lcp?sslmode=disable

EDIT 2:

The error is due to the fact that the script tries prepare command. I update the minimal example and it fails too.

package main

import (
    "database/sql"
    "fmt"
    "strings"

    _ "github.com/lib/pq"
)

func dbFromURI(uri string) (string, string) {
    parts := strings.Split(uri, "://")
    return parts[0], parts[1]
}

func main() {
    driver, cnxn := dbFromURI("postgres://user:[email protected]:5432/lcp?sslmode=disable")

    fmt.Println("The driver " + driver)
    fmt.Println("The cnxn " + cnxn)

    db, err := sql.Open(driver, cnxn)

     _, err = db.Prepare("SELECT id,encryption_key,location,length,sha256,type FROM content WHERE id = ? LIMIT 1")
    if err != nil {
         fmt.Println("Prepare failed")
         fmt.Println(err)

    }

   if err == nil {
        fmt.Println("Successfully connected")
    } else {
        panic(err)
    }
}

I got : prepare failed

pq: SSL is not enabled on the server
2021/03/07 17:20:13 This panic 3
panic: pq: SSL is not enabled on the server

Solution

The first problem is a typo in the connection string: postgresql://user:[email protected]:5432/lcp?sslmode=disable. In Go code it should be postgres://user:[email protected]:5432/lcp?sslmode=disable.

We also need to pass the full connection string as the second argument to sql.Open. For now, the dbFromURI function returns user:[email protected]:5432/lcp?sslmode=disable, but we need postgres://user:[email protected]:5432/lcp?sslmode=disable, because pq is waiting for this prefix to parse it.

After fixing this, I was able to establish a connection using a minimal postgres client based on your code.

To try this yourself, start the server with the following command:

docker run --rm -p 5432:5432 -e POSTGRES_PASSWORD=some_password postgres

And try to connect using the following client code:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/lib/pq"
)

func main() {
    cnxn := "postgres://postgres:[email protected]:5432/lcp?sslmode=disable"

    _, err := sql.Open("postgres", cnxn)
    if err != nil {
        panic(err)
    }

    _, err = db.Prepare("SELECT id,encryption_key,location,length,sha256,type FROM content WHERE id = ? LIMIT 1")
    if err != nil {
         fmt.Println("Prepare failed")
         panic(err)
    }
}