Re: Providing catalog view to pg_hba.conf file - Patch submission

From: Magnus Hagander [mailto:magnus(at)hagander(dot)net]
Sent: Friday, 14 March 2014 9:33 PM
To: Prabakaran, Vaishnavi
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

On Fri, Mar 14, 2014 at 6:30 AM, Prabakaran, Vaishnavi <vaishnavip(at)fast(dot)au(dot)fujitsu(dot)com<mailto:vaishnavip(at)fast(dot)au(dot)fujitsu(dot)com>> wrote:
Hi,

In connection to my previous proposal about "providing catalog view to pg_hba.conf file contents" , I have developed the attached patch .

[Current situation]
Currently, to view the pg_hba.conf file contents, DB admin has to access the file from database server to read the settings. In case of huge and multiple hba files, finding the appropriate hba rules which are loaded will be difficult and take some time.

[What this Patch does]
Functionality of the attached patch is that it will provide a new view "pg_hba_settings" to admin users. Public access to the view is restricted. This view will display basic information about HBA setting details of postgresql cluster. Information to be shown , is taken from parsed hba lines and not directly read from pg_hba.conf files. Documentation files are also updated to include details of this new view under "Chapter 47.System Catalogs". Also , a new note is added in "chapter 19.1 The pg_hba.conf File"

[Advantage]
Advantage of having this "pg_hba_settings" view is that the admin can check, what hba rules are loaded in runtime via database connection itself. And, thereby it will be easy and useful for admin to check all the users with their privileges in a single view to manage them.

>This looks like a useful feature, so make sure you register it on https://commitfest.postgresql.org/action/commitfest_view?id=22.

>I haven't looked at the actual code yet, btu I did notice one thing at a very quick lookover at the docs - it seems to be completely ignoring the key/value parameters given on a row, and >stops reporting after the auth method? That seems bad. And also, probably host/mask should be using the inet style datatypes and not text?

Agree, am now working on including a new column "configuration_option" to display the key/value parameter set. I will send the updated patch once after adding new column.
Host/mask values are stored as sockaddr_storage structure in parsed_hba_lines, so I have used text datatype to display the hostname.

Thanks & Regards,
Vaishnavi
Fujitsu Australia
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/