Skip site navigation (1) Skip section navigation (2)

Re: pgcrypto and database encryption

From: Silvana Di Martino <silvanadimartino(at)tin(dot)it>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: pgcrypto and database encryption
Date: 2004-03-08 22:12:22
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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 

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

In response to


pgsql-admin by date

Next:From: GorshkovDate: 2004-03-08 22:25:34
Subject: Re: HIPAA
Previous:From: Stephan SzaboDate: 2004-03-08 22:07:17
Subject: Re: pgcrypto and database encryption

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group