英文:
Can't connect to PostgreSQL database using pgx driver but can using terminal
问题
以下是翻译的内容：
从代码中
下面的代码输出如下内容：
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)
// 使用数据库进行操作...
}
从终端中
然而，从终端连接到数据库是可行的。例如，使用相同的参数（数据库名称、用户名、密码）运行以下命令将给出正确的输出：
psql -d local -U postgres -W -c 'select * from interest;'
注意事项
- 即使由既不是
root也不是postgres的用户发送，该命令也能正常工作。 - 本地连接的身份验证方法在
pg_hba.conf中设置为trust：
local all all trust
那么，我在这里漏掉了什么？为什么从命令行一切正常，但从代码中无法工作？
英文:
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
- Command works correctly even if sent by user that is neither
rootnorpostgres. - Authentication method for local connection is set to
trustinsidepg_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?
答案1
得分: 1
Go的默认设置与psql的默认设置不同。如果没有提供主机名，Go默认使用localhost，而psql默认使用Unix域套接字。
要指定一个Unix域套接字给Go，你需要以奇怪的方式来使其通过URI验证：
postgres://postgres:smashthestate@:5432/local?host=%2Ftmp
尽管最后可能需要更像?host=%2Fvar%2Frun%2Fpostgresql，这取决于服务器的配置方式。
但是为什么要指定一个不需要的密码呢？这只会导致无谓的混淆。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论