In my venturing into createdb/dropdb I came to that little artifact that
allows you to create databases at alternate locations using environment
variables as part of the path (CREATE DATABASE elsewhere WITH LOCATION =
'PGDATA2/foo').

The problem with this is that it doesn't work. It never could have, and
God help you if you try to use it anyway. And it's not only one isolated
problem.

First off, the paths generated by the expansion and the ones generated by
initlocation are different, so the directory will never be found unless
you tweak it by hand. This can be fixed.

Worse, however, is that the expanded path is stored in pg_database (at
least in theory, since you never get there) and once you try to reference
(e.g., remove) the database, the same expansion routine will see the now
absolute path and refuses to allow it.

What really gets me, though, is how this sort of scheme is supposed to
create security in the first place. Perhaps I can create a path based on
the environment variable HOME, or maybe SHELL? Or how about this: you take
an empty environment variable and specify VAR/usr/local/pgsql/lib as your
path. Fun ensues! You can never completely control this stuff. ISTM, this
just makes things worse and more complicated.

How could we still keep this feature but select another method of
specifying the list of allowed paths? The Unix file permissions should
help, but that doesn't necessarily prevent anyone from frying your
existing databases, if you exercise a little imagination when specifying
the paths. How about a) something in some options file (lot of work), or
b) some environment variable of the form PGSQL_ALTLOC=path:path:path?

This is of course barring the potential fact that parts of the code which
I don't completely understand or which I haven't read yet prevent this
whole concept from working in other ways.

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Sat, 11 Dec 1999 23:04:10 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
createdb with alternate location
Peter Eisentraut <peter_e@gmx.net> writes:
> [ CREATE DATABASE WITH LOCATION shouldn't depend on environment vars ]

I agree, this oughta be flushed.  Is the expansion routine used in any
other contexts where depending on an environment var *would* make sense?

> What really gets me, though, is how this sort of scheme is supposed to
> create security in the first place.

I doubt security was foremost in the mind of whoever did that.  Still,
the environment vars in question are those created by the dbadmin before
starting the postmaster; it's not like unprivileged users can affect
them.  So I'd say it's just a chance to shoot yourself in the foot,
not a question of exposing yourself to enemy fire...

			regards, tom lane

Sun, 12 Dec 1999 01:13:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] createdb with alternate location 

On Sat, 11 Dec 1999, Peter Eisentraut wrote:

> In my venturing into createdb/dropdb I came to that little artifact that
> allows you to create databases at alternate locations using environment
> variables as part of the path (CREATE DATABASE elsewhere WITH LOCATION =
> 'PGDATA2/foo').

And what add to create-database management a TABLESPACE layout? It is
standard in any SQL (Oracle). It is good "investment" to future, because
on TABLESPACE tie storage management ..etc (see old "Raw device" thread
in the hackers list).         

							Karel

----------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz>              http://home.zf.jcu.cz/~zakkr/

Docs:        http://docs.linux.cz                    (big docs archive)	
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/        (process manager)
FTP:         ftp://ftp2.zf.jcu.cz/users/zakkr/        (C/ncurses/PgSQL)
-----------------------------------------------------------------------


Mon, 13 Dec 1999 13:26:47 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] createdb with alternate location
On 1999-12-13, Karel Zak - Zakkr mentioned:

> On Sat, 11 Dec 1999, I wrote:
> 
> > In my venturing into createdb/dropdb I came to that little artifact that
> > allows you to create databases at alternate locations using environment
> > variables as part of the path (CREATE DATABASE elsewhere WITH LOCATION =
> > 'PGDATA2/foo').
> 
> And what add to create-database management a TABLESPACE layout? It is
> standard in any SQL (Oracle). It is good "investment" to future, because

It's not standard in _the_ SQL though.

> on TABLESPACE tie storage management ..etc (see old "Raw device" thread
> in the hackers list).         

I don't know how much use such a generalized version would be vs. the
overhead involved. You can hang on to that thought though, if you like.
I'd like to get this "advertised" feature working first, however.

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




Tue, 14 Dec 1999 00:27:43 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] createdb with alternate location