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

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                 METHOD local   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改成md5 local   all             postgres                                md5  # TYPE  DATABASE        USER            ADDRESS                 METHOD local   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                                     peer host    replication     all             127.0.0.1/32            scram-sha-256 host    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” 认证方法受到了上述原因之一的限制。