Re: control pg_hba.conf via SQL

Hi all,

i would like to work on control setting pg_hba.conf via SQL....( cf TODO
List ), and i would know if the pg_hda.conf must be replaced by pg_hba
table ?

Emmanuel BERTHOULE

I'd like to expand this idea with this TODO item. If this is already
on the TODO, please disregard. I'm thinking something like the
following:

Enable the alteration and persistence of postgresql.conf and
pg_hba.conf configuration parameters via SQL.

On 3/29/06, BERTHOULE Emmanuel <pgdev(at)manberth(dot)homeip(dot)net> wrote:
> Hi all,
>
> i would like to work on control setting pg_hba.conf via SQL....( cf TODO
> List ), and i would know if the pg_hda.conf must be replaced by pg_hba
> table ?
>
> Emmanuel BERTHOULE
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

ISTM that the first requirement is for a sane API that will handle the
fact that HBA lines are ordered. Persistence in itself shouldn't be a
big problem - we already do that with some shared tables, iirc.

so we might have some functions like:

insert_hba_rule(at_position int, connection_type text, username text,
dbname text, cidr_host text, method text)
move_hba_rule(from_position int, to_position int)
delete_hba_rule(at_position int)

Inventing new SQL syntax might make things a bit tougher.

cheers

andrew

Jonah H. Harris wrote:
> I'd like to expand this idea with this TODO item. If this is already
> on the TODO, please disregard. I'm thinking something like the
> following:
>
> Enable the alteration and persistence of postgresql.conf and
> pg_hba.conf configuration parameters via SQL.
>
>
> On 3/29/06, BERTHOULE Emmanuel <pgdev(at)manberth(dot)homeip(dot)net> wrote:
>
>> Hi all,
>>
>> i would like to work on control setting pg_hba.conf via SQL....( cf TODO
>> List ), and i would know if the pg_hda.conf must be replaced by pg_hba
>> table ?
>>
>>

On 3/29/06, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> ISTM that the first requirement is for a sane API that will handle the
> fact that HBA lines are ordered. Persistence in itself shouldn't be a
> big problem - we already do that with some shared tables, iirc.

I agree.

--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> ISTM that the first requirement is for a sane API that will handle the
> fact that HBA lines are ordered. Persistence in itself shouldn't be a
> big problem - we already do that with some shared tables, iirc.

I'm a bit suspicious of proposals that we move either hba or conf into
SQL tables --- one of the main reasons why they are flat files is so
you can still edit them after you've hosed them to the point that the
database won't start or won't let you in. If you don't have a non-kluge
solution to the DBA-mistake-recovery scenario, this is not going to be
an improvement.

Pushing postgresql.conf into a SQL table will also destroy all the work
that was done recently to allow config sharing across multiple
installations (eg the recent commit to support "include" goes out the
window again). If we no longer care about that, why not?

regards, tom lane

On Wednesday 29 March 2006 17:04, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > ISTM that the first requirement is for a sane API that will handle the
> > fact that HBA lines are ordered. Persistence in itself shouldn't be a
> > big problem - we already do that with some shared tables, iirc.
>
> I'm a bit suspicious of proposals that we move either hba or conf into
> SQL tables --- one of the main reasons why they are flat files is so
> you can still edit them after you've hosed them to the point that the
> database won't start or won't let you in. If you don't have a non-kluge
> solution to the DBA-mistake-recovery scenario, this is not going to be
> an improvement.
>

I've often thought that a GUC in postgresql.conf could control whether to use the hba file or an hba table. Most likely you would need to restart the db to toggle control, but if your at the point where you've locked yourself out thisdoesn't seem onerous. If pushing postgresql.conf into the db would negate this plan, we could either allow a command line flag to override the conf/hba behavior, or force postgresql to use files if started in single operator mode. In any case, I don't think this restriction is insurmountable.

> Pushing postgresql.conf into a SQL table will also destroy all the work
> that was done recently to allow config sharing across multiple
> installations (eg the recent commit to support "include" goes out the
> window again). If we no longer care about that, why not?
>

Honestly I never cared much about that, and I run several machines that contain 3+ versions of the db on them. Certainly not as much as I would like to enhance remote administration between machines.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Tom Lane wrote:

>Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>
>>ISTM that the first requirement is for a sane API that will handle the
>>fact that HBA lines are ordered. Persistence in itself shouldn't be a
>>big problem - we already do that with some shared tables, iirc.
>>
>>
>
>I'm a bit suspicious of proposals that we move either hba or conf into
>SQL tables --- one of the main reasons why they are flat files is so
>you can still edit them after you've hosed them to the point that the
>database won't start or won't let you in. If you don't have a non-kluge
>solution to the DBA-mistake-recovery scenario, this is not going to be
>an improvement.
>
>Pushing postgresql.conf into a SQL table will also destroy all the work
>that was done recently to allow config sharing across multiple
>installations (eg the recent commit to support "include" goes out the
>window again). If we no longer care about that, why not?
>
>
>

I think we should treat pg_hba.conf and postgresql.conf as separate
cases. The proposal was only for pg_hba.conf.

There are several possible ways around the "settings hosed" issue,
including Robert's suggestion of a flag to say "don't read the table,
read this file instead".

I agree about the value of "include" for postgresql.conf.

cheers

andrew

Andrew Dunstan wrote:
>
> ISTM that the first requirement is for a sane API that will handle the
> fact that HBA lines are ordered. Persistence in itself shouldn't be a
> big problem - we already do that with some shared tables, iirc.
>
> so we might have some functions like:
>
> insert_hba_rule(at_position int, connection_type text, username text,
> dbname text, cidr_host text, method text)
> move_hba_rule(from_position int, to_position int)
> delete_hba_rule(at_position int)
>
> Inventing new SQL syntax might make things a bit tougher.
>

I dont think it has to be ordered preliminary. Since we are
dealing with subnets and stuff - the ordering already lays
in the data - just like routing tables work: most specific
matches first.

I could think of a solution where pg_hba.conf just
overrides the database table (so you have a starting
point with empty table and/or reentry in case of a
mistake)

regards
Tino

On Wed, 2006-03-29 at 16:20 -0500, Jonah H. Harris wrote:

> Enable the alteration and persistence of postgresql.conf and
> pg_hba.conf configuration parameters via SQL.

Agreed.

I'd also add the seemingly obvious caveat that parameters should be
unique. A common problem is for one person to edit a postgresql.conf by
editing in place, then another person to add a line at the bottom. The
second entry overrides the first.

I would also like these settings to be grouped together as Profiles.
It's fairly common to have multiple postgresql.conf files for different
modes of processing: Normal Running, Bulkload, TuningMode so we would
want to be able to retain that capability in the future.

Best Regards, Simon Riggs

Tom Lane wrote:
> I'm a bit suspicious of proposals that we move either hba or conf into
> SQL tables --- one of the main reasons why they are flat files is so
> you can still edit them after you've hosed them to the point that the
> database won't start or won't let you in. If you don't have a non-kluge
> solution to the DBA-mistake-recovery scenario, this is not going to be
> an improvement.
>
What about a line in pg_hba which tells pgsql how to handle it. I.e. an
example is :

--- pg_hba.conf ---
Include_file = {only, first, last, not}
< normal lines >
--- pg_hba.conf ---

Not means only use in DB data.
First means load file before data from DB (so db can overwrite file
settings)
Last means load file after data from DB (so file can overwrite db settings)
Only means just use file settings (as today)
If the line is missing, assume only (backwards compatibility).

By the way, I really think there should be real grammar for it, not just
a couple of sql helper functions.

Svenne

Tino Wildenhain wrote:
> Andrew Dunstan wrote:
>
>> ISTM that the first requirement is for a sane API that will handle the
>> fact that HBA lines are ordered. Persistence in itself shouldn't be a
>> big problem - we already do that with some shared tables, iirc.
>>
>> so we might have some functions like:
>>
>> insert_hba_rule(at_position int, connection_type text, username text,
>> dbname text, cidr_host text, method text)
>> move_hba_rule(from_position int, to_position int)
>> delete_hba_rule(at_position int)
>>
>> Inventing new SQL syntax might make things a bit tougher.
>>
>>
>
> I dont think it has to be ordered preliminary. Since we are
> dealing with subnets and stuff - the ordering already lays
> in the data - just like routing tables work: most specific
> matches first.
>
> I could think of a solution where pg_hba.conf just
> overrides the database table (so you have a starting
> point with empty table and/or reentry in case of a
> mistake)
>
>
>

We don't have the luxury of being able just to throw out old stuff
because we think it might be neater to do it another way. The current
rules for HBA are order dependent. The issue raised as I understood it
was not to invent a new scheme but to be able to manage it from inside a
postgres session.

Of course, if we go for a new scheme that is not order dependent, then
inventing a reasonable SQL syntax to support it becomes a heck of a lot
easier. Something along the lines of GRANT/REVOKE CONNECT ... should do
the trick.

Maybe we could do something like this: if there is a pg_hba.conf file
present, then use it as now and ignore the access rights table - if
someone does GRANT/REVOKE CONNECT while under pg_hba.conf then process
it but issue a warning. Maybe there could also be an initdb switch that
gave users a choice.

cheers

andrew

Andrew Dunstan wrote:
> Tino Wildenhain wrote:
...
>> I dont think it has to be ordered preliminary. Since we are
>> dealing with subnets and stuff - the ordering already lays
>> in the data - just like routing tables work: most specific
>> matches first.
>>
>> I could think of a solution where pg_hba.conf just
>> overrides the database table (so you have a starting
>> point with empty table and/or reentry in case of a
>> mistake)
...
>
> We don't have the luxury of being able just to throw out old stuff
> because we think it might be neater to do it another way. The current
> rules for HBA are order dependent. The issue raised as I understood it
> was not to invent a new scheme but to be able to manage it from inside a
> postgres session.

Not sure about the luxury - iirc there was some change in the format
of pg_hba.conf anyway over the time and beside pgadmin3 I dont see
many tools to edit this file (apart from the usual text editor ;)

So I dont see a strong reason to keep it the way it is now just for
some legacy nobody depends on anyway. Alternatively there could
be something like security.conf or the like which depreciates
pg_hba.conf - so if pg_hba.conf is there any has any active
entry in it - things would be like they are now.
if not, then security.conf and the system table would
work like designed, having security.conf read before the table.

A pg_securitydump or the like could be usefull to dump the table
to a file in the security.conf format.

Regards
Tino

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> We don't have the luxury of being able just to throw out old stuff
> because we think it might be neater to do it another way.

Well, we could if there were a groundswell of demand showing that the
pg_hba.conf approach were inadequate (don't think so) or hard to
understand (there you could make an argument --- newbies get it wrong
regularly). But "I want to manage it via SQL" is not such an argument.

> Of course, if we go for a new scheme that is not order dependent, then
> inventing a reasonable SQL syntax to support it becomes a heck of a lot
> easier. Something along the lines of GRANT/REVOKE CONNECT ... should do
> the trick.

I've been thinking about proposing a database CONNECT right anyway, but
it'd be an additional filter atop the pg_hba.conf rules. You still need
pg_hba because a CONNECT right could only filter user-and-database
combinations; it would offer no traction on limiting which hosts can
connect, nor on deciding what authentication mechanism to use.

Having said that, though, it does seem that limiting user-and-database
combinations is the main problem for many people, and that allowing that
part to be managed from SQL might defuse the need for manipulating
pg_hba.conf from SQL. If your pg_hba.conf looks like
host all all 0.0.0.0/32 md5
there's not much call to update it dynamically ...

regards, tom lane

Tom Lane wrote:
> If your pg_hba.conf looks like
> host all all 0.0.0.0/32 md5
> there's not much call to update it dynamically ...
>
>

There'll be a call to update it once - to 0.0.0.0/0 ;-)

I guess you proved the point about how easy it is the get wrong ;-)

(sorry, couldn't resist)

But it's not clear to me why a CONNECT right shouldn't encompass all the things that hba does, i.e. connect method, source address and auth method.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>> If your pg_hba.conf looks like
>> host all all 0.0.0.0/32 md5
>> there's not much call to update it dynamically ...

> There'll be a call to update it once - to 0.0.0.0/0 ;-)

Doh ;-). Should make more effort to check my throwaway examples ...

> But it's not clear to me why a CONNECT right shouldn't encompass all
> the things that hba does, i.e. connect method, source address and auth
> method.

Because that stuff doesn't fit into either the syntax of GRANT or the
system tables that store grant information. It's talking about concepts
that don't even exist in the SQL world (while users and databases
certainly do).

Also, we know from experience that there's value in applying an ordered
set of tests in pg_hba.conf --- in particular, rules about "local" vs
"local net" vs "anywhere" connections are most easily expressed that
way. We would need some substitute rule or concept in order to do the
same work in GRANT, and I don't see what that would be.

Recently in another thread someone was remarking about how ugly MySQL's
authentication methods are. I think that's in part because they have
chosen to wedge the client hostname into their concept of user. It doesn't
fit nicely.

regards, tom lane

Could postgres offer at least a read-only view of the data in the interim?
Ordering could be controlled by line number.

On Thu, March 30, 2006 10:14 am, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> Tom Lane wrote:
>>
>>> If your pg_hba.conf looks like
>>> host all all 0.0.0.0/32 md5 there's not much call to update it
>>> dynamically ...
>
>> There'll be a call to update it once - to 0.0.0.0/0 ;-)
>>
>
> Doh ;-). Should make more effort to check my throwaway examples ...
>
>
>> But it's not clear to me why a CONNECT right shouldn't encompass all
>> the things that hba does, i.e. connect method, source address and auth
>> method.
>
> Because that stuff doesn't fit into either the syntax of GRANT or the
> system tables that store grant information. It's talking about concepts
> that don't even exist in the SQL world (while users and databases
> certainly do).
>
> Also, we know from experience that there's value in applying an ordered
> set of tests in pg_hba.conf --- in particular, rules about "local" vs
> "local net" vs "anywhere" connections are most easily expressed that
> way. We would need some substitute rule or concept in order to do the same
> work in GRANT, and I don't see what that would be.
>
> Recently in another thread someone was remarking about how ugly MySQL's
> authentication methods are. I think that's in part because they have
> chosen to wedge the client hostname into their concept of user. It
> doesn't fit nicely.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>
> http://archives.postgresql.org
>
>

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.

cheers

andrew

>
> Not sure about the luxury - iirc there was some change in the format
> of pg_hba.conf anyway over the time and beside pgadmin3 I dont see
> many tools to edit this file (apart from the usual text editor ;)
>
Just a FYI, PG Lightning Admin edits the pg_hba.conf as well as the
postgresql.conf remotely, and does it pretty much the same
way as pgAdmin III.

--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration

andrew(at)dunslane(dot)net (Andrew Dunstan) writes:
> We don't have the luxury of being able just to throw out old stuff
> because we think it might be neater to do it another way. The current
> rules for HBA are order dependent. The issue raised as I understood it
> was not to invent a new scheme but to be able to manage it from inside
> a postgres session.

If the need to support "legacy usage" mandates something like Svenne
Krap's suggestion of a control flag inside pg_hba.conf, or something
otherwise akin to Robert Treat's suggestions, then I think this *is*
designing something new/neater.

I think it would take a fair bit of work (and kludging of design) to
build something to slavishly emulate pg_hba.conf; it seems to me that
it is a much better thing to have an inside-the-database HBA scheme be
based on what is a good design inside-the-database.

> Of course, if we go for a new scheme that is not order dependent,
> then inventing a reasonable SQL syntax to support it becomes a heck
> of a lot easier. Something along the lines of GRANT/REVOKE CONNECT
> ... should do the trick.

Sure. This would come as something of a 2 level attack on the problem:

1. Find a decent representation for the data;

2. Find a decent way to tell the system about the data...

> Maybe we could do something like this: if there is a pg_hba.conf
> file present, then use it as now and ignore the access rights table
> - if someone does GRANT/REVOKE CONNECT while under pg_hba.conf then
> process it but issue a warning. Maybe there could also be an initdb
> switch that gave users a choice.

initdb is a terrible choice for that; that means you have to
re-initialize the database to change the option.

I think a better approach is to control this in postgresql.conf...

An option like...

host_based_authentication_file = "/etc/postgresql/pg_hba.conf"
host_based_authentication = "internal" [looks at table pg_catalog.pg_hba, let's say]
# options here are "internal", "file", "file, internal", "internal, file"

That way, if a problem arises that is locking the administrator out,
it can be resolved by a "pg_ctl reload".
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/lisp.html
"When I was a boy of fourteen, my father was so ignorant I could
hardly stand to have the old man around. But when I got to be
twenty-one, I was astonished at how much the old man had learned in
seven years." -- Mark Twain

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> If your pg_hba.conf looks like
> host all all 0.0.0.0/32 md5
> there's not much call to update it dynamically ...

There's one case, where .pgpass got hosed, and you didn't have a
backup of it, and need to assign new passwords...

I once ran into a case like this, where nobody had bothered to record
the "postgres" user's password, and had to override md5 authentication
in order to get in and reset passwords...
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www3.sympatico.ca/cbbrowne/lisp.html
"When I was a boy of fourteen, my father was so ignorant I could
hardly stand to have the old man around. But when I got to be
twenty-one, I was astonished at how much the old man had learned in
seven years." -- Mark Twain

Chris Browne wrote:
>> Maybe we could do something like this: if there is a pg_hba.conf
>> file present, then use it as now and ignore the access rights table
>> - if someone does GRANT/REVOKE CONNECT while under pg_hba.conf then
>> process it but issue a warning. Maybe there could also be an initdb
>> switch that gave users a choice.
>>
>
> initdb is a terrible choice for that; that means you have to
> re-initialize the database to change the option.
>
>

Not at all. Where did you get that idea? Not everything set by initdb is
set in concrete. For example, initdb sets shared_buffers but you sure
don't need to rerun initdb to change that or anything else it sets in
postgresql.conf.

cheers

andrew

On Thu, 2006-03-30 at 12:43, Chris Browne wrote:
> andrew(at)dunslane(dot)net (Andrew Dunstan) writes:
> > We don't have the luxury of being able just to throw out old stuff
> > because we think it might be neater to do it another way. The current
> > rules for HBA are order dependent. The issue raised as I understood it
> > was not to invent a new scheme but to be able to manage it from inside
> > a postgres session.
>
> If the need to support "legacy usage" mandates something like Svenne
> Krap's suggestion of a control flag inside pg_hba.conf, or something
> otherwise akin to Robert Treat's suggestions, then I think this *is*
> designing something new/neater.
>
> I think it would take a fair bit of work (and kludging of design) to
> build something to slavishly emulate pg_hba.conf; it seems to me that
> it is a much better thing to have an inside-the-database HBA scheme be
> based on what is a good design inside-the-database.
>

+1

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

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(at)themactionfaction(dot)com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬

Er, how can the file be changed behind the scenes? Only if you have
opened up permission to the directory to someone else, or you don't
trust your sysadmins.

Either way you would then have much bigger problems than this.

cheers

andrew

Agent M wrote:

> 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(at)themactionfaction(dot)com
> ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

But there is still no way to verify that the information in the file is
what postgres saw last. DBAs make mistakes too. A simple way to view
the current access state would be much appreciated.

On Apr 1, 2006, at 1:01 PM, Andrew Dunstan wrote:

>
> Er, how can the file be changed behind the scenes? Only if you have
> opened up permission to the directory to someone else, or you don't
> trust your sysadmins.
>
> Either way you would then have much bigger problems than this.
>
> cheers
>
> andrew

¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬
AgentM
agentm(at)themactionfaction(dot)com
¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬