SEO

-----Original Message-----
From: Bruce Momjian [mailtogman@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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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

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)

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match