postgresql14测试用户连接报错 psql: error: connection to server on Peer authentication failed for user “guoh“

1、问题

postgresql14测试用户连接报错 psql: error: connection to server on Peer authentication failed for user "guoh"

2、解决方案

在Ubuntu 22.04上使用PostgreSQL时，如果遇到了“Peer authentication failed for user 'guo121'”这样的错误，这通常意味着PostgreSQL的认证方式配置为peer或ident，而系统无法通过这些方式确认用户的身份。这种情况经常发生在通过非本地连接（例如，通过网络连接数据库）时。

以下是解决此问题的几个步骤：

2.1、确认PostgreSQL的认证方式

首先，你需要检查PostgreSQL的pg_hba.conf文件，看看对于你的用户或数据库是如何配置认证方式的。这个文件通常位于/etc/postgresql/14/main/（版本号可能会不同，根据你的PostgreSQL版本而定）。使用以下命令打开pg_hba.conf文件：

guoh@ubuntuguoh:~$ sudo nano /etc/postgresql/14/main/pg_hba.conf

打开pg_hba.conf的文件内容如下所示。

 GNU nano 7.2                                         /etc/postgresql/14/main/pg_hba.conf                                                  
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.




# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

2.2、修改pg_hba.conf文件内容

修改pg_hba.conf文件内容如下，修改完输入Ctrl+O ,Enter，Ctrl+X命令保存并退出。

  GNU nano 7.2                                         /etc/postgresql/14/main/pg_hba.conf *                                                
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.




# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0            scram-sha-256
# IPv6 local connections:
host    all             all             ::0/0                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             0.0.0.0/0              scram-sha-256
host    replication     all             ::0/0                 scram-sha-256

2.3、重启postgresql服务

修改配置后，需要重启PostgreSQL服务确保更改生效。

# 重启postgresql服务
sudo systemctl restart postgresql

2.4、确认postgresql监听设置

确保PostgreSQL配置为监听来自外部的连接。检查postgresql.conf文件中的listen_addresses设置。

# 打开postgresql.conf文件
sudo nano /etc/postgresql/14/main/postgresql.conf

修改以下内容

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories

# - SSL -

#ssl = on
ssl = off
#ssl_ca_file = ''

修改完输入Ctrl+O ,Enter，Ctrl+X命令保存并退出。

2.5、重启postgresql服务

修改配置后，需要重启PostgreSQL服务确保更改生效。

# 重启postgresql服务
sudo systemctl restart postgresql

2.6、使用正确的用户和密码测试连接

确保你使用正确的用户名和密码从客户端连接到数据库。例如，使用psql命令：

psql -h [hostname] -p [portnumber] -U [yourusername] -d [databasename] -W

其中[hostname]是你的服务器地址，[portnumber]是数据库访问的端口号， [yourusername]是访问数据库的用户名，[databasename]是数据库名。系统会提示你输入密码。

guoh@ubuntuguoh:~$ psql -h 127.0.0.1 -p 5432 -U guoh -d mydb -W
Password: 
psql (14.19 (Ubuntu 14.19-1.pgdg24.04+1))
Type "help" for help.

mydb=#

出现“mydb=# ”说明数据库登录成功，即解决“Peer authentication failed for user 'guo121'”的问题。如果问题仍然存在，检查网络设置和防火墙规则是否允许从你的客户端IP地址到服务器的数据库端口（默认是5432）。