暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

PostgreSQL 会话ssl状态查询 - pg_stat_ssl , sslinfo

digoal 2019-09-08
1959

作者

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

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议,奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等,奖品丰富,快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.

digoal's wechat

文章转载自digoal,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论