Re: pg_hba_lookup function to get all matching pg_hba.conf entries - Mailing list pgsql-hackers

Hi Hackers,

This is the patch adds a new function called pg_hba_lookup to get all
matching entries
from the pg_hba.conf for the providing input data.The rows are
displayed from the other
the hba conf entries are matched.

This is an updated version of previous failure attempt to create a
catalog view for the
pg_hba.conf [1]. The view is not able to handle the SQL queries properly because
keywords that are present in database and user columns.


currently the following two types are added:

pg_hba_lookup(database, user)
pg_hba_lookup(database, user, address, hostname)

How it works:

With the provided input data, it tries to match the entries of
pg_hba.conf and populate the
result set with all matching entries.

With the recent Tomlane's commit
1e24cf645d24aab3ea39a9d259897fd0cae4e4b6 of "Don't leave pg_hba and
pg_ident data lying around in running backends" [2], the parsed hba
conf entries are not available in the backend side. Temporarily I just
reverted this patch for the
proof of concept purpose. Once we agree with the approach, I will try
to find out a solution
to the same.


How is it useful:

With the output of this view, administrator can identify the lines
that are matching for the given
criteria easily without going through the file.


Record format:

Column name | datatype
-------------------------------
line_number | int4
type              | text
database      | jsonb
user              | jsonb
address        | inet
hostname     | text
method         | text
options          | jsonb

Please suggest me for any column additions or data type changes that
are required.


Example output:

postgres=# select pg_hba_lookup('postgres','all');
                     pg_hba_lookup
-------------------------------------------------------
 (84,local,"[""all""]","[""all""]",,,trust,{})
 (86,host,"[""all""]","[""all""]",127.0.0.1,,trust,{})
 (88,host,"[""all""]","[""all""]",::1,,trust,{})

Here I attached a proof of concept patch for the same.

Any suggestions/comments on this proposed approach?

[1] http://www.postgresql.org/message-id/F40B0968DB0A904DA78A924E633BE78645FE29@SYDEXCHTMP2.au.fjanz.com

[2] http://www.postgresql.org/message-id/E1ZAQuy-00072J-7G@gemulon.postgresql.org

Regards,
Hari Babu
Fujitsu Australia