Re: pgcrypto and database encryption - Mailing list pgsql-admin

Alle 18:19, domenica 7 marzo 2004, Joe Conway ha scritto:
> Silvana Di Martino wrote:
> > 4) What could actually solve our problem is something like the following
> > scenario. Imagine that postmaster (or pg_ctrl) could accept a new CL
> > parameter called "pw". This parameter would contain a sequence of
> > comma-separated databasename/encryption-password pairs. I mean, something
> > like this:
> >
> > postmaster -i -pw=postnuke:"arriba!",phpnuke:"blade runner"
>
> But you mentioned earlier that the DBA cannot know the passwords, so who
> is going to type all that in?

Accordingly to law, a "authorized operator". A piece of paper transforms a
generic user/sysadmin into a trusted person who can perform such operations
(again, not our choice: law imposes it).

> Does the law require protection from a determined DBA, or just casual
> viewing by the DBA? *If* it's the latter, you could do something like this:
>
> 1. Export an environment variable , say PGMASTERPASS containing a hex
>     encoded password, something like:
>
>     PGMASTERPASS=0102000304 pg_ctl start
>
> 2. Use a C function to grab the value of the environment variable -- one
>     exists in PL/R already. You could write your own based on that.
>
> 3. Combine the master password with other information to make it
>     sufficiently unique as a key for your various purposes. For example,
>     you might use the md5 hashed password for the current user from
>     pg_shadow. This combining should be done securely -- I'd recommend
>     taking the HMAC of the user password using the master as the key. The
>     result of the HMAC becomes your data encryption/decryption key.

This looks like a viable solution. Thanks for the idea.

> > 5) There is also a problem related to what pgcrypto can encrypt and what
> > it cannot. For example: pgcrypto encrypt functions cannot be applied to
> > DATE and TIME data types because it would mess up them and make them
> > unacceptable by the RDBMS engine. We would need specific encrypted data
> > types like ENCDATA and ENCTIME to handle these cases.
>
> Just use bytea for the encrypted stuff, and write plpgsql functions to
> convert the bytea output of the decrypt function back to its native
> datatype. Here's a more-or-less complete example of what I mean by all
> this:

Thanks again! I will use it!

> As I said above (and others in this thread too), if the DBA (or anyone
> with root access on the database server) is sufficiently determined,
> they can get around this scheme and view whatever data they want. If
> you're really concerned about that scenario, the data should be
> encrypted in your application before it ever gets sent to the database,
> using a key that is unavailable on the database server.

Yes, sure: the encryption key must be unavailable on the db server (at least,
unavailable on its hard disk). Using Blowfish or Rijandel and a 128 - 1024
character-long password it would be almost impossible to decript our data
with widely available hardware in human-reasonable time. Nevertheless, this
does not mean that the on-the-fly encryption/decryption process must
necessarly take place out of the DB server/host (a hypothesys that would
complicate our work a lot). Even a determined and skilled sysadmin will have
big problems in finding, reading and decrypting a password stored in the
volatile memory of a running (and strictly surveilled) host by a RDBMS
application (a task similar to finding a mimetic computer virus in memory).
Adding to this the fact that the original password could be supplied by a
remote user (or process) operating on a SSH channel (encrypted channel +
user/process PKI authentication on both channel ends), it could be even hard
to get it by impersonating the legitimate RDBMS program and ask for it to the
password server (a "man-in-the-middle" attack).

Thanks again for your help.
-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
alessandrobottoni@interfree.it
silvanadimartino@tin.it