Re: Updated instrumentation patch

Let me try to outline where I think our goals are for remote
administration. I will not comment on Dave's analysis of the patch
review process, but I think he has some valid points that this patch was
not treated properly.

Basically, I think everyone wants remote administration. Remote
administration requires several things:

o edit postgresql.conf
o edit pg_hba.conf
o reload the config files
o restart the server (for config variables requiring restart)
o view log files
o recycle log files
o rename/remove log files

All these items are on the TODO list already.

The idea of the patch was to give applications the full unix I/O
capabilities, allowing them to program these functions into
administration applications. I think the group generally would like a
higher-level API that allows something like:

SET GLOBAL log_statement = 'mod';

that would modify postgresql.conf and signal all backends to-read the
file, or a way to control pg_hba.conf using SQL queries.

While the Unix API works, it isn't really what we finally want for
remote administration. I thought this was discussed back in the 8.0
beta period, and was surprised to see the file I/O patch resurface, but
because no one objected to it, I moved forward to getting it into the
queue and applied. Later, others did object, some to the too-general
API, others based on security concerns.

I probably should have stated more clearly that the high-level API was
the proper approach, rather than moving forward with a patch I knew
untimately would lead to concerns. However, I try to refrain from
pre-judging a patch lest I become too unbiased toward patch submissions.
I try to be the advocate for patches, rather than cast judgement. (What
surprised me is that the concerns didn't surfaced so late.)

So, where does this leave us for 8.1? I don't think we have time to
implement many of the bulleted items listed above, and I don't think the
file API patch would pass a vote, but I will support a vote if people
want that.

I think it might be possible to do a few of the bulleted items while we
clean out the patches queue. In fact, I think the reload the config
file functionality was already in the file I/O patch, so we can easily
apply that. (It is a TODO item.)

Given the confusion about the patch, I think we can give folks some time
to work on any additional remote administration bulleted items while we
clean out the patches queue.

Does that sound like a plan?

[ FYI, I think some of the bulleted items can be done now via COPY.]

---------------------------------------------------------------------------

Dave Page wrote:

None of these functions are getting into 8.1 anyway; we should be
designing the long-term solution not making up short-lived hacks.

So, going back to pre 8.0, we fixed them so they don't work outside of
the data directory as requested, yet they were not included for unknown
reasons.

We revisited some weeks before prior to feature freeze, and I researched
all issues raised and ask for clarification on what you weren't happy
with as all I'd found in the archives was a sentence along the lines
of "I really don't see any value in these". I found no outstanding
issues in the archives, nor did I receive any in response to my
questions.

Having received no further objections, the patch was added to the queue.
As soon as Bruce starts to look at it, presumably to apply it, you
decide it's an unacceptable security problem, and say you'd be
perfectly happy if there was a GUC to disable the potentially dangerous
functions. This info would have been nice before feature freeze, but,
OK, I appreciate you're busy.

Magnus updates the patch because he's yet another one of us that thinks
this is useful functionality and adds the GUC you said would make you
happy with these functions.

You then state, with no discussion at all, that they're not going into
8.1 anyway, despite us doing everything you have asked.

I have two questions if I may:

1) Is there any point us working on any kind of enhanced API for remote
admin in the future, or will the same treatment be given to that?

2) Do you now have sole say over what does and doesn't go into the
project?

I don't mean to be disrespectful - your hard work and skills are hugely
appreciated by the whole community, but I know for a fact that a number
of them, who between them have contributed thousands of hours and lines
of code to the project (and I'm talking about the core project, never
mind pgAdmin et al) cannot understand your apparent insistence on us
not providing remote admin capabilities. I think we simply need
clarification on how the project works these days.

Regards, Dave

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your message can get through to the mailing list cleanly

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Sat, Jul 30, 2005 at 11:39:20PM -0400, Bruce Momjian wrote:

Let me try to outline where I think our goals are for remote
administration. I will not comment on Dave's analysis of the patch
review process, but I think he has some valid points that this patch was
not treated properly.

Basically, I think everyone wants remote administration. Remote
administration requires several things:

o edit postgresql.conf
o edit pg_hba.conf
o reload the config files
o restart the server (for config variables requiring restart)
o view log files
o recycle log files
o rename/remove log files

All these items are on the TODO list already.

My security spider-sense tingles when I see the ability for a remote
attacker to not only completely override password, certificate and IP
absed authentication but also to easily remove logfiles.

So, while I can see the attraction of being able to futz with the
database security configuration through a PHP web interface running on
an unpatched Apache build somewhere out on the open internet (and
would like to be able to do so myself, sometimes) I'd really, really
like to see the ability to disable as much of this at compile time as
is convenient.

Cheers,
Steve

On Sat, Jul 30, 2005 at 09:35:16PM -0700, Steve Atkins wrote:

On Sat, Jul 30, 2005 at 11:39:20PM -0400, Bruce Momjian wrote:

Let me try to outline where I think our goals are for remote
administration. I will not comment on Dave's analysis of the patch
review process, but I think he has some valid points that this patch was
not treated properly.

Basically, I think everyone wants remote administration. Remote
administration requires several things:

o edit postgresql.conf
o edit pg_hba.conf
o reload the config files
o restart the server (for config variables requiring restart)
o view log files
o recycle log files
o rename/remove log files

All these items are on the TODO list already.

My security spider-sense tingles when I see the ability for a remote
attacker to not only completely override password, certificate and IP
absed authentication but also to easily remove logfiles.

Yes, I'd trim that part to support only rename of log files, and
constrain the destination to the log directory. (I guess I don't need
to mention that all log file operations are already constrained to files
inside the log directory.)

For the "edit postgresql.conf" part I guess it would be important to
have some settings that would not be changeable via this interface.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un gui�n que no toque nada para no causar da�os." (Jakob Nielsen)

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org on behalf of Steve Atkins
Sent: Sun 7/31/2005 5:35 AM
To: PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

So, while I can see the attraction of being able to futz with the
database security configuration through a PHP web interface running on
an unpatched Apache build somewhere out on the open internet (and
would like to be able to do so myself, sometimes) I'd really, really
like to see the ability to disable as much of this at compile time as
is convenient.

Yes, Tom expressed a similar concern and suggested he would be happy with a GUC to disable potentially dangerous functions (which we could later extend to other features like untrusted PLs). This GUC was added to the patch by Magnus yesterday and would allow you to secure your system from attacks via the new functions in an insecure environment.

Regards, Dave.

Bruce Momjian wrote:

I think the group generally would like a
higher-level API that allows something like:

SET GLOBAL log_statement = 'mod';

This is the typical Core point of view. Any function not usable from
psql can't be ok. So until psql isn't enabled to SET GLOBAL, the rest of
the world has to wait.

Please note that this configurability is not sufficient. If the server
is not running, it obviously can't work so a config tool must be able to
work on *.conf directly. This is how pgadmin already works, enabling an
config edit only mode to be included in pginstaller. I'm not inclined to
recode the wheel psql style.

Regards,
Andreas

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Sun 7/31/2005 4:39 AM
To: Dave Page
Cc: Tom Lane; Magnus Hagander; PostgreSQL-development
Subject: Remote administration functionality

The idea of the patch was to give applications the full unix I/O
capabilities, allowing them to program these functions into
administration applications. I think the group generally would like a
higher-level API that allows something like:

SET GLOBAL log_statement = 'mod';

Sounds reasonable (and quite nice) for postgresql.conf, but consider pg_hba.conf. The production systems I run at work have heavily commented pg_hba.conf files, with entries that are intentionally ordered. As you know, unlike postgresql.conf, there is no fixed set of possible entries. How can we create a cleaner inteface for that, and be able to maintain annotations in the file in a way that works well when using tools and text editors at different times?

The best I have come up with is functions similar to:

SELECT pg_set_hba_line(20, 'hostssl all all 192.168.1.1/32 md5');
SELECT pg_add_hba_line(19, '# Allow global access for Dave''s test workstation');
SELECT pg_delete_hba_line(24);

However, there are a couple of things that concern me about doing it this way:

- It would make the client code much more complex as it would need to track each change the user makes individually, before applying the end result.

- It doesn't really give us a cleaner, less hackish interface and just seems like work for the sake of it.

I suppose we could just add functions like:

pg_write_hba_file('File contents'::text);
pg_read_hba_file() AS text;

Which would limit what the functions could be used for to their precisely intended purpose, without compromising flexibility.

Given the confusion about the patch, I think we can give folks some time
to work on any additional remote administration bulleted items while we
clean out the patches queue.

Thank you - and my apologies if anyone thought my previous rant came across too srongly, or was unjustified.

Regards, Dave

Bruce Momjian wrote:

o ...
o recycle log files
o ...

All these items are on the TODO list already.

Didn't find this on the TODO, what does it mean? Is it what
pg_logfile_rotate() does since my very first logger subprocess posts?

Regards,
Andreas

Andreas Pflug wrote:

Bruce Momjian wrote:

o ...
o recycle log files
o ...

All these items are on the TODO list already.

Didn't find this on the TODO, what does it mean? Is it what
pg_logfile_rotate() does since my very first logger subprocess posts?

Yes, I think so, and I will try to get that into CVS.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Dave Page wrote:

The idea of the patch was to give applications the full unix I/O
capabilities, allowing them to program these functions into
administration applications. I think the group generally would like a
higher-level API that allows something like:

SET GLOBAL log_statement = 'mod';

Sounds reasonable (and quite nice) for postgresql.conf, but consider
pg_hba.conf. The production systems I run at work have heavily commented
pg_hba.conf files, with entries that are intentionally ordered. As you
know, unlike postgresql.conf, there is no fixed set of possible entries.
How can we create a cleaner inteface for that, and be able to maintain
annotations in the file in a way that works well when using tools and
text editors at different times?

TODO has:

o Allow pg_hba.conf settings to be controlled via SQL

This would require a new global table that is dumped to flat file for
use by the postmaster. We do a similar thing for pg_shadow currently.

I was thinking of a global table that can be modified with
INSERT/UPDATE/DELETE and is then dumped to a flat file, like we do with
pg_shadow. For changing the file, I think we would need a sequence
number for each row. In fact, perhaps it should act like a float, so if
you insert row sequence number 2.5, it goes between rows 2 and 3, and
then the rows are renumbered, perhaps automatically. This is how APL
programming used to work, if I remember correctly.

Given the confusion about the patch, I think we can give folks some time
to work on any additional remote administration bulleted items while we
clean out the patches queue.

Thank you - and my apologies if anyone thought my previous rant came
across too srongly, or was unjustified.

You comments were justified.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce,

On 7/31/05 6:58 AM, "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:

TODO has:

o Allow pg_hba.conf settings to be controlled via SQL

This would require a new global table that is dumped to flat file
for
use by the postmaster. We do a similar thing for pg_shadow
currently.

I was thinking of a global table that can be modified with
INSERT/UPDATE/DELETE and is then dumped to a flat file, like we do with
pg_shadow. For changing the file, I think we would need a sequence
number for each row. In fact, perhaps it should act like a float, so if
you insert row sequence number 2.5, it goes between rows 2 and 3, and
then the rows are renumbered, perhaps automatically. This is how APL
programming used to work, if I remember correctly.

This sounds great.

Has there been any agreement or a concept for remote reboot?

- Luke

Dave Page wrote:

The best I have come up with is functions similar to:

SELECT pg_set_hba_line(20, 'hostssl all all 192.168.1.1/32 md5');
SELECT pg_add_hba_line(19, '# Allow global access for Dave''s test workstation');
SELECT pg_delete_hba_line(24);

However, there are a couple of things that concern me about doing it this way:

- It would make the client code much more complex as it would need to track each change the user makes individually, before applying the end result.

- It doesn't really give us a cleaner, less hackish interface and just seems like work for the sake of it.

I suppose we could just add functions like:

pg_write_hba_file('File contents'::text);
pg_read_hba_file() AS text;

Which would limit what the functions could be used for to their precisely intended purpose, without compromising flexibility.

This is what bothers me about this whole exercise - it is addressed to
our particular storage method for this stuff. That's analogous to us
having to address tuples in pages directly, rather than using a higher
level abstraction like SQL. Ideally, any API would adapt to us changing
the storage methods completely (say by putting the info in tables)
without any change being necessary in the clients. That goes for the
config file as well as the hba file, although the hba file case is
harder because order is much more important.

Incidentally, I thought I had voiced some similar concerns some time ago
- I certainly know I have had them for a while - if I am late in
expressing them then I apologise. I would just hate to see us adopt a
bad solution now that would make moving to a good solution later much
harder.

cheers

andrew

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Sun 7/31/2005 2:58 PM
To: Dave Page
Cc: Tom Lane; Magnus Hagander; PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

I was thinking of a global table that can be modified with
INSERT/UPDATE/DELETE and is then dumped to a flat file, like we do with
pg_shadow.

The problem is, pg_hba.conf might be editted via the OS unlike the text version of pg_shadow which is only editted via the server, which would make appropriate locking nigh-on impossible afaics.

Unless you're advocating only allowing pg_hba modifications via the server, in which case it must be started in default configuration before any mods can be made. That doesn't seem like a good idea to me :-(

Regards, Dave.

Luke Lonergan wrote:

Bruce,

On 7/31/05 6:58 AM, "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:

TODO has:

o Allow pg_hba.conf settings to be controlled via SQL

This would require a new global table that is dumped to flat file
for
use by the postmaster. We do a similar thing for pg_shadow
currently.

I was thinking of a global table that can be modified with
INSERT/UPDATE/DELETE and is then dumped to a flat file, like we do with
pg_shadow. For changing the file, I think we would need a sequence
number for each row. In fact, perhaps it should act like a float, so if
you insert row sequence number 2.5, it goes between rows 2 and 3, and
then the rows are renumbered, perhaps automatically. This is how APL
programming used to work, if I remember correctly.

This sounds great.

Has there been any agreement or a concept for remote reboot?

Reload of config file and rotate log files were part of the original
patch that I will try to apply. I am not sure how remote restart would
work.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Dave Page wrote:

-----Original Message----- From: Bruce Momjian
[mailto:pgman@candle.pha.pa.us] Sent: Sun 7/31/2005 2:58 PM To: Dave
Page Cc: Tom Lane; Magnus Hagander; PostgreSQL-development Subject:
Re: [HACKERS] Remote administration functionality

I was thinking of a global table that can be modified with
INSERT/UPDATE/DELETE and is then dumped to a flat file, like we do with
pg_shadow.

The problem is, pg_hba.conf might be editted via the OS unlike the text
version of pg_shadow which is only editted via the server, which would
make appropriate locking nigh-on impossible afaics.

Unless you're advocating only allowing pg_hba modifications via the
server, in which case it must be started in default configuration before
any mods can be made. That doesn't seem like a good idea to me :-(

I am thinking we will need load_pg_hba() and write_pg_hba() that will
load and write the table to pg_hba.conf.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce,

On 7/31/05 5:33 PM, "Bruce Momjian" <pgman@candle.pha.pa.us> wrote:

Reload of config file and rotate log files were part of the original
patch that I will try to apply. I am not sure how remote restart would
work.

Reload of config, refresh of IPC structures should be equivalent. It all
sounds very useful.

- Luke

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Luke Lonergan wrote:

Has there been any agreement or a concept for remote reboot?

Reload of config file and rotate log files were part of the original
patch that I will try to apply. I am not sure how remote restart would
work.

Remote reboot to change shared_buffers and other shmem-sizing parameters
seems pretty doable: all you need is a slightly more user-friendly
version of the standard response to backend crash, since that sequence
already kills and recreates the shmem segment. The postmaster itself
doesn't have to change anything.

I'm not sure how to handle remote reconfiguration of, say,
listen_addresses. The postmaster doesn't normally reconsider that after
postmaster startup. We'd have to either fix that (difficulty uncertain)
or invent a way for the postmaster to quit and restart (ick).

None of this seems like 8.1 material, though. May I remind you that
we're already a month past feature freeze?

regards, tom lane

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 01 August 2005 01:35
To: Dave Page
Cc: Tom Lane; Magnus Hagander; PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

I am thinking we will need load_pg_hba() and write_pg_hba() that will
load and write the table to pg_hba.conf.

Yeah, that bit is straghtforward enough, but what about the situation
where dba #1 updates the pg_hba table, at the same time as dba #2 is
editting pg_hba.conf in vi?

Regards, Dave.

Bruce Momjian wrote:

Dave Page wrote:

The problem is, pg_hba.conf might be editted via the OS unlike the text
version of pg_shadow which is only editted via the server, which would
make appropriate locking nigh-on impossible afaics.

Unless you're advocating only allowing pg_hba modifications via the
server, in which case it must be started in default configuration before
any mods can be made. That doesn't seem like a good idea to me :-(

I am thinking we will need load_pg_hba() and write_pg_hba() that will
load and write the table to pg_hba.conf.

Isn't the pg_hba.conf situation quite the same as postgresql.conf? The
GUCs with pg_settings is the GUC like a table, but with comments that
exceed config_generic.long_desc.

Regards,
Andreas

Dave Page wrote:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 01 August 2005 01:35
To: Dave Page
Cc: Tom Lane; Magnus Hagander; PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

I am thinking we will need load_pg_hba() and write_pg_hba() that will
load and write the table to pg_hba.conf.

Yeah, that bit is straghtforward enough, but what about the situation
where dba #1 updates the pg_hba table, at the same time as dba #2 is
editting pg_hba.conf in vi?

I don't see any way to fix that. The hope is that the administrator has
enough control that this isn't happenening --- it could already happen
now if who people edit the same file with different editors/locking
systems.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Andreas Pflug wrote:

Bruce Momjian wrote:

Dave Page wrote:

The problem is, pg_hba.conf might be editted via the OS unlike the text
version of pg_shadow which is only editted via the server, which would
make appropriate locking nigh-on impossible afaics.

Unless you're advocating only allowing pg_hba modifications via the
server, in which case it must be started in default configuration before
any mods can be made. That doesn't seem like a good idea to me :-(

I am thinking we will need load_pg_hba() and write_pg_hba() that will
load and write the table to pg_hba.conf.

Isn't the pg_hba.conf situation quite the same as postgresql.conf? The
GUCs with pg_settings is the GUC like a table, but with comments that
exceed config_generic.long_desc.

Well, pg_hba.conf is ordered, which is different, and it more of a
columnar values that postgresql.conf.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

I am thinking we will need load_pg_hba() and write_pg_hba() that
will load and write the table to pg_hba.conf.

Yeah, that bit is straghtforward enough, but what about the

situation

where dba #1 updates the pg_hba table, at the same time as

dba #2 is

editting pg_hba.conf in vi?

I don't see any way to fix that.  The hope is that the 
administrator has enough control that this isn't happenening 
--- it could already happen now if who people edit the same 
file with different editors/locking systems.

The difference is that if the other admin edited it in vi *last week* it
will still break with your way, unless every admin always rembers to do
load_pg_hba() before doing *anything at all*.

I fail to see how this is better than just editing the file. Because it
basically *is* a file editing function limited to pg_hba.conf. Perhaps
what we need is a file reader/writer that is hardcoded to the
pg_hba.conf file?

//Magnus

On Mon, Aug 01, 2005 at 12:28:55AM -0400, Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Luke Lonergan wrote:

Has there been any agreement or a concept for remote reboot?

Reload of config file and rotate log files were part of the original
patch that I will try to apply. I am not sure how remote restart would
work.

Remote reboot to change shared_buffers and other shmem-sizing parameters
seems pretty doable: all you need is a slightly more user-friendly
version of the standard response to backend crash, since that sequence
already kills and recreates the shmem segment. The postmaster itself
doesn't have to change anything.

Let's consider what to do if the new shmem size is bigger than the
current value, and the new value exceeds kernel limits. How can we
measure that in advance? Maybe create a new segment, sized as the
difference between new and old; then destroy both and recreate the new,
bigger one. It doesn't strike me as super straightforward.

Are we prepared to "rollback to a known-safe value"?

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Los dioses no protegen a los insensatos. �stos reciben protecci�n de
otros insensatos mejor dotados" (Luis Wu, Mundo Anillo)

"Dave Page" <dpage@vale-housing.co.uk> writes:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]

I am thinking we will need load_pg_hba() and write_pg_hba() that will
load and write the table to pg_hba.conf.

Yeah, that bit is straghtforward enough, but what about the situation
where dba #1 updates the pg_hba table, at the same time as dba #2 is
editting pg_hba.conf in vi?

"Don't do that then."

With this change, editing ph_hba.conf by hand should only be done when
the database is down...

-Doug

Magnus Hagander wrote:

I am thinking we will need load_pg_hba() and write_pg_hba() that
will load and write the table to pg_hba.conf.

Yeah, that bit is straghtforward enough, but what about the

situation

where dba #1 updates the pg_hba table, at the same time as

dba #2 is

editting pg_hba.conf in vi?

I don't see any way to fix that.  The hope is that the 
administrator has enough control that this isn't happenening 
--- it could already happen now if who people edit the same 
file with different editors/locking systems.

The difference is that if the other admin edited it in vi *last week* it
will still break with your way, unless every admin always rembers to do
load_pg_hba() before doing *anything at all*.

Yes, good point. In thinking about this, I think we are better having
the load() function load the file into a temporary table, which can then
be modified and flushed down to the flat file. Another option is that
queries to the table automatically read the flat file, but that might
force writes to the file on first update, so that might be bad.

I fail to see how this is better than just editing the file. Because it
basically *is* a file editing function limited to pg_hba.conf. Perhaps
what we need is a file reader/writer that is hardcoded to the
pg_hba.conf file?

It allows remote administration, and by using columns for the
pg_hba.conf lines (except for comments), we are making it somewhat
easier.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Isn't the pg_hba.conf situation quite the same as postgresql.conf? The
GUCs with pg_settings is the GUC like a table, but with comments that
exceed config_generic.long_desc.

Well, pg_hba.conf is ordered,

From a text editor user's view, postgresql.conf is ordered too. I'd be
annoyed if some function would screw it up; same with comments which are
deliberately placed where they are.

which is different, and it more of a
columnar values that postgresql.conf.

Hm, pg_settings gives me the same picture.

Regards,
Andreas

The difference is that if the other admin edited it in vi

*last week*

it will still break with your way, unless every admin

always rembers

to do
load_pg_hba() before doing *anything at all*.

Yes, good point. In thinking about this, I think we are
better having the load() function load the file into a
temporary table, which can then be modified and flushed down
to the flat file. Another option is that queries to the
table automatically read the flat file, but that might force
writes to the file on first update, so that might be bad.

That would be very bad. You can only flush at controlled times.

I fail to see how this is better than just editing the

file. Because

it basically *is* a file editing function limited to pg_hba.conf.
Perhaps what we need is a file reader/writer that is

hardcoded to the

pg_hba.conf file?

It allows remote administration, and by using columns for the
pg_hba.conf lines (except for comments), we are making it
somewhat easier.

I fail to see a real use-case for somebody editing pg_hba.conf *by hand*
using this. I can see it happening through a tool like phppgadmin or
pgadmin, in which case this will actually make it *harder* to implement.

//Magnus

Andreas Pflug wrote:

Bruce Momjian wrote:

Isn't the pg_hba.conf situation quite the same as postgresql.conf? The
GUCs with pg_settings is the GUC like a table, but with comments that
exceed config_generic.long_desc.

Well, pg_hba.conf is ordered,

From a text editor user's view, postgresql.conf is ordered too. I'd be
annoyed if some function would screw it up; same with comments which are
deliberately placed where they are.

True, but there is no purpose to modify the ordering of postgresql.conf,
while with pg_hba.conf, there is a need to do that. Also,
postgresql.conf has a fixed set of lines, while pg_hba.conf doesn't.

which is different, and it more of a
columnar values that postgresql.conf.

Hm, pg_settings gives me the same picture.

Yes, we could use that for updates, rather than SET GLOBAL. Good point.
However, it seems SET GLOBAL is a cleaner API, while we can't use such a
nice API for pg_hba.conf.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

-----Original Message-----
From: Magnus Hagander [mailto:mha@sollentuna.net]
Sent: 01 August 2005 15:18
To: Bruce Momjian
Cc: Dave Page; Tom Lane; PostgreSQL-development
Subject: RE: [HACKERS] Remote administration functionality

It allows remote administration, and by using columns for the
pg_hba.conf lines (except for comments), we are making it
somewhat easier.

I fail to see a real use-case for somebody editing
pg_hba.conf *by hand*
using this. I can see it happening through a tool like phppgadmin or
pgadmin, in which case this will actually make it *harder* to
implement.

Agreed.

Regards, Dave

Magnus Hagander wrote:

The difference is that if the other admin edited it in vi *last week* it
will still break with your way, unless every admin always rembers to do
load_pg_hba() before doing *anything at all*.

What if you send patches over the wire rather than the whole or
subsets of the file. You could make the patching fail if anything
doesn't match and force the client to re-diff the file and send a
new patch if it doesn't match exactly.

Quite a large increase in complexity, but at least the window of
opportunity that it has to go wrong would be vastly reduced. Plus
you can do whatever operating specific locking on the file is
appropiate while the update is in progress without needing to expose
anything to the client.

This is sounding more like a job for something external to Postgres
and you just need some way of telling Postgres that it needs to
reload the appropiate config file though.

Sam

Magnus Hagander wrote:

The difference is that if the other admin edited it in vi

*last week*

it will still break with your way, unless every admin

always rembers

to do
load_pg_hba() before doing *anything at all*.

Yes, good point. In thinking about this, I think we are
better having the load() function load the file into a
temporary table, which can then be modified and flushed down
to the flat file. Another option is that queries to the
table automatically read the flat file, but that might force
writes to the file on first update, so that might be bad.

That would be very bad. You can only flush at controlled times.

Right.

I fail to see how this is better than just editing the

file. Because

it basically *is* a file editing function limited to pg_hba.conf.
Perhaps what we need is a file reader/writer that is

hardcoded to the

pg_hba.conf file?

It allows remote administration, and by using columns for the
pg_hba.conf lines (except for comments), we are making it
somewhat easier.

I fail to see a real use-case for somebody editing pg_hba.conf *by hand*
using this. I can see it happening through a tool like phppgadmin or
pgadmin, in which case this will actually make it *harder* to implement.

Uh, not sure why it would be harder. What system would be easier?
Sure, file I/O works, but you can't even try to do that from psql.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

The problem is, pg_hba.conf might be editted via the OS unlike the text
version of pg_shadow which is only editted via the server, which would
make appropriate locking nigh-on impossible afaics.

Alright, sorry to just jump in here in the middle, but I don't see why
pg_hba.conf couldn't be made to work just like pg_shadow (or rather,
pg_authid or whatever it is now :). It's a file on the disk, created
initially by initdb, used for initial backend-startup, but 'owned' by
the database. It's also a catalog-table, and the file is written out
every time the catalog-table is modified.

We could implement some functions, or adjust things like ALTER, to make
working with the catalog-table a little nicer/easier, etc. For
pg_hba.conf, that seems like the most sensible way (to me, anyway) to
make it remotely-administratable.

Generally I'd think the same of the other config files, though I do
appriciate the concern about how to safely restart remotely and
associated with that 'testing' the changes somehow before reverting
back. Although, it seems like that could be done with files too I'd
think. Upon a remote restart if the backend fails to start with file X,
it reverts back to the file it was originally started with.

Just my 2c.

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

Alright, sorry to just jump in here in the middle, but I don't see why
pg_hba.conf couldn't be made to work just like pg_shadow (or rather,
pg_authid or whatever it is now :).

(1) pg_hba.conf is fundamentally order-sensitive; SQL tables are
fundamentally not. I think it would be a bad idea to try to make
pg_hba.conf be an image of a SQL table.

(2) You have to be able to edit pg_hba.conf manually before you start
the server for the first time.

regards, tom lane

-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: 01 August 2005 15:41
To: Bruce Momjian
Cc: Andreas Pflug; Dave Page; Tom Lane; Magnus Hagander;
PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

The problem is, pg_hba.conf might be editted via the OS

unlike the text

version of pg_shadow which is only editted via the

server, which would

make appropriate locking nigh-on impossible afaics.

Alright, sorry to just jump in here in the middle, but I don't see why
pg_hba.conf couldn't be made to work just like pg_shadow (or rather,
pg_authid or whatever it is now :).

Because the admin doesn't edit pg_shadow using vi or some other editor,
and then possibly forget to tell the postmaster to reload it before
someone else writes a new copy via the server.

Regards, Dave.

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Alright, sorry to just jump in here in the middle, but I don't see why
pg_hba.conf couldn't be made to work just like pg_shadow (or rather,
pg_authid or whatever it is now :).

(1) pg_hba.conf is fundamentally order-sensitive; SQL tables are
fundamentally not. I think it would be a bad idea to try to make
pg_hba.conf be an image of a SQL table.

You could, of course, have an 'order by' and a column which specifies
the ordering to be applied. I'm not entirely convinced pg_hba is best
as an ordered setup anyway, and this would perhaps be an opportunity to
move it from first-match to best-match.

(2) You have to be able to edit pg_hba.conf manually before you start
the server for the first time.

We could have a default setup and options to initdb to create it
correctly if you need something different initially. I'm not convinced
you couldn't have a decent default anyway though.

Thanks,

Stephen

* Dave Page (dpage@vale-housing.co.uk) wrote:

Alright, sorry to just jump in here in the middle, but I don't see why
pg_hba.conf couldn't be made to work just like pg_shadow (or rather,
pg_authid or whatever it is now :).

Because the admin doesn't edit pg_shadow using vi or some other editor,
and then possibly forget to tell the postmaster to reload it before
someone else writes a new copy via the server.

This isn't actually an argument against my proposal. The admin doesn't
edit pg_shadow using vi because it's understood to be 'owned' by the
database. The same would be true of 'pg_hba' in my solution.

Thanks,

Stephen

-----Original Message-----
From: Douglas McNaught [mailto:doug@mcnaught.org]
Sent: 01 August 2005 15:16
To: Dave Page
Cc: Bruce Momjian; Tom Lane; Magnus Hagander; PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

"Dave Page" <dpage@vale-housing.co.uk> writes:

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]

I am thinking we will need load_pg_hba() and

write_pg_hba() that will

load and write the table to pg_hba.conf.

Yeah, that bit is straghtforward enough, but what about the

situation

where dba #1 updates the pg_hba table, at the same time as dba #2 is
editting pg_hba.conf in vi?

"Don't do that then."

With this change, editing ph_hba.conf by hand should only be done when
the database is down...

Don't tell me, tell all the users that log bugs when their changes get
lost because they didn't read that bit of the manual for whatever
version this might or might not end up in :-).

People are used to editting the file and hupping the server, and I would
guess few of us existing users re-read the pg_hba.conf manual pages when
setting up our shiny new version.

Regards, Dave.

-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: 01 August 2005 15:51
To: Dave Page
Cc: Bruce Momjian; Andreas Pflug; Tom Lane; Magnus Hagander;
PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

This isn't actually an argument against my proposal. The
admin doesn't
edit pg_shadow using vi because it's understood to be 'owned' by the
database. The same would be true of 'pg_hba' in my solution.

Only if it were moved to a different location and renamed. Otherwise
people would be bound to try to edit it as that is what they have done
for years.

Regards, Dave.

* Dave Page (dpage@vale-housing.co.uk) wrote:

This isn't actually an argument against my proposal. The
admin doesn't
edit pg_shadow using vi because it's understood to be 'owned' by the
database. The same would be true of 'pg_hba' in my solution.

Only if it were moved to a different location and renamed. Otherwise
people would be bound to try to edit it as that is what they have done
for years.

Sure, it could be moved/renamed to avoid confusion. Seems like alot
better than having the database try to deal with user-editable files,
which are in a format that's intended to be user-editable though.

Thanks,

Stephen

-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: 01 August 2005 15:36
To: Magnus Hagander
Cc: Dave Page; Tom Lane; PostgreSQL-development
Subject: Re: [HACKERS] Remote administration functionality

Uh, not sure why it would be harder. What system would be easier?
Sure, file I/O works, but you can't even try to do that from psql.

In pgAdmin we would need to track all the changes the user makes to the
file, and generate all the appropriate insert, update and delete queries
to make the server match what the user wants. With file IO, we just let
the user edit, then send the resulting file in one select statement.

WRT to doing it in psql, the average psql user is probably the same
person that would prefer to use vi or emacs to do the job anyway.

/D

I fail to see how this is better than just editing the

file. Because

it basically *is* a file editing function limited to

pg_hba.conf.

Perhaps what we need is a file reader/writer that is

hardcoded to the

pg_hba.conf file?

It allows remote administration, and by using columns for the
pg_hba.conf lines (except for comments), we are making it

somewhat

easier.

I fail to see a real use-case for somebody editing pg_hba.conf *by
hand* using this. I can see it happening through a tool like
phppgadmin or pgadmin, in which case this will actually

make it *harder* to implement.

Uh, not sure why it would be harder. What system would be easier?
Sure, file I/O works, but you can't even try to do that from psql.

Doing it from psql is the part I fail to see a real use-case for. The
people who would use psql for it would probably just use vi or emacs or
sed or whatever on the file itself.
If you needed to do it remotely, you could just call pqsl once to get
the data out to a local file, edit that file, and then call it again to
put it back on the server, no?

//Magnus

Dave Page wrote:

With this change, editing ph_hba.conf by hand should only be done when
the database is down...

Don't tell me, tell all the users that log bugs when their changes get
lost because they didn't read that bit of the manual for whatever
version this might or might not end up in :-).

People are used to editting the file and hupping the server, and I would
guess few of us existing users re-read the pg_hba.conf manual pages when
setting up our shiny new version.

A change like this would have to be mentioned prominently in the release
notes. I am afraid SET GLOBAL/postgresql.conf would have the same
issues.

One trick we could do is to warn if the loaded pg_hba.conf doesn't match
the file contents at the time we flush to disk, but I see no way to warn
cases where editing pg_hba.conf overwrites a change made by SQL.

I can't see how to offer SQL and file system editor capabilities to the
same file without such possible conflicts. I guess I then wonder if we
still want both capabilities.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Magnus Hagander wrote:

I fail to see how this is better than just editing the

file. Because

it basically *is* a file editing function limited to

pg_hba.conf.

Perhaps what we need is a file reader/writer that is

hardcoded to the

pg_hba.conf file?

It allows remote administration, and by using columns for the
pg_hba.conf lines (except for comments), we are making it

somewhat

easier.

I fail to see a real use-case for somebody editing pg_hba.conf *by
hand* using this. I can see it happening through a tool like
phppgadmin or pgadmin, in which case this will actually

make it *harder* to implement.

Uh, not sure why it would be harder. What system would be easier?
Sure, file I/O works, but you can't even try to do that from psql.

Doing it from psql is the part I fail to see a real use-case for. The
people who would use psql for it would probably just use vi or emacs or
sed or whatever on the file itself.
If you needed to do it remotely, you could just call pqsl once to get
the data out to a local file, edit that file, and then call it again to
put it back on the server, no?

I don't think the proposed read/write API would allow client-side files
to be written to the server, or at least I didn't see that API in there.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073