Thread: RFE: Transparent encryption on all fields

RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
Dear pgsql hackers,

The encryption options
  http://www.postgresql.org/docs/8.3/static/encryption-options.html

fall short for my thread case. Consider the case where all users of a  
machine are trusted and the machine automatically locks itself down on  
a period of inactivity, and only local psql connections are allowed.

If the machine is stolen, the only current protection is to use an  
encrypted drive. This is impractical because it requires a manual  
start of the psql server and essentially means each user has to use a  
separate instance and copy of the databases, each storing their data  
in their own encrypted drives. (e.g. consider Apple OS X FileVault,  
Windows TrueCrypt, or Linux/BSD equivalents)

If it were feasible, a transparent crypto on all fields for a given  
database would be just the trick! Connections to such databases could  
require a key as well as the user login. Queries could then continue  
as if it was a normal connection and all fields would be unencrypted  
on the fly. This particular approach might be a little too naive, but  
it is a threat case I would urge you to consider. All security comes  
with a cost, the cost here should be only minimal performance hit and  
no changes to queries.

-- 
Sam





Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Apr 23, 2009 at 12:43:30PM +0100, Sam Halliday wrote:
> Dear pgsql hackers,
>
> The encryption options
>
>   http://www.postgresql.org/docs/8.3/static/encryption-options.html

[...]

> If it were feasible, a transparent crypto on all fields for a given 
> database would be just the trick! Connections to such databases could 
> require a key as well as the user login [...]

If I understand you correctly you are proposing to do the decryption
server-side. This doesn't seem to make much sense (at least not beyond
encrypting the partition where the data is). Either the machine is
stolen when shut down, or the machine is "stolen" when running. In the
first case you are fine, in the second you are lost. It's the same as
with an encrypted partition.

Providing the key/passphrase to unlock the partition is possible over
the net.

Keeping the (at least the decryption) key client-side makes much more
sense (and you can provide different clients with different keys). The
only drawback is when you need an index over an encrypted field :-(

Regards
- -- tomás


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8HzFBcgs9XrR2kYRAlcMAJ4irB6+J0/8KxSpDFKCidRyVkA6cgCeKSBi
UqMNLQ1QLrYGsb0YZ+d1aNY=
=RSOK
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Bill Moran
Date:
In response to tomas@tuxteam.de:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Thu, Apr 23, 2009 at 12:43:30PM +0100, Sam Halliday wrote:
> > Dear pgsql hackers,
> >
> > The encryption options
> >
> >   http://www.postgresql.org/docs/8.3/static/encryption-options.html
> 
> [...]
> 
> > If it were feasible, a transparent crypto on all fields for a given 
> > database would be just the trick! Connections to such databases could 
> > require a key as well as the user login [...]
> 
> If I understand you correctly you are proposing to do the decryption
> server-side. This doesn't seem to make much sense (at least not beyond
> encrypting the partition where the data is). Either the machine is
> stolen when shut down, or the machine is "stolen" when running. In the
> first case you are fine, in the second you are lost. It's the same as
> with an encrypted partition.
> 
> Providing the key/passphrase to unlock the partition is possible over
> the net.
> 
> Keeping the (at least the decryption) key client-side makes much more
> sense (and you can provide different clients with different keys). The
> only drawback is when you need an index over an encrypted field :-(

It's possible that this could be accomplished by something like Veil,
or the built-in implementation that's coming in some future version of
PG (is it scheduled for 8.5 at this point?)

Anyway, if a Veil rule required the user to enter a password that would
decrypt their key then store it in the session, this could be used for
subsequent queries to encrypted fields.  Since each user has their own
key, the data is protected from all sorts of priv escalations, theft,
etc (really, just about every attack vector aside from password brute
forcing and social engineering ... and those will require other
methods of protection anyway.)

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Apr 23, 2009 at 10:38:55AM -0400, Bill Moran wrote:

[...]

> It's possible that this could be accomplished by something like Veil,

Veil? Care to share an URL?

Sorry for my ignorance

- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8IeHBcgs9XrR2kYRAk/CAJ9d9vuth3gcEkmYSLOzHXkYDSaOXQCffEjE
DTKysAGopIw6nUQIrAk7NRs=
=TNgv
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Bill Moran
Date:
In response to tomas@tuxteam.de:
>
> On Thu, Apr 23, 2009 at 10:38:55AM -0400, Bill Moran wrote:
> 
> [...]
> 
> > It's possible that this could be accomplished by something like Veil,
> 
> Veil? Care to share an URL?

Google knows :)

http://veil.projects.postgresql.org/curdocs/index.html

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Apr 23, 2009 at 11:23:20AM -0400, Bill Moran wrote:

[...]

> > Veil? Care to share an URL?
> 
> Google knows :)
> 
> http://veil.projects.postgresql.org/curdocs/index.html

Thanks! [yes, Google knew, but it had so many veils it got me completely
furled]

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8IvdBcgs9XrR2kYRAlT8AJ41IsOphvUE1dYh1vZAcf6OnJnSAACcDPpE
OnCJlvNjaeAyzkCxeBK0xFY=
=iEJC
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Marc Munro
Date:
On Thu, 2009-04-23 at 16:08 -0300, pgsql-hackers-owner@postgresql.org
wrote:
> On Thu, Apr 23, 2009 at 10:38:55AM -0400, Bill Moran wrote:
>
> [...]
>
> > It's possible that this could be accomplished by something like
> Veil,
>
> Veil? Care to share an URL?

http://veil.projects.postgresql.org/curdocs/index.html

Veil is intended to enable implementation of virtual private databases.
It provides a bunch of primitives for managing bitmaps of privileges.
These privileges can be used to control access to individual rows within
a table.

In principle it could be used in the way that Bill Moran suggests though
I have never used it that way.  I am somewhat suspicious of passing
encryption keys to the database server as there is always the potential
for them to be leaked.  It is generally much safer to keep keys and the
decryption process on a separate server.

__
Marc

Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Apr 23, 2009 at 10:38:55AM -0400, Bill Moran wrote:
[...]

> It's possible that this could be accomplished by something like Veil,
> or the built-in implementation that's coming in some future version of
> PG (is it scheduled for 8.5 at this point?)
> 
> Anyway, if a Veil rule required the user to enter a password that would
> decrypt their key then store it in the session [...]

Still, I don't see much advantage in doing the decryption server-side --
and one disadvantage: if someone hijacks the "live" server, they have
your key.

(The only possible addvantage would be indexing, but you would have to
solve tougher problems: how do you keep the index key protected?

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8hbWBcgs9XrR2kYRAvChAJ9LMYjO1P0T5nB45ChooYBMxQHbvgCeJBfM
sqIJ9JMMZ0BNdsW2/XmxFOU=
=yqq/
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, Apr 23, 2009 at 01:31:39PM -0700, Marc Munro wrote:

[...]

> In principle it could be used in the way that Bill Moran suggests though
> I have never used it that way.  I am somewhat suspicious of passing
> encryption keys to the database server as there is always the potential
> for them to be leaked.

Exactly.

>                         It is generally much safer to keep keys and the
> decryption process on a separate server.

Or just client-side. Minimum spread of knowledge. Decrypting fields
server-side gains us nothing which can't be achieved by encrypting the
whole data partition (this would protect us against the server being
stolen in a "shut down" state). And encrypting the partition gives us
indexing "as usual", which wouldn't be as easy to achieve with encrypted
fields.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8hgEBcgs9XrR2kYRAju5AJ4pRma6bOffFIDAf7yAzrS6vjMo6gCfW7r0
E5qa+P3hDT78qKrzLpWEi2Y=
=b8/v
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Bill Moran
Date:
In response to tomas@tuxteam.de:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Thu, Apr 23, 2009 at 10:38:55AM -0400, Bill Moran wrote:
> [...]
> 
> > It's possible that this could be accomplished by something like Veil,
> > or the built-in implementation that's coming in some future version of
> > PG (is it scheduled for 8.5 at this point?)
> > 
> > Anyway, if a Veil rule required the user to enter a password that would
> > decrypt their key then store it in the session [...]
> 
> Still, I don't see much advantage in doing the decryption server-side --
> and one disadvantage: if someone hijacks the "live" server, they have
> your key.
> 
> (The only possible addvantage would be indexing, but you would have to
> solve tougher problems: how do you keep the index key protected?

Someone hijacking your live server does not automatically give anyone
the key, unless you implement this wrong (which is, of course, possible).

Each _user_ gets their own key, encrypted with their password.  Thus,
even if an attack gets an offline dump of the database, it does them
no good unless they already have the user's password.  If they have
that, they they've been given license to bypass your security
restrictions _without_ doing anything funky.

But it's still protected.  If an attacker manages to get an offline
copy of the database (let's imagine a horrific scenario where they
steal an unencrypted backup tape, or they find a huge SQL injection
hole that allows them access to the entire database ...) they still
only have access to the data for users that they know the password
for.  Even if they have a certain user's password, it only unlocks a
single key, which only unlocks that user's data.

Sure, there are challenges, but there are methods to work through all
of those challenges.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


Re: RFE: Transparent encryption on all fields

From
Bill Moran
Date:
In response to tomas@tuxteam.de:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Thu, Apr 23, 2009 at 01:31:39PM -0700, Marc Munro wrote:
> 
> [...]
> 
> > In principle it could be used in the way that Bill Moran suggests though
> > I have never used it that way.  I am somewhat suspicious of passing
> > encryption keys to the database server as there is always the potential
> > for them to be leaked.
> 
> Exactly.
> 
> >                         It is generally much safer to keep keys and the
> > decryption process on a separate server.
> 
> Or just client-side. Minimum spread of knowledge. Decrypting fields
> server-side gains us nothing which can't be achieved by encrypting the
> whole data partition (this would protect us against the server being
> stolen in a "shut down" state). And encrypting the partition gives us
> indexing "as usual", which wouldn't be as easy to achieve with encrypted
> fields.

Not true.  If each user has their own key, it's considerably more
secure than encrypting the partition, since it protects from through-
application attacks as well as physically stolen hardware.

Also, putting the key on the client machine causes the client machine to
be an attack vector, and client machines are usually more numerous and
more difficult to secure than servers.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Apr 24, 2009 at 03:45:16PM -0400, Bill Moran wrote:
> In response to tomas@tuxteam.de:

[...]

> Someone hijacking your live server does not automatically give anyone
> the key, unless you implement this wrong (which is, of course, possible).
> 
> Each _user_ gets their own key, encrypted with their password.  Thus,
> even if an attack gets an offline dump of the database, it does them
> no good unless they already have the user's password.  If they have
> that, they they've been given license to bypass your security
> restrictions _without_ doing anything funky.

Sorry, I was unclear: what I meant was the "live" server in the sense
that it's running (so either by physical access or via a trojan). In
this case the keys have to be around somewhere (say in RAM) -- as
opposed to the "quiescent" server (there I agree: keys are locked, or
better: not even there).

> But it's still protected.  If an attacker manages to get an offline
> copy of the database (let's imagine a horrific scenario where they
> steal an unencrypted backup tape, or they find a huge SQL injection
> hole that allows them access to the entire database ...) they still
> only have access to the data for users that they know the password
> for.  Even if they have a certain user's password, it only unlocks a
> single key, which only unlocks that user's data.

Not if the users have already provided the password and unlocked their
keys (i.e. they are working with the database).

> Sure, there are challenges, but there are methods to work through all
> of those challenges.

I seem to be less optimistic than you are: I think as soon as the server
"has" all the necessary key material to decrypt the data you are't more
secure than a "traditional" system, with some access control.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8pcZBcgs9XrR2kYRAvH4AJ9Lx9Li3y1cpqIyhjorrkKvLfQ/4gCfTzbf
vrXd6oq37UmrARqVrN8FrVY=
=kENy
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Apr 24, 2009 at 03:48:16PM -0400, Bill Moran wrote:
> In response to tomas@tuxteam.de:
> 

[...]

> > >                         It is generally much safer to keep keys and the
> > > decryption process on a separate server.
> > 
> > Or just client-side. Minimum spread of knowledge [...]

[...]

> Not true.  If each user has their own key, it's considerably more
> secure than encrypting the partition

That's definitely the advantage of this approach.
[...]

>                                       since it protects from through-
> application attacks as well as physically stolen hardware.

That depends which state the hardware is in when it's "stolen". If it's
quiescent, good. If it's running (that's what I was referring to with
"live"), the attacker will be able to troll the RAM for unlocked keys,
or whatever. Granted, with a per-user key only the keys of the users
currently "on line" will be compromised.

> Also, putting the key on the client machine causes the client machine to
> be an attack vector, and client machines are usually more numerous and
> more difficult to secure than servers.

Let's face it: once the attacker "has" the client machine, (s)he has
nearly won. watch all those trojans, keyloggers, whatever in action.
"Having" the client machine means a trojan can impersonate as the user
- -- game over (but at least only to the data this particular user has
access to).

Note that I'm not talking about stealing the hardware, but hijacking,
trojanizing, whatever. That's the real threat, in this
Javascript/Flash/Silverlight infested world.

Regards
- -- tomáss
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8pztBcgs9XrR2kYRAnWrAJ9Q1TEYlm1M/ipGb+EEyW0AY3vQ0gCeIwCE
qta1Q0oAv8bYHsHQHgHO16s=
=OTTh
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
Please continue to CC me on this thread as I have disabled receiving  
messages from this list, although remain subscribed.

On 25 Apr 2009, at 05:52, tomas@tuxteam.de wrote:
>> Sure, there are challenges, but there are methods to work through all
>> of those challenges.
>
> I seem to be less optimistic than you are: I think as soon as the  
> server
> "has" all the necessary key material to decrypt the data you are't  
> more
> secure than a "traditional" system, with some access control.

Remember, the threat case here is a stolen server/desktop/laptop which  
is either completely locked down or has been powered off. The secure  
transmission of keys is someone else's problem.

This is essentially a proposal to get around the limitations imposed  
by running PostgreSQL on an encrypted partition. The requirements are  
the following, regardless of how it is implemented:-

- data for selected databases/columns are stored encrypted on disc,  
passwords not persisted (although may remain in RAM)
- a single psql server can autonomously start up and serve connection  
requests (this cannot be done with encrypted disc)
- client PSQL queries must remain unchanged (connection request may  
have additional parameters)
- minimal performance penalty, no more than running on top of an  
encrypted drive

Of course if an intruder has physical access to the device AND the  
keys, then all is lost. That is always the case. But that's 2  
intrusions they must make.



Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, Apr 25, 2009 at 11:43:14AM +0100, Sam Halliday wrote:
> Please continue to CC me on this thread as I have disabled receiving 
> messages from this list, although remain subscribed.

OK

[...]

> Remember, the threat case here is a stolen server/desktop/laptop which is 
> either completely locked down or has been powered off. The secure 
> transmission of keys is someone else's problem.
>
> This is essentially a proposal to get around the limitations imposed by 
> running PostgreSQL on an encrypted partition. The requirements are the 
> following, regardless of how it is implemented:-
>
> - data for selected databases/columns are stored encrypted on disc, 
> passwords not persisted (although may remain in RAM)

An encrypted partition does most of this (except the "selected columns"
part). Note that encrypting the column most probably will make it
impossible to put an index on that).

so it's +1 for encrypted column.

> - a single psql server can autonomously start up and serve connection 
> requests (this cannot be done with encrypted disc)

Sure it can -- it will be strongly architecture dependent though. Look
at [1] for an example of how this might be done for the _root partition_
in GNU/Linux (it'll be easier for a dedicated partition, when all else
is up and running).

> - client PSQL queries must remain unchanged (connection request may have 
> additional parameters)

Same with encrypted partition.

> - minimal performance penalty, no more than running on top of an encrypted 
> drive

Same with encrypted partition.

> Of course if an intruder has physical access to the device AND the keys, 
> then all is lost. That is always the case. But that's 2 intrusions they 
> must make.

Same with encrypted partition.

There is another advantage of encrypted column mentioned upthread (and I
think it's the main one): each user can unlock her specific data set.
This is cute, but I still think it can be better addressed doing the
decryption client-side (and I maintain that this is _at least as
secure_, possibly even more secure). A drawback would be that each
client would need its own implementation. For the libpq based clients
there is e.g. libpqtypes [2], but non-libpq based (notably Java) would
have to implement that themselves.

I'm still firmly on the camp of "client side", it seems.

[1] <http://gpl.coulmann.de/ssh_luks_unlock.html>
[2] <http://www.postgresql.org/about/news.1021>

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ8/mnBcgs9XrR2kYRAqjlAJ9uS1z5xjqhS9AcrbnZc9bobpfO4wCfY4E3
flhU9Rjf9k47iRlnSxs21Oc=
=Abyx
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
On 26 Apr 2009, at 07:05, tomas@tuxteam.de wrote:
>> - a single psql server can autonomously start up and serve connection
>> requests (this cannot be done with encrypted disc)
>
> Sure it can -- it will be strongly architecture dependent though. Look
> at [1] for an example of how this might be done for the _root  
> partition_
> in GNU/Linux (it'll be easier for a dedicated partition, when all else
> is up and running).

I read the reference and I disagree that this is currently possible.  
Even this example is not an autonomous startup of the psql server. It  
requires an inward network connection, for a start. Consider the case  
where the PSQL server is on a laptop and its primary function is to  
serve local requests, therefore "dialling in" over ssh is not an option.

If there were a way to prompt the user for the password to an  
encrypted drive on startup for all OS, with an equivalent for headless  
machines... then perhaps encrypted drives would be practical enough to  
be used by psql. At the moment, the bootup sequence and requirements  
of psql mean its only really an option for user-started servers. An  
alternative is necessary.



Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:

Tomas Zerolo wrote:
> 
> Note that I'm not talking about stealing the hardware, but hijacking,
> trojanizing, whatever. That's the real threat, in this
> Javascript/Flash/Silverlight infested world.
> 

I'm still talking about theft of machines (particularly laptops) as that is
a major threat. One need only read the British newspapers to discover story
after story of articles where "sensitive information was on a laptop which
was stolen". As pointed out elsewhere, psql + encrypted drive is entirely
unpractical as no OS is setup to ask for an encrypted drive password on boot
(similarly for headless machines, user interaction is required). A practical
solution that accomplishes the same goals as the encrypted drive is
necessary.
-- 
View this message in context:
http://www.nabble.com/RFE%3A-Transparent-encryption-on-all-fields-tp23195216p23241410.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



Re: RFE: Transparent encryption on all fields

From
Andrej
Date:
2009/4/26 Sam Halliday <sam.halliday@gmail.com>:

> I'm still talking about theft of machines (particularly laptops) as that is
> a major threat. One need only read the British newspapers to discover story
> after story of articles where "sensitive information was on a laptop which
> was stolen". As pointed out elsewhere, psql + encrypted drive is entirely
> unpractical as no OS is setup to ask for an encrypted drive password on boot
> (similarly for headless machines, user interaction is required). A practical
> solution that accomplishes the same goals as the encrypted drive is
> necessary.
Buy a higher end thinkpad, it uses a BIOS password and an ASIC
to encrypt the data in hardware, w/o impact on performance.


Re: RFE: Transparent encryption on all fields

From
Sam Mason
Date:
On Mon, Apr 27, 2009 at 07:03:32AM +1200, Andrej wrote:
> 2009/4/26 Sam Halliday <sam.halliday@gmail.com>:
> > I'm still talking about theft of machines (particularly laptops) as that is
> > a major threat. One need only read the British newspapers to discover story
> > after story of articles where "sensitive information was on a laptop which
> > was stolen". As pointed out elsewhere, psql + encrypted drive is entirely
> > unpractical as no OS is setup to ask for an encrypted drive password on boot
> > (similarly for headless machines, user interaction is required). A practical
> > solution that accomplishes the same goals as the encrypted drive is
> > necessary.
>
> Buy a higher end thinkpad, it uses a BIOS password and an ASIC
> to encrypt the data in hardware, w/o impact on performance.

There are various tools that allow you to do this without specialised
hardware, TrueCrypt[1] is one I've used in the past and is very easy for
naive users to get their heads around.

--  Sam  http://samason.me.uk/
[1] http://www.truecrypt.org/


Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
TrueCrypt is exactly the "encrypted drive" solution. It has problems. They
are described in this thread.


Sam Mason wrote:
> 
> There are various tools that allow you to do this without specialised
> hardware, TrueCrypt[1] is one I've used in the past and is very easy for
> naive users to get their heads around.
> 

-- 
View this message in context:
http://www.nabble.com/RFE%3A-Transparent-encryption-on-all-fields-tp23195216p23247320.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



Re: RFE: Transparent encryption on all fields

From
Gurjeet Singh
Date:
On Mon, Apr 27, 2009 at 3:43 AM, Sam Halliday <sam.halliday@gmail.com> wrote:

TrueCrypt is exactly the "encrypted drive" solution. It has problems. They
are described in this thread.

If there were a way to prompt the user for the password to an encrypted drive on startup for all OS, with an equivalent for headless machines... then perhaps encrypted drives would be practical enough to be used by psql

Perhaps TrueCrypt _is_ the solution you are looking for. It allows you to encrypt the root/system partition (at least on Windows) and can ask for decryption password at boot time.

http://www.truecrypt.org/docs/?s=system-encryption

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, Apr 26, 2009 at 03:13:16PM -0700, Sam Halliday wrote:
> 
> TrueCrypt is exactly the "encrypted drive" solution. It has problems. They
> are described in this thread.

No. This is about *clients* (i.e. laptops which can be stolen). How
some companies allow their employees to run around with unencrypted
laptops really escapes me (actually it angers me :-(

Encrypting the server drive for the database has some drawbacks,
discussed in this thread.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ9TT6Bcgs9XrR2kYRAtG4AJ9CND9Ldfdt6a3dpuZDvNHNcpKSkwCbBNTl
ZKrtJUeNHj2Ath/jv2q0xzg=
=6Esk
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, Apr 26, 2009 at 04:40:33AM -0700, Sam Halliday wrote:
> 
> 
> Tomas Zerolo wrote:
> > 
> > Note that I'm not talking about stealing the hardware, but hijacking,
> > trojanizing, whatever. That's the real threat, in this
> > Javascript/Flash/Silverlight infested world.
> > 
> 
> I'm still talking about theft of machines (particularly laptops) as that is
> a major threat. One need only read the British newspapers to discover story
> after story of articles where "sensitive information was on a laptop which
> was stolen". As pointed out elsewhere, psql + encrypted drive is entirely
> unpractical as no OS is setup to ask for an encrypted drive password on boot
> (similarly for headless machines, user interaction is required). A practical
> solution that accomplishes the same goals as the encrypted drive is
> necessary.

Now you are mixing things.
* A laptop (by definition *not* a headless machine) which you carry  around and has sensitive data on it: there is _no_
excusenot to  encrypt the drive. There are lots of options (TrueCrypt, for Linux  there's Luks, some laptop vendors
providetheir own). There are lots  of variants to enter the passphrase, some more convenient  (fingerprint, I'm a
littlewary of this one).
 
  Same goes for removable media, e.g. thumb drives (they get lost too).
  Note that this solution doesn't fly without user education: if your  laptop is stolen and then "mysteriously"
re-appearsyou _have_ to  assume that some has jigged it. Don't enter the passphrase! Nuke it  and install from backup.
 
* "No OS is setup to ask for an encrypted drive on bootup" -- this is  a red herring. It's not the OS's job to do that,
it'sthe mount  process (remember: it has to work on insertion of a thumb drive too).  TrueCrypt manages this fine, as
doesLuks. Doing that at boot time  for built-in media (my laptop does that) is just convenience.
 
* Server on a headless machine -- agreed. That's what we were talking  about.

But i fear we are getting seriously off-topic by now :-/

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ9TgPBcgs9XrR2kYRAoeGAJ9nupOLzQ0X5Lh9R9utCPuzsCaU9gCfXhjd
kUUGAg96JJ9Tu9CZXKkJYOs=
=Gw7S
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sun, Apr 26, 2009 at 11:54:55AM +0100, Sam Halliday wrote:
> On 26 Apr 2009, at 07:05, tomas@tuxteam.de wrote:
>>> - a single psql server can autonomously start up and serve connection
>>> requests (this cannot be done with encrypted disc)
>>
>> Sure it can -- it will be strongly architecture dependent though
>> [...]

> I read the reference and I disagree that this is currently possible.

I didn't try, but knowing how LUKS works I would be very surprised if
that wasn't possible.

>                                                                      Even 
> this example is not an autonomous startup of the psql server. It requires 
> an inward network connection, for a start.

I didn't understand that.

>                                            Consider the case where the PSQL 
> server is on a laptop and its primary function is to serve local requests, 
> therefore "dialling in" over ssh is not an option.

If the sensitive data is in a laptop, the sysadmin should be hit three
times over the head with the newest SQL standard if (s)he doesn't
encrypt the drive in the first place.

> If there were a way to prompt the user for the password to an encrypted 
> drive on startup for all OS, with an equivalent for headless machines... 

There definitely is. We even need more flexibility: prompt for
credentials at the time of *mounting* a secured partition (this might be
the time you put in a thumb drive, or the time where you take this
particular secured database on-line).

> then perhaps encrypted drives would be practical enough to be used by psql. 
> At the moment, the bootup sequence and requirements of psql mean its only 
> really an option for user-started servers. An alternative is necessary.

There would be two steps: unlock database (starting the server), connect
to it. If that's unpractical, remember: client-side decryption. The
server _never_ sees the decrypted data (and more important: the
decryption key). The only point of failure is the client (and the client
is a point of failure in any case).

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ9VeZBcgs9XrR2kYRAhSVAJ4jm6PxMZ7ZVDsWHt1UjBceNXjscACdHeOJ
Q/DTDRTTCfc858dboD8Dmno=
=ei8t
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
Not looking for a Windows solution. Must be cross platform and work  
for headless machines, laptops and desktops. Encrypted drive solutions  
fall short of these requirements. Other considerations which rule out  
encrypted drives have been discussed earlier in the thread.

For the record, I have a working solution at the moment that involves  
using an encrypted drive and a manual per-user startup sequence. *I am  
not looking for user advice*, this is an RFE for an additional server- 
side encryption approach to security.

-- 
Sam

On 27 Apr 2009, at 01:54, Gurjeet Singh wrote:

> On Mon, Apr 27, 2009 at 3:43 AM, Sam Halliday  
> <sam.halliday@gmail.com> wrote:
>
> TrueCrypt is exactly the "encrypted drive" solution. It has  
> problems. They
> are described in this thread.
>
> If there were a way to prompt the user for the password to an  
> encrypted drive on startup for all OS, with an equivalent for  
> headless machines... then perhaps encrypted drives would be  
> practical enough to be used by psql
>
> Perhaps TrueCrypt _is_ the solution you are looking for. It allows  
> you to encrypt the root/system partition (at least on Windows) and  
> can ask for decryption password at boot time.
>
> http://www.truecrypt.org/docs/?s=system-encryption
>
> Best regards,
> -- 
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB      http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device



Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:

Tomas Zerolo wrote:
> 
>> If there were a way to prompt the user for the password to an encrypted 
>> drive on startup for all OS, with an equivalent for headless machines... 
> 
> There definitely is. We even need more flexibility: prompt for
> credentials at the time of *mounting* a secured partition (this might be
> the time you put in a thumb drive, or the time where you take this
> particular secured database on-line).
> 

There is a difference between "it's possible" and "there is". I know of no
such standard support of either of the standard OSes.


Tomas Zerolo wrote:
> 
>> then perhaps encrypted drives would be practical enough to be used by
>> psql. 
>> At the moment, the bootup sequence and requirements of psql mean its only 
>> really an option for user-started servers. An alternative is necessary.
> 
> There would be two steps: unlock database (starting the server), connect
> to it. If that's unpractical, remember: client-side decryption. The
> server _never_ sees the decrypted data (and more important: the
> decryption key). The only point of failure is the client (and the client
> is a point of failure in any case).
> 

Ignore client side issues... that's a separate problem. The threat case here
is a stolen desktop/laptop/server where the attacker rips out the hard drive
to peak inside.
-- 
View this message in context:
http://www.nabble.com/RFE%3A-Transparent-encryption-on-all-fields-tp23195216p23252021.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



Re: RFE: Transparent encryption on all fields

From
Martijn van Oosterhout
Date:
On Mon, Apr 27, 2009 at 01:28:45AM -0700, Sam Halliday wrote:
> Tomas Zerolo wrote:
> >
> >> If there were a way to prompt the user for the password to an encrypted
> >> drive on startup for all OS, with an equivalent for headless machines...
>
> There is a difference between "it's possible" and "there is". I know of no
> such standard support of either of the standard OSes.

For the record, in Debian Linux you can setup an encrypted root FS
during installation and it will ask to a password on every boot.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Re: RFE: Transparent encryption on all fields

From
Sam Mason
Date:
On Mon, Apr 27, 2009 at 09:24:55AM +0100, Sam Halliday wrote:
> Not looking for a Windows solution. Must be cross platform and work  
> for headless machines, laptops and desktops. Encrypted drive solutions  
> fall short of these requirements. Other considerations which rule out  
> encrypted drives have been discussed earlier in the thread.

Just for reference; TrueCrypt is for Mac OS/X and Linux.  Never tried it
on them, but it's supposed to work!

> For the record, I have a working solution at the moment that involves  
> using an encrypted drive and a manual per-user startup sequence. *I am  
> not looking for user advice*, this is an RFE for an additional server- 
> side encryption approach to security.

OK, I've just re-read your original messages--my mail client had decided
to break the thread into several pieces and I think I've put it all back
together now and have an idea what you're after.

As far as I can tell, it would currently be somewhat hard with PG the
way it's currently implemented.  It assumes shared access to various
catalogue tables and I don't think these couldn't be encrypted (I
believe they're needed during database startup).

One possible arrangement would be if each user/encryption key had its
own database cluster.  If that's OK, then maybe you could hack pg-pool
around so that once it received the secret it would be able to run off,
mount the appropriate partitions, and start the database engine before
connecting to it.  I've not used pg-pool before, but have a feeling
that it can re-write queries on the fly so must have some non-trivial
protocol knowledge--I may be wrong about that though.

Allowing multiple users/encryption keys access the same database seems
problematic; how would you allow catalogue access and enforce unique or
other constraints if the server couldn't look to see what's there.  Not
sure what you're after here though.

--  Sam  http://samason.me.uk/


Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
On 27 Apr 2009, at 13:55, Sam Mason wrote:
> Allowing multiple users/encryption keys access the same database seems
> problematic; how would you allow catalogue access and enforce unique  
> or
> other constraints if the server couldn't look to see what's there.   
> Not
> sure what you're after here though.

Any valid key should have full access (i.e. this isn't about access  
control). As a first approximation, a single key would do the trick.  
As a more rigorous solution, there are ways to encrypt data allowing  
multiple keys to decrypt.


Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
I think Sam Mason's proposal of hacking pg-pool sounds feasible. Is  
there any way to create a formal RFE for this? Is anybody interested  
in implementing this?

On 27 Apr 2009, at 13:55, Sam Mason wrote:
> One possible arrangement would be if each user/encryption key had its
> own database cluster.  If that's OK, then maybe you could hack pg-pool
> around so that once it received the secret it would be able to run  
> off,
> mount the appropriate partitions, and start the database engine before
> connecting to it.  I've not used pg-pool before, but have a feeling
> that it can re-write queries on the fly so must have some non-trivial
> protocol knowledge--I may be wrong about that though.



Re: RFE: Transparent encryption on all fields

From
tomas@tuxteam.de
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, Apr 27, 2009 at 01:28:45AM -0700, Sam Halliday wrote:
> 
> 
> Tomas Zerolo wrote:
> > 
> >> If there were a way to prompt the user for the password to an encrypted 
> >> drive on startup for all OS, with an equivalent for headless machines... 

[...]

> There is a difference between "it's possible" and "there is". I know of no
> such standard support of either of the standard OSes.

Sorry. Denial doesn't help. It's not only "possible", it's being done
all the time. Cf. <http://www.saout.de/tikiwiki/tiki-index.php?page=LUKS>,
for example. But you are attacking a strawman anyway.

Client-side decryption matches much better what you had in mind -- and
I think it's provably no less secure (and more convenient).

The only hypothetical advantage of server-side encryption (there might
be an opportunity of indexing) seems to be so mired in technical
difficulties (if you want to avoid information leaks anyway) that I
can't even imagine whether it's a real advantage.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFJ9oriBcgs9XrR2kYRAj/CAJ9c1UERONoqYtjEj0N/aSp5IELFAgCffeTR
nomoWcaFoE9fiYPD0EOr9To=
=KevK
-----END PGP SIGNATURE-----


Re: RFE: Transparent encryption on all fields

From
Sam Halliday
Date:
If it works without any change to client SQL queries and compatible with JPA,
then I'm all ears. Otherwise, I really think Sam Mason's idea was spot on...
it works around the inadequacies of encrypted drives and provides the same
level of on-server security.


Tomas Zerolo wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Mon, Apr 27, 2009 at 01:28:45AM -0700, Sam Halliday wrote:
>>
>>
>> Tomas Zerolo wrote:
>> >
>> >> If there were a way to prompt the user for the password to an
>> encrypted
>> >> drive on startup for all OS, with an equivalent for headless
>> machines...
>
> [...]
>
>> There is a difference between "it's possible" and "there is". I know of
>> no
>> such standard support of either of the standard OSes.
>
> Sorry. Denial doesn't help. It's not only "possible", it's being done
> all the time. Cf. <http://www.saout.de/tikiwiki/tiki-index.php?page=LUKS>,
> for example. But you are attacking a strawman anyway.
>
> Client-side decryption matches much better what you had in mind -- and
> I think it's provably no less secure (and more convenient).
>
> The only hypothetical advantage of server-side encryption (there might
> be an opportunity of indexing) seems to be so mired in technical
> difficulties (if you want to avoid information leaks anyway) that I
> can't even imagine whether it's a real advantage.
>
> Regards
> - -- tomás
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFJ9oriBcgs9XrR2kYRAj/CAJ9c1UERONoqYtjEj0N/aSp5IELFAgCffeTR
> nomoWcaFoE9fiYPD0EOr9To=
> =KevK
> -----END PGP SIGNATURE-----
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

--
View this message in context:
http://www.nabble.com/RFE%3A-Transparent-encryption-on-all-fields-tp23195216p23272501.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.