【PostgreSQL】PostgreSQL开启ssl/tls
2022/7/10 2:21:46
本文主要是介绍【PostgreSQL】PostgreSQL开启ssl/tls,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
没有Cert/Key文件时开启SSL/TLS
来看看在没有证书或keys文件的场景下,开启SSL/TLS:
postgres=# show ssl; ssl ----- off (1 row) postgres=# alter system set ssl=on; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=#
修改过程没有任何报错,但是我们真的使用了SSL?如果我们查看错误日志,会看到以下错误信息:
2022-07-06 15:13:41.574 CST [1196] LOG: received SIGHUP, reloading configuration files 2022-07-06 15:13:41.575 CST [1196] LOG: parameter "ssl" changed to "on" 2022-07-06 15:13:41.576 CST [1196] LOG: could not load server certificate file "server.crt": No such file or directory 2022-07-06 15:13:41.576 CST [1196] LOG: SSL configuration was not reloaded
创建证书
因此,我们首先要创建上面的提到的文件。如果没有,可以创建一个,例如:
# cd /var/lib/pgsql/14/data # openssl req -nodes -new -x509 -keyout server.key -out server.crt -subj '/C=US/L=NYC/O=Percona/CN=postgres' Generating a 2048 bit RSA private key ....+++ .........................+++ writing new private key to 'server.key' -----
在创建之前,我们已经修改了当前目录到postgresql的数据目录。然后修改一下文件的权限和属主
# chmod 400 server.{crt,key} # chown postgres:postgres server.{crt,key} # ll server.{crt,key} -r--------. 1 postgres postgres 1212 Jul 6 20:12 20:49 server.crt -r--------. 1 postgres postgres 1704 Jul 6 20:12 20:49 server.key
开启SSL/TLS
postgres=# alter system set ssl=on; ALTER SYSTEM postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
这次就不会报错了:
2022-07-06 20:49:05.823 UTC [5284] LOG: received SIGHUP, reloading configuration files 2022-07-06 20:49:05.823 UTC [5284] LOG: parameter "ssl" changed to "on"
至此,我们已经开启了SSL。但是,我们还要配置文件pg_hba.conf。我们可以使用关键字”hostssl“来代替”host“强制postgresql使用ssl。”host“验证也可以看到有会话使用ssl,因为”host“支持使用ssl连接,但是不会强制。假设pg_hba.conf配置是这样的:
# TYPE DATABASE USER ADDRESS METHOD local all all peer host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 host all all 0.0.0.0/0 md5 host replication all 10.124.33.113/24 md5
如果希望所有远程连接都是用ssl连接,可以这样:
# TYPE DATABASE USER ADDRESS METHOD local all all peer host all all 127.0.0.1/32 scram-sha-256 host all all ::1/128 scram-sha-256 hostssl all all 0.0.0.0/0 md5 hostssl replication all 10.124.33.113/24 md5
同样,如果我们坚持真正强制连接以使用SSL,这还不够。我们必须再次调用pg_reload_conf()以确保它们被加载:
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
至此,远程非ssl连接会被拒绝。
# psql "host=192.168.56.111 sslmode=disable" psql: error: connection to server at "192.168.56.111", port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.56.112", user "postgres", database "postgres", no encryption
检查使用SSL/TLS的会话连接
postgres=# select pg_ssl.pid, pg_ssl.ssl, pg_ssl.version, pg_sa.backend_type, pg_sa.usename, pg_sa.client_addr from pg_stat_ssl pg_ssl join pg_stat_activity pg_sa on pg_ssl.pid = pg_sa.pid; pid | ssl | version | backend_type | usename | client_addr ------+-----+---------+----------------+----------+--------------- 5547 | f | | walsender | postgres | 192.168.56.12 5549 | f | | client backend | postgres | 192.168.56.11 5556 | f | | client backend | postgres | 192.168.56.12 (3 rows)
这里,复制连接walsender还没有使用ssl,其它两个会话也是。需要重启一下让他们重新建立连接。可以重启,也可以使用pg_terminate_backend(主节点或复制节点都可以)。例如:
postgres=# select pg_terminate_backend(5547); pg_terminate_backend ---------------------- t (1 row)
重新建立连接后就使用ssl了:
postgres=# select pg_ssl.pid, pg_ssl.ssl, pg_ssl.version, pg_sa.backend_type, pg_sa.usename, pg_sa.client_addr from pg_stat_ssl pg_ssl join pg_stat_activity pg_sa on pg_ssl.pid = pg_sa.pid; pid | ssl | version | backend_type | usename | client_addr ------+-----+---------+----------------+----------+--------------- 5557 | t | TLSv1.2 | walsender | postgres | 192.168.56.12 5549 | f | | client backend | postgres | 192.168.56.11 5556 | f | | client backend | postgres | 192.168.56.12 (3 rows)
pid 5549是psql自己,可以轻松解决:
postgres=# select pg_backend_pid(); pg_backend_pid ---------------- 5549 (1 row)
客户端可以通过 \conninfo检查自己的连接信息:
postgres=# \conninfo You are connected to database "postgres" as user "postgres" on host "192.168.56.11" at port "5432". SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
这篇关于【PostgreSQL】PostgreSQL开启ssl/tls的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-05快速清空 PostgreSQL 数据库中的所有表格,让你的数据库重新焕然一新!
- 2024-01-04在PostgreSQL中创建角色:判断角色是否存在并创建
- 2023-05-16PostgreSQL一站式插件推荐 -- pg_enterprise_views
- 2022-11-22PostgreSQL 实时位置跟踪
- 2022-11-22如何将PostgreSQL插件移植到openGauss
- 2022-11-11PostgreSQL:修改数据库用户的密码
- 2022-11-06Windows 环境搭建 PostgreSQL 物理复制高可用架构数据库服务
- 2022-10-27Windows 环境搭建 PostgreSQL 逻辑复制高可用架构数据库服务
- 2022-10-11PostgreSql安装(Windows10版本)
- 2022-09-13PostgreSQL-Network Address类型操作和函数