Re: [HACKERS] Remote administration functionality
Bruce Momjian 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
On 2005-08-01, Oliver Jowett <[EMAIL PROTECTED]> wrote: > There's no dependency on socket PF mentioned there, and the obvious > reading of that text is that a "level identifier" uniquely identifies > the "protocol controlling the option" -- so IPPROTO_TCP unambiguously > means "the TCP protocol". You're reading more into the spec than is present. It should be obvious that the existence of, number of and organization of any layers that might happen to exist below the socket layer is entirely dependent on the protocol family. The fact that PF-dependent constants are used for referring to such layers should reinforce this. > Having multiple socket-PF-dependent namespaces which might overlap is > just asking for hard-to-find bugs (if you accidentally manage to use the > wrong namespace for the socket, you run the risk of getting weird > behaviour rather than an error). Think about what happens when you add a brand-new protocol family. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
Andrew - Supernews wrote: > On 2005-07-31, Oliver Jowett <[EMAIL PROTECTED]> wrote: > >>I'm not worried about changing values; I think that representing the >>"option level" as an IP protocol number, in an interface that >>encompasses non-IP protocols, is a bad API design decision. > > The interpretation of that parameter, if not equal to SOL_SOCKET, is > clearly protocol-dependent and therefore driven by the protocol family of > the socket. It's *not* clearly protocol dependent, that's my point about the API not being clearly designed. SUS just says this: >> The level argument specifies the protocol level at which the option >> resides. To set options at the socket level, specify the level argument >> as SOL_SOCKET. To set options at other levels, supply the appropriate >> level identifier for the protocol controlling the option. For example, >> to indicate that an option is interpreted by the TCP (Transport Control >> Protocol), set level to IPPROTO_TCP as defined in the >> header. There's no dependency on socket PF mentioned there, and the obvious reading of that text is that a "level identifier" uniquely identifies the "protocol controlling the option" -- so IPPROTO_TCP unambiguously means "the TCP protocol". Having multiple socket-PF-dependent namespaces which might overlap is just asking for hard-to-find bugs (if you accidentally manage to use the wrong namespace for the socket, you run the risk of getting weird behaviour rather than an error). Still counts as badly designed in my book, sorry. -O ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
On 2005-07-31, Oliver Jowett <[EMAIL PROTECTED]> wrote: > I'm not worried about changing values; I think that representing the > "option level" as an IP protocol number, in an interface that > encompasses non-IP protocols, is a bad API design decision. The interpretation of that parameter, if not equal to SOL_SOCKET, is clearly protocol-dependent and therefore driven by the protocol family of the socket. > If the rule for setsockopt is "pass the IP protocol number as the level > argument", then what value should I pass to manipulate, say, > X.25-specific settings on an X.25 socket? The rule is "pass whatever value the socket's protocol family expects". For IP that happens to be an IP protocol number. For other families it is some constant meaningful to that family. > In practice you use a synthetic value which is outside the range of > valid IP protocols. No, you don't. There is no assumption that socket level parameters form a single namespace (with the exception of SOL_SOCKET which is common to all protocol families). > getprotoent() won't know about this value getprotoent() is specific to the INET address family and makes no attempt to pretend to support anything else (if it did, it'd need to take an AF_* parameter to disambiguate between families). >> Also, that assignment is not mere BSD-ism, it's specified by the Single >> Unix Spec, as well as every other document I could lay my hands on >> today. Whoever wrote the comments in linux/socket.h is just >> clueless. > > That seems a bit unfair, the comment is accurate (the SOL_* constants do > need to match the protocol number assignments for compatibility with > code that expects the BSD behaviour) and might even predate SUS. It's based on a fundamental misunderstanding that SOL_* constants other than SOL_SOCKET should even exist and that they form a namespace (which they do not). -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Remote administration functionality
Bruce, On 7/31/05 5:33 PM, "Bruce Momjian" 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
Re: [HACKERS] Remote administration functionality
Dave Page wrote: > > > > -Original Message- From: Bruce Momjian > [mailto:[EMAIL PROTECTED] 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 ---(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
Re: [HACKERS] Remote administration functionality
Luke Lonergan wrote: > Bruce, > > On 7/31/05 6:58 AM, "Bruce Momjian" 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
[HACKERS] pg_dumpall --clean versus roles and shared dependencies
I've been trying to figure out what to do about pg_dumpall's --clean option in view of our recent changes. The problem is that pg_dumpall tries to delete existing users and groups by putting this in its output script: DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0'); DELETE FROM pg_group; CVS tip of course will just respond to these with ERROR: cannot delete from a view HINT: You need an unconditional ON DELETE DO INSTEAD rule. So we have two problems: what do we want 8.1 pg_dumpall to do instead, and what are we going to do about legacy pg_dump scripts that already contain these commands? A couple of relevant points: * Nowhere else do pg_dump and pg_dumpall interpret --clean as a license for a scorched-earth policy; rather, it means "drop the specific objects you are going to re-create". One could argue that these commands are therefore wrong by design, and what we should emit instead is DROP ROLE commands for just the individual roles we are going to create. * In view of the shared-dependencies patch, it is *highly* likely that some or all of the deletions would fail anyway, due to the users owning objects or permissions that haven't been deleted (yet). We could reduce the risk of this by emitting DROP DATABASE commands before the DROP ROLE commands, but of course this doesn't fix things if there are additional databases in the target installation. I am strongly tempted to propose that --clean is wrongheaded when it comes to roles, and that pg_dumpall should just always emit all role information in the style CREATE ROLE foo; ALTER ROLE foo WITH ... options ... ; which will have the effect of ensuring that the role exists with all the desired settings whether it pre-existed or not. Role membership data is a different story. Part of the effect of DELETE FROM pg_group; was to eliminate group membership data as well as the groups themselves. As of CVS tip, I have the code doing this instead: DELETE FROM pg_auth_members; followed by GRANT commands to restore membership links. I do not like this solution though. In the first place, it is still following a scorched-earth policy, which will completely mess up any pre-existing groups in the destination installation, even (or especially) if they are unrelated to what the dump script is loading. In the second place, this is certainly failing to learn from experience: we should not have the output scripts presuming such familiarity with system catalogs of future Postgres releases. One possibility is to invent a "REVOKE role FROM *" kind of command and have --clean mode issue that for each role being reloaded. This still leaves us with the question of "what about the DELETE commands in existing scripts?". I thought about adding rules and triggers to try to make those operations do approximately what is intended, but I fear it's a hopeless cause: because of shared dependencies there is little or no likelihood that *all* of the individual user drops will succeed, and since they would necessarily be happening in a single transaction, that means none of them will. So my current idea is to just ignore the problem: those commands won't do what is intended but they should be relatively harmless. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Remote administration functionality
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Remote administration functionality
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
Re: [HACKERS] Remote administration functionality
Bruce, On 7/31/05 6:58 AM, "Bruce Momjian" 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
Re: [HACKERS] Remote administration functionality
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
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
Tom Lane wrote: > Oliver Jowett <[EMAIL PROTECTED]> writes: > >>per my linux/socket.h: > > >>>/* Setsockoptions(2) level. Thanks to BSD these must match IPPROTO_xxx */ >>>#define SOL_IP 0 >>>/* #define SOL_ICMP 1 No-no-no! Due to Linux :-) we cannot use >>>SOL_ICMP=1 */ >>>#define SOL_TCP 6 > > >>(I won't get into why using wire-level-protocol constants for syscall >>option numbering is a bad idea.. :) > > > [ raised eyebrow... ] You think the wire protocol numbers are more > likely to change than the syscall assignments? Consider the range of > systems that each change would affect. I'm not worried about changing values; I think that representing the "option level" as an IP protocol number, in an interface that encompasses non-IP protocols, is a bad API design decision. If the rule for setsockopt is "pass the IP protocol number as the level argument", then what value should I pass to manipulate, say, X.25-specific settings on an X.25 socket? In practice you use a synthetic value which is outside the range of valid IP protocols. getprotoent() won't know about this value and there's no IPPROTO_ define for it -- just a SOL_ one. So, er, why were you using IP protocol constants as levels again? A clear design would have them as two separate namespaces even if they happened to share values. > Also, that assignment is not mere BSD-ism, it's specified by the Single > Unix Spec, as well as every other document I could lay my hands on > today. Whoever wrote the comments in linux/socket.h is just > clueless. That seems a bit unfair, the comment is accurate (the SOL_* constants do need to match the protocol number assignments for compatibility with code that expects the BSD behaviour) and might even predate SUS. >From a portability point of view, it certainly seems better to use IPPROTO_* and I have no problem with doing that. It just grates at a design level. Anyway, this doesn't affect the patch one way or the other, which is why I didn't really want to get into it in the first place.. -O ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remote administration functionality
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]
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
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remote administration functionality
-Original Message- From: [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: Add GUC variables to control keep-alive
Oliver Jowett <[EMAIL PROTECTED]> writes: > per my linux/socket.h: >> /* Setsockoptions(2) level. Thanks to BSD these must match IPPROTO_xxx */ >> #define SOL_IP 0 >> /* #define SOL_ICMP 1 No-no-no! Due to Linux :-) we cannot use >> SOL_ICMP=1 */ >> #define SOL_TCP 6 > (I won't get into why using wire-level-protocol constants for syscall > option numbering is a bad idea.. :) [ raised eyebrow... ] You think the wire protocol numbers are more likely to change than the syscall assignments? Consider the range of systems that each change would affect. Also, that assignment is not mere BSD-ism, it's specified by the Single Unix Spec, as well as every other document I could lay my hands on today. Whoever wrote the comments in linux/socket.h is just clueless. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
