Thread: Using PG with Windows EFS or TrueCrypt for encryption

Using PG with Windows EFS or TrueCrypt for encryption

From
Brady Mathis
Date:
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@r-hsoftware.com | 877.696.6547 ext 102

Re: Using PG with Windows EFS or TrueCrypt for encryption

From
Magnus Hagander
Date:
On Wed, Dec 8, 2010 at 01:19, Brady Mathis <bmathis@r-hsoftware.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/

Re: Using PG with Windows EFS or TrueCrypt for encryption

From
"Massa, Harald Armin"
Date:
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@r-hsoftware.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

Re: Using PG with Windows EFS or TrueCrypt for encryption

From
Brady Mathis
Date:
Hey Harald - 

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

Brady

On Wed, Dec 8, 2010 at 6:18 AM, Massa, Harald Armin <chef@ghum.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@r-hsoftware.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@r-hsoftware.com | 877.696.6547 ext 102