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

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.

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

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.

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; 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.)

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services