Re: pgcrypto and database encryption

Alle 15:08, lunedì 8 marzo 2004, Stephan Szabo ha scritto:
> That's what I figured, but given a system where the password is given on
> startup to the database, you would need another layer for the security,
> have you though about how that would work? A not terrible option would be
> to compose the key from something given by the user (perhaps at session
> start with a query) with something already stored, but I don't know if the
> law would allow that.

> Also, there's a question about multiple databases in one instance that
> might want separate encryption esp where the authorized users might be
> non-overlapping sets. It might be better to allow a human to use a query
> to initialize the passwords.

I'm not sure (nobody can actually read the mind of our lawmakers) but I think
that using the same "password's password" (aka "global server password") for
all of the databases managed by a single instance of the PostgreSQL server
would be fine if we are able to guarantee that each user has his own
password.

So, we should have this scenario:
- a single "global server password", supplied just once at the server start-up
by a trusted admin via SSH/SSL and used to encrypt/decrypt all other
passwords. This password should never be stored on disk/db on the same host
as the RDBMS. It must be encrypted and kept in volatile memory (RAM), only.
If the server is shut down, the trusted admin will have to supply it again.
- a set of database-specific passwords used to encrypt/decrypt data. These
passwords could be encrypted using the "global server password" and safely
stored into a table of the database. If we use strong encryption and a long
password (Blowfish or Rijandel with a 512-1024 char-long key) to encrypt the
keys it should be very hard (almost impossible) to crack them.

The user that connect to a database, must authenticate himself using Ident,
SSH or Kerberos. Once accepted the user's connection, a set of server-side
function (pgcrypto) encrypt/decryp data using the database-specific key.

This is almost the same technique used by Oracle. To implement it, PostgreSQL
just lacks a way to supply the "global server password" at start-up. A SQL
Query cannot be used for this both because the password must be visible to
all the database managed by a server and because this password must not be
stored inside the db.

Note that the trusted administrator _can_ be replaced by a server process if:
- the client process that asks for a "global server password" can authenticat
itself in a reliable way (SSH, Kerberos, SSH any other PKI) as a trusted
PostgreSQL server.
- the communication channel is encrypted (SSH/SSL)

> As above, automatic runs into questions about who can do it and maint
> issues, like if encryption happens on insert and the admin cannot see the
> decrypted form, the admin cannot backup the database using something like
> pg_dump. This is probably best solved by doing whatever is necessary to
> make the admin authorized.

Well, data encryption is meant _also_ to protect data from the admin's eye. As
long as I can see, there is no need to access or decrypt data for performing
backup, restore and any other database-level operation. From the admin point
of view, the database can and should be a black box.

> It should be reasonably easy to make types that took input and encrypted
> it and had say appropriate output function that checked and decrypted
> along with implicit casts that do the same thing. It'd be CPU consuming,
> but workable. Here you run into the question of how it's configured
> again.

Specific encrytped data types are coming out to be a real need. Without them,
I cannot see how we could perform data sorting, comparison and any other
basic operation. IMHO, this topic should be brought to the attention of the
PostgreSQL developers with the intent to develop a standard set of reliable
encrypted data types. I'm sure that performance would be hugely affected by
encryption but, again, this is out of our reach: law imposes it.

-----------------------------------------
Alessandro Bottoni and Silvana Di Martino
alessandrobottoni(at)interfree(dot)it
silvanadimartino(at)tin(dot)it