Thread: createdb with alternate location
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
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
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)
-----------------------------------------------------------------------
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