Thread

WAL file location

lockhart@fourpalms.org — 2002-07-30T01:54:22Z

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

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T04:08:09Z

Thomas Lockhart <lockhart@fourpalms.org> 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

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-30T04:13:36Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-30T04:14:54Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T04:56:53Z

Tom Lane wrote:
> Thomas Lockhart <lockhart@fourpalms.org> 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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

lockhart@fourpalms.org — 2002-07-30T05:56:49Z

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

Re: WAL file location

lockhart@fourpalms.org — 2002-07-30T06:09:28Z

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

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-30T11:10:02Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Andrew Sullivan <andrew@libertyrms.info> — 2002-07-30T16:45:50Z

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
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T16:49:01Z

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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

Lamar Owen <lamar.owen@wgcr.org> — 2002-07-30T16:57:55Z

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

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T18:05:57Z

Andrew Sullivan <andrew@libertyrms.info> 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

Re: WAL file location

Andrew Sullivan <andrew@libertyrms.info> — 2002-07-30T18:19:46Z

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
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T18:34:06Z

Andrew Sullivan <andrew@libertyrms.info> 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

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T18:37:12Z

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 <andrew@libertyrms.info> 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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

Lamar Owen <lamar.owen@wgcr.org> — 2002-07-30T19:50:25Z

On Tuesday 30 July 2002 02:34 pm, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> 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.

I said it.  In any case, using strings that are in the environment requires an 
untrusted PL, or a C function.  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.  And if 
someone can exploit the environment in that way, with a server-side function, 
then that same person will be able to execute arbitrary code as the 
postmaster run user anyway, without any environment variables being accessed.  
Unless environment access is allowed in trusted functions....

Although that is one reason the HOME for the RPMset is /var/lib/pgsql, a place 
postgres has free rein anyway.

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

To data already owned by postgres only.  I wouldn't mind seeing an example of 
such an exploit, for educational purposes.

Having said all that, I still believe that this is something tailor-made for 
postgresql.conf.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T20:17:51Z

Lamar Owen <lamar.owen@wgcr.org> 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

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-30T20:20:08Z

Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> 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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

Peter Eisentraut <peter_e@gmx.net> — 2002-07-30T21:41:41Z

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   peter_e@gmx.net

Re: WAL file location

lockhart@fourpalms.org — 2002-07-30T22:02:35Z

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

True, in the simplest implementation (Peter E. has suggested extensions
to address this complaint). But not relevant to security under likely
scenarios. See below.

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

Actually, this can not happen! You will see that the combination of
initlocation, environment variables, and other backend requirements will
force this kind of exploit to fail, since PostgreSQL requires a
structure of directories (like $PGDATA/data/base) but the environment
variable is only allowed to prepend the "data/base/oid" part of the
path.

So the directory *must* be set up properly beforehand, and must have the
correct structure, greatly reducing if not eliminating possible
exploits.

This is *not* possible in any scenerio for which the postmaster or a
server instance or a client connection has full control over the
attributes and definitions of data storage areas. Decoupling them
(requiring two distinct orthogonal mechanisms) is what enhances security
and data integrity. None of the "I hate environment variables"
discussions have addressed this issue.

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

Again, not likely possible. See above.

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

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

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

                  - Thomas

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-30T22:21:40Z

Thomas Lockhart <lockhart@fourpalms.org> 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

Re: WAL file location

lockhart@fourpalms.org — 2002-07-30T22:50:12Z

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

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-30T23:21:09Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-30T23:46:04Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

lockhart@fourpalms.org — 2002-07-31T00:00:07Z

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

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-31T00:09:28Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Lamar Owen <lamar.owen@wgcr.org> — 2002-07-31T03:18:16Z

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

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-31T03:51:38Z

Lamar Owen <lamar.owen@wgcr.org> 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

Re: WAL file location

Lamar Owen <lamar.owen@wgcr.org> — 2002-07-31T04:01:08Z

On Tuesday 30 July 2002 11:51 pm, Tom Lane wrote:
> Lamar Owen <lamar.owen@wgcr.org> 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

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-07-31T04:17:12Z

Lamar Owen wrote:
> On Tuesday 30 July 2002 11:51 pm, Tom Lane wrote:
> > Lamar Owen <lamar.owen@wgcr.org> 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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-31T04:36:44Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-31T04:42:25Z

On Wed, 31 Jul 2002, Lamar Owen wrote:

> On Tuesday 30 July 2002 11:51 pm, Tom Lane wrote:
> > Lamar Owen <lamar.owen@wgcr.org> 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.
>
> ...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.

We've already had three incorrect security analysis of this in the
space of a couple of hours, from people are reasonably familiar
with postgres and (presumably) use it all the time, and you think
this is not a security problem?!

Anyway, I'll shut up now.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

lockhart@fourpalms.org — 2002-07-31T05:48:39Z

> 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

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-31T06:00:52Z

Thomas Lockhart <lockhart@fourpalms.org> 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

Re: WAL file location

lockhart@fourpalms.org — 2002-07-31T06:13:38Z

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

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-07-31T06:27:41Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

lockhart@fourpalms.org — 2002-07-31T06:41:41Z

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

Re: WAL file location

Andrew Sullivan <andrew@libertyrms.info> — 2002-07-31T12:41:45Z

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
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: WAL file location

lockhart@fourpalms.org — 2002-07-31T14:01:49Z

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

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-31T14:23:07Z

Andrew Sullivan <andrew@libertyrms.info> 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

Re: WAL file location

Andrew Sullivan <andrew@libertyrms.info> — 2002-07-31T14:50:03Z

On Wed, Jul 31, 2002 at 10:23:07AM -0400, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> 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
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: WAL file location

Tom Lane <tgl@sss.pgh.pa.us> — 2002-07-31T15:09:58Z

Andrew Sullivan <andrew@libertyrms.info> 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

Re: WAL file location

Andrew Sullivan <andrew@libertyrms.info> — 2002-07-31T15:30:37Z

On Wed, Jul 31, 2002 at 11:09:58AM -0400, Tom Lane wrote:
> Andrew Sullivan <andrew@libertyrms.info> 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
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: WAL file location

Peter Eisentraut <peter_e@gmx.net> — 2002-07-31T17:16:40Z

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   peter_e@gmx.net

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-08-01T03:10:55Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-08-01T03:13:08Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-01T03:20:35Z

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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

Andrew Sullivan <andrew@libertyrms.info> — 2002-08-01T15:35:38Z

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
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Re: WAL file location

Greg Copeland <greg@copelandconsulting.net> — 2002-08-02T14:51:27Z

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

Re: WAL file location

lockhart@fourpalms.org — 2002-08-02T18:46:15Z

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

Re: WAL file location

scott.marlowe <scott.marlowe@ihs.com> — 2002-08-02T19:07:14Z

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

Re: WAL file location

Greg Copeland <greg@copelandconsulting.net> — 2002-08-02T20:07:04Z

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

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-03T01:12:39Z

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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

lockhart@fourpalms.org — 2002-08-03T02:28:34Z

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

Re: WAL file location

Bruce Momjian <pgman@candle.pha.pa.us> — 2002-08-03T02:36:05Z

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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: WAL file location

Curt Sampson <cjs@cynic.net> — 2002-08-03T11:42:46Z

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  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC