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

On Sat, Dec 5, 2015 at 3:31 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> Haribabu Kommi wrote:
>
>> 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)
>
> What did you do to the whitespace when posting that table? I had to
> reformat it pretty heavily to understand what you had.
> Anyway, I think the "mode" column should be right after the line number.
> I assume the "reason" for skipped lines is going to be somewhere in the
> table too.

when i try to copy paste the output from psql, it didn't come properly, so
I adjusted the same to looks properly, but after sending mail, it look ugly.

Added reason column also as the last column of the table and moved the mode
as the second column.

> What happens if a "reject" line is matched? I hope the lookup
> would terminate there.

whenever any line matches with the given arguments, the function stops
processing further lines.

> What does it mean to query for "all"? Do you have database and user
> named "all"? Because otherwise that seems wrong to me; you should be
> able to query for specific databases/users, but not for special
> keywords; maybe I am wrong and there is a use case for this, in which
> case please state what it is.

The 'all' is just passed as a database and user name. In my configuration
I just put every database to match. so just for a test i did that way. There is
no special handling for keywords.

> I see three problems in your code. One is that the translation of
> auth_method enum to text should be a separate function, not the SQL
> function layer;

Moved into a different function.

>another is that the code to put keywords as JSON object
> values is way too repetitive; the other is that messing with the JSON
> API like that is not nice. (I don't think we're closed to doing that,
> but that would be a separate discussion). I think this patch should
> just use the "push value" interface rather than expose add_jsonb.
>
> (I assume the usage of JSON rather than a regular array was already
> discussed and JSON was chosen for some reason.)

Repetitive jsonb object code is moved into a function and used those functions.
Changed all jsonb calls into push value functions.

Regards,
Hari Babu
Fujitsu Australia