digoal
2019-09-08
PostgreSQL , pg_stat_ssl , sslinfo
PG 支持ssl会话。
《PostgreSQL 如何实现网络压缩传输或加密传输(openssl)》
阿里云rds pg可以通过控制台安全管理开启ssl连接。
客户端可以选择是否使用ssl连接，所以到底客户端连接时有没有使用ssl呢，在会话中如何判断？
关闭举例：
sslmode=disable
psql postgresql://digoal@xxx.xxx.xxx.xxx:5432/postgres?application_name=abc\&sslmode=disable
Password for user digoal:
psql (11.5, server 11.4)
Type "help" for help.
开启举例：
sslmode=prefer
psql postgresql://digoal@xxx.xxx.xxx.xxx:5432/postgres?application_name=abc\&sslmode=prefer
Password for user digoal:
psql (11.5, server 11.4)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
1、使用sslinfo插件。
postgres=> create extension sslinfo;
CREATE EXTENSION
postgres=> select * from ssl_is_used(), ssl_cipher();
ssl_is_used | ssl_cipher
-------------+-----------------------------
t | ECDHE-RSA-AES256-GCM-SHA384
(1 row)
2、查看pg_stat_ssl视图
postgres=> select * from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | clientdn
------+-----+---------+-----------------------------+------+-------------+----------
344 | f | | | | |
347 | f | | | | |
348 | f | | | | |
493 | f | | | | |
664 | f | | | | |
675 | f | | | | |
5559 | f | | | | |
5756 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f |
342 | f | | | | |
341 | f | | | | |
343 | f | | | | |
(11 rows)
postgres=> select * from pg_stat_ssl where pid=pg_backend_pid();
pid | ssl | version | cipher | bits | compression | clientdn
------+-----+---------+-----------------------------+------+-------------+----------
5756 | t | TLSv1.2 | ECDHE-RSA-AES256-GCM-SHA384 | 256 | f |
(1 row)
PG 12 增加客户端证书信息输出。
Add information about the client certificate to the system view pg_stat_ssl (Peter Eisentraut)
The new columns are client_serial and issuer_dn. Column clientdn has been renamed to client_dn, for clarity.
postgres=# select * from pg_stat_ssl;
pid | ssl | version | cipher | bits | compression | client_dn | client_serial | issuer_dn
-------+-----+---------+--------+------+-------------+-----------+---------------+-----------
14949 | f | | | | | | |
14952 | f | | | | | | |
14954 | f | | | | | | |
14947 | f | | | | | | |
14946 | f | | | | | | |
14948 | f | | | | | | |
(6 rows)
https://www.postgresql.org/docs/12/libpq-connect.html#LIBPQ-PARAMKEYWORDS
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
