Re: BUG #3232: Regression: pgsql server startup problem with encrypted partitions

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