Обсуждение: Remote administration functionality

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.confo  edit pg_hba.confo  reload the config fileso  restart the server (for config variables
requiringrestart)o  view log fileso  recycle log fileso  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,
Pennsylvania19073
 

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
yesterdayand 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
haveheavily 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
tomaintain 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,
beforeapplying 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,
Pennsylvania19073
 

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
similarthing 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,
Pennsylvania19073
 

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,
beforeapplying 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
theserver, 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
configurationbefore 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,
Pennsylvania19073
 

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,
Pennsylvania19073
 

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,
Pennsylvania19073
 

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,
Pennsylvania19073
 

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

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,
Pennsylvania19073
 

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,
Pennsylvania19073
 

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

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

"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

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

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,
Pennsylvania19073
 

> -----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,
Pennsylvania19073
 

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,
Pennsylvania19073