ubuntu 上安装 postgresql 时 出现的错误:psql: error: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:FATAL: password authentication failed for user “xxxxx”

安装postgresql:postgresql

1. 安装

依次按下面的命令执行…

# Create the file repository configuration:sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'# Import the repository signing key:wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -# Update the package lists:sudo apt-get update# Install the latest version of PostgreSQL.# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':sudo apt-get -y install postgresql

2. 确认postgresql是否正确安装

sudo service postgresql status

如果出现像这样的，说明就成功运行了

Active: active (exited) since Wed 2024-03-13 15:44:00 CST; 17min ago

tips: 如果你运行起来了，请看第3步

如果没有启动，请运行：

sudo service postgresql start

3. 使用默认用户和密码登录：

sudo -u postgres psql

设置密码：

# 注意：postgres 是你的用户名，最后的your_password写你自己的密码ALTER USER postgres WITH PASSWORD 'your_password';

4. 退出psql

/q

5. 登录：

# 注意：这里的postgres是我刚才创建的用户名，如果你不是创建的这个，请换成自己的用户名psql -U postgres -W

然后输入你自己的密码

报错 psql: error: 连接到套接字"/var/run/postgresql/.s.PGSQL.5432"上的服务器失败:FATAL: password authentication failed for user “xxxxx”

1. 编辑 pg_hba.conf 文件：

# 注意把<version>换成自己的版本sudo gedit /etc/postgresql/<version>/main/pg_hba.conf

2. 为普通用户添加认证规则：(最好放在最下面的一段的最上面)

# TYPE  DATABASE        USER            ADDRESS                 METHODlocal   all             all                                     md5

这是我的pg_hba.conf完整配置

# PostgreSQL Client Authentication Configuration File# ===================================================## Refer to the "Client Authentication" section in the PostgreSQL# documentation for a complete description of this file.  A short# synopsis follows.## This file controls: which hosts are allowed to connect, how clients# are authenticated, which PostgreSQL user names they can use, which# databases they can access.  Records take one of these forms:## local         DATABASE  USER  METHOD  [OPTIONS]# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]## (The uppercase items must be replaced by actual values.)## The first field is the connection type:# - "local" is a Unix-domain socket# - "host" is a TCP/IP socket (encrypted or not)# - "hostssl" is a TCP/IP socket that is SSL-encrypted# - "hostnossl" is a TCP/IP socket that is not SSL-encrypted# - "hostgssenc" is a TCP/IP socket that is GSSAPI-encrypted# - "hostnogssenc" is a TCP/IP socket that is not GSSAPI-encrypted## DATABASE can be "all", "sameuser", "samerole", "replication", a# database name, or a comma-separated list thereof. The "all"# keyword does not match "replication". Access to replication# must be enabled in a separate record (see example below).## USER can be "all", a user name, a group name prefixed with "+", or a# comma-separated list thereof.  In both the DATABASE and USER fields# you can also write a file name prefixed with "@" to include names# from a separate file.## ADDRESS specifies the set of hosts the record matches.  It can be a# host name, or it is made up of an IP address and a CIDR mask that is# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that# specifies the number of significant bits in the mask.  A host name# that starts with a dot (.) matches a suffix of the actual host name.# Alternatively, you can write an IP address and netmask in separate# columns to specify the set of hosts.  Instead of a CIDR-address, you# can write "samehost" to match any of the server's own IP addresses,# or "samenet" to match any address in any subnet that the server is# directly connected to.## METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".# Note that "password" sends passwords in clear text; "md5" or# "scram-sha-256" are preferred since they send encrypted passwords.## OPTIONS are a set of options for the authentication in the format# NAME=VALUE.  The available options depend on the different# authentication methods -- refer to the "Client Authentication"# section in the documentation for a list of which options are# available for which authentication methods.## Database and user names containing spaces, commas, quotes and other# special characters must be quoted.  Quoting one of the keywords# "all", "sameuser", "samerole" or "replication" makes the name lose# its special character, and just match a database or username with# that name.## This file is read on server startup and when the server receives a# SIGHUP signal.  If you edit the file on a running system, you have to# SIGHUP the server for the changes to take effect, run "pg_ctl reload",# or execute "SELECT pg_reload_conf()".## Put your actual configuration here# ----------------------------------## 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# 其实最主要的就是下面这一句：之前这个md5是peer，所以你需要把peer改成md5local   all             postgres                                md5# TYPE  DATABASE        USER            ADDRESS                 METHODlocal   all             all                                     md5# “local”仅用于Unix域套接字连接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                                     peerhost    replication     all             127.0.0.1/32            scram-sha-256host    replication     all             ::1/128                 scram-sha-256

3. 保存并重启psql

sudo service postgresql restart

4. 连接psql

psql -U postgres -W

5. 查看所有数据库：

/l

总结：我出错的问题就是默认的peer改成md5就可以了，我也问了一下 chat.gpt ：peer和md5有什么不同？为什么改成md5就好了？
gpt:

当连接到 PostgreSQL 数据库时，需要进行身份验证以确定连接是否被允许。在 PostgreSQL 中，有几种不同的身份验证方法，其中两种常见的方法是 “peer” 和 “md5”。

Peer 认证：

在 “peer” 认证方法中，PostgreSQL 使用操作系统的用户身份验证信息来进行连接。这意味着，如果连接到数据库的操作系统用户与 PostgreSQL 数据库中的相应数据库用户同名，并且具有操作系统用户的访问权限，那么连接就会被允许。

这种方法的优点是简单且安全，因为它利用了操作系统的用户管理机制。但它也有限制，例如它仅适用于本地连接，并且依赖于操作系统用户的身份验证信息。

MD5 认证：

在 “md5” 认证方法中，用户必须提供用户名和密码才能连接到数据库。密码经过加密后存储在数据库中，并与用户提供的密码进行比较以进行验证。

这种方法允许用户在远程连接时提供用户名和密码，并且不受操作系统用户身份验证的限制。它提供了更灵活的认证方式，并且适用于远程连接。

因此，主要区别在于：

“Peer” 认证使用操作系统的用户身份验证信息，适用于本地连接，简单且安全。
“MD5” 认证要求用户提供用户名和密码，并且适用于远程连接，提供了更灵活的认证方式。
根据你的需求和安全要求，你可以选择使用适合你的认证方法。通常情况下，对于远程连接，推荐使用 “MD5” 认证方法。

那我为什么使用peer的时候失败呢，使用了md5就可以了?

当你使用 “peer” 认证方法时，PostgreSQL 会尝试使用操作系统的用户身份验证信息来进行连接。连接失败可能有几种原因：

用户名不匹配：如果连接到 PostgreSQL 的操作系统用户与数据库中的用户不匹配，即使密码正确，连接也会失败。确保你的操作系统用户名与 PostgreSQL 中的用户名匹配。

权限不足：即使用户名匹配，如果操作系统用户没有足够的权限来连接到数据库，也会导致连接失败。确保你的操作系统用户具有连接到数据库的权限。

规则不正确：在 pg_hba.conf 文件中，“peer” 认证方法需要正确的规则以允许连接。如果规则配置不正确，连接也会失败。确保你的 pg_hba.conf 文件中有允许使用 “peer” 认证方法连接的正确规则。

使用 “MD5” 认证方法时，你需要提供用户名和密码，因此连接可能更加灵活并且不受操作系统用户身份验证的限制。如果你成功地使用 “MD5” 认证方法连接到数据库，但无法使用 “peer” 认证方法连接，那么可能是 “peer” 认证方法受到了上述原因之一的限制。