Thread: RFE: Transparent encryption on all fields
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
-----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
-----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-----
-----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-----
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/
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 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] 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-----
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] 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.halliday@gmail.com> wrote:
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,
--
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
-----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_ 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-----
-----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-----
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
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.
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.
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.
-----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-----
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.