在postgressql内部修改pg_hba.conf

查看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=# 

虽然可以在postgresql内部可以查看，但是并不能直接在postgresql修改该文件。当然，如果你有操作系统权限，可以在os中直接修改。

 

我们来看看，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=# 

 

不过，这种方式建议谨慎使用！