WAL file location

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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