Re: control pg_hba.conf via SQL

Unfortunately, there is still one serious deficiency with the solution
below- it may not be the actual information postgresql is currently
using to determine who can log in and how- the file can be easily
changed behind the scenes and there is currently no way to know.

I (speaking as a DBA) would still very much appreciate a static, frozen
table view accessible from SQL.

On Mar 30, 2006, at 3:05 PM, David Fetter wrote:

> On Thu, Mar 30, 2006 at 10:43:31AM -0500, Andrew Dunstan wrote:
>> A.M. wrote:
>>> Could postgres offer at least a read-only view of the data in the
>>> interim?  Ordering could be controlled by line number.
>>
>> You can get the contents as a single text field like this:
>>
>> |  select pg_read_file|('pg_hba.conf', 0, 50*1024);
>>
>> Writing a plperl function that would strip comments and blank lines
>> and return the rest as a numbered set of lines would be fairly
>> trivial.
>
> You don't even need PL/Perl :)
>
> SELECT * FROM (
>     SELECT
>         s.t AS "Ordering",
>         (string_to_array(pg_read_file(
>             'pg_hba.conf',
>             0,
>             (pg_stat_file('pg_hba.conf')).size
>         ), '\n'))[s.t] AS "Line"
>     FROM
>         generate_series(
>             1,
>             array_upper(
>                 string_to_array(pg_read_file(
>                     'pg_hba.conf',
>                     0,
>                     (pg_stat_file('pg_hba.conf')).size
>                 ), '\n'),
>                 1
>             )
>         ) AS s(t)
> ) AS foo
> WHERE
>     "Line" !~ '^#'
> AND
>     "Line" !~ '^\s*$'
> ;
>
> Cheers,
> D

¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm@themactionfaction.com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬