1

From code

The code bellow outputs the following:

2022/06/21 16:01:07 Failed to connect to db: failed to connect to 'host=localhost user=postgres database=local': server error (FATAL: Ident authentication failed for user "postgres" (SQLSTATE 28000)) exit status 1

import (
    "context"
    "log"

    "github.com/jackc/pgx/v4"
)

func main() {
    dbCtx := context.Background()
    db, err := pgx.Connect(
        dbCtx, 
        "postgres://postgres:smashthestate@localhost:5432/local",
    )
    if err != nil {
        log.Fatalf("Failed to connect to db: %v\n", err)
    }
    defer db.Close(dbCtx)
    
    // do stuff with db...
}

From terminal

However, connection to db is possible from terminal. For example, this command if run with the same parameters (db name, user name, password) will give correct output:

psql -d local -U postgres -W -c 'select * from interest;'

Notes

  1. Command works correctly even if sent by user that is neither root nor postgres.
  2. Authentication method for local connection is set to trust inside pg_hba.conf:
local   all             all                                     trust

So, what am I missing here? Why everything works fine from the command line but doesn't work from code?

mcv_dev
  • 338
  • 3
  • 14
  • 1
    Seems like a permission problem. Try to create another user in DB, flush all privileges then try to connect. – Nairi Abgaryan Jun 21 '22 at 12:28
  • @NairiAbgaryan Found a time to check it today. It didn't help. – mcv_dev Jun 21 '22 at 14:20
  • 1
    Try `psql` with `-h localhost` for a true comparison. The line in `pga_hba.conf` is irrelevant [because `local`](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html) "matches connection attempts using Unix-domain sockets" and your go app is connecting via TCP not unix sockets. – Brits Jun 21 '22 at 22:32
  • @Brits It worked. Indeed it will not auth me with `-h localhost`. Am I correct to assume that line related to ipv4 connections that I edited out is more relevant in this case? – mcv_dev Jun 22 '22 at 11:45
  • 1
    I don't know what you commented out; you will need a `host` line in your `pg_hba.conf` to allow connections - e.g. `host all all 0.0.0.0/0 scram-sha-256` (but you should tune this to maintain security). – Brits Jun 22 '22 at 20:14

1 Answers1

1

Go's defaults are different from psql's. If no hostname is given, Go defaults to using localhost, while psql defaults to using the Unix domain sockets.

To specify a Unix domain socket to Go, you need to do it oddly to get it to survive URI validation:

postgres://postgres:smashthestate@:5432/local?host=%2Ftmp

Though the end might need to be more like ?host=%2Fvar%2Frun%2Fpostgresql, depending on how the server was configured.

But why are you specifying a password which is not needed? That is going to cause pointless confusion.

jjanes
  • 37,812
  • 5
  • 27
  • 34