Security - PostgreSQL 9 Administration - page 141

SSL mode Eavesdropping
protection

MITM
protection

Statement

verify-full Yes Yes I want my data encrypted, and I accept the

overhead. I want to be sure that I connect to a
server I trust, and that it's the one I specify.

The MITM in the preceding table means Man-In-The-Middle attack, that is, someone posing as
your server, but actually just observing and forwarding the traffic.

Checking server authenticity
The last two SSL modes allow you to be reasonably sure that you are actually talking to your
server, by checking the SSL certificate presented by the server.

See also

To understand more about SSL in general, and OpenSSL library used by PostgreSQL in
particular, visit http://www.openssl.or g , or get a good topic about SSL.

There was also a nice presentacion named "Encrypted PostgreSQL" explaining these issues
at pgcon2009. The slides are available at the following website:

http://www.pgcon.org/2009/schedule/events/120.en.htm l

Encrypting sensitive data

This recipe shows how to encrypt data using the pgcrypto package.

Getting ready

Make sure you (or your database server) are in a country where encryption is not illegal—it still
is in some countries.

Make sure pgcrypto is installed on your database host. On Ubuntu, it comes in package
postgresql-contrib.

Install it into the database in which you want to use it:

psql mydb < /usr/share/postgresql/8.4/contrib/pgcrypto.sql

You also need to have GPG keys set up:

pguser@laptop:~$ gpg --gen-key

Answer some questions here, select key type "DSA and Elgamal", and enter an empty password.