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

2015-12-04 5:33 GMT+01:00 Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>:

> On Fri, Dec 4, 2015 at 8:05 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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)
>

the tabular interface is better, and then NOTICEs are not necessary. I
like to see some info why row was skipped in the table.

Regards

Pavel

>
>
> 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
>