proposal -- Re: [HACKERS] Re: [SQL] createdb -D xxxx not working

On 2000-01-12, Thomas Lockhart mentioned:

> Huh? The "environment variable deal" is intended to provide security
> and easier operation. The examples above which allow specifying
> absolute paths (e.g. "/some/where/pg_class") are specifically
> disallowed by default, for a reason. If there were bugs in that, let's
> talk about it, but I'm *very* uncomfortable making wholesale changes
> without a discussion. And what exactly were "several bugs in the
> code"?? Better be specific; that's my code we're talking about :/

The starting point for my investigation was actually the issue of
arbitrary characters in database names, which lead me to fix up the
createdb and dropdb code to make it more error proof, which led me to all
kinds of areas of the code that looked ages old, with comments not
matching the code, dead code, etc. The location issue was just one of
those. (Especially those /* this is work arround only !!! */ sections from
more than 5 years ago concern me a little ...) It's my idea of a learning
experience.

> > > initlocation is used to create the directory structure *with correct
> > > permissions* for an alternate location. It takes an environment

If I create a database with a normal name, the code makes a directory
/usr/local/pgsql/data/base/testdb with the proper permissions. There's no
reason why it wouldn't be able to do the same somewhere else. It's
redundant.

> 1) there is not likely to be a random environment variable which
> happens to point to a valid directory which *also* has a subdirectory
> called "base".

Forgive me, but "not likely" means zero security to me. If you want to
make it secure, be for real. Perhaps I'm a little envvar-phobic in
general. I got my reasons, but that shouldn't stand in other's ways.

> 2) it reduces the chance that a user/dbadmin will not use initlocation
> to create the database area, hence reducing the chance that a
> user/dbadmin has not set the permissions correctly.

The prototype code I got lying around insists on creating the directory
itself, so the chances of using an insecure directory are zero. But there
are also other ways to ensure this rather than another utility.

Okay, now to the "formal" proposal:

* A database name can contain any character (up to NAMEDATALEN)

* By default, the database name is appended to DataDir/base/ to form the
file system location of the database.

* There are certain characters that will not be allowed in database paths
because they are potentially "shell'ishly dangerous". In my current layout
this includes everything from 1 to 31 ascii as well as single-quote
(') and dot (.). If you choose to name a database this way, you need to
override the path to something else.

* If you override the path to a name not containing a slash, the name will
in the same fashion be appended to DataDir/base/. Any future attempts to
use the same path will fail.

* If you override the path to a name including a slash but not at the
start, the part up to the first slash will be interpreted as an
environment variable. The database directory will be the immediate
directory specified and will be created by createdb() (which must have
permission to do so). If it already exists, it will attempt to fix the
permissions.

* If you specify an absolute path then it will use that very path and it
will create the directory as above.

* Either way, in order to use a path outside DataDir, it must be listed in
some configuration option (such as PG_ALTLOC). Environment variable based
paths can be included in the natural way, e.g., to allow a path
'PGDATA2/foo' write PG_ALTLOC=$PGDATA/foo, to allow anything under
PGDATA2, write PG_ALTLOC=$PGDATA/*.

In practice that would mean:

1. If you don't use this feature, nothing changes.

2. If you want to use this feature to "recode" funny characters, you
may. (e.g., CREATE DATABASE "bbb'''..." WITH LOCATON = 'bbb______';)

3. If you want to stick one particular database somewhere else, create the
directory (or at least the directory above it), include it in PG_ALTLOC
and go ahead.

4. If you want to provide users the option of storing databases at several
alternative locations, set up mnemonic environment variables, create the
directories corresponding to them, and put something like
PGDATA2/*:PGDATA3/*:... in PG_ALTLOC.

Are there other circumstances where this would be used?

What could be discussed it the exact interpretation of a path such as
LOCATION = '/mnt/somewhere/foo' with regards to whether 'base' should be
appended or not or the name of the database should be appended to it or
not or how one could otherwise do the name "recoding" in that
circumstance.

--
Peter Eisentraut Sernanders väg 10:115
peter_e(at)gmx(dot)net 75262 Uppsala
http://yi.org/peter-e/ Sweden