Re: Using PG with Windows EFS or TrueCrypt for encryption

Hi -

I have searched the lists for comments about using PG with EFS and/or
TrueCrypt in order to encrypt the entire database transparently. I found a
few posts making reference to this possibility so I have tried them both,
but I didn't get either to work.

I have PG-8.3 running on Windows server 2008 (64-bit).

In the first scenario I just used Windows EFS (encrypting file system) to
encrypt the database OID folder in the data\ folder. After I did this, the
PG service started, but I could not access the database in pgAdmin.

Then I attempted to mount a normal encrypted volume with TrueCrypt, move the
data\ and sub-folders to this volume and reconfigure PG to point to this as
the data folder. Now, the PG service will not start at all.

Has anyone implemented something like this for PG in Windows?

Thanks!
Brady

--
Brady Mathis | bmathis(at)r-hsoftware(dot)com | 877.696.6547 ext 102

On Wed, Dec 8, 2010 at 01:19, Brady Mathis <bmathis(at)r-hsoftware(dot)com> wrote:
> Hi -
> I have searched the lists for comments about using PG with EFS and/or
> TrueCrypt in order to encrypt the entire database transparently.  I found a
> few posts making reference to this possibility so I have tried them both,
> but I didn't get either to work.
> I have PG-8.3 running on Windows server 2008 (64-bit).
> In the first scenario I just used Windows EFS (encrypting file system) to
> encrypt the database OID folder in the data\ folder.  After I did this, the
> PG service started, but I could not access the database in pgAdmin.
> Then I attempted to mount a normal encrypted volume with TrueCrypt, move the
> data\ and sub-folders to this volume and reconfigure PG to point to this as
> the data folder.  Now, the PG service will not start at all.
> Has anyone implemented something like this for PG in Windows?

Either one of these two should work fine. What you have to worry about
is if they honor the synchronous I/O flags and commands properly - I
don't know if either of them do. And of course, it'll be really slow.

You need to look in your eventlog to get the messages that tell you
why it failed...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Brady,

Then I attempted to mount a normal encrypted volume with TrueCrypt, move the
> data\ and sub-folders to this volume and reconfigure PG to point to this as
> the data folder. Now, the PG service will not start at all.
>
> moving data and subfolder on NTFS is a Level-20 operation. The usual cases
for PostgreSQL-Service not starting ar:

a) user account has wrong privileges
b) user account has lost "Logon as Service"
c) password of user account was changed / invalidate by some system policy /
administrator
d) user account which the PostgreSQL service logs on with is not able to
acces the data-directories. d) is usually anaylizable via the system
eventviewer.

Most likely cause during your copy operation: the permission on the
directories where changed. OR: the link to the Data-directory (part of the
service-configuration) within services.msc is no longer valid (as in: data
in different place)

I can confirm that is possible to have a database on a TrueCrypt encrypted
volume. It is dog slow. My impression is that data from that encypted volume
is not really cached.

Harald

> Has anyone implemented something like this for PG in Windows?
>
> Thanks!
> Brady
>
> --
> Brady Mathis | bmathis(at)r-hsoftware(dot)com | 877.696.6547 ext 102
>

--
GHUM GmbH
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607

Amtsgericht Stuttgart, HRB 734971
-
persuadere.
et programmare

Hey Harald -

The permissions! Of course! Thanks, you fixed me.

Brady

On Wed, Dec 8, 2010 at 6:18 AM, Massa, Harald Armin <chef(at)ghum(dot)de> wrote:

> Brady,
>
> Then I attempted to mount a normal encrypted volume with TrueCrypt, move
>> the data\ and sub-folders to this volume and reconfigure PG to point to this
>> as the data folder. Now, the PG service will not start at all.
>>
>> moving data and subfolder on NTFS is a Level-20 operation. The usual cases
> for PostgreSQL-Service not starting ar:
>
> a) user account has wrong privileges
> b) user account has lost "Logon as Service"
> c) password of user account was changed / invalidate by some system policy
> / administrator
> d) user account which the PostgreSQL service logs on with is not able to
> acces the data-directories. d) is usually anaylizable via the system
> eventviewer.
>
> Most likely cause during your copy operation: the permission on the
> directories where changed. OR: the link to the Data-directory (part of the
> service-configuration) within services.msc is no longer valid (as in: data
> in different place)
>
> I can confirm that is possible to have a database on a TrueCrypt encrypted
> volume. It is dog slow. My impression is that data from that encypted volume
> is not really cached.
>
> Harald
>
>
>
>> Has anyone implemented something like this for PG in Windows?
>>
>> Thanks!
>> Brady
>>
>> --
>> Brady Mathis | bmathis(at)r-hsoftware(dot)com | 877.696.6547 ext 102
>>
>
>
>
> --
> GHUM GmbH
> Harald Armin Massa
> Spielberger Straße 49
> 70435 Stuttgart
> 0173/9409607
>
> Amtsgericht Stuttgart, HRB 734971
> -
> persuadere.
> et programmare
>

--
Brady Mathis | bmathis(at)r-hsoftware(dot)com | 877.696.6547 ext 102