Thread: BUG #3232: Regression: pgsql server startup problem with encrypted partitions
The following bug has been logged online: Bug reference: 3232 Logged by: Carsten Email address: fcarsten@tpg.com.au PostgreSQL version: 8.2.3 Operating system: Windows XP SP 2 Description: Regression: pgsql server startup problem with encrypted partitions Details: I am running the following scenario: * Windows XP SP 2 * Postgres 8.2.3 installed on a normal Windows partition * One highly sensitive database on a seperate table space which is located on a TureCrypt partition * The TrueCrypt partition only can be mounted after I log into the machine (as I have to enter the password) In the version of PostgreSQL which I was using previously (don't remember version number) this scenario worked fine. As long as I didn't try to access a database on the encrypted partition before mounting it the pgsql server was happy. In current version this no longer works. I have to manually restart the pgsql server after mounting the encyprted partition to access the database on it. Were there any changes which made the pgsql server stricter in requiring that all table-spaces exist on start-up? If yes, any chance of reversing this to support databases on encrypted partitions or removable devices?
Re: BUG #3232: Regression: pgsql server startup problem with encrypted partitions
From
Tom Lane
Date:
"Carsten" <fcarsten@tpg.com.au> writes: > Were there any changes which made the pgsql server stricter in requiring > that all table-spaces exist on start-up? Not that I can think of --- except that startup for crash recovery has always expected everything to be accessible. If that's not what you're running into, please provide a full test case. regards, tom lane
Re: BUG #3232: Regression: pgsql server startup problem with encrypted partitions
From
Magnus Hagander
Date:
On Mon, Apr 16, 2007 at 11:56:29PM +0000, Carsten wrote: > > The following bug has been logged online: > > Bug reference: 3232 > Logged by: Carsten > Email address: fcarsten@tpg.com.au > PostgreSQL version: 8.2.3 > Operating system: Windows XP SP 2 > Description: Regression: pgsql server startup problem with encrypted > partitions > Details: > > I am running the following scenario: > > * Windows XP SP 2 > * Postgres 8.2.3 installed on a normal Windows partition > * One highly sensitive database on a seperate table space which is located > on a TureCrypt partition > * The TrueCrypt partition only can be mounted after I log into the machine > (as I have to enter the password) > > In the version of PostgreSQL which I was using previously (don't remember > version number) this scenario worked fine. As long as I didn't try to access > a database on the encrypted partition before mounting it the pgsql server > was happy. That was most likely just pure luck. > In current version this no longer works. I have to manually restart the > pgsql server after mounting the encyprted partition to access the database > on it. > > Were there any changes which made the pgsql server stricter in requiring > that all table-spaces exist on start-up? If yes, any chance of reversing > this to support databases on encrypted partitions or removable devices? The PostgreSQL startup process can access pretty much *any* file, depending on circumstances. I don't see how we can make a guarantee about that. We do support databases on encrypted partitions or removable drive - but sticking *parts* of them there and just hopeing that the drive will be there by the time you access just that part is not a very stable thing to do. Keep the whole db on it, and make sure it's available before you start PostgreSQL, andi t should work fine. (This is simlar to NFS mounted data directories, which do work but only if you're careful) Now, you might want to consider using EFS for your encryption. When properly set up, you won't need to input your password to get it going, which in turn means that it can start up properly. If you really want the password input part, set it up so PostgreSQL is started after TrueCrypt. //Magnus
Re: BUG #3232: Regression: pgsql server startup problem with encrypted partitions
From
Magnus Hagander
Date:
On Thu, Apr 19, 2007 at 10:54:30AM +1000, Carsten Friedrich wrote: > Hi Magnus, > > thanks for the reply. > > I don't think the fact that this worked in the past was pure luck. I did > this for more than a year (almost 2 years) 5 days a week and never had a > problem. It seems very unlikely to me that this was pure luck. Well, then it was very good luck :-) But there are no guarantees in the code for doing that, and AFAIK never has been. Maybe back before we had WAL, which was added in 7.0 or 7.1, IIRC. > > We do support databases on encrypted partitions or removable drive > > Can you give me a pointer to documentation on this? No, I don't think there exists any specific documentation for that. But as long as the data directory does not go away, and as long as the filesystem is "stable" wrt not losing data, we should work fine on whatever. > > - but sticking *parts* of them there and just hopeing that the drive > will be > > there by the time you access just that part is not a very stable thing to > > do. Keep the whole db on it, > > I might not have explained my situation well enough: The database in > question is in fact completely on the encryoted partition. The postgres > server executables and template are on non-encrypted partitions. Ok, I was unclear. You need to have the *cluster* on the same disk. Specifically, you need all the data files, all the WAL files and all the clog files and other global files thre. Also, you should be aware that your data is in no way secure, given that any modifications you make go both into the database (on your encrypted disk) and into the WAL (on your unencrypted disk). > > and make sure it's available before you start PostgreSQL, andi t > should work fine. > > This would require me to set the postgres service to manual start. I'd > like to avoid this, but it would solve the problem. I don't recall how truecrypt works, but hopefullyi there is a way to hook into it so that it starts pg on mount, and stops it before unmount? If not, you should be able to script that. > > Now, you might want to consider using EFS for your encryption. > > This is apparently not very secure. While the encryption itself seems to > be alright it is apparently quite easy to retrieve the keys from the > system. It's certainly not as secure as keeping the keys manual, but it's notexactly easy to retreive them. Make it even harder by using syskey for a boot password on the machine. //Magnus