Обсуждение: 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?

"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

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

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