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

> Darn, now I already rewrote the thing to where I considered it working. We
> had several on-and-off discussions (mostly Tom and I) about it during the
> last two months at least, including users complaining.

afaik I've been on the hackers list most of the time, and don't recall
significant discussion.

> The sort of scheme I came up with scraps the environment variable stuff
> (since it's not quite safe either and has several bugs in the code that
> create a bunch of problems along the way) and lets the following
> happen (example paths):
>
> CREATE DATABASE foo; --> /usr/local/pgsql/data/base/foo/pg_class
> CREATE DATABASE foo WITH LOCATION 'bar';
> --> /usr/local/pgsql/data/base/bar/pg_class
> CREATE DATABASE foo WITH LOCATION '/some/where';
> --> /some/where/pg_class
> CREATE DATABASE foo WITH LOCATION 'foo/bar';
> is disabled. I suppose I could stick the
> environment variable deal back in so that users don't have to remember any
> complicated paths, but this is the dbadmin's job anyway, so he should be
> able to remember it.

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

It sounds like you and Tom Lane have had discussions on this, but I'm
not certain it was with a clear understanding of what the environment
variables provided. Before killing the capability, I'd like to make
sure that we understand what it did.

otoh, you have a new proposal (the first I've heard of it afaik)...

> Anyway, in order for a path to be allowed it has to be listed in
> PG_ALTLOC, which is a colon-delimited, marginally wildcard enabled list of
> allowed locations. In some future life this could be included in a
> configuration file.

That sounds like a good capability. There is no reason (yet) why the
environment variable mechanism can not use this too; e.g.

setenv PG_ALTLOC PGDATA2:/home/peter/unsafe/unprotected/open/path

(just had to put that last one in :)))

> > initlocation is used to create the directory structure *with correct
> > permissions* for an alternate location. It takes an environment
> I think one of the problems was actually that the path initlocation
> assumed and the one createdb worked with were different.

I'm not sure to what you are referring. To the fact that there is an
implicit "/base" in the actual path? e.g.

initlocation PGDATA2
createdb -D PGDATA2 test

gives an actual path $PGDATA2/base/pg_class ? That's a
security/integrity benefit since:

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

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.

> > variable because usually the backend should have that same environment
> > variable defined to ensure consistancy and as a security measure. The
> > environment variable can be expanded or not; initlocation does the
> > right thing in either case.
> It's false security though, since for complete security the dbadmin would
> have to delete all other environment variables altogether.

Theoretically true (though low-risk per above discussion), but this
would be addressed by your PG_ALTLOC augmentation, which I like btw.

> > > I'd vote for taking
> > > out the auto-expansion, so that the correct invocation becomes
> > > initlocation $PGDATA2
> > > which is what an average user would expect.
> > But the average user does not necessarily have access to the PGDATA2
> > environment variable! This is usually a sysadmin function.
> The average user doesn't even have access to the machine the database is
> running on, so he can't do any initlocation either way. Then again the
> average user doesn't create databases either. But there is no real point
> for initlocation since a mere mkdir() call in createdb() will do the job.

Hmm. Until I see what you are actually doing, I can't comment on
whether this is indeed adequate. But decoupling the location creation
from database creation give the dbadmin control over how permissions
are set and where databases can be created. I'm not sure that is the
case if it is all done within the backend at database creation time.

> Now I just got done with that coding 10 minutes ago but now that someone
> actually spoke up in defence of this mechanism I'm going to wait and see
> what you think about the revised (or any) scheme.

PG_ALTLOC seems to be a great feature. But afaict the environment
variable feature is useful, safe (as much or more so than absolute
paths, anyway), can coexist, and is a convenience.

I'd like to continue the discussion until I'm convinced, or least
beaten into submission ;)

- Thomas

--
Thomas Lockhart lockhart(at)alumni(dot)caltech(dot)edu
South Pasadena, California