PostgreSQL10:新增 pg_hba_file_rules 视图

PostgreSQL10 新增pg_hba_file_rules视图,根据视图名字大概能猜到这个视图的内容,的确,此视图显示 $PGDATA/pg_hba.conf 文件中的内容,pg_hba.conf 中每一条正常或异常的策略在此视图中都会保存一行记录。

pg_hba_file_rules 视图一个重要的意义是能够快速诊断 pg_hba.conf 文件中的错误,以下会做测试。

pg_hba.conf 文件内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# TYPE DATABASE    USER      ADDRESS         METHOD

# "local" is for Unix domain socket connections only
#local francs francs md5
#host francs francs 127.0.0.1/32 md5

local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication repuser 20.26.28.74/32 md5
host replication repuser 20.26.28.75/32 md5
host replication repuser 20.26.28.76/32 md5
host all repuser 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5

备注: pg_hba.conf 中有7条策略。

查看 pg_hba_file_rules

1
2
3
4
5
6
7
8
9
10
11
postgres=# select line_number,type,database,user_name,address,netmask,auth_method,error from pg_hba_file_rules ;
line_number | type | database | user_name | address | netmask | auth_method | error
-------------+-------+---------------+-----------+-------------+-----------------+-------------+-------
87 | local | {all} | {all} | | | trust |
89 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust |
93 | host | {replication} | {repuser} | 20.26.28.74 | 255.255.255.255 | md5 |
94 | host | {replication} | {repuser} | 20.26.28.75 | 255.255.255.255 | md5 |
95 | host | {replication} | {repuser} | 20.26.28.76 | 255.255.255.255 | md5 |
96 | host | {all} | {repuser} | 0.0.0.0 | 0.0.0.0 | md5 |
97 | host | {all} | {all} | 0.0.0.0 | 0.0.0.0 | md5 |
(7 rows)

备注:刚好 pg_hba_file_rules 也有7条策略,内容和 pg_hba.conf 一致。接下来计划在 pg_hba.conf 文件中新增一条错误的策略。

pg_hba.conf 测试

pg_hba.conf 文件末尾增加以下内容

1
host  all   all 0.0.0.0 md5

备注:IP字段故意没写掩码。

重新载入 pg_hba.conf

1
2
[pg10@csv-tfcs01 ~]$ pg_ctl reload
server signaled

备注:pg_ctl reload 操作并没有返回异常信息,个人认为这里不太友好,需要到日志里查看 pg_hba.conf 中的策略是否填写正确。

查看 pg_log

1
2
3
2017-06-07  20:42:13.459 CST,,,5862,,592fceaf.16e6,7,,2017-06-01  16:22:07 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,""
2017-06-07 20:42:13.460 CST,,,5862,,592fceaf.16e6,8,,2017-06-01 16:22:07 CST,,0,LOG,F0000,"invalid IP mask ""md5"": Name or service not known",,,,,"line 98 of configuration file ""/database/pg10/pg_root/pg_hba.conf""",,,,""
2017-06-07 20:42:13.460 CST,,,5862,,592fceaf.16e6,9,,2017-06-01 16:22:07 CST,,0,LOG,00000,"pg_hba.conf was not reloaded",,,,,,,,,""

备注: pg_hba.conf 文件第98 行有报错信息。

查看 pg_hba_file_rules

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=# select line_number,type,database,user_name,address,netmask,auth_method,error from pg_hba_file_rules ;
line_number | type | database | user_name | address | netmask | auth_method | error
-------------+-------+---------------+-----------+-------------+-----------------+-------------+------------------------------------
--------------
87 | local | {all} | {all} | | | trust |
89 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust |
93 | host | {replication} | {repuser} | 20.26.28.74 | 255.255.255.255 | md5 |
94 | host | {replication} | {repuser} | 20.26.28.75 | 255.255.255.255 | md5 |
95 | host | {replication} | {repuser} | 20.26.28.76 | 255.255.255.255 | md5 |
96 | host | {all} | {repuser} | 0.0.0.0 | 0.0.0.0 | md5 |
97 | host | {all} | {all} | 0.0.0.0 | 0.0.0.0 | md5 |
98 | | | | | | | invalid IP mask "md5": Name or serv
ice not known
(8 rows)

备注:pg_hba_file_rules 增加了一条line_number=98记录, line_number 字段显示错误策略所在 pg_hba.conf 文件中的行号,error 字段显示具体错误信息。

注意

  1. pg_hba_file_rules 只有超级用户才有读的权限;
  2. pg_hba.conf 异常的策略在 pg_hba_file_rules 视图中只有 line_number,error 两个字段有信息。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%