Обсуждение: CREATE DATABASE

Was looking through the new docs and noticed that the example for
creating a database in an alternate location has trouble:

   $ mkdir private_db
   $ initlocation ~/private_db
   Creating Postgres database system directory
/home/olly/private_db/base

   $ chmod a+rx private_db
   $ chmod a+rwx private_db/base
   $ psql
   ...

The chmod's are a Bad Idea (tm) since it blows the security assumptions
for Postgres. The protections are explicitly set by initlocation to lock
down these directories.

I guess that the alternate location setup (initlocation) was really
meant as a tool for the Postgres administrator, not for individual
users. If users create alternate locations, and then for example create
a database and then delete the directories from the file system rather
than through Postgres things will become ugly. The assumption is that
the administrator is likely to be more careful since she is likely to be
more aware of the issues.

I have (or had) some #ifdef code which _requires_ that environment
variables be used to specify alternate locations, rather than allowing
absolute paths also. This helps ensure that locations are used which
have been set up by the Postgres administrator, since the admin must
have defined the environment variables for the backend before it starts
up.

I'm not sure how to write an example which had initlocation being run by
someone other than the Postgres superuser while still being clear on
these security/integrity issues. What would you suggest?

                          - Tom

"Thomas G. Lockhart" wrote:
  >Was looking through the new docs and noticed that the example for
  >creating a database in an alternate location has trouble:
  >
  >   $ mkdir private_db
  >   $ initlocation ~/private_db
  >   Creating Postgres database system directory
  >/home/olly/private_db/base
  >
  >   $ chmod a+rx private_db
  >   $ chmod a+rwx private_db/base
  >   $ psql
  >   ...
  >
  >The chmod's are a Bad Idea (tm) since it blows the security assumptions
  >for Postgres. The protections are explicitly set by initlocation to lock
  >down these directories.

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

I got the impression that this particular feature had not been properly
thought out!

  >
  >I guess that the alternate location setup (initlocation) was really
  >meant as a tool for the Postgres administrator, not for individual
  >users. If users create alternate locations, and then for example create
  >a database and then delete the directories from the file system rather
  >than through Postgres things will become ugly.

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?

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

  >I have (or had) some #ifdef code which _requires_ that environment
  >variables be used to specify alternate locations, rather than allowing
  >absolute paths also. This helps ensure that locations are used which
  >have been set up by the Postgres administrator, since the admin must
  >have defined the environment variables for the backend before it starts
  >up.
  >
  >I'm not sure how to write an example which had initlocation being run by
  >someone other than the Postgres superuser while still being clear on
  >these security/integrity issues. What would you suggest?
  >

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.

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?)


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.

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.

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

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Let not your heart be troubled: ye believe in God,
      believe also in me."       John 14:1

> 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

"Thomas G. Lockhart" wrote:
  >> 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.

I was actually working from the existing man pages, but now that I check,
it isn't sufficiently clear from the guide that initlocation is an
administrator-only command. (Sections II.7, III.22)  Even reading them in
the light of your comments, I cannot see that this restriction is stated.
(Or have you updated this since 6.3.2?)
...
  >"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.

If initlocation is to be run only by the administrator, it should be owned
by the administrator login and have execute-permission only for the
owner.
...
  >> 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 ;)

I still don't see it!  Sorry.
...
  >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*.

So make it executable only by the superuser.  If some site wanted to have
multiple administrators, it would need some fancy code to check that
the postmaster listening on $PGPORT was owned by the same user as is
running initlocation. Then it could be made generally executable.

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

How many alternate locations can be specified? I see PGDATA2 listed in your
documentation.  Can this be extended to PGDATAn?

  >
  >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 :)?

No problem!  In this case, I was documenting from the point of view of a
user, since I was running commands in my own name (with database creation
privileges).  The old man pages don't mention any restriction, so it didn't
occur to me that this was an administrator only command.  The way I read it,
it seemed to be designed for a user to set up his own private area.



--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "In my Father's house are many mansions: if it were not
      so, I would have told you. I go to prepare a place for
      you. And if I go and prepare a place for you, I will
      come again, and receive you unto myself; that where I
      am, there ye may be also."        John 14:2,3

-----BEGIN PGP SIGNED MESSAGE-----

Then <lockhart@alumni.caltech.edu> spoke up and said:
> 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.

Personally, I don't care *how* alternate locations are
setup/specified, as long as
1)  With a single postmaster, I can access databases in all of the
locations.
2)  Adding a new location should be reserved to the PostgreSQL
superuser.
3)  Adding a new location should *not* require restarting the
postmaster.

Additionally, it would be nice to have syntax available for spreading
a database across multiple locations (a la Ingres).
- --
=====================================================================
| JAVA must have been developed in the wilds of West Virginia.      |
| After all, why else would it support only single inheritance??    |
=====================================================================
| Finger geek@andrew.cmu.edu for my public key.                     |
=====================================================================

-----BEGIN PGP SIGNATURE-----
Version: 2.6.2

iQBVAwUBNWAxr4dzVnzma+gdAQEeGQH/c2L/THi6ZpijGz+JyCfnLhgaVnf/CC0Z
w3iX7UEAp1C5Oc/UkmkX3cMZ3cEYkdYAYOBvGtKbhnxO9x90wp5+Mw==
=8QdB
-----END PGP SIGNATURE-----