[2022-03-16]posgresql连接提示psql: error: FATAL: no pg_hba.conf

新安装的数据库连接时提示如下错误：

[postgres@postgres data]$ psql -h 192.168.40.160 -p5432 -d postgres
psql: error: FATAL:  no pg_hba.conf entry for host "192.168.40.160", user "postgres", database "postgres", SSL off
[postgres@postgres data]$ 

出现该错误的原因是没有在pg_hba.conf 里面配置IP地址，pg_hba.conf默认的配置格式如下

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

# IPv4 local connections:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            trust

可以看到,默认可以连接的ADDRESS为127.0.0.1/32， 所以通过localhost是可以连接的

[postgres@postgres data]$ psql -hlocalhost -p5432
psql (12.10)
Type "help" for help.

postgres=# 

如果想指定某些IP或者某段范围IP可以连接，比如通过IP 192.168.40.160连接，并且DATABASE列为postgres数据库， 允许通过IP 192.168.40.160连接postgres数据库，添加如下内容

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

更改pg_hba.conf之后，需要重新启动数据库

[postgres@postgres data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-03-16 14:50:39.720 CST [15259] LOG:  starting PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
2022-03-16 14:50:39.721 CST [15259] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-03-16 14:50:39.721 CST [15259] LOG:  listening on IPv6 address "::", port 5432
2022-03-16 14:50:39.723 CST [15259] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-03-16 14:50:39.729 CST [15259] LOG:  redirecting log output to logging collector process
2022-03-16 14:50:39.729 CST [15259] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@postgres data]$

连接postgres数据库测试

[postgres@postgres data]$ psql -h 192.168.40.160 -p5432 -d postgres -U postgres
psql (12.10)
Type "help" for help.

postgres=# \q

连接其他数据库则会报错，测试如下：

[postgres@postgres data]$ psql -h 192.168.40.160 -p5432 -d postgres -U postgres
psql (12.10)
Type "help" for help.

postgres=# \h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/12/sql-createdatabase.html

postgres=# create database oracle;
CREATE DATABASE
postgres=# \q
[postgres@postgres data]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-03-16 15:05:16.653 CST [16534] LOG:  starting PostgreSQL 12.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
2022-03-16 15:05:16.654 CST [16534] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-03-16 15:05:16.654 CST [16534] LOG:  listening on IPv6 address "::", port 5432
2022-03-16 15:05:16.656 CST [16534] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-03-16 15:05:16.664 CST [16534] LOG:  redirecting log output to logging collector process
2022-03-16 15:05:16.664 CST [16534] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@postgres data]$
[postgres@postgres data]$ psql -h 192.168.40.160 -p5432 -d oracle -U postgres        
psql: error: FATAL:  no pg_hba.conf entry for host "192.168.40.160", user "postgres", database "oracle", SSL off
[postgres@postgres data]$