Re: [HACKERS] WAL file location
On Fri, 2 Aug 2002, Thomas Lockhart wrote: [Symlinks] don't scale, Given that we have only one directory for the log file, this would not appear to be a problem. they are not portable, That's certainly a problem if we intend to run on systems without them. and it is difficult for applications (like the Postgres backend) to know that they are dealing with a simlink or a real file. Er...that's the whole point of symlinks. Not that I really care either way about the whole issue, so long as we do *something*. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
On Wed, 2002-07-31 at 22:20, Bruce Momjian wrote: I am wondering why we even want to specify the WAL location anywhere except as a flag to initdb. If you specify a location at initdb time, it creates the /xlog directory, then symlinks it into /data. Does this have any negative implications for Win32 ports? Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] WAL file location
I am wondering why we even want to specify the WAL location anywhere except as a flag to initdb. If you specify a location at initdb time, it creates the /xlog directory, then symlinks it into /data. Does this have any negative implications for Win32 ports? Sure. the symlinks thing was just a suggestion. Everything else is portable for sure... Or is there some other area you are concerned about? - Thomas ---(end of broadcast)--- TIP 3: 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] WAL file location
On Fri, 2 Aug 2002, Thomas Lockhart wrote: I am wondering why we even want to specify the WAL location anywhere except as a flag to initdb. If you specify a location at initdb time, it creates the /xlog directory, then symlinks it into /data. Does this have any negative implications for Win32 ports? Sure. the symlinks thing was just a suggestion. Everything else is portable for sure... Or is there some other area you are concerned about? NTFS does support symlinks. It's just not very well known, but the gnu utilities for windows can let you create soft links. Scott Marlowe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
On Fri, 2002-08-02 at 13:46, Thomas Lockhart wrote: I am wondering why we even want to specify the WAL location anywhere except as a flag to initdb. If you specify a location at initdb time, it creates the /xlog directory, then symlinks it into /data. Does this have any negative implications for Win32 ports? Sure. the symlinks thing was just a suggestion. Everything else is portable for sure... Or is there some other area you are concerned about? Well, as another poster pointed out, Cygwin does support soft links but I was also under the impression that lots of Win32 related development was underway. I wasn't sure if those plans called for the use of Cygwin or not. I was just trying to highlight a possible cause for concern...as I honestly don't know how it relates to the current Win32 efforts. Greg signature.asc Description: This is a digitally signed message part
Re: [HACKERS] WAL file location
Thomas Lockhart wrote: I am wondering why we even want to specify the WAL location anywhere except as a flag to initdb. If you specify a location at initdb time, it creates the /xlog directory, then symlinks it into /data. Does this have any negative implications for Win32 ports? Sure. the symlinks thing was just a suggestion. Everything else is portable for sure... Or is there some other area you are concerned about? I was just wondering why we would deal with environment variables or postgresql.conf settings. Just make it an initdb flag, create it in the desired location with a symlink in /data and then we don't have to do any more work for WAL locations unless people want to move it around after then initdb'ed, in which case they have to do it manually. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
... I was just wondering why we would deal with environment variables or postgresql.conf settings. Just make it an initdb flag, create it in the desired location with a symlink in /data and then we don't have to do any more work for WAL locations unless people want to move it around after then initdb'ed, in which case they have to do it manually. Well, I have the same reaction to symlinks as some others might have to environment variables ;) Symlinks are inherently evil for determining fundamental properties of our database, and inherently evil for determining locations of files within our database. They don't scale, they are not portable, and it is difficult for applications (like the Postgres backend) to know that they are dealing with a simlink or a real file. - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
Thomas Lockhart wrote: ... I was just wondering why we would deal with environment variables or postgresql.conf settings. Just make it an initdb flag, create it in the desired location with a symlink in /data and then we don't have to do any more work for WAL locations unless people want to move it around after then initdb'ed, in which case they have to do it manually. Well, I have the same reaction to symlinks as some others might have to environment variables ;) Symlinks are inherently evil for determining fundamental properties of our database, and inherently evil for determining locations of files within our database. They don't scale, they are not portable, and it is difficult for applications (like the Postgres backend) to know that they are dealing with a simlink or a real file. OK, I understand now, though I personally like symlinks. At least I understand your point of view. From my perspective, I think they are portable, they do scale unless you are talking about tons of symlinks, which we aren't, and I don't think PostgreSQL has to care whether they are symlinks or not. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
On Wed, Jul 31, 2002 at 11:20:35PM -0400, Bruce Momjian wrote: I am wondering why we even want to specify the WAL location anywhere except as a flag to initdb. If you specify a location at initdb time, it creates the /xlog directory, then symlinks it into /data. I thought the whole point of it was to make it easy to move WAL. Which is certainly a Good Thing. A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WAL file location
On Tue, 30 Jul 2002, Thomas Lockhart wrote: The no envar camp has not thought through the issues yet, though the issues can be found in the threads. Better to decide what the requirements are before throwing out the solution. Ok, so what issues has the no envvar camp not yet dealt with? What's missing in that patch posted here a while back to specify your data files in the configuration file? (Presumably we'd just add the log file to that in a similar way.) cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
... But ... my recollection is that we've had a *huge* number of complaints about the initlocation behavior, at least by comparison to the number of people using the feature. No one can understand how it works, let alone how to configure it so that it works reliably. I really fail to understand why you want to drive this new feature off environment variables. You say you've pointed out the utility and desirability of doing it that way, but I sure missed it; would you explain again? Tomorrow, when I've got my sarcasm back in the cellar ;) And what the heck are you doing up this late???!!! - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
On Wed, Jul 31, 2002 at 02:00:52AM -0400, Tom Lane wrote: let alone how to configure it so that it works reliably. I really fail to understand why you want to drive this new feature off environment variables. You say you've pointed out the utility and desirability of doing it that way, but I sure missed it; would you explain again? Without wishing to argue for one direction or another, do I have this description of the options right: a. The system uses no environment variables at all; some other method is used to determine where the config file is (maybe compiled into the code); b. The system might use only one environment variable, which sets the data dir; c. The system (in some cases optionally) uses several environment variables to set options. Some of these may be set in the config file as well. If I understand it, nobody is really arguing for (a). I think the argument for (b), from a security point of view, is that it is simpler: fewer variables offer fewer points of attack. Also, from the point of view of support, (b) is simpler, because with only one possible environment variable issue, there will be fewer troubles. (I have my doubts about the latter, but never mind that now.) I think the argument for (c) is that it is maximally flexible. Allowing a DBA to manage things in whatever way s/he is comfortable allows for competent administration. If it is a potential foot gun, well, there are already plenty of those. Is this a fair account? A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
... Is this a fair account? Yes. You may note that we have not explored the implementation details on any of these, so the attributes of each are not cast in stone (except for the purposes of argument of course ;) - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
Andrew Sullivan [EMAIL PROTECTED] writes: a.The system uses no environment variables at all; some other method is used to determine where the config file is (maybe compiled into the code); If I understand it, nobody is really arguing for (a). I am. I see absolutely no advantage in depending on environment variables rather than a config file. Here's another point beyond the ones I've made already: config files are self-documenting if we set them up in the style used by postgresql.conf (ie, comments showing all the allowed settings) --- self-documenting with respect to both what you might do, and what you actually have done in the running system. Environment variables are not; do you know exactly which strings in your environment affect Postgres, or what other settings you might have made but didn't? Where would you go to find out? (This is partly a failure of documentation, no doubt, but the point about a config file is that it offers an extremely obvious place to find out.) Also, how could you find out the actual configuration of a running server ... especially if you are admining it remotely? We have SHOW for GUC variables, and nothing at all for environment variables. Bottom line: we have an extremely nice configuration engine in place already. I really fail to understand why we want to ignore it and emulate inferior pre-GUC approaches. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
On Wed, Jul 31, 2002 at 10:23:07AM -0400, Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] writes: a. The system uses no environment variables at all; some other method is used to determine where the config file is (maybe compiled into the code); If I understand it, nobody is really arguing for (a). I am. I see absolutely no advantage in depending on environment Ok, how then would one set the location of the config file? Though I mentioned it, I don't really thing that compiled-in is an option: I don't want to have to have four versions of the binary to just to run four postmasters on four ports. Maybe a --with-config-file option to start the postmaster? And I presume this is all for the server only, right? Nobody is talking about getting rid of (for instance) $PGPORT for clients, right? (I'm sorry if I seem obtuse, or if this is really none of my business, since I'm not offering to fix this up, since I can't. But I'm very keen to make sure that administration of large postgres installations doesn't become terribly difficult.) A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
Andrew Sullivan [EMAIL PROTECTED] writes: Ok, how then would one set the location of the config file? The config file itself has to be found the same way we do it now, obviously: either a command line argument or the environment variable $PGDATA. But that's a red herring. This thread is not about where you find the config file, it's about locations for other files such as WAL logs and tablespaces. And I presume this is all for the server only, right? Nobody is talking about getting rid of (for instance) $PGPORT for clients, right? I wasn't. regards, tom lane ---(end of broadcast)--- TIP 3: 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] WAL file location
On Wed, Jul 31, 2002 at 11:09:58AM -0400, Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] writes: Ok, how then would one set the location of the config file? The config file itself has to be found the same way we do it now, obviously: either a command line argument or the environment variable $PGDATA. That was my option (b): one environment variable to locate the file, and nothing else. I can think of ways that might be a little awkward, but not intolerably so. Thanks for the clarification. A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
Bruce Momjian writes: Thomas, are you going to extend this to locations for any table/index? Seems whatever we do for WAL should fix in that scheme. The trick is that it might not. For relations you simply need a system table mapping location names to file system locations, and you can add and remove those mappings at will. Moving an object between locations can be accomplished by locking the object down, moving the files, updating the system catalogs, and unlocking. But the location of the WAL logs, and the commit logs, if anyone is thinking in that direction, needs to be known to initdb. And if you want to move them later you'd need to halt the entire system or implement some smarts for transition. So I'm not sure if these things fit under one umbrella. It would probably be nice if they did. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
On Wed, 31 Jul 2002, Andrew Sullivan wrote: Ok, how then would one set the location of the config file? Option on the command line. Works for lots of different servers out there already (BIND, apache, etc.). Whether we also want to emulate them using a compiled-in default if the command line option is not specified, I don't know. I would tend to prefer not, because that might change from system to system, and also if someone leaves the default config file around but isn't using it, you can accidently start up postgres with the wrong config. But I won't argue that point heavily. I hate environment variables for servers because the environment changes, is hard to detect on some systems (ps always shows you the command line unless you muck with it), etc. And I presume this is all for the server only, right? Nobody is talking about getting rid of (for instance) $PGPORT for clients, right? I'm certainly not wanting to get rid of it on the client. I won't go into the reasons unless anybody really cares cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
On Wed, 31 Jul 2002, Peter Eisentraut wrote: But the location of the WAL logs, and the commit logs, if anyone is thinking in that direction, needs to be known to initdb. And if you want to move them later you'd need to halt the entire system I don't see this as a big problem. Right now you also have to halt the entire system to move them. And there are other configuration file options which also cannot be changed after the system has started. I don't see a big need for being able to move the log files around when the system is running. If there is such a need, let's make it a separate feature from being able to specify the location in the logfile, and implement it separately so we don't slow down the original feature we're all looking for. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
Curt Sampson wrote: On Wed, 31 Jul 2002, Andrew Sullivan wrote: Ok, how then would one set the location of the config file? Option on the command line. Works for lots of different servers out there already (BIND, apache, etc.). Whether we also want to emulate them using a compiled-in default if the command line option is not specified, I don't know. I would tend to prefer not, because that might change from system to system, and also if someone leaves the default config file around but isn't using it, you can accidently start up postgres with the wrong config. But I won't argue that point heavily. I hate environment variables for servers because the environment changes, is hard to detect on some systems (ps always shows you the command line unless you muck with it), etc. And I presume this is all for the server only, right? Nobody is talking about getting rid of (for instance) $PGPORT for clients, right? I'm certainly not wanting to get rid of it on the client. I won't go into the reasons unless anybody really cares I am wondering why we even want to specify the WAL location anywhere except as a flag to initdb. If you specify a location at initdb time, it creates the /xlog directory, then symlinks it into /data. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WAL file location
On Tue, Jul 30, 2002 at 08:10:02PM +0900, Curt Sampson wrote: BTW, you mention in another message that environment variables work well for you. Well, they are a security problem waiting to happen, IMHO. Do you have any objections to having a file containing a list of the various data directories? Maybe we could put the log directory in it, too, and have PGDATA point to that file, so we'd need only one environment variable? (And then we'd have a more obviously accessable list of where everything is, as well.) I guess I'm dumb, but I'm not seeing how these environment variables are a big security risk. It's true, however, that putting such settings in the config file or something might be better, if only because that limits the number of places where various config things happen. In any case, it'd be a _very good_ thing to have a tablespace-like facility. Its lack is a real drawback of PostgreSQL for anyone looking to manage a large installation. RAID is your friend, of course, but one can get a real boost to both performance and flexibility by adding this sort of feature, and anything that moves PostgreSQL closer to such a goal is, in my view, nothing but a good thing. A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WAL file location
On Tue, Jul 30, 2002 at 02:05:57PM -0400, Tom Lane wrote: If we add more environment-variable-dependent mechanisms to allow more different things to be done, we increase substantially the odds of creating an exploitable security hole. Ok, true enough, but I'm not sure that a config file or any other such mechanism is any safer. As Lamar Owen said, anyone who can poison the postgres user's environment can likely do evil things to postgresql.conf as well. Still, environment variables _are_ a notorious weak point for crackers. As I said, I don't much care how it is implemented, but I think _that_ it is implemented is important, at least for our (Liberty's) uses. If the only way it's going to be done is to accept a potential security risk, maybe the answer is to allow the security risk, but set by default to off. A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 3: 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] WAL file location
On Mon, 29 Jul 2002, Thomas Lockhart wrote: It is supported by the installation environment, and does not require the explicit three steps of 1) creating a new directory area 2) moving files to the new area 3) creating a symlink to point to the new area So basically it gives you the ability to initdb and have your log files elsewhere without having to shutdown, move the log, link, and restart. Is there anything else it adds? BTW, you mention in another message that environment variables work well for you. Well, they are a security problem waiting to happen, IMHO. Do you have any objections to having a file containing a list of the various data directories? Maybe we could put the log directory in it, too, and have PGDATA point to that file, so we'd need only one environment variable? (And then we'd have a more obviously accessable list of where everything is, as well.) I tend to side with you on not putting these paths in the database itself; it can make restores rather hairy. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
Andrew Sullivan wrote: On Tue, Jul 30, 2002 at 08:10:02PM +0900, Curt Sampson wrote: BTW, you mention in another message that environment variables work well for you. Well, they are a security problem waiting to happen, IMHO. Do you have any objections to having a file containing a list of the various data directories? Maybe we could put the log directory in it, too, and have PGDATA point to that file, so we'd need only one environment variable? (And then we'd have a more obviously accessable list of where everything is, as well.) I guess I'm dumb, but I'm not seeing how these environment variables are a big security risk. It's true, however, that putting such settings in the config file or something might be better, if only because that limits the number of places where various config things happen. In any case, it'd be a _very good_ thing to have a tablespace-like facility. Its lack is a real drawback of PostgreSQL for anyone looking to manage a large installation. RAID is your friend, of course, but one can get a real boost to both performance and flexibility by adding this sort of feature, and anything that moves PostgreSQL closer to such a goal is, in my view, nothing but a good thing. Why not put the WAL location in postgresql.conf. Seems like the logical location for it. You could define tablespaces there in the future too if you prefer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
On Tuesday 30 July 2002 07:10 am, Curt Sampson wrote: BTW, you mention in another message that environment variables work well for you. Well, they are a security problem waiting to happen, IMHO. Do you have any objections to having a file containing a list of the various data directories? Maybe we could put the log directory in it, too, and have PGDATA point to that file, so we'd need only one environment variable? (And then we'd have a more obviously accessable list of where everything is, as well.) $PGDATA/postgresql.conf just needs extending in this direction. There is a patch to do most of this already -- just not the WAL stuff. Due to the heat it generated the last time, and the fact that we were in beta at the time, the author of that patch left the list. Now, let me make the statement that the environment in this case is not likely to be a security issue any worse than having the stuff in postgresql.conf, as any attacker that can poison the postmaster environment can probably poison postgresql.conf. Such poisoning isn't an issue here, as postmaster is just going to gripe about the WAL files being missing, or it's going to create new ones. Since postmaster doesn't run as root, it can't be used to overwrite system files, the typcial target for environment poisoning. You might want to see about reading the archives -- even though I know they tend to be broken whenever you want to search them. The idea you mention has not only been brought up, but has been thoroughly discussed at length, and a patch exists for the majority of the locations in question, just not WAL. I have some of the discussion locally archived, but not the original patch. Search on 'Explicit config patch'. Also see 'Thoughts on the location of configuration files' and 'Explicit configuration file'. Explaining what you mean by the potential security implications would be nice. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
Andrew Sullivan [EMAIL PROTECTED] writes: I guess I'm dumb, but I'm not seeing how these environment variables are a big security risk. The trouble with relying on environment variables for paths (especially paths to places that we might scribble on) is that the postmaster has no idea which strings in its environment were actually intended for that use, and which were not. As an example, the postmaster very likely has $HOME in its environment. This means that anyone with createdb privilege can try to create a database in the postgres user's home directory. It's relatively harmless (since what will actually get mkdir'd is some name like /home/postgres/base/173918, which likely can't overwrite anything interesting) but it's still not a good idea. $PWD would be another likely attack point, and possibly one could do something with $PATH, not to mention any custom environment variables that might happen to exist in the local environment. If we add more environment-variable-dependent mechanisms to allow more different things to be done, we increase substantially the odds of creating an exploitable security hole. In any case, it'd be a _very good_ thing to have a tablespace-like facility. Absolutely. But let's not drive it off environment variables. A config file is far safer. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WAL file location
Andrew Sullivan [EMAIL PROTECTED] writes: On Tue, Jul 30, 2002 at 02:05:57PM -0400, Tom Lane wrote: If we add more environment-variable-dependent mechanisms to allow more different things to be done, we increase substantially the odds of creating an exploitable security hole. Ok, true enough, but I'm not sure that a config file or any other such mechanism is any safer. As Lamar Owen said, anyone who can poison the postgres user's environment can likely do evil things to postgresql.conf as well. Who said anything about poisoning the environment? My point was that there will be strings in the environment that were put there perfectly legitimately, but could still serve as an attack vehicle. The weakness of the existing database-locations-are-environment-variables feature is really that the attacker gets to choose which environment variable gets used, and so he can use a variable intended to serve purpose A for some other purpose B. If A and B are sufficiently different then you got trouble --- and since we are talking about a purpose B that involves writing on something, there's definitely a risk. A mechanism based only on a fixed environment variable name doesn't create the sort of threat I'm contemplating. For example, if the postmaster always and only looked at $PGXLOG to find the xlog then you'd not have this type of risk. But Thomas said he was basing the feature on database locations, and in the absence of seeing the code I don't know if he's creating a security hole or not. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
In my logic, we have PGDATA environment variable for the server only so the server can find the /data directory. After that, everything should be in /data. I see no reason to make it an environment variable. In fact, a file in /data should be able to track the xlog directory a lot better than an evironment variable will. --- Tom Lane wrote: Andrew Sullivan [EMAIL PROTECTED] writes: On Tue, Jul 30, 2002 at 02:05:57PM -0400, Tom Lane wrote: If we add more environment-variable-dependent mechanisms to allow more different things to be done, we increase substantially the odds of creating an exploitable security hole. Ok, true enough, but I'm not sure that a config file or any other such mechanism is any safer. As Lamar Owen said, anyone who can poison the postgres user's environment can likely do evil things to postgresql.conf as well. Who said anything about poisoning the environment? My point was that there will be strings in the environment that were put there perfectly legitimately, but could still serve as an attack vehicle. The weakness of the existing database-locations-are-environment-variables feature is really that the attacker gets to choose which environment variable gets used, and so he can use a variable intended to serve purpose A for some other purpose B. If A and B are sufficiently different then you got trouble --- and since we are talking about a purpose B that involves writing on something, there's definitely a risk. A mechanism based only on a fixed environment variable name doesn't create the sort of threat I'm contemplating. For example, if the postmaster always and only looked at $PGXLOG to find the xlog then you'd not have this type of risk. But Thomas said he was basing the feature on database locations, and in the absence of seeing the code I don't know if he's creating a security hole or not. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
Lamar Owen [EMAIL PROTECTED] writes: Having said all that, I still believe that this is something tailor-made for postgresql.conf. Well, exactly. Regardless of how serious you may think the security argument is, it still remains that a config-file entry seems the ideal way to do it. I can't see any good argument in favor of relying on environment variables instead. They don't bring any new functionality to the party; and we have an awful lot of work invested in putting all sorts of functionality into the GUC module. I think that doing configuration-like stuff outside the GUC framework is now something that we should resist --- or at least have a darn good reason for it when we do it. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: Having said all that, I still believe that this is something tailor-made for postgresql.conf. Well, exactly. Regardless of how serious you may think the security argument is, it still remains that a config-file entry seems the ideal way to do it. I can't see any good argument in favor of relying on environment variables instead. They don't bring any new functionality to the party; and we have an awful lot of work invested in putting all sorts of functionality into the GUC module. I think that doing configuration-like stuff outside the GUC framework is now something that we should resist --- or at least have a darn good reason for it when we do it. Thomas, are you going to extend this to locations for any table/index? Seems whatever we do for WAL should fix in that scheme. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
Thomas Lockhart writes: I've developed patches to be able to specify the location of the WAL directory, with the default location being where it is now. The patches define a new environment variable PGXLOG (a la PGDATA) and postmaster, postgres, initdb and pg_ctl have been taught to recognize a new command line switch -X a la -D. I'm not in favor of keeping this sort of information in environment variables or in a command-line option. It should be kept in permanent storage in the data area. (In other words, it should be stored in a file.) Consider, tomorrow someone wants to move the commit log or wants to keep duplicate copies of either log. We should have some extensible structure in place before we start moving things around. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
Thomas Lockhart [EMAIL PROTECTED] writes: If we add more environment-variable-dependent mechanisms to allow more different things to be done, we increase substantially the odds of creating an exploitable security hole. No. See above. Your argument seems to reduce to it's not insecure because we have these backup checks in place. Sure, but why should we use a configuration-specifying mechanism that even potentially has a security risk, when it offers no real advantage over a mechanism that does not? Disagree, but in a friendly sort of way ;) I will likely implement both, if either. Along the way I will give some specific use cases so we don't go 'round on this topic every time... I'd like to see the use case that justifies environment variables as an easier way to set Postgres parameters than a config file. In general they are not easy to use, because it's so easy to start the postmaster in the wrong environment. We used to constantly see problems from people who had different environments when they started PG by hand (from an interactive shell) vs when it got launched from a boot script. We've reduced those problems by reducing PG's sensitivity to environment settings, and I think we should continue to reduce it. Not increase it. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
... Thomas, are you going to extend this to locations for any table/index? Seems whatever we do for WAL should fix in that scheme. Yes, the longer-term goal is enabling table/index-specific locations. I'm not certain whether WAL can use *exactly* the same mechanism, since 1) the location for WAL is (currently) not particularly related to the directory structure for other resources such as databases and tables. 2) postmaster may want to access WAL-kinds of information before having access to the global database info. I'll have a question for -hackers very soon on why I seem to be having trouble adding a column to pg_class (which will end up being an OID for the internally supported view of what locations are). I'm getting access violations after adding a column which is initialized to zero and never explicitly used in the code... - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
On Tue, 30 Jul 2002, Lamar Owen wrote: Now, let me make the statement that the environment in this case is not likely to be a security issue any worse than having the stuff in postgresql.conf, as any attacker that can poison the postmaster environment can probably poison postgresql.conf. Unfortunately, the environment is already pre-poisoned. Typically the environment is full of variables that have nothing to do with postgres but which have paths pointing to various places. This is the sort of thing that might allow you to exploit an otherwise unexploitable bug in postgres. Potgres not being able to use any of that information would be one layer of security. You might argue that it's not a big one, but it's often just dumb little things like this that give you remote exploits. Since postmaster doesn't run as root, it can't be used to overwrite system files, the typcial target for environment poisoning. So? It can still be used to read some files on the system, which might provide useful information to an attacker. And future additions to postgres might change the situation. Say, for example, that someone added the ability to store data on raw devices. Now you have to worry that someone might be able to get postgres to write rubbish to some raw devices it has access to if an environment variable has /dev in it. Simplicty is always a big help to security. Rather than spending time doing a big, complex analysis of just why we think using the environment variables are safe, it's much simpler just not to use them. And if we re-used existing configuration file processing code to get the information we need, we'd also be removing some code from the system, thus removing the potential for bugs in that code. The discussion in the archives seems quite positive about the patch, except for one or two recalcitrant people that disagree with everyone else. And in the very first post I found, Tom Lane said: This whole thread makes me more and more uncomfortable about the fact that the postmaster/backend pay attention to environment variables at all. An explicit configuration file would seem a better answer. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 3: 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] WAL file location
On Tue, 30 Jul 2002, Lamar Owen wrote: On Tuesday 30 July 2002 02:34 pm, Tom Lane wrote: Who said anything about poisoning the environment? My point was that there will be strings in the environment that were put there perfectly legitimately, but could still serve as an attack vehicle. I said it. In any case, using strings that are in the environment requires an untrusted PL, or a C function. Ah. See, we already have a failure in a security analysis here. This command: CREATE DATABASE foo WITH LOCATION = 'BAR' uses a string that's in the environment. Regardless, if such a hole was exploited the only security risk to the system at large is that posed by the postgres user, which, IMHO, shouldn't even have write access to its own executables. So what you're saying is that we should make the opportunity for people to configure the system in an insecure manner? Configuration errors by administrators are probably the number one cause of security breaches, you know. I wouldn't mind seeing an example of such an exploit, for educational purposes. I don't have one. But consider a couple of possibilities: 1. The exploit can't exist until someone adds more code to postgres. So maybe it doesn't exist in 7.3, but will appear in 7.4. 2. The exploit is there, but nobody has figured it out yet. The recent BIND resolver library vulnerability has been in that code for at least ten years, but it was only last month that someone figured out that it was even there, much less how to exploit it. I've been securing systems since I started an ISP in 1995, and so I've seen a lot of security vulnerabilities come and go, and I've got a bit of a feel for what kinds of things are typically exploited. And this one one just screams, potential security vulnerability! to me. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
... I've been securing systems since I started an ISP in 1995, and so I've seen a lot of security vulnerabilities come and go, and I've got a bit of a feel for what kinds of things are typically exploited. And this one one just screams, potential security vulnerability! to me. Sure, there is screaming all over the place :) But the zeroth-order issue is not security. It is storage management for large databases. Any scheme we have for accomplishing that must hold up to scrutiny, but we can not refuse to proceed just because there are lions tigers and bears out there. I know you are being thoughtful about the issues, but the most secure database is one which is not running. The most robust database is the one with no data. We're pushing past that into large data management issues and have to find a way through the forest. Security will be one aspect by which we measure the solution. Scalability and robustness are other issues, and there are still others. We'll talk about them all before we are done ;) - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
On Tue, 30 Jul 2002, Thomas Lockhart wrote: But the zeroth-order issue is not security. It is storage management for large databases. Any scheme we have for accomplishing that must hold up to scrutiny, but we can not refuse to proceed just because there are lions tigers and bears out there. Well, I'm not sure how using a config file rather than environment variables is stopping us from proceeding cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
On Tuesday 30 July 2002 07:46 pm, Curt Sampson wrote: On Tue, 30 Jul 2002, Lamar Owen wrote: I said it. In any case, using strings that are in the environment requires an untrusted PL, or a C function. Ah. See, we already have a failure in a security analysis here. This command: CREATE DATABASE foo WITH LOCATION = 'BAR' uses a string that's in the environment. And requires you to be a database superuser anyway. You got something better? :-) If you're the database superuser, you can do anything you want inside the database. Your analysis here is faulty. So what you're saying is that we should make the opportunity for people to configure the system in an insecure manner? No, what I'm saying is that there is no such thing as absolute security -- and time is better spent where there is a measureable result. If a security hole requires root to exploit, then it's not a hole. Show me a case where an envvar can be exploited by an unprivileged database user without accessing a user written C function or some other function in an untrusted PL. Configuration errors by administrators are probably the number one cause of security breaches, you know. No. Failure to keep up with security updates is the number one cause of security breaches. I guess you could call that a configuration problem of sorts. Been there; done that. Experienced one hack in -- caught it in the act. But I _have_ been there, and I have had to clean up other people's configuration errors. I've been securing systems since I started an ISP in 1995, and so I've seen a lot of security vulnerabilities come and go, and I've got a bit of a feel for what kinds of things are typically exploited. And this one one just screams, potential security vulnerability! to me. But, just like any other vulnerability, an admin must ask the question 'is a successful exploit a problem?' Again, if an exploit requires root to activate, then it's not a problem in reality. If I have to be the database superuser to activate an ennvar exploit in postgresql, then it's not a vulnerability, as I have more powerful tools at my disposal as superuser. Things such as DROP DATABASE. Now if a normal user can easily exploit it remotely (like the two in a row for OpenBSD in the past month), then it's an issue. A big issue. You just have to keep perspective. And I'm not going to put myself as any authority on the subject, but I do have a couple of years in the trenches, having admined systems for over 15 years. I've been at it long enough to realize that I am most certainly fallible. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
Lamar Owen [EMAIL PROTECTED] writes: Ah. See, we already have a failure in a security analysis here. This command: CREATE DATABASE foo WITH LOCATION = 'BAR' uses a string that's in the environment. And requires you to be a database superuser anyway. CREATE DATABASE does not require superuser privs, only createdb which is not usually considered particular dangerous. Whether you think that there is a potentially-exploitable security hole here is not really the issue. The point is that two different arguments have been advanced against using environment variables for configuration (if you weren't counting, (1) possible security issues now or in the future and (2) lack of consistency between manual and boot-script startup), while zero (as in 0, nil, nada) arguments have been advanced in favor of using environment variables instead of configuration files. I do not see why we are debating the negative when there is absolutely no case on the positive side. regards, tom lane ---(end of broadcast)--- TIP 3: 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] WAL file location
On Tuesday 30 July 2002 11:51 pm, Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: CREATE DATABASE foo WITH LOCATION = 'BAR' And requires you to be a database superuser anyway. CREATE DATABASE does not require superuser privs, only createdb which is not usually considered particular dangerous. Pardon my misspeak, as there are those two components to the privs. My error. Typically normal users aren't given create database privileges -- at least on my systems. And, again, I'm completely for the idea of this being in postgresql.conf. But I'm not convinced that the security angle is a valid reason. The consistency reason is enough alone to warrant it being that way. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
Lamar Owen wrote: On Tuesday 30 July 2002 11:51 pm, Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: CREATE DATABASE foo WITH LOCATION = 'BAR' And requires you to be a database superuser anyway. CREATE DATABASE does not require superuser privs, only createdb which is not usually considered particular dangerous. Pardon my misspeak, as there are those two components to the privs. My error. Typically normal users aren't given create database privileges -- at least on my systems. And, again, I'm completely for the idea of this being in postgresql.conf. But I'm not convinced that the security angle is a valid reason. The consistency reason is enough alone to warrant it being that way. Agreed. Consistency argues for the postgresql.conf solution, not security. Also, I would like to see initlocation removed as soon as we get a 100% functional replacement. We have fielded too many questions about how to set it up. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WAL file location
On Tue, 30 Jul 2002, Lamar Owen wrote: On Tuesday 30 July 2002 07:46 pm, Curt Sampson wrote: Ah. See, we already have a failure in a security analysis here. This command: CREATE DATABASE foo WITH LOCATION = 'BAR' uses a string that's in the environment. And requires you to be a database superuser anyway. Yup. So once again, we're getting in to the loop well, if you do this, this other layer of security protects from some other thing and blah blah blah. Given the choice between doing something simple that eliminates one possible avenue of security holes, or doing an extensive, error-prone analysis, to try to prove that that avenue doesn't have any holes and is not likely to have any in the future, which is going to be more secure? Show me a case where an envvar can be exploited by an unprivileged database user without accessing a user written C function or some other function in an untrusted PL. Well, if this is your approach to security, we're just going to have to stop arguing here. The correct approach to security is not, leave this line of attack open, if we can't show how it could fail but close off that line of attack even if we can't show how it would fail. If you don't agree with that, you're in disagreement with me and every Internet security expert out there. No. Failure to keep up with security updates is the number one cause of security breaches. Bzzt! cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
Whether you think that there is a potentially-exploitable security hole here is not really the issue. The point is that two different arguments have been advanced against using environment variables for configuration (if you weren't counting, (1) possible security issues now or in the future and (2) lack of consistency between manual and boot-script startup), while zero (as in 0, nil, nada) arguments have been advanced in favor of using environment variables instead of configuration files. I have been counting, in both English and Spanish. Other folks can count too, and no point in just being pissy about it. You haven't been listening ;) I've discussed these issues in the past, and we get stuck in the same place. You don't like environment variables and have advanced two hypothetical issues with no specific plausible case to back it up. I have pointed out the utility and desirability otherwise. Frankly, I'm not sure why you are pushing so hard to make sure that we accomplish nothing in this area, while minimizing the joys of working out the issues. In any case, the main work is in the internal mechanisms, not in the exterior varnish. From my experience as a designer, developer, and operator of large data handling systems *without* adequate decoupling of disk topology from internal resource definitions (Ingres just didn't do it right), I'll point out that it is an issue. A big issue. With real-life examples to back it up. If the PostgreSQL solution continues to be an issue, we can continue to discuss *productive* alternatives. But there is nothing in the work ahead which paints us into a corner. As you may already know (read the docs to freshen up if not) environment variables are not required to be used for the current implementation of locations. It is supported, and I recommend their use. But absolute paths can be used also; I implemented both strategies to accommodate the difference in opinion on the pros and cons of each approach. Nothing has to be different in the upcoming work. The behavior of initlocation has been absolutely no burden on -hackers for the nearly *5 years* that it has been available, and that is the best evidence that we're just talking through hats. Let's get on with it, or at least get back to being civil. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
Thomas Lockhart [EMAIL PROTECTED] writes: ... The behavior of initlocation has been absolutely no burden on -hackers for the nearly *5 years* that it has been available, and that is the best evidence that we're just talking through hats. Let's get on with it, or at least get back to being civil. I do apologize if you felt I was being uncivil; that wasn't my intention. Nor do I want to overstate the importance of the issue; as you say, this is just a small user-interface detail, not the meat of the feature. But ... my recollection is that we've had a *huge* number of complaints about the initlocation behavior, at least by comparison to the number of people using the feature. No one can understand how it works, let alone how to configure it so that it works reliably. I really fail to understand why you want to drive this new feature off environment variables. You say you've pointed out the utility and desirability of doing it that way, but I sure missed it; would you explain again? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
... Agreed. Consistency argues for the postgresql.conf solution, not security. Also, I would like to see initlocation removed as soon as we get a 100% functional replacement. We have fielded too many questions about how to set it up. Hmm. I'm not sure the best way to look, but I was able to find three or four questions since 1999 on the mailing lists (used initlocation problem help; most other choices got lots of false hits). And they do seem to sometimes involve the inability to type commands or to define environment variables. I'll avoid the snide remarks about DBAs who don't know an envar from a hole in the wall (oops...). This day of sarcasm is getting sort of fun. Can't wait to take a shower and move on to a more polite tomorrow though ;) btw, a 100% functional replacement is escaping definition so far. The no envar camp has not thought through the issues yet, though the issues can be found in the threads. Better to decide what the requirements are before throwing out the solution. - Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
I've developed patches to be able to specify the location of the WAL directory, with the default location being where it is now. The patches define a new environment variable PGXLOG (a la PGDATA) and postmaster, postgres, initdb and pg_ctl have been taught to recognize a new command line switch -X a la -D. Uh ... if I randomly modify PGXLOG and restart the postmaster, what happens? Unless you've added code to *move* pg_xlog, this doesn't seem like a good idea. Perhaps you don't remember the current (and future) behavior of PostgreSQL. If it does not find the WAL files it declines to start up. The behavior is very similar to that for the data area. If it does not exist, the postmaster declines to start. As noted above, the default behavior remains the same as now. So what is the objection precisely? More generally, I do not like depending on postmaster environment variables --- our experience with environment variables for database locations has been uniformly bad, and so ISTM that extending that mechanism into pg_xlog is exactly the wrong direction to head. Unsupported allegation. My experience with environment variables for database locations has been uniformly good (also an unsupported allegation, but what the heck). And you will note that, as with PGDATA, the environment variable is not required. So you can easily stay away from that which you do not like, for whatever reason. From my experience with other DBMSes, storage management was always a weak point. You have suggested in the past that hardcoding paths into the database itself is the Right Way, but in my large Ingres production systems that was precisely the weak point in their support; you were hamstrung when planning and expanding storage by choices you made way back when the the system was first installed. Linking external definitions to internal logical areas enhances flexibility, it is not required and does not compromise the system. Refusing to allow them at all just limits options with no offsetting benefit. The current mechanism for moving pg_xlog around is to create a symlink from $PGDATA/pg_xlog to someplace else. I'd be all in favor of creating some code to help automate moving pg_xlog that way, but I don't think introducing an environment variable will improve matters. Your opinion is noted. I'm intending to head towards finer control of locations of tables and indices next by implementing some notion of named storage area, perhaps including the tablespace nomenclature though it would not be the same thing as in Oracle since it would not be fixed size but more akin to the secondary locations that we support now for entire databases. The existing secondary-location mechanism is horrible. Please do not emulate it... I've not quite understood why you have a strong opinion on something you don't care about and don't care to contribute to. - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] WAL file location
I've developed patches to be able to specify the location of the WAL directory, with the default location being where it is now. The patches define a new environment variable PGXLOG (a la PGDATA) and postmaster, postgres, initdb and pg_ctl have been taught to recognize a new command line switch -X a la -D. Comments or suggestions? I'm intending to head towards finer control of locations of tables and indices next by implementing some notion of named storage area, perhaps including the tablespace nomenclature though it would not be the same thing as in Oracle since it would not be fixed size but more akin to the secondary locations that we support now for entire databases. There has been some discussion of this already but I'm not recalling that someone has picked this up yet. Comments or suggestions? - Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] WAL file location
Thomas Lockhart [EMAIL PROTECTED] writes: I've developed patches to be able to specify the location of the WAL directory, with the default location being where it is now. The patches define a new environment variable PGXLOG (a la PGDATA) and postmaster, postgres, initdb and pg_ctl have been taught to recognize a new command line switch -X a la -D. Uh ... if I randomly modify PGXLOG and restart the postmaster, what happens? Unless you've added code to *move* pg_xlog, this doesn't seem like a good idea. More generally, I do not like depending on postmaster environment variables --- our experience with environment variables for database locations has been uniformly bad, and so ISTM that extending that mechanism into pg_xlog is exactly the wrong direction to head. The current mechanism for moving pg_xlog around is to create a symlink from $PGDATA/pg_xlog to someplace else. I'd be all in favor of creating some code to help automate moving pg_xlog that way, but I don't think introducing an environment variable will improve matters. I'm intending to head towards finer control of locations of tables and indices next by implementing some notion of named storage area, perhaps including the tablespace nomenclature though it would not be the same thing as in Oracle since it would not be fixed size but more akin to the secondary locations that we support now for entire databases. The existing secondary-location mechanism is horrible. Please do not emulate it... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WAL file location
On Mon, 29 Jul 2002, Thomas Lockhart wrote: I've developed patches to be able to specify the location of the WAL directory, with the default location being where it is now. The patches define a new environment variable PGXLOG (a la PGDATA) and postmaster, postgres, initdb and pg_ctl have been taught to recognize a new command line switch -X a la -D. What's the advantage of this over just using a symlink? I'm intending to head towards finer control of locations of tables and indices next by implementing some notion of named storage area, perhaps including the tablespace nomenclature This I would really love to have. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] WAL file location
On Tue, 30 Jul 2002, Tom Lane wrote: More generally, I do not like depending on postmaster environment variables --- our experience with environment variables for database locations has been uniformly bad The existing secondary-location mechanism is horrible. Please do not emulate it... Right. I get really, really worried about security issues when I see something like just specify an environment variable. Who knows what the heck else is in the environment. I'd really like to see that removed and replaced with a configuration file or something similar. cjs -- Curt Sampson [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] WAL file location
Tom Lane wrote: Thomas Lockhart [EMAIL PROTECTED] writes: I've developed patches to be able to specify the location of the WAL directory, with the default location being where it is now. The patches define a new environment variable PGXLOG (a la PGDATA) and postmaster, postgres, initdb and pg_ctl have been taught to recognize a new command line switch -X a la -D. Uh ... if I randomly modify PGXLOG and restart the postmaster, what happens? Unless you've added code to *move* pg_xlog, this doesn't seem like a good idea. More generally, I do not like depending on postmaster environment variables --- our experience with environment variables for database locations has been uniformly bad, and so ISTM that extending that mechanism into pg_xlog is exactly the wrong direction to head. The current mechanism for moving pg_xlog around is to create a symlink from $PGDATA/pg_xlog to someplace else. I'd be all in favor of creating some code to help automate moving pg_xlog that way, but I don't think introducing an environment variable will improve matters. 100% agree. I'm intending to head towards finer control of locations of tables and indices next by implementing some notion of named storage area, perhaps including the tablespace nomenclature though it would not be the same thing as in Oracle since it would not be fixed size but more akin to the secondary locations that we support now for entire databases. The existing secondary-location mechanism is horrible. Please do not emulate it... 200% agree. ;-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] WAL file location
I've developed patches to be able to specify the location of the WAL directory, with the default location being where it is now. The patches define a new environment variable PGXLOG (a la PGDATA) and postmaster, postgres, initdb and pg_ctl have been taught to recognize a new command line switch -X a la -D. What's the advantage of this over just using a symlink? It is supported by the installation environment, and does not require the explicit three steps of 1) creating a new directory area 2) moving files to the new area 3) creating a symlink to point to the new area The default behavior for the patch is exactly what happens now, with the location plopped into $PGDATA/pg_xlog/ I'm intending to head towards finer control of locations of tables and indices next by implementing some notion of named storage area, perhaps including the tablespace nomenclature This I would really love to have. Yup. These are all pieces of overall resource management for PostgreSQL. - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
