Thread
-
Re: CREATE DATABASE
Oliver Elphick <olly@lfix.co.uk> — 1998-05-15T15:28:28Z
"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 -
Re: CREATE DATABASE
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-05-16T02:00:07Z
> 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 -
Re: [HACKERS] Re: CREATE DATABASE
Brian E Gallew <geek+@cmu.edu> — 1998-05-18T13:03:43Z
-----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-----