Re: CREATE DATABASE - Mailing list pgsql-hackers

> Well, I documented what I actually had to do to make it work.

Well, not to cause trouble here but it will work as I intended, and
didn't work as you tried to use it. That's not so bad, and the usage is
documented in the administrator's guide. But your points below are good
and should be used to improve things.

> Shouldn't it cope with this possibility anyway?  (The mad systems
> administrator....) What would happen to PostgreSQl if a database
> directory disappeared from under it?

Postgres, via the master database, would think that the database
existed, but would not be able to connect and not be able to delete it.
The administrator would have to brute-force copy a database (template1
would do) into the removed area, at which time Postgres could remove it
and clean up internally.

>   > The assumption is that
>   >the administrator is likely to be more careful since she is likely
>   >to be more aware of the issues.
> If so, the restriction should be placed in the code: only the postgres
> administrator should be allowed to run the command.  That would get
> rid of the permissions problems.  On the other hand, it would remove
> most of the point of having a separate location (seeing that it could
> just as well be implemented by symbolic links).

No, there is a point to having a separate location; that and the
softlink kludge are two different things. The "initlocation" alternate
location can be used by any database user (with createdb privileges) to
create and maintain a new database, once set up by the Postgres
administrator.
Soft links don't do that; Postgres doesn't know about them and can't
create/delete/create databases in multiple locations by using them
afaik.

"initlocation" isn't actually the issue; the real issue is whether
alternate locations not created by the Postgres superuser should be
allowed at all. I'm thinking not, given these issues which would have to
be kept in mind by every user trying to use the command themselves.

> The first thing to do is decide whether this feature is required at
> all. I'm trying to think of things in its favour:
> 1. It enables the administrator to direct a large database to a
>    partition with sufficient space.

bingo. This is sufficient to make the feature desirable, but the
downsides should be addressed.

> 2. It allows especially sensitive data to be stored on an encrypted
>    file system.
> 3. It allows a database to be created on removable media. (Is this a
>    good thing?)

Removable media are not a good thing for this mechanism. Postgres used
to have direct support for databases on removable media (tape); this
should be resurrected if someone wants the feature.

> If run by a user:
> 4. It might give the chance of having data that is secure against the
>    administrator, but only if a backend can be launched that is owned
>    by the user rather than by the administrator.

In that case the user would be the administrator. Postgres allows that,
and allows multiple servers on the same machine (the listening port
would need to be different for each one).

> Since 4 isn't possible at the moment, there seems to be no reason for
> allowing a user to run this command, even if he is otherwise allowed
> to create databases.  Removing the ability from users means that the
> documentation can be simplified by documenting it for administrative
> use only.

I did document it for administrators only ;)

> Even simpler is to remove it altogether and let the administrator
> handle 1-3 by unix commands and symbolic links.

No, that is not a solid option.

At the moment, initlocation is just a shell utility which sets up a
directory area and changes permissions to be appropriate for Postgres
use *if it is run by the Postgres superuser*.

If we are concerned that alternate database locations can be misused and
should be under tighter control of the Postgres superuser, then it would
be easy to take out (or #ifdef) the code which allows absolute path
names and instead require that all paths for alternate locations be
specified as environment variables (that is already supported and was
always preferable imho).

That way the Postgres administrator can run initlocation, define an
environment variable pointing at that alternate location, and then start
up the backend. Users would have only those previously defined areas to
work with.

I'm being pretty direct here in my comments because you are raising very
good issues and we haven't had much discussion of them in the past. I am
not intending to be difficult or obstinant, just trying to clear things
up and get a plan for improving things.

Comments (on the issues, and/or whether I'm being a pain :)?

                       - Tom