查看pg_hba.conf文件的内容:
postgres=# select * from pg_hba_file_rules; line_number | type | database | user_name | address | netmask | auth_method | options | error -------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+------- 84 | local | {all} | {all} | | | trust | | 86 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 88 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 91 | host | {all} | {all} | 27.0.0.0 | 255.0.0.0 | md5 | | 92 | local | {replication} | {all} | | | trust | | 93 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 94 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 95 | host | {all} | {all} | 27.0.0.0 | 255.0.0.0 | md5 | | (8 rows) postgres=#
我们来看看,pg_hba.conf文件在操作系统上位置:
postgres=# select setting from pg_settings where name like '%hba%'; setting ---------------------------------- /pgdata/11/data/pg_hba.conf (1 row) postgres=#
知道这些信息后,我们就可以将这些内容加载到表中:
postgres=# create table hba(lines text); CREATE TABLE postgres=# copy hba from '/pgdata/11/data/pg_hba.conf'; COPY 94 postgres=#
一旦加载到表中,我们就有了pg_hba.conf文件中的内容。
postgres=# select * from hba where lines !~ '^#' and lines !~ '^$'; lines ----------------------------------------------------------------------- local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust host all all 27.0.0.0/8 md5 local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust (7 rows) postgres=#
现在我们就可以按照常规表的方式插入记录了:
postgres=# insert into hba (lines) values ('host all mydb ::1/128 trust'); INSERT 0 1 postgres=# select * from hba where lines !~ '^#' and lines !~ '^$'; lines ----------------------------------------------------------------------- local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust host all all 27.0.0.0/8 md5 local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust host all mydb ::1/128 trust (8 rows) postgres=#
再把表的内容写回到文件中:
postgres=# copy hba to '/pgdata/11/data/pg_hba.conf'; COPY 95 postgres=#
读取文件,看看我们新加的规则是否已经在文件中了:
postgres=# select pg_read_file('pg_hba.conf'); ....
修改了配置后,需要reload一下方可生效:
postgres=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=#
不过,这种方式建议谨慎使用!