☰

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

tag-icon • tag-icon 18.04 postgresql ssl
如何在postgreSQL-11的pg_hba.conf中正确设置SSL？

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

我在 postgresql.conf 中打开了 ssl：

sudo nano /etc/postgresql/11/main/postgresql.conf

ssl = on

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

sudo nano /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.

我收到此错误：

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

我已经尝试过这里找到的建议：无法连接到端口 5432 上的 postgresql 没有任何成功

在注释 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

相关内容