This short blog to share a helpful stuff with the pg_hba.conf file :<\/p>\n
The pg_hba.conf file is the central configuration file to control client authentication. When you add or modify an entry in this file, you have to reload the cluster to take the changes in account : Oops… Have you seen my mistake ? Probably not. The system didn’t see it either. In Postgres 10 there is now a new view called “pg_hba_file_rules” which will returns the current content of the pg_hba.conf file entries AND reports the errors : This will allows you to get a quick look at what is wrong in pg_hba.conf and fixing it as necessary.<\/p>\n","protected":false},"excerpt":{"rendered":" This short blog to share a helpful stuff with the pg_hba.conf file : The pg_hba.conf file is the central configuration file to control client authentication. It is located in the database cluster’s data directory : postgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1] ll pg_hba.conf lrwxrwxrwx. 1 postgres postgres 59 Jun 30 10:19 pg_hba.conf postgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1] When you add or modify […]<\/p>\n","protected":false},"author":30,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[1127,1128,1129,570,1108],"type_dbi":[],"class_list":["post-10293","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-beta","tag-pg_hba","tag-pg_hba_file_rules","tag-postgres","tag-postgresql-10"],"acf":[],"yoast_head":"\n
\nIt is located in the database cluster’s data directory :
\npostgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1] ll pg_hba.conf
\nlrwxrwxrwx. 1 postgres postgres 59 Jun 30 10:19 pg_hba.conf
\npostgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1]
\n<\/code><\/p>\n
\n
\npostgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1] echo \"host all efm 192.168.22.38\/35 md5\" >> \/u02\/pgdata\/PG10BETA1\/pg_hba.conf
\npostgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1]
\npostgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1] pg_ctl -D \"\/u02\/pgdata\/PG10BETA1\/\" reload
\nserver signaled
\npostgres@ppas01:\/u02\/pgdata\/PG10BETA1\/ [PG10BETA1]
\n<\/code><\/p>\n
\nThat was in Postgres 9.6 and older versions.<\/p>\n
\npostgres=# select version();
\n version
\n-------------------------------------------------------------------------------------------------------------------------------
\n PostgreSQL 10beta1<\/span> dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
\n(1 row)
\n
\npostgres=# select line_number, error, type, database, user_name, address, netmask from pg_hba_file_rules<\/span>;
\n line_number | error | type | database | user_name | address | netmask
\n-------------+-------------------------------------------------+-------+---------------+-----------+-----------+-----------------------------------------
\n 80 | | local | {all} | {all} | |
\n 82 | | host | {all} | {all} | 127.0.0.1 | 255.255.255.255
\n 84 | | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
\n 87 | | local | {replication} | {all} | |
\n 88 | | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255
\n 89 | | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
\n 90 | invalid CIDR mask in address \"192.168.22.38\/35\"<\/span> | | | | |
\n(7 rows)
\n<\/code><\/p>\n