Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Remote administration functionality
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 ---(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: 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remote administration functionality
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 ---(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: 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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. 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Remote administration functionality
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
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remote administration functionality
-Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Remote administration functionality
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 ---(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
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 :). 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 signature.asc Description: Digital signature
Re: [HACKERS] Remote administration functionality
Stephen Frost [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remote administration functionality
-Original Message- From: Stephen Frost [mailto:[EMAIL PROTECTED] 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. ---(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 dpage@vale-housing.co.uk writes: -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remote administration functionality
-Original Message- From: Douglas McNaught [mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Remote administration functionality
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] 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 signature.asc Description: Digital signature
Re: [HACKERS] Remote administration functionality
-Original Message- From: Stephen Frost [mailto:[EMAIL PROTECTED] 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. ---(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 (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 signature.asc Description: Digital signature
Re: [HACKERS] Remote administration functionality
* 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 signature.asc Description: Digital signature
Re: [HACKERS] Remote administration functionality
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 ---(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: 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Remote administration functionality
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
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] 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: 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: 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
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] 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
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
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
-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
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
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
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
Re: [HACKERS] Remote administration functionality
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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] 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.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 [EMAIL PROTECTED] so
Re: [HACKERS] Remote administration functionality
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 ---(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
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) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
