ssl - 如何在 postgreSQL-11 的 pg_hba.conf 中正确设置 SSL？

Question

在带有 postgreSQL-11 的 Ubuntu 18.04.02 服务器版中，我想在 postgreSQL 服务器和客户端之间添加 SSL 通信的可能性。

我在 postgresql.conf 中打开了 ssl：

须藤纳米/etc/postgresql/11/main/postgresql.conf

ssl = on

在 pg_hba.conf 我为 ssl 添加了一行：

须藤纳米/etc/postgresql/11/main/pg_hba.conf

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            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

host    all             all             192.168.1.1/24          md5

# Only for SSL connections :
hostssl all             all                                     cert

但是在重新加载新配置并重新启动 postgreSQL 时：

(base) marco@pc:~$ sudo /etc/init.d/postgresql reload
[ ok ] Reloading postgresql configuration (via systemctl):   
postgresql.service.
(base) marco@pc:~$ sudo service postgresql restart
(base) marco@pc:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;  
vendor preset: enabled)
   Active: active (exited) since Mon 2019-09-16 18:48:13 CEST; 6s 
ago
  Process: 3349 ExecReload=/bin/true (code=exited, status=0/SUCCESS)
  Process: 3399 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 3399 (code=exited, status=0/SUCCESS)

Sep 16 18:48:13 pc systemd[1]: Starting PostgreSQL RDBMS...
Sep 16 18:48:13 pc systemd[1]: Started PostgreSQL RDBMS.

我收到此错误：

(base) marco@pc:~$ sudo su -l postgres postgres@pc:~$ psql psql: 无法连接到服务器: 没有这样的文件或目录服务器是否在本地运行并接受 Unix 域套接字上的连接 "/var/运行/postgresql/.s.PGSQL.5432”？

我已经尝试过在这里找到的建议：https: //askubuntu.com/questions/50621/cannot-connect-to-postgresql-on-port-5432 没有任何成功

在 pg_hba.conf 中注释 hostssl 行时：

# Only for SSL connections :
#hostssl all             all      cert

错误消失：

(base) marco@pc:~$ sudo /etc/init.d/postgresql reload
[ ok ] Reloading postgresql configuration (via systemctl):   
postgresql.service.
(base) marco@pc:~$ sudo service postgresql restart
(base) marco@pc:~$ sudo service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor preset: enabled)
   Active: active (exited) since Mon 2019-09-16 18:52:37 CEST; 3s 
ago
  Process: 3455 ExecReload=/bin/true (code=exited, status=0/SUCCESS)
  Process: 3511 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 3511 (code=exited, status=0/SUCCESS)

Sep 16 18:52:37 pc systemd[1]: Starting PostgreSQL RDBMS...
Sep 16 18:52:37 pc systemd[1]: Started PostgreSQL RDBMS.
(base) marco@pc:~$ sudo su -l postgres
postgres@pc:~$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=# 

所以，我的问题是：如何在 /etc/postgresql/11/main/pg_hba.conf 中正确设置 ssl？

已解决：在 pg_hba.conf 中添加 IP 地址范围：

hostssl all             all             192.168.1.0/24          cert

标签： sslubuntu-18.04postgresql-11pg-hba.conf