Re: pg_hba_lookup function to get all matching pg_hba.conf entries

On Fri, Dec 4, 2015 at 8:05 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>> >> Here I attached the patch with the suggested changes.
>> >> Along with line number, I kept the options column also with authentication
>> >> options as a jsonb datatype.
>> >>
>> >> Example output:
>> >>
>> >> postgres=# select pg_hba_lookup('test','all','::1');
>> >> NOTICE:  Skipped 84 Hba line, because of non matching IP.
>> >> NOTICE:  Skipped 86 Hba line, because of non matching database.
>> >> NOTICE:  Skipped 87 Hba line, because of non matching role.
>> >>  pg_hba_lookup
>> >> ---------------
>> >>  (89,trust,{})
>> >> (1 row)
>> >>
>> >> comments?
>
> I don't like this interface.  It's nice for psql, but everybody else is
> going to lose.  I think these should be reported in the SRF result set
> as well; perhaps add a "mode" column that says "skipped" for such rows,
> and "matched" for the one that, uh, matches.  (Please try calling your
> function with "select * from" which should give nicer output.)
>

How about as follows?

postgres=# select * from pg_hba_lookup('all','all','::1');
 line_number | type  | database |  user   |  address  | hostname |
method | options |  mode
-------------+-------+----------+---------+-----------+----------+--------+---------+---------
          84       | local  | ["all"]        | ["all"]   |
    |                 | trust      | {}          | skipped
          86       | host   | ["all"]        | ["all"]   | 127.0.0.1 |
                | trust      | {}          | skipped
          88       | host   | ["all"]        | ["all"]   | ::1
   |                 | trust      | {}          | matched
(3 rows)

In the above case, all the columns are displayed. Based on the
feedback we can keep
the required columns. I didn't yet removed the NOTICE messages in the
attached version.
Are they still required?

Regards,
Hari Babu
Fujitsu Australia