Database Encryption (now required by law in Italy)

[Silvana Di Martino]

The new italian law about personal data protection (aka "privacy") requires
the use of cryptography _both_ for protecting the network traffic _and_ for
protecting the (personal) data stored on the hard disk.

While I can see how to protect a PGSQL connection using SSH tunneling or a
VPN, I cannot see any way to encrypt a PGSQL database stored on a hard disk,
put aside the possibility to encrypt the hosting file system with LoopAES.

Is there any (native/third party) tool for encrypting a PGSQL database on the
disk? Is there any PGSQL option for encrypting data on the fly?

Many thanks in advance for your attention.

NOTE: the italian law on privacy ("Decreto Legge 196/03", Dicembre 2003) is
available on the net:

http://www.garanteprivacy.it
http://www.interlex.it

Of course, just in italian...
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Radu-Adrian Popescu]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Silvana Di Martino wrote:

| The new italian law about personal data protection (aka "privacy")
| requires the use of cryptography _both_ for protecting the network
| traffic _and_ for protecting the (personal) data stored on the hard
| disk.
|
| While I can see how to protect a PGSQL connection using SSH
| tunneling or a VPN, I cannot see any way to encrypt a PGSQL
| database stored on a hard disk, put aside the possibility to
| encrypt the hosting file system with LoopAES.
|
| Is there any (native/third party) tool for encrypting a PGSQL
| database on the disk? Is there any PGSQL option for encrypting data
| on the fly?
|
| Many thanks in advance for your attention.
|
| NOTE: the italian law on privacy ("Decreto Legge 196/03", Dicembre
| 2003) is available on the net:
|
| http://www.garanteprivacy.it http://www.interlex.it
|
| Of course, just in italian...
| ----------------------------------------- Alessandro Bottoni and
| Silvana Di Martino ***@interfree.it
| ***@tin.it
|
| ---------------------------(end of
| broadcast)--------------------------- TIP 7: don't forget to
| increase your free space map settings
|
|
Are you sure you need to encrypt the _database_ ? It seems strange to
require encryption
of all the data, as you would get using LoopAES. I think you only need
to decide (and probably
the privacy protection law stipulates this) what data you need to
encrypt and store that data
encrypted in the database; such as customer's names, addresses, social
data, payment data
and so on. On the other hand, I think you should be doing this anyway.
I know we are :-)

Regards,

- --
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
[phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFASEQCVZmwYru5w6ERAqrSAJ0b2LPIe2TznFS4f4l9iYC3nMA9VgCgiKDs
MMFfrReUhbvI5xXfG+Ha1PE=
=X1ZW
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Dave Ewart]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Radu-Adrian Popescu
| While I can see how to protect a PGSQL connection using SSH
| tunneling or a VPN, I cannot see any way to encrypt a PGSQL
| database stored on a hard disk, put aside the possibility to
| encrypt the hosting file system with LoopAES.

What's wrong with using a LoopAES filesystem? It protects against
someone walking off with the server, or at least the hard disk, and
being able to see the data.

Keeping encrypted data in the database itself is also perfectly
possible; you just need to decide what you're going to use to decrypt it
afterwards.

Dave.
- --
Dave Ewart
***@cancer.org.uk
Computing Manager, Epidemiology Unit, Oxford
Cancer Research UK
PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370

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

iD8DBQFASEZpbpQs/WlN43ARApVHAKD+ymIbp+HAFUJwaTQ7JyUL07f3GgCeN9D7
miJut0SeQ7/16ANQf1NUUWk=
=lzG/
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Matt Clark]

Post by Dave Ewart
What's wrong with using a LoopAES filesystem? It protects against
someone walking off with the server, or at least the hard disk, and
being able to see the data.

Yes, but only if the password has to entered manually [1] at boot time.
And it gives zero protection against someone who gains root access to the
server.

So you _also_ have to encrypt the sensitive data before giving it to the
DB, using a key that is not stored on the DB server.

Of course that means your app servers have to have _those_ passwords/
keys entered manually at boot time, or else someone who roots them can
read your sensitive data quite trivially.

And to do any better than that you need one of those very snazzy cards
from nCipher or whoever, that allow you to process encrypted data in a
hardware sandbox so even your application doesn't see it, or at least
only allow signed code to manipulate the data.


Matt

[1] There are ways of avoiding having to enter the info manually, but
they're very tricky to implement securely.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Radu-Adrian Popescu]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Matt Clark wrote:

|>What's wrong with using a LoopAES filesystem? It protects against
|>someone walking off with the server, or at least the hard disk, and
|>being able to see the data.
|
|
|Yes, but only if the password has to entered manually [1] at boot time.
|And it gives zero protection against someone who gains root access to the
|server.
|
|So you _also_ have to encrypt the sensitive data before giving it to the
|DB, using a key that is not stored on the DB server.
|
|Of course that means your app servers have to have _those_ passwords/
|keys entered manually at boot time, or else someone who roots them can
|read your sensitive data quite trivially.
|
|And to do any better than that you need one of those very snazzy cards
|from nCipher or whoever, that allow you to process encrypted data in a
|hardware sandbox so even your application doesn't see it, or at least
|only allow signed code to manipulate the data.
|
|
|Matt
|
|[1] There are ways of avoiding having to enter the info manually, but
| they're very tricky to implement securely.
|
|
|---------------------------(end of broadcast)---------------------------
|TIP 2: you can get off all lists at once with the unregister command
| (send "unregister YourEmailAddressHere" to ***@postgresql.org)
|
|
We're basically doing it this way: generate a pair of private/public
keys and save these.
Then we get sensitive data in from some outside source, use it in the
processing part,
then we encrypt it using the public key (obviously) and store it in
the database. They
public key lives itself inside the database, because it presents no
security risk if exposed.
The private key lives on a separate machine in another physical
location. So no-one can
decrypt the data even if gaining access to the server machines.
Obviously this assumes
that we do not decrypt the data after using it... Which is something
rather hard to enforce,
depending on the application.

If we do need to decrypt the data, we take the encrypted data to a
safe location, get the private key
securely and then decrypt and use the data.

- --
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
[phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFASGTdVZmwYru5w6ERAtCkAJ47bkhYffP3CeehdOKZThhCSoOoJgCfQGU3
9ZQkJc6yiuK5WwpmHtvRQ0E=
=jkE+
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Silvana Di Martino]

Post by Radu-Adrian Popescu
We're basically doing it this way: generate a pair of private/public
keys and save these.
Then we get sensitive data in from some outside source, use it in the
processing part,
then we encrypt it using the public key (obviously) and store it in
the database. They
public key lives itself inside the database, because it presents no
security risk if exposed.
The private key lives on a separate machine in another physical
location. So no-one can
decrypt the data even if gaining access to the server machines.
Obviously this assumes
that we do not decrypt the data after using it... Which is something
rather hard to enforce,
depending on the application.
If we do need to decrypt the data, we take the encrypted data to a
safe location, get the private key
securely and then decrypt and use the data.

That is quite complicated. It looks like what we actually need is a system
like Kerberos (devoted to data encryption "on the fly" instead of user
authentication). The logical schema of Kerberos fits well our model: a
third-party system authenticate and authorize whoever (process or human) that
have to access the data. Just the third party stores the private keys and use
them. All other parties just uses public keys.

Unfortunately, just a community of crypto-specialists can implement such a
mechanism.

See you.
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Dave Ewart]

Post by Matt Clark

Post by Dave Ewart
What's wrong with using a LoopAES filesystem? It protects against
someone walking off with the server, or at least the hard disk, and
being able to see the data.

Yes, but only if the password has to entered manually [1] at boot
time. And it gives zero protection against someone who gains root
access to the server.
[...]
[1] There are ways of avoiding having to enter the info manually, but
they're very tricky to implement securely.

Not sure I follow this - there's no point AT ALL in using LoopAES if you
can mount the encrypted partitions without needing manual intervention
at boot time.

Dave.
--
Dave Ewart
***@cancer.org.uk
Computing Manager, Epidemiology Unit, Oxford
Cancer Research UK
PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Grega Bremec]

Post by Dave Ewart

Post by Matt Clark
[1] There are ways of avoiding having to enter the info manually, but
they're very tricky to implement securely.

Not sure I follow this - there's no point AT ALL in using LoopAES if you
can mount the encrypted partitions without needing manual intervention
at boot time.

Why not, Dave? As far as I understand it, one of the major values of fs-level
data encryption was protection from the abused data being collected via the
means of stolen laptops or even failed hard disks that've been handed over
to various data-rescue labs.

If a machine is reasonably secure both locally and remotely (i.e. secure-
-enough passwords, rotated on a regular basis and frequently enough, or even
some sort of PKID mechanism such as smartcards etc. and proper access control
for local security, tight and reasonably reliable encrypted authentication and
authorization for remote use), implementing an extra layer of any level of
security is plain senseless and simply too expensive, both in terms of CPU
overhead and thus performance drop (in the event of application-level
encryption, for example), and time dedicated to research and implementation,
as far as I'm concerned. One is better off by improving techniques in active
use, investigating for the real weaknesses of their information system or
dedicating the time to finding a better general solution that would cover
as many areas as possible in one step.

Same goes for interactive booting. In the world where even power failures
are accounted for and automated, what does a user have to do with booting
a machine? Implementing strong authentication in boot loaders for any kind
of configuration other than the default would be perfectly sufficient, if
you ask me. Also, if I may remind you at this point, human failure has so
far remained to be one of the top reasons for security compromises, social
engineering not being an uncommon one, so eliminating an extra point of
failure in the case of an operator being required to guide the machine
through the boot process doesn't harm in my opinion.

One of the most important guidelines in implementing and assuring security
for one's data would be the fact that security pays off only as long as the
total cost of its implementation is lower than or equal to the maximum
reasonably expectable harm done in the case of a security breach.

Cheers,

[Dave Ewart]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Grega Bremec

Post by Dave Ewart

Post by Matt Clark
[1] There are ways of avoiding having to enter the info manually,
but they're very tricky to implement securely.

Not sure I follow this - there's no point AT ALL in using LoopAES if
you can mount the encrypted partitions without needing manual
intervention at boot time.

Why not, Dave? As far as I understand it, one of the major values of
fs-level data encryption was protection from the abused data being
collected via the means of stolen laptops or even failed hard disks
that've been handed over to various data-rescue labs.

Yes, that's exactly my point. Yet it sounds like you're disagreeing
with me?!

Post by Grega Bremec
If a machine is reasonably secure both locally and remotely (i.e.
secure- -enough passwords, rotated on a regular basis and frequently
enough, or even some sort of PKID mechanism such as smartcards etc.
and proper access control for local security, tight and reasonably
reliable encrypted authentication and authorization for remote use),
implementing an extra layer of any level of security is plain
senseless and simply too expensive, both in terms of CPU overhead and
thus performance drop (in the event of application-level encryption,
for example), and time dedicated to research and implementation, as
far as I'm concerned. One is better off by improving techniques in
active use, investigating for the real weaknesses of their information
system or dedicating the time to finding a better general solution
that would cover as many areas as possible in one step.

I agree with this too. Yet I still get the feeling you're disagreeing
with my original point ... !

Post by Grega Bremec
Same goes for interactive booting. In the world where even power
failures are accounted for and automated, what does a user have to do
with booting a machine? Implementing strong authentication in boot
loaders for any kind of configuration other than the default would be
perfectly sufficient, if you ask me. Also, if I may remind you at this
point, human failure has so far remained to be one of the top reasons
for security compromises, social engineering not being an uncommon
one, so eliminating an extra point of failure in the case of an
operator being required to guide the machine through the boot process
doesn't harm in my opinion.
One of the most important guidelines in implementing and assuring
security for one's data would be the fact that security pays off only
as long as the total cost of its implementation is lower than or equal
to the maximum reasonably expectable harm done in the case of a
security breach.

I'm not sure I get your point, Grega - are you suggesting that using
LoopAES is a good idea and that a system with LoopAES should also boot
up automatically?

I don't understand that at all. If it boots up automatically, then the
encryption is effectively transparent and pointless. All data on the
disk is 'pre-decrypted', very conveniently for whoever has just stolen
the server/disk.

If you're using LoopAES, it should *require* manual intervention, i.e.
passphrase to be entered. If you want the system to boot up
automatically, using LoopAES is redundant and so should not be used.

Dave.
- --
Dave Ewart
***@cancer.org.uk
Computing Manager, Epidemiology Unit, Oxford
Cancer Research UK
PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370

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

iD8DBQFASI9VbpQs/WlN43ARAoJdAKDSEGYJ4QpAiavHjQo9faNDK9ihIgCdG/CP
X0nuHIF989baKSfaq4oy7c8=
=Na0T
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Grega Bremec]

Post by Dave Ewart
I'm not sure I get your point, Grega - are you suggesting that using
LoopAES is a good idea and that a system with LoopAES should also boot
up automatically?
I don't understand that at all. If it boots up automatically, then the
encryption is effectively transparent and pointless. All data on the
disk is 'pre-decrypted', very conveniently for whoever has just stolen
the server/disk.
If you're using LoopAES, it should *require* manual intervention, i.e.
passphrase to be entered. If you want the system to boot up
automatically, using LoopAES is redundant and so should not be used.

Darn, you made me shorten my hefty post into ten lines. :-(
Just when I I was beginning to like it the way it was... :)

Mind, this is not only in reference to LoopAES.

All I'm saying is that good local security _can_ provide for lack of any
kind of interactive unlock procedure during boot in case the piece of
information required to un(b)lock access to encrypted media can be securely
stored and used by the boot loader in a non-revelatory manner, for example
by pushing the authentication process one step down. One example of that
would be a PSK-protecting boot loader (for boot arguments) and some sort
of key-encrypted boot loader config file.

That way, even if someone can boot the Damn Thing [tm], they can not log
into the system and access the protected data, nor do they have access to
information required to access unencrypted data (passphrases, keys, ...).

Cheers,

[Silvana Di Martino]

Post by Matt Clark
Yes, but only if the password has to entered manually [1] at boot time.
And it gives zero protection against someone who gains root access to the
server.

This is a problem for italian users because the italian law clearly states
that only the people delegated to perform maintenance on the personal data
can have access to them. SysAdmin are clearly escluded. So, we do have to
protect the data even from the "prying eyes" of our SysAdmin... :-(

Post by Matt Clark
So you _also_ have to encrypt the sensitive data before giving it to the
DB, using a key that is not stored on the DB server.

Right.

Post by Matt Clark
Of course that means your app servers have to have _those_ passwords/
keys entered manually at boot time, or else someone who roots them can
read your sensitive data quite trivially.

Right.

Post by Matt Clark
And to do any better than that you need one of those very snazzy cards
from nCipher or whoever, that allow you to process encrypted data in a
hardware sandbox so even your application doesn't see it, or at least
only allow signed code to manipulate the data.

Actually, we are considering the adoption of USB cryptographic keys for this
task. Having the passwords stored in a USB EPROM should be safer and easier
to use than a set of hard-to-remember/hard-to-crack alphanumeric passwords
(each at least 8 characters long, accordingly with our law).

IMHO, this crypto topic should be seriously taken into account by the
developers of all of the major Open Source Database Engines (PostgreSQL,
MySQL and Firebird). Given that a large part of the data stored into these DB
are somehow "personal" or even "sensitive", the lack of built-in
cryptographic protection could push our beloved GPL RDBMS out of the market.

See you
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Silvana Di Martino]

Post by Dave Ewart
What's wrong with using a LoopAES filesystem? It protects against
someone walking off with the server, or at least the hard disk, and
being able to see the data.

Nothing! I just wonder if there is any other tool, more specific to
PostgreSQL.

Post by Dave Ewart
Keeping encrypted data in the database itself is also perfectly
possible; you just need to decide what you're going to use to decrypt it
afterwards.

Of course, that is right. I meant a different thing, anyway: I would like to
have a encrypt/decrypt tool that I do not have to implement by myself (both
because I'm lazy and because I like standard tools.)

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Silvana Di Martino]

Post by Radu-Adrian Popescu
Are you sure you need to encrypt the _database_ ? It seems strange to
require encryption
of all the data, as you would get using LoopAES. I think you only need
to decide (and probably
the privacy protection law stipulates this) what data you need to
encrypt and store that data
encrypted in the database; such as customer's names, addresses, social
data, payment data
and so on. On the other hand, I think you should be doing this anyway.
I know we are :-)

Deciding which data are relevant is not easy. The law stipulates that all of
the "personal data" have to be encrypted and that "personal data" are the
data that allow a "spy" to infer any of the following information about a
person:
- identity
- age
- health status
- political orientation
- religious faith
- address
- phone number
- email address
- and a few more...
As you can see, almost everything is a "personal data". At least, almost
everything worth to be stored in a database is.

We just think it is easier and safer to encrypt the whole database, or even
the whole disk, than try to understand what the law actually means.

At the moment, our data are on a server protected by a firewall and accessible
just by authorized people. This was clearly declared as being "sufficient" by
the italian law until December 2003. The new law, instead, clearly states
that personal data have to be encrypted even when stored in a safe place like
that.

See you
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dave Ewart]

Post by Silvana Di Martino
Deciding which data are relevant is not easy. The law stipulates that all of
the "personal data" have to be encrypted and that "personal data" are the
data that allow a "spy" to infer any of the following information about a
- identity
- age
- health status
- political orientation
- religious faith
- address
- phone number
- email address
- and a few more...

Interesting - of course some of the above, such as age, political
orientation and religious faith are NOT personal data if the other
identifying information is absent (e.g. you know that someone aged 35 is
a Communist and a Christian - not terribly helpful on its own).

The exception to that rule is where there are very few people having the
characteristics in question.

But as you say, encrypting 'the whole database' in some way is probably
safest ...

If you find any 'automated' front-end to do this at the database-level,
rather than something like loopback at the filesystem level or at the
field level for specific fields, I think there would be a lot of
interest.

Dave.
--
Dave Ewart
***@cancer.org.uk
Computing Manager, Epidemiology Unit, Oxford
Cancer Research UK
PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Mitch Pirtle]

Post by Dave Ewart
If you find any 'automated' front-end to do this at the database-level,
rather than something like loopback at the filesystem level or at the
field level for specific fields, I think there would be a lot of
interest.

But that is the problem, isn't it? Any 'automated'
encryption/decryption will be just as useful to the would-be perpetrator
of data theft. This is like having an automatic alarm system on your
car that works for anyone that walks up to it.

The same logic applies to encrypting the data in the database -
somewhere on your server the application has to know how to decrypt it,
and that means anyone that gains access to your server will have that
ability also...

I understand (and demand) requiring SSL connections for database
clients, and MD5 hashing of passwords before storing in the database,
but implementing two-way encryption of database data just doesn't make
sense to me.

-- Mitch

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Matt Davies]

And how does one account for key information? If one encrypts any information
deemed worthy to be a key then you have to decrypt the entire database to find
particular information.


Of course, you could keep keys unencrypted for use, but then again, why encrypt
it at all?

Post by Mitch Pirtle

Post by Dave Ewart
If you find any 'automated' front-end to do this at the database-level,
rather than something like loopback at the filesystem level or at the
field level for specific fields, I think there would be a lot of
interest.

But that is the problem, isn't it? Any 'automated'
encryption/decryption will be just as useful to the would-be perpetrator
of data theft. This is like having an automatic alarm system on your
car that works for anyone that walks up to it.
The same logic applies to encrypting the data in the database -
somewhere on your server the application has to know how to decrypt it,
and that means anyone that gains access to your server will have that
ability also...
I understand (and demand) requiring SSL connections for database
clients, and MD5 hashing of passwords before storing in the database,
but implementing two-way encryption of database data just doesn't make
sense to me.
-- Mitch
---------------------------(end of broadcast)---------------------------

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Matt Davies]

Post by Dave Ewart

Post by Matt Davies
And how does one account for key information? If one encrypts any

information

Post by Matt Davies
deemed worthy to be a key then you have to decrypt the entire database to

find

Post by Matt Davies
particular information.
Of course, you could keep keys unencrypted for use, but then again, why

encrypt

Post by Matt Davies
it at all?

My question is much more basic than that: Why encrypt anything beyond
passwords? If you secure the accounts on the machine, and encrypt all
network traffic to the machine (ssh, scp, ssl) then what additional
security can you add?

I totally agree. Depending on interpretation this would totally negate the
usefullness of a database at all.

Post by Dave Ewart
I have servers in remote facilities all over the world. It is just not
possible for me to fly to each datacenter to be there at boot time when
I upgrade the kernel. I'd love the travel, but it is not feasible.
Second, hard-disk encryption will only come into play if someone stole
the hardware, right? And even then, as long as the thing boots, then
they would have access! That is, unless we went back to the
human-required-at-boot scenario.
As a former CSO for an 18000-person company, I'm a horribly paranoid
person when it comes to security; but security that is easily bypassed
(or dificult-to-impossible to enforce) is just added effort, isn't it?
Here is an idea to beat up on: how about having the end user of the
application supply the key that is used to decrypt their data, and only
their data? Take your basic, garden variety PHP website, for example.
When the user is given an account, they are also given a password. This
password is also used as the key for the (blowfish, via mcrypt maybe?)
encryption of the data that gets stored for that person. If you do not
have that key, then you cannot decrypt their data. To boot, their key
is useless for everyone else's data as they used their own...

I have thought about this scenario many times. The problem then is lost
passwords and so forth. All data is then useless because you can't decrypt it.


I worked for a public certification authority at one time and we were starting
to explore key escrow. That would be a useful feature, but in the end, one
sufficiently motivated will probably find a way to circumvent the system.

Post by Dave Ewart
Excellent discussion, maybe we could all come up with a sort of best
practices for PostgreSQL and security :)
-- Mitch

Yes, excellent discussion. Security and privacy are always important, but it is
also necessary to remember the business case and use of databases and not
reduce their functionality to nil. It is a hard balance to find.



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Mitch Pirtle]

Post by Matt Davies
And how does one account for key information? If one encrypts any information
deemed worthy to be a key then you have to decrypt the entire database to find
particular information.
Of course, you could keep keys unencrypted for use, but then again, why encrypt
it at all?

My question is much more basic than that: Why encrypt anything beyond
passwords? If you secure the accounts on the machine, and encrypt all
network traffic to the machine (ssh, scp, ssl) then what additional
security can you add?

I have servers in remote facilities all over the world. It is just not
possible for me to fly to each datacenter to be there at boot time when
I upgrade the kernel. I'd love the travel, but it is not feasible.

Second, hard-disk encryption will only come into play if someone stole
the hardware, right? And even then, as long as the thing boots, then
they would have access! That is, unless we went back to the
human-required-at-boot scenario.

As a former CSO for an 18000-person company, I'm a horribly paranoid
person when it comes to security; but security that is easily bypassed
(or dificult-to-impossible to enforce) is just added effort, isn't it?

Here is an idea to beat up on: how about having the end user of the
application supply the key that is used to decrypt their data, and only
their data? Take your basic, garden variety PHP website, for example.

When the user is given an account, they are also given a password. This
password is also used as the key for the (blowfish, via mcrypt maybe?)
encryption of the data that gets stored for that person. If you do not
have that key, then you cannot decrypt their data. To boot, their key
is useless for everyone else's data as they used their own...

Excellent discussion, maybe we could all come up with a sort of best
practices for PostgreSQL and security :)

-- Mitch

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Bruno Wolff III]

On Fri, Mar 05, 2004 at 10:00:23 -0500,

Post by Dave Ewart
Second, hard-disk encryption will only come into play if someone stole
the hardware, right? And even then, as long as the thing boots, then
they would have access! That is, unless we went back to the
human-required-at-boot scenario.

Your can generate a random key on boot that is stored in volatile memory.
If the system is powered down the data will become unavailable. This
might be useful for some groups of people (e.g. criminal organizations),
but for others the problems with losing the data due to a power outage might
be too high.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Silvana Di Martino]

Post by Dave Ewart
My question is much more basic than that: Why encrypt anything beyond
passwords? If you secure the accounts on the machine, and encrypt all
network traffic to the machine (ssh, scp, ssl) then what additional
security can you add?

The following:
- protect your data from the "prying eyes" of your SysAdmins (our law imposes
this kind of protection)
- protect your data in case of hardware theft

Post by Dave Ewart
I have servers in remote facilities all over the world. It is just not
possible for me to fly to each datacenter to be there at boot time when
I upgrade the kernel. I'd love the travel, but it is not feasible.

Technically speaking, this is not required:
- we could have a boot system that requires the password on the net to a
"password server" or a human. A few network-based booting systems for
diskless workstations do something like that already. We just need a
network-based password system similar to Kerberos or DHCP. It does not exists
yet, and it will be hard to implement, but it can be created.

Post by Dave Ewart
Second, hard-disk encryption will only come into play if someone stole
the hardware, right? And even then, as long as the thing boots, then
they would have access! That is, unless we went back to the
human-required-at-boot scenario.

See above. The laptop must ask for a password on the net. You just lock the
password of any stolen/missing PC on your password server.

Post by Dave Ewart
As a former CSO for an 18000-person company, I'm a horribly paranoid
person when it comes to security; but security that is easily bypassed
(or dificult-to-impossible to enforce) is just added effort, isn't it?

That's why I did not vote Berlusconi: he is prone to enforce this kind of
"security"... ;-)

Post by Dave Ewart
Here is an idea to beat up on: how about having the end user of the
application supply the key that is used to decrypt their data, and only
their data? Take your basic, garden variety PHP website, for example.
When the user is given an account, they are also given a password. This
password is also used as the key for the (blowfish, via mcrypt maybe?)
encryption of the data that gets stored for that person. If you do not
have that key, then you cannot decrypt their data. To boot, their key
is useless for everyone else's data as they used their own...

This is not a solution: "delegated operators" must be able to access the data
without bothering the data "owner" (that is: the person described by the
data). They cannot (and must not) ask the owner to grant them access to the
data every time they need to use them.

Post by Dave Ewart
Excellent discussion, maybe we could all come up with a sort of best
practices for PostgreSQL and security :)

I do hope so: this problem is going to affect a lot of SysAdmins EU-wide and
deserves a standard solution.

See you

BTW: if you have a USA-based company and collect info regarding Italian
people, you have to comply with this absurd Italian law. Funny, isn't it?

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruno Wolff III]

On Fri, Mar 05, 2004 at 20:12:37 +0000,

Post by Silvana Di Martino
BTW: if you have a USA-based company and collect info regarding Italian
people, you have to comply with this absurd Italian law. Funny, isn't it?

Only if you are going to come within reach of Italian LE. I doubt Bush
will let Italy extradite and business people from the US who violate
this law.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Silvana Di Martino]

Post by Bruno Wolff III
On Fri, Mar 05, 2004 at 20:12:37 +0000,

Post by Silvana Di Martino
BTW: if you have a USA-based company and collect info regarding Italian
people, you have to comply with this absurd Italian law. Funny, isn't it?

Only if you are going to come within reach of Italian LE. I doubt Bush
will let Italy extradite and business people from the US who violate
this law.

I agree. We are still waiting the american pilot of the A3 Intruder that
crashed down a ropeway on our mountains in (I think) 1996 and killed 20
people. He was promoted the last year and still flies (but this time on _USA_
mountains, for our sake...).

Humor apart, there are already a few cases of privacy law violations of this
kind being examined by our authority. It looks like that the foreing web
sites that violated this law will be obscurated. Considering that this law
will slowly spread UE-wide, I will take it seriously even if I worked from
USA.

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Michael Chaney]

Post by Silvana Di Martino
BTW: if you have a USA-based company and collect info regarding Italian
people, you have to comply with this absurd Italian law. Funny, isn't it?

No, we don't. We're not bound by Italian law, only US law.

Michael

[Silvana Di Martino]

Post by Michael Chaney

Post by Silvana Di Martino
BTW: if you have a USA-based company and collect info regarding Italian
people, you have to comply with this absurd Italian law. Funny, isn't it?

No, we don't. We're not bound by Italian law, only US law.

Unfortunately, that is not so easy. Consider this:
- you sell books online, like Amazon
- you collect personal data from your customers
- a part of your customers are from Italy (or from any other of the 25
countries of 2005 EU, something like 400 milion people)
- you violate the privacy law of these country
- they obscure your site (using their firewall), a technique already used
against porno web sites.
- you lose 400 milion potential customers and win a nice (extremely expensive)
trial for violation of an international law on online commerce.

It happened already. The new law (that I consider quite stupid) imposes
exactly this punishment to companies that violate it (plus a trans-national
criminal trial and a quite large fine).

Both USA and UE companies have to comply with WTO rules. There are
international agreements between USA and EU regarding online commerce and
these agreements states that a company in EU cannot violate the law on
commerce of USA if it operates on the USA territory and, of course, vice
versa. Nowadays, no one can play just for himself on this small planet, in
particular if he wants to sell his stuff to others.

Of course, you do not have to trust me. Ask your company's lawyer about this.

See you
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Gorshkov]

Post by Silvana Di Martino
Both USA and UE companies have to comply with WTO rules. There are
international agreements between USA and EU regarding online commerce and
these agreements states that a company in EU cannot violate the law on
commerce of USA if it operates on the USA territory and, of course, vice
versa. Nowadays, no one can play just for himself on this small planet, in
particular if he wants to sell his stuff to others.
Of course, you do not have to trust me. Ask your company's lawyer about this.

You are, of course, correct - but you're missing one thing.

When you connect to amazon.com from italy, my understanding is that you're
(legally) conducting business in the USA, because that's where the
transaction actually takes place - that's where the servers are.

The reverse however should be correct - I should now be able to connect to an
Italian website from here in Canada, and not have to worry about my info
winding up in the hands of some @#*($&@#*( spammer :-)


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Silvana Di Martino]

Post by Gorshkov
When you connect to amazon.com from italy, my understanding is that you're
(legally) conducting business in the USA, because that's where the
transaction actually takes place - that's where the servers are.

Well, there are two different and unrelated business operations:
- When you buy a book on Amazon, you are doing buiness in the USA, because the
transaction takes place in the USA (or, at least, so it looks like)
- When Amazon collects personal data regarding italian people in a wrong way,
is violating an italian law because the data collection regards italian
citizens and takes place in Italy. At least, this is what our law-makers
implicity states in the text of this new privacy law.

Anyway, the only reliable source of info about this topic is WTO. Is WTO that
rules these international aspect of business.

Post by Gorshkov
The reverse however should be correct - I should now be able to connect to
an Italian website from here in Canada, and not have to worry about my info

That is exactly what should happen.

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Bruno Wolff III]

On Fri, Mar 05, 2004 at 07:43:10 -0700,

Post by Matt Davies
And how does one account for key information? If one encrypts any information
deemed worthy to be a key then you have to decrypt the entire database to find
particular information.

It depends on what kinds of searches you do. If they are searches for
equals, you can use one way hashes and still have efficient searches.
"Transluscent Databases" has some other tricks in it along these lines.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Mitch Pirtle]

It all comes down to what you're trying to protect your data *from*. If
you're trying to protect it from people sniffing network traffic between
clients and the server, then SSL is sensible. If you're trying to
protect against somebody reading passwords out of a database and using
them to impersonate other users, use MD5 (or SHA) hashing.

Yep. And the previously-mentioned concept of encrypting identifying
data (name, contact info) per-user using their password as the key would
also be a great complement. If their password was ever lost, then you
would only have to verify their identity and have them select a new
password. I think I will implement this scheme in my next weblication
just to see how it would look and work...

If you're trying to protect against somebody taking down your server
room door with a sledgehammer, lifting your server out of the rack,
driving it away and booting off an alternative medium to avoid needing
to know your root password, then a loopback encrypted partition (or data
encrypted in GPG where the decryption key is not stored on the database
server) is a sensible precaution.

In my entire career, the only time I have lost a server to compromise
was just that - the physical threat. I had a development server in an
office location, and the perpetrator simply climbed over the walls
(through the ceiling tiles) to get into the office. NOTE: Their
elevator key was supposed to have been deactivated, or they would never
had access to my floor. So this was a double-whammy security failure by
both myself and the building's security. Never again!

I expect that for most database users, it comes down to meeting the
standards defined by law rather than realistic expectations of an attack
- I expect that most of the situations we attempt to prevent are
unlikely in the extreme, but we have various contractual and legal
obligations which mean we have to defend against them anyway.

If this list could come up with a solid approach that supported these
requirements, that would be a huge win on a global scale :)

Here is my proposal, and I'd like to see if it could pass muster on this
list:

The only identifier for the account would be the login, all other
contact data (phone, name, address etc.) would be encrypted before
storage by (blowfish, twofish, AES, your choice), using the password as
the key. Password is stored in the database hashed by MD5. You could
steal the database and all the data, but no contact information could be
decrypted, as each account has had their personal information encrypted
with a different key.

What do the rest of you think?

Of course, this loopback encryption with a boot-time passphrase may fail
if they take the rackmount UPS as *well*, and keep the machine powered
at all times ;)

Two problems I see with this:

1) I've never seen a quad-xeon live on a UPS for more than 90 seconds
2) They will probably fail to get the external RAID storage unit

ROTFL

-- Mitch

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Fri, Mar 05, 2004 at 10:31:41 -0500,

Post by Mitch Pirtle
1) I've never seen a quad-xeon live on a UPS for more than 90 seconds

A UPS isn't supposed to do that. Its purpose to is to allow for a clean
shutdown or time to switch to an alternate power source. You need a backup
generator if you want to continue running through power outages.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Chris Ruprecht]

On Friday 05 March 2004 10:31, Mitch Pirtle wrote:

[snip]

Post by Mitch Pirtle
1) I've never seen a quad-xeon live on a UPS for more than 90 seconds

I had mine (Acer Altos G900) running for more than 10 minutes the other day,
until I decied the outage would take a little longer than usual and shut
everything down. I have a 1400 VA APC UPS without any spare battery packs.

Best regards,
Chris


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alex Page]

Post by Mitch Pirtle
I understand (and demand) requiring SSL connections for database
clients, and MD5 hashing of passwords before storing in the database,
but implementing two-way encryption of database data just doesn't make
sense to me.

It all comes down to what you're trying to protect your data *from*. If
you're trying to protect it from people sniffing network traffic between
clients and the server, then SSL is sensible. If you're trying to
protect against somebody reading passwords out of a database and using
them to impersonate other users, use MD5 (or SHA) hashing.

If you're trying to protect against somebody taking down your server
room door with a sledgehammer, lifting your server out of the rack,
driving it away and booting off an alternative medium to avoid needing
to know your root password, then a loopback encrypted partition (or data
encrypted in GPG where the decryption key is not stored on the database
server) is a sensible precaution.

I expect that for most database users, it comes down to meeting the
standards defined by law rather than realistic expectations of an attack
- I expect that most of the situations we attempt to prevent are
unlikely in the extreme, but we have various contractual and legal
obligations which mean we have to defend against them anyway.

Of course, this loopback encryption with a boot-time passphrase may fail
if they take the rackmount UPS as *well*, and keep the machine powered
at all times ;)

Alex
--
Mail: Alex Page <***@cancer.org.uk>
Real: Systems/Network Assistant, Epidemiology Unit, Oxford
Tel: [phone] (external) / 223 (internal)
PGP: 8868 21D7 3D35 DD77 9D06 BF0A 0746 2DE6 55EA 367E

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Silvana Di Martino]

If you're trying to protect against somebody taking down your server
room door with a sledgehammer, lifting your server out of the rack,
driving it away and booting off an alternative medium to avoid needing
to know your root password, then a loopback encrypted partition (or data
encrypted in GPG where the decryption key is not stored on the database
server) is a sensible precaution.

Unfortunately, the new Italian law forces us to take seriously into account
this catastrophic scenario and another one that is almost as worring: an
unfaithful SysAdmin that copies your data and sells them to KGB. So, database
encryption (and not disk encryption) is the _only_ answer.

- I expect that most of the situations we attempt to prevent are
unlikely in the extreme, but we have various contractual and legal
obligations which mean we have to defend against them anyway.

This is the point.

Of course, this loopback encryption with a boot-time passphrase may fail
if they take the rackmount UPS as *well*, and keep the machine powered
at all times ;)

The server should listen to the (encrypted/digitally signed) "Heartbeat" of a
password server through the net to prevent this kind of attack.

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[scott.marlowe]

Post by Silvana Di Martino

If you're trying to protect against somebody taking down your server
room door with a sledgehammer, lifting your server out of the rack,
driving it away and booting off an alternative medium to avoid needing
to know your root password, then a loopback encrypted partition (or data
encrypted in GPG where the decryption key is not stored on the database
server) is a sensible precaution.

Unfortunately, the new Italian law forces us to take seriously into account
this catastrophic scenario and another one that is almost as worring: an
unfaithful SysAdmin that copies your data and sells them to KGB. So, database
encryption (and not disk encryption) is the _only_ answer.

the only way for this to work is for it to be a "two key system" like the
military uses for missile launch.

One sysadmin as the "key" to the database box, but the data is encrypted
before being sent to the database box on another system with another admin
with another "key". Preferably these two would never interact or know
each other.

If the encryption and decryption happen on the same box that runs that
database, then it's simply more work for the sysadmin to get at the data,
not an impossibility. Anything outside of two seperate systems, one with
storage, the other doing encrypting without any form long term storage is
just a charade of security.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Silvana Di Martino]

Post by scott.marlowe

Post by Silvana Di Martino
Unfortunately, the new Italian law forces us to take seriously into
account this catastrophic scenario and another one that is almost as
worring: an unfaithful SysAdmin that copies your data and sells them to
KGB. So, database encryption (and not disk encryption) is the _only_
answer.

the only way for this to work is for it to be a "two key system" like the
military uses for missile launch.
One sysadmin as the "key" to the database box, but the data is encrypted
before being sent to the database box on another system with another admin
with another "key". Preferably these two would never interact or know
each other.

Well, this is not necessarly true. Data maintainers and SysAdmin performs
different tasks (accordingly to italian law):
- SysAdmins take care of the hardware and of the software. They should never
need to access data. They just need to access the RDBMS software and its
configuration.
- Just Data Maintainers need to access data.
This should allow us to have two password for two different tasks. So, there
is not any need to use the military scheme to enforce data security.

See you
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[scott.marlowe]

Post by Silvana Di Martino

Post by scott.marlowe

Post by Silvana Di Martino
Unfortunately, the new Italian law forces us to take seriously into
account this catastrophic scenario and another one that is almost as
worring: an unfaithful SysAdmin that copies your data and sells them to
KGB. So, database encryption (and not disk encryption) is the _only_
answer.

the only way for this to work is for it to be a "two key system" like the
military uses for missile launch.
One sysadmin as the "key" to the database box, but the data is encrypted
before being sent to the database box on another system with another admin
with another "key". Preferably these two would never interact or know
each other.

Well, this is not necessarly true. Data maintainers and SysAdmin performs
- SysAdmins take care of the hardware and of the software. They should never
need to access data. They just need to access the RDBMS software and its
configuration.
- Just Data Maintainers need to access data.
This should allow us to have two password for two different tasks. So, there
is not any need to use the military scheme to enforce data security.

Sorry, but that's the wrong answer. Once someone has root on a unix box
her can do ANYTHING he wants. and he can cover his tracks. If the
encryption takes place on his box, he can attach to the process doing the
encryption and /or replace it with a trojan copy of his own and get your
data. The ONLY way to keep the data secure is for it to be encrypted
elsewhere before it gets to the storage box. If the box that stores it
encrypts, it, the root user on that box can impersonate anyone and any
process on that box to get to the data in mid stream.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Silvana Di Martino]

Post by scott.marlowe
Sorry, but that's the wrong answer. Once someone has root on a unix box
her can do ANYTHING he wants. and he can cover his tracks. If the
encryption takes place on his box, he can attach to the process doing the
encryption and /or replace it with a trojan copy of his own and get your
data. The ONLY way to keep the data secure is for it to be encrypted
elsewhere before it gets to the storage box. If the box that stores it
encrypts, it, the root user on that box can impersonate anyone and any
process on that box to get to the data in mid stream.

That's right, of course, but I think we have to consider what we actually have
to prevent, accordingly by law.

A "man-in-the-middle" attack to the encryption system or a
brute-force/dictionary-based attack to the password/data is a crime "per se",
both in Italy and in many other countries. The law does not impose on us the
burden to defend the end-user from a well-planned, well-performed criminal
act. This is the business of our Police. We just have to do our best to
protect our data from human curiosity, human errors and teenager hackers.

The italian law states exactly this: protect your data at the best of your
technological capabilities. Real crime is a police problem.

Anyway, even data encrypted on Mars would be vulnerable to a well-performed
brute-force attack. It is just a matter of computing resource and time.

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Lamar Owen]

Post by scott.marlowe
Sorry, but that's the wrong answer. Once someone has root on a unix box
her can do ANYTHING he wants. and he can cover his tracks.

This is what things like the capabilities system and SELinux are designed to
prevent in the Linux world. As Fedora Core 2 will ship with SELinux
installed and enabled, it will become much more difficult for someone to
randomly get root and do damage. It is quite simple with SELinux to prevent
any of the attacks you mentioned. Root is no longer root. Things on an
SELinux system, or a system fully implementing the kernel capabilities model,
can indeed be locked away from root, at least in network attached multiuser
mode. This does, of course, make maintenance of the data more difficult; one
must be at the console in a special mode to do full maintenance. But someone
remotely cracking root no longer is the threat they once were, when some
system like SELinux is in use.
--
Lamar Owen
Director of Information Technology
Pisgah Astronomical Research Institute
1 PARI Drive
Rosman, NC 28772
[phone]
www.pari.edu

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Silvana Di Martino]

Post by Lamar Owen

Post by scott.marlowe
Sorry, but that's the wrong answer. Once someone has root on a unix box
her can do ANYTHING he wants. and he can cover his tracks.

This is what things like the capabilities system and SELinux are designed
to prevent in the Linux world. As Fedora Core 2 will ship with SELinux
installed and enabled, it will become much more difficult for someone to
randomly get root and do damage. It is quite simple with SELinux to
prevent any of the attacks you mentioned. Root is no longer root. Things
on an SELinux system, or a system fully implementing the kernel
capabilities model, can indeed be locked away from root, at least in
network attached multiuser mode. This does, of course, make maintenance of
the data more difficult; one must be at the console in a special mode to do
full maintenance. But someone remotely cracking root no longer is the
threat they once were, when some system like SELinux is in use.

A better, more structured architecture of permissions on Unix is a
long-standing need. It looks like SELinux is offering a new and interesting
approach to this problem.

Regarding this topic I have a dream: the hyerarchical permission architecture
of OS/400 (and many other IBM OSs for mainframe) ported to Linux. Just imagine
this: you have a omnipotent "root" who can access the machine from the
console only, a whole set of powerful, configurable administrators who can
act from the net, each of them devoted to administer a specific part of the
OS or of the File System, and finally a crowd of simple users, with
configurable permissions. Nobody would have more power of what it actually
need for his job, not even the root.

Would not it be a better (safer and more manageable) world to live on?

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mitch Pirtle]

Post by Silvana Di Martino
Regarding this topic I have a dream: the hyerarchical permission architecture
of OS/400 (and many other IBM OSs for mainframe) ported to Linux. Just imagine
this: you have a omnipotent "root" who can access the machine from the
console only, a whole set of powerful, configurable administrators who can
act from the net, each of them devoted to administer a specific part of the
OS or of the File System, and finally a crowd of simple users, with
configurable permissions. Nobody would have more power of what it actually
need for his job, not even the root.

Great, then all of my linux users, thanks to the administrators in their
physical presence, would get an account with SECADM privileges.

(rimshot)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Silvana Di Martino]

Post by Mitch Pirtle

Post by Silvana Di Martino
Regarding this topic I have a dream: the hyerarchical permission
architecture of OS/400 (and many other IBM OSs for mainframe) ported to
Linux. Just imagine this: you have a omnipotent "root" who can access the
machine from the console only, a whole set of powerful, configurable
administrators who can act from the net, each of them devoted to
administer a specific part of the OS or of the File System, and finally a
crowd of simple users, with configurable permissions. Nobody would have
more power of what it actually need for his job, not even the root.

Great, then all of my linux users, thanks to the administrators in their
physical presence, would get an account with SECADM privileges.
(rimshot)

This would be a human act of will (a "betrayal"). It looks like that not even
God can protect humans from this (have you ever heard of "free will"?). This
would not be different from a Linux/Unix Root giving away its password. I
cannot see any way to protect ourselves from such a betrayal, neither with
technological tools nor in any other way.

BTW: you understandably suppose that administrators have the power to give
SECADM privileges to other users. This may be true or may be not. I cannot
remember which was the situation on OS/400 but I would not be surprised to
discover that ADMINs do not have such a power. Most likely, just a SECADM can
create a new SECADM. A strict division of powers is a fundamental concept of
any security system.

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Stephan Szabo]

Post by Silvana Di Martino

If you're trying to protect against somebody taking down your server
room door with a sledgehammer, lifting your server out of the rack,
driving it away and booting off an alternative medium to avoid needing
to know your root password, then a loopback encrypted partition (or data
encrypted in GPG where the decryption key is not stored on the database
server) is a sensible precaution.

Unfortunately, the new Italian law forces us to take seriously into account
this catastrophic scenario and another one that is almost as worring: an
unfaithful SysAdmin that copies your data and sells them to KGB. So, database
encryption (and not disk encryption) is the _only_ answer.

But since your sysadmin (if not trusted) could go behind your back and
replace the database, any applications that are using the data, etc, I'm
not sure that's even sufficient.

Post by Silvana Di Martino

Of course, this loopback encryption with a boot-time passphrase may fail
if they take the rackmount UPS as *well*, and keep the machine powered
at all times ;)

The server should listen to the (encrypted/digitally signed) "Heartbeat" of a
password server through the net to prevent this kind of attack.

That'll help prevent this sort of attack (although doesn't entirely unless
you can guarantee that the password server cannot be taken at the same
time) but also gives you a remote point of failure.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Silvana Di Martino]

Post by Stephan Szabo

Post by Silvana Di Martino
Unfortunately, the new Italian law forces us to take seriously into
account this catastrophic scenario and another one that is almost as
worring: an unfaithful SysAdmin that copies your data and sells them to
KGB. So, database encryption (and not disk encryption) is the _only_
answer.

But since your sysadmin (if not trusted) could go behind your back and
replace the database, any applications that are using the data, etc, I'm
not sure that's even sufficient.

Replacing the RDBMS engine and/or the "client" application, would be useless:
the "cracker" still need the password to access the encrypted data.

Post by Stephan Szabo

Post by Silvana Di Martino

Of course, this loopback encryption with a boot-time passphrase may
fail if they take the rackmount UPS as *well*, and keep the machine
powered at all times ;)

The server should listen to the (encrypted/digitally signed) "Heartbeat"
of a password server through the net to prevent this kind of attack.

That'll help prevent this sort of attack (although doesn't entirely unless
you can guarantee that the password server cannot be taken at the same
time) but also gives you a remote point of failure.

Right.

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Stephan Szabo]

Post by Silvana Di Martino

Post by Stephan Szabo

Post by Silvana Di Martino
Unfortunately, the new Italian law forces us to take seriously into
account this catastrophic scenario and another one that is almost as
worring: an unfaithful SysAdmin that copies your data and sells them to
KGB. So, database encryption (and not disk encryption) is the _only_
answer.

But since your sysadmin (if not trusted) could go behind your back and
replace the database, any applications that are using the data, etc, I'm
not sure that's even sufficient.

the "cracker" still need the password to access the encrypted data.

Only for data that's already there though, right? What about any
additions (and possibly changes) made after that point?

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Radu-Adrian Popescu]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Mitch Pirtle wrote:

| Dave Ewart wrote:
|
|> If you find any 'automated' front-end to do this at the
|> database-level, rather than something like loopback at the
|> filesystem level or at the field level for specific fields, I
|> think there would be a lot of interest.
|
|
| But that is the problem, isn't it? Any 'automated'
| encryption/decryption will be just as useful to the would-be
| perpetrator of data theft. This is like having an automatic alarm
| system on your car that works for anyone that walks up to it.
|
| The same logic applies to encrypting the data in the database -
| somewhere on your server the application has to know how to decrypt
| it, and that means anyone that gains access to your server will
| have that ability also... I understand (and demand) requiring SSL
| connections for database clients, and MD5 hashing of passwords
| before storing in the database, but implementing two-way encryption
| of database data just doesn't make sense to me.
|
| -- Mitch
|
| ---------------------------(end of
| broadcast)--------------------------- TIP 1: subscribe and
| unsubscribe commands go to ***@postgresql.org
|
|

My point and I think Mitch's point is this: if you use the data when
you receive it, and then encrypt it and store it, then you offer
nothing to a possible intruder
and are safe and sound, provided that you use a PKI scheme, where your
application has only the public key that it encrypts with.
If you also take data out and decrypt it, then you must do so in a
manner that does not compromise the system. That is, transfer the needed
data to a known to be safe location, such as the computer in front of
you, and decrypt it there. At all times, except when you use the data
at another
location, the secret (decryption) key does not reside anywhere near
the data. If someone stole your hard disk, they can make really good
use of it
by storing porn flics over your DB, but they can never, ever retrieve
that data.

Cheers,

- --
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
[phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFASMr4VZmwYru5w6ERAhLFAJ9oLXaahBMEZelYcdjzMIcr+vnLfQCffRbf
WoSqd/9IZRmyrtVWwRzBEgw=
=wN4g
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Silvana Di Martino]

Post by Mitch Pirtle
The same logic applies to encrypting the data in the database -
somewhere on your server the application has to know how to decrypt it,
and that means anyone that gains access to your server will have that
ability also...

That's true, of course but...
1) The cryptographic keys used by the application to access the data could be
stored (encrypted) inside a compiled C/C++ or Delphi/Kylyx program.
2) No matter what we think about data encryption, a (stupid) italian law
enforces it ("Allegato B, Decreto Legge 196/03, Dicembre 2003).
3) As I told in another message, our law prohibites that our SysAdmin had
access to data. Just authorized operators can read them.

Post by Mitch Pirtle
I understand (and demand) requiring SSL connections for database
clients, and MD5 hashing of passwords before storing in the database,
but implementing two-way encryption of database data just doesn't make
sense to me.

Neither to me, actually, but it makes to our law-makers (Have you heard of
Silvio Berlusconi?).

See you
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Silvana Di Martino]

Post by Dave Ewart
If you find any 'automated' front-end to do this at the database-level,
rather than something like loopback at the filesystem level or at the
field level for specific fields, I think there would be a lot of
interest.

As I told in another message, this problem should be faced in a systematic and
community-oriented way because this law is going to affect all EU SysAdmin in
a few years and could push many GPL RDBMS out of the market. I will try to
manage it somehow, just to satisfy my specific needs of the moment, but this
problem do deserves a standard solution.

See you
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Hans Spaans]

On Fri, Mar 05, 2004 at 12:52:05PM +0000, Silvana Di Martino wrote:
<knip>

Post by Silvana Di Martino
At the moment, our data are on a server protected by a firewall and accessible
just by authorized people. This was clearly declared as being "sufficient" by
the italian law until December 2003. The new law, instead, clearly states
that personal data have to be encrypted even when stored in a safe place like
that.

Hopefully not to off-topic.

I'm just wondering, does that law applies on all databases or just on
new databases. In the Netherlands laws about privacy only apply on new
databases and for old databases the law of that time-frame only
applies. I must note that the database must be registered with
http://www.cbpweb.nl/

Hans

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Silvana Di Martino]

Post by Hans Spaans
I'm just wondering, does that law applies on all databases or just on
new databases. In the Netherlands laws about privacy only apply on new
databases and for old databases the law of that time-frame only
applies. I must note that the database must be registered with
http://www.cbpweb.nl/

The italian law applies to all databases (containing personal data) and they
must be registered at www.garanteprivacy.it.

As I told before, there is a EU directive that enforces this logic. All UE
countries will have to comply with it in a few years.

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[l***@hnit.is]

Is it enough to encrypt some table fields only, i.e. "personnal data"
personal(social security) number, name-surname and birth data - if one can
not easily tell whom the data(e.g. bank account) belongs to then it may be
quite enough?
Just because "personal" data is quite a loose term: it can be all or nearly
nothing.

Brgds, Laimis

-----Original Message-----
Silvana Di Martino
Sent: 5. mars 2004 09:54
Subject: [ADMIN] Database Encryption (now required by law in Italy)
The new italian law about personal data protection (aka
"privacy") requires
the use of cryptography _both_ for protecting the network
traffic _and_ for
protecting the (personal) data stored on the hard disk.
While I can see how to protect a PGSQL connection using SSH
tunneling or a
VPN, I cannot see any way to encrypt a PGSQL database stored
on a hard disk,
put aside the possibility to encrypt the hosting file system
with LoopAES.
Is there any (native/third party) tool for encrypting a PGSQL
database on the
disk? Is there any PGSQL option for encrypting data on the fly?
Many thanks in advance for your attention.
NOTE: the italian law on privacy ("Decreto Legge 196/03",
Dicembre 2003) is

http://www.garanteprivacy.it
http://www.interlex.it

Of course, just in italian...
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino ***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Dave Ewart]

Post by l***@hnit.is
Is it enough to encrypt some table fields only, i.e. "personnal data"
personal(social security) number, name-surname and birth data - if one
can not easily tell whom the data(e.g. bank account) belongs to then
it may be quite enough?
Just because "personal" data is quite a loose term: it can be all or
nearly nothing.

I realise the original poster is from Italy, but in the UK there is a
Data Protection Act which defines personal data.

It says that personal data means "data that relate to a living
individual who can be identified from those data or from those data and
other information in the possession of, or likely to come into the
possession of, the data controller". This seems like a good definition
to me, although we extend it to also apply to people who have died.

There is an extension which states that "sensitive" personal data is a
subset of personal data where the data is one of several categories,
such as "physical or mental health" (this is most appropriate one of for
us, doing medical research).

I guess there may similar legislation in Italy and elsewhere.

Suggestions:

1. You need to decide which fields in the database should be encrypted.

2. Perhaps keep all 'personal' data in a separate table and control
access to that table more closely with database permissions.
Optionally, encrypt the contents of that table too.

3. Use a loopback-encrypted filesystem.

Dave.
--
Dave Ewart
***@cancer.org.uk
Computing Manager, Epidemiology Unit, Oxford
Cancer Research UK
PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Silvana Di Martino]

Post by Dave Ewart
I guess there may similar legislation in Italy and elsewhere.

Actually, the Italian law is an implementation of a EU directive strictly
inspired to the existing UK law. It will affect all EU in a few years.

See you

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Dave Ewart]

Post by Silvana Di Martino

Post by Dave Ewart
I guess there may similar legislation in Italy and elsewhere.

Actually, the Italian law is an implementation of a EU directive
strictly inspired to the existing UK law. It will affect all EU in a
few years.

That's interesting to know, certainly.

Dave.
--
Dave Ewart
***@cancer.org.uk
Computing Manager, Epidemiology Unit, Oxford
Cancer Research UK
PGP: CC70 1883 BD92 E665 B840 118B 6E94 2CFD 694D E370


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Sergio Chaves]

Hi.

That is an article on www.linuxuser.co.uk , issue 34 entitled "A Guide
to filesystem encryption"
that may do what you want to do.
It uses a method called "Loop-AES" http://loop-aes.sourceforge.net .

Hope it works for you

Sergio

Post by Silvana Di Martino
The new italian law about personal data protection (aka "privacy") requires
the use of cryptography _both_ for protecting the network traffic _and_ for
protecting the (personal) data stored on the hard disk.
While I can see how to protect a PGSQL connection using SSH tunneling or a
VPN, I cannot see any way to encrypt a PGSQL database stored on a hard disk,
put aside the possibility to encrypt the hosting file system with LoopAES.
Is there any (native/third party) tool for encrypting a PGSQL database on the
disk? Is there any PGSQL option for encrypting data on the fly?
Many thanks in advance for your attention.
NOTE: the italian law on privacy ("Decreto Legge 196/03", Dicembre 2003) is
http://www.garanteprivacy.it
http://www.interlex.it
Of course, just in italian...
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

--
°v° Sergio Chaves °v°
/(_)\ www.turbocorp.com /(_)\
^ ^ Enhanced Solutions Computing ^ ^
770.532.2239
Linux User #221305






---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Mark Gibson]

Post by Silvana Di Martino
Is there any (native/third party) tool for encrypting a PGSQL database on the
disk? Is there any PGSQL option for encrypting data on the fly?

Have a look in contrib/pgcrypto.
Although I haven't actually tried it yet.
--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Silvana Di Martino]

Post by Mark Gibson

Post by Silvana Di Martino
Is there any (native/third party) tool for encrypting a PGSQL database on
the disk? Is there any PGSQL option for encrypting data on the fly?

Have a look in contrib/pgcrypto.
Although I haven't actually tried it yet.

Thanks!

The pgcrypto package contains a few server-side (C + PL/PGSQL) functions that
implement a few data encryption/decryption methods. It looks like they can be
used to protect my data from unauthorized access.

See you
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
***@interfree.it
***@tin.it

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings