RFE: Transparent encryption on all fields

-----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-----

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/

-----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-----

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/

-----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-----

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

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/

-----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-----

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/

-----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-----

-----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-----

-----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-----

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.

-----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]<http://gpl.coulmann.de/ssh_luks_unlock.html&gt; 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]<http://www.postgresql.org/about/news.1021&gt;, 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&gt;
[2]: <http://www.postgresql.org/about/news.1021&gt;

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

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

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.

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.

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.

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]http://www.truecrypt.org/ 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/

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.