Re: RFE: Transparent encryption on all fields

On Thu, 2009-04-23 at 16:08 -0300, pgsql-hackers-owner(at)postgresql(dot)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(at)tuxteam(dot)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:48:16PM -0400, Bill Moran wrote:
> In response to tomas(at)tuxteam(dot)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-----

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(dot)halliday(at)gmail(dot)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(dot)halliday(at)gmail(dot)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/

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.

On Mon, Apr 27, 2009 at 3:43 AM, Sam Halliday <sam(dot)halliday(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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

-----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_ excuse not to
encrypt the drive. There are lots of options (TrueCrypt, for Linux
there's Luks, some laptop vendors provide their own). There are lots
of variants to enter the passphrase, some more convenient
(fingerprint, I'm a little wary 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-appears you _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's the mount
process (remember: it has to work on insertion of a thumb drive too).
TrueCrypt manages this fine, as does Luks. 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-----

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(dot)halliday(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
> singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device

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/

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.

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.