postgresql安装后链接报错psql: FATAL: Ident authentication failed for user “postgres“

最新推荐文章于 2024-09-28 16:44:33 发布

原创最新推荐文章于 2024-09-28 16:44:33 发布 · 2.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库 #database

本文介绍了解决 PostgreSQL 在本地和远程连接时出现的 Ident 认证失败问题的方法。通过修改 pg_hba.conf 文件中的认证方式，并重启 PostgreSQL 服务，最终实现了正常连接。

postgresql 安装成功后，使用链接语句链接

[root@iZbp1hdum7hefdeb75mnyfZ ~]# psql -d postgres -U postgres -W
Password for user postgres: 
psql (9.6.24)
Type "help" for help.

postgres=# 
postgres=# 
postgres=#

这样链接并没有报错，但是这样登录就会报错误

[root@iZbp1hdum7hefdeb75mnyfZ ~]# psql -hlocalhost -d postgres -U postgres -W
Password for user postgres: 
psql: FATAL:  Ident authentication failed for user "postgres"
[root@iZbp1hdum7hefdeb75mnyfZ ~]# psql -h127.0.0.1 -d postgres -U postgres -W
Password for user postgres: 
psql: FATAL:  Ident authentication failed for user "postgres"

Navicat的远程链接也会报错Ident authentication failed for user “postgres”

修改

vim /var/lib/pgsql/9.6/data/pg_hba.conf

将IPv6等注释掉


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                md5
# IPv4 local connections:
#host    all             all             127.0.0.1/32            ident
host    all             all             0.0.0.0/0               md5
host    replication     replica         10.29.0.0/16            md5

# IPv6 local connections:
#host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

重新启动postgresql

systemctl restart postgresql-9.6.service

再次链接就好用了

[root@iZbp1hdum7hefdeb75mnyfZ ~]# psql -h localhost -p 5432 -U postgres
Password for user postgres: 
psql (9.6.24)
Type "help" for help.

postgres=#