pg_hba_lookup function to get all matching pg_hba.conf entries

Hi

Any suggestions/comments on this proposed approach?

This is interesting functionality - The same was requested by one important
Czech customer.

I'll do review

Regards

Pavel

Hi

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?

If I understand well to your proposal, the major benefit is in
impossibility to enter pg_hba keywords - so you don't need to analyse if
parameter is keyword or not? It has sense, although It can be hard to do
image about pg_hba conf from these partial views.

There can be other way - theoretically we can have a function pg_hba_debug
with similar API like pg_hba_conf. The result will be a content of
pg_hba.conf with information about result of any rule.

Regards

Pavel

On Mon, Sep 7, 2015 at 4:34 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

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?

If I understand well to your proposal, the major benefit is in impossibility
to enter pg_hba keywords - so you don't need to analyse if parameter is
keyword or not? It has sense, although It can be hard to do image about
pg_hba conf from these partial views.

From the function output, it is little bit difficult to map the
pg_hba.conf file.
Because of problems in processing keywords in where clause of a view, I changed
from view to function.

Is there any possibility with rule or something, that the where clause
details can be passed
as function arguments to get the data?

There can be other way - theoretically we can have a function pg_hba_debug
with similar API like pg_hba_conf. The result will be a content of
pg_hba.conf with information about result of any rule.

The output of pg_hba_debug function looks like, the entry of
pg_hba.conf and the result
match for the given input data.

Regards,
Hari Babu
Fujitsu Australia

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi

2015-07-21 11:15 GMT+02:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

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)

After testing your prototype I am thinking so it is not good way. It is
hard to understand what is result of these functions.

-1 from me

Regards

Pavel

On 7/21/15 5:15 AM, Haribabu Kommi wrote:

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

How is this useful? I could see the use if you want to debug cases of
user foo on host bar says they can't connect, but you can't impersonate
them to verify it. But then all you need is a function with a scalar
result, not a result set.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Nov 16, 2015 at 2:30 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On 7/21/15 5:15 AM, Haribabu Kommi wrote:

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

How is this useful? I could see the use if you want to debug cases of
user foo on host bar says they can't connect, but you can't impersonate
them to verify it. But then all you need is a function with a scalar
result, not a result set.

Do you mean the function should return true or false based on the connection
status with the provided arguments?

I also feel difficult to understand the function result as compared to a view.

Regards,
Hari Babu
Fujitsu Australia

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 11/16/15 2:37 AM, Haribabu Kommi wrote:

On Mon, Nov 16, 2015 at 2:30 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On 7/21/15 5:15 AM, Haribabu Kommi wrote:

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

How is this useful? I could see the use if you want to debug cases of
user foo on host bar says they can't connect, but you can't impersonate
them to verify it. But then all you need is a function with a scalar
result, not a result set.

Do you mean the function should return true or false based on the connection
status with the provided arguments?

I also feel difficult to understand the function result as compared to a view.

An hba lookup is essentially a lookup by user name, database name,
client address, yielding an authentication method (possibly with
parameters). So I think this function should work that way as well:
arguments are user name, database name, and so on, and the return value
is an authentication method. Maybe it would be some kind of record,
with line number and some parameters.

That would address the use case I put forth above. I don't know whether
that's what you were going for.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Nov 17, 2015 at 9:37 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

On 11/16/15 2:37 AM, Haribabu Kommi wrote:

On Mon, Nov 16, 2015 at 2:30 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On 7/21/15 5:15 AM, Haribabu Kommi wrote:

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

How is this useful? I could see the use if you want to debug cases of
user foo on host bar says they can't connect, but you can't impersonate
them to verify it. But then all you need is a function with a scalar
result, not a result set.

Do you mean the function should return true or false based on the connection
status with the provided arguments?

I also feel difficult to understand the function result as compared to a view.

An hba lookup is essentially a lookup by user name, database name,
client address, yielding an authentication method (possibly with
parameters). So I think this function should work that way as well:
arguments are user name, database name, and so on, and the return value
is an authentication method. Maybe it would be some kind of record,
with line number and some parameters.

That would address the use case I put forth above. I don't know whether
that's what you were going for.

Thanks. Here I attached the poc patch that returns authentication method of the
first matched hba entry in pg_hba.conf with the given input values.
Currently these
functions returns text type. Based on the details required to be
printed, it can
be changed.

postgres=# select pg_hba_lookup('all', 'all');
pg_hba_lookup
---------------
trust
(1 row)

comments for the approach?

Regards,
Hari Babu
Fujitsu Australia

2015-11-25 8:05 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

On Tue, Nov 17, 2015 at 9:37 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

On 11/16/15 2:37 AM, Haribabu Kommi wrote:

On Mon, Nov 16, 2015 at 2:30 PM, Peter Eisentraut <peter_e@gmx.net>

wrote:

On 7/21/15 5:15 AM, Haribabu Kommi wrote:

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

How is this useful? I could see the use if you want to debug cases of
user foo on host bar says they can't connect, but you can't impersonate
them to verify it. But then all you need is a function with a scalar
result, not a result set.

Do you mean the function should return true or false based on the

connection

status with the provided arguments?

I also feel difficult to understand the function result as compared to

a view.

An hba lookup is essentially a lookup by user name, database name,
client address, yielding an authentication method (possibly with
parameters). So I think this function should work that way as well:
arguments are user name, database name, and so on, and the return value
is an authentication method. Maybe it would be some kind of record,
with line number and some parameters.

That would address the use case I put forth above. I don't know whether
that's what you were going for.

Thanks. Here I attached the poc patch that returns authentication method
of the
first matched hba entry in pg_hba.conf with the given input values.
Currently these
functions returns text type. Based on the details required to be
printed, it can
be changed.

postgres=# select pg_hba_lookup('all', 'all');
pg_hba_lookup
---------------
trust
(1 row)

comments for the approach?

From my perspective, it shows too less informations.

What I am expecting:

1. line num of choosed rule
2. some tracing - via NOTICE, what and why some rules was skipped.

Regards

Pavel

On Wed, Nov 25, 2015 at 7:18 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-11-25 8:05 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

Thanks. Here I attached the poc patch that returns authentication method
of the
first matched hba entry in pg_hba.conf with the given input values.
Currently these
functions returns text type. Based on the details required to be
printed, it can
be changed.

postgres=# select pg_hba_lookup('all', 'all');
pg_hba_lookup
---------------
trust
(1 row)

comments for the approach?

From my perspective, it shows too less informations.

What I am expecting:

1. line num of choosed rule
2. some tracing - via NOTICE, what and why some rules was skipped.

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?

Regards,
Hari Babu
Fujitsu Australia

2015-12-03 5:00 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

On Wed, Nov 25, 2015 at 7:18 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-11-25 8:05 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

Thanks. Here I attached the poc patch that returns authentication method
of the
first matched hba entry in pg_hba.conf with the given input values.
Currently these
functions returns text type. Based on the details required to be
printed, it can
be changed.

postgres=# select pg_hba_lookup('all', 'all');
pg_hba_lookup
---------------
trust
(1 row)

comments for the approach?

From my perspective, it shows too less informations.

What I am expecting:

1. line num of choosed rule
2. some tracing - via NOTICE, what and why some rules was skipped.

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

The text of notice can be reduced "Skipped xx line, ..." - it have to be
pg_hba

Pavel

2015-12-03 5:53 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2015-12-03 5:00 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

On Wed, Nov 25, 2015 at 7:18 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-11-25 8:05 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

Thanks. Here I attached the poc patch that returns authentication

method

of the
first matched hba entry in pg_hba.conf with the given input values.
Currently these
functions returns text type. Based on the details required to be
printed, it can
be changed.

postgres=# select pg_hba_lookup('all', 'all');
pg_hba_lookup
---------------
trust
(1 row)

comments for the approach?

From my perspective, it shows too less informations.

What I am expecting:

1. line num of choosed rule
2. some tracing - via NOTICE, what and why some rules was skipped.

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

The text of notice can be reduced "Skipped xx line, ..." - it have to be
pg_hba

this tracing can be implemented to main pg_hba processing. When you are
connect from some specific client - and you can see, why you cannot to
connect to Postgres

Pavel

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

On Fri, Dec 4, 2015 at 7:45 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

this tracing can be implemented to main pg_hba processing. When you are
connect from some specific client - and you can see, why you cannot to
connect to Postgres

The trace messages that are going to print doesn't come to client until the
connection gets successful. The traces may not useful for the clients
to find out
why the connection is failing. But it may be useful for administrators.
How about the attached patch?

[kommih@localhost bin]$ ./psql postgres -h ::1
psql (9.6devel)
Type "help" for help.

postgres=#

ServerLog:
NOTICE: Skipped 84 pg_hba line, because of host connection type.
NOTICE: Skipped 86 pg_hba line, because of non matching IP.

Regards,
Hari Babu
Fujitsu Australia

2015-12-04 5:33 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

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)

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

2015-12-04 5:48 GMT+01:00 Haribabu Kommi <kommi.haribabu@gmail.com>:

On Fri, Dec 4, 2015 at 7:45 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

this tracing can be implemented to main pg_hba processing. When you are
connect from some specific client - and you can see, why you cannot to
connect to Postgres

The trace messages that are going to print doesn't come to client until the
connection gets successful. The traces may not useful for the clients
to find out
why the connection is failing. But it may be useful for administrators.
How about the attached patch?

yes, it is only for admin and should be stored to log

Haribabu Kommi wrote:

The trace messages that are going to print doesn't come to client until the
connection gets successful. The traces may not useful for the clients
to find out
why the connection is failing. But it may be useful for administrators.
How about the attached patch?

[kommih@localhost bin]$ ./psql postgres -h ::1
psql (9.6devel)
Type "help" for help.

postgres=#

ServerLog:
NOTICE: Skipped 84 pg_hba line, because of host connection type.
NOTICE: Skipped 86 pg_hba line, because of non matching IP.

That's going to be way too noisy. Some applications open dozens of
connections per second -- imagine a dozen NOTICEs per each connection
established. It's going to fill any disk you install as the server log
partition ...

I can imagine worse nightmares, but this one's a pretty ugly one.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-12-04 17:16 GMT+01:00 Alvaro Herrera <alvherre@2ndquadrant.com>:

Haribabu Kommi wrote:

The trace messages that are going to print doesn't come to client until

the

connection gets successful. The traces may not useful for the clients
to find out
why the connection is failing. But it may be useful for administrators.
How about the attached patch?

[kommih@localhost bin]$ ./psql postgres -h ::1
psql (9.6devel)
Type "help" for help.

postgres=#

ServerLog:
NOTICE: Skipped 84 pg_hba line, because of host connection type.
NOTICE: Skipped 86 pg_hba line, because of non matching IP.

That's going to be way too noisy. Some applications open dozens of
connections per second -- imagine a dozen NOTICEs per each connection
established. It's going to fill any disk you install as the server log
partition ...

I can imagine worse nightmares, but this one's a pretty ugly one.

It should be disabled by default

only when you have some problems, then you can enable it

Regards

Pavel