Categories
Uncategorized

Checking pg_hba.conf content using SQL

Creating new users and granting database access is a common DBA’s task. But unless you have a very permissive network access policy like “all all 0.0.0.0/0 md5”, you may sometimes be unsure if current HBA rules are sufficient for newly created user to get access (and not return to you with angry “it doesn’t work!” instead). You have to leave psql / pgAdmin / another-tool of choice, and check the pg_hba.conf file content, right? Well, with PostgreSQL 10 or newer, you don’t have to. Just run:

SELECT * FROM pg_hba_file_rules;

and you’ll see all the rules in a tabular format.

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 | md5 | |
88 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
91 | local | {replication} | {all} | | | trust | |
92 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | |
93 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | |
94 | host | {savannah} | {elephant} | 0.0.0.0 | 0.0.0.0 | md5 | |
(7 rows)