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

On Mon, 8 Mar 2004, Silvana Di Martino wrote:

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

Except that you then need someone who is authorized to view all the
databases correct since that user would potentially be able to get all the
keys?  That still seems to miss the case where the sets of authorized
users have no intersections (which might come up in shared hosting).

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

I think there might still be issues with people who have access to some
(presumably non-encrypted) data in the database while not having access to
the encrypted data unless the SQL permissions system were sufficient.

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

I think C functions with access to a shared memory block wouldn't fail
those two considerations.

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

This sounds alot better all in all.  This also seems like it might be
possible to prototype outside the database proper so as to get an idea of
what it would look like.

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

But, given the fact that the form of the backup is a set of SQL
statements, if the encryption is automatic on input, either the SQL
statements contain encrypted data and you need some way to on restore tell
the system to not encrypted them again or they contain decrypted data. If
accessing the encrypted data without being properly authorized throws an
error rather than giving you the encrypted form, how does the admin do the
selects necessary on the table to even get the encrypted form.

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

Given that it's possible that the types might be an input function, an
output function and one or more type conversion functions, I'd think it'd
be possible to prototype them first.