PostgreSQL offers encryption
at several levels, and provides flexibility in protecting data
from disclosure due to database server theft, unscrupulous
administrators, and insecure networks. Encryption might also be
required to secure sensitive data such as medical records or
financial transactions.

Password Storage Encryption

By default, database user passwords are stored as MD5
hashes, so the administrator cannot determine the actual
password assigned to the user. If MD5 encryption is used
for client authentication, the unencrypted password is
never even temporarily present on the server because the
client MD5 encrypts it before being sent across the
network.

Encryption For Specific Columns

The /contrib function library
pgcrypto allows certain
fields to be stored encrypted. This is useful if only some
of the data is sensitive. The client supplies the
decryption key and the data is decrypted on the server and
then sent to the client.

The decrypted data and the decryption key are present on
the server for a brief time while it is being decrypted and
communicated between the client and server. This presents a
brief moment where the data and keys can be intercepted by
someone with complete access to the database server, such
as the system administrator.

Data Partition Encryption

On Linux, encryption can be layered on top of a file
system mount using a "loopback
device". This allows an entire file system partition
be encrypted on disk, and decrypted by the operating
system. On FreeBSD, the equivalent facility is called GEOM
Based Disk Encryption, or gbde.

This mechanism prevents unencrypted data from being read
from the drives if the drives or the entire computer is
stolen. This does not protect against attacks while the
file system is mounted, because when mounted, the operating
system provides an unencrypted view of the data. However,
to mount the file system, you need some way for the
encryption key to be passed to the operating system, and
sometimes the key is stored somewhere on the host that
mounts the disk.

Encrypting Passwords Across A Network

The MD5 authentication method
double-encrypts the password on the client before sending
it to the server. It first MD5 encrypts it based on the
user name, and then encrypts it based on a random salt sent
by the server when the database connection was made. It is
this double-encrypted value that is sent over the network
to the server. Double-encryption not only prevents the
password from being discovered, it also prevents another
connection from using the same encrypted password to
connect to the database server at a later time.

Encrypting Data Across A Network

SSL connections encrypt all data sent across the
network: the password, the queries, and the data returned.
The pg_hba.conf file allows
administrators to specify which hosts can use non-encrypted
connections (host) and which
require SSL-encrypted connections (hostssl). Also, clients can specify that
they connect to servers only via SSL. Stunnel or SSH can also be used to encrypt
transmissions.

SSL Host Authentication

It is possible for both the client and server to provide
SSL keys or certificates to each other. It takes some extra
configuration on each side, but this provides stronger
verification of identity than the mere use of passwords. It
prevents a computer from pretending to be the server just
long enough to read the password send by the client. It
also helps prevent "man in the middle" attacks where a
computer between the client and server pretends to be the
server and reads and passes all data between the client and
server.

Client-Side Encryption

If the system administrator cannot be trusted, it is
necessary for the client to encrypt the data; this way,
unencrypted data never appears on the database server. Data
is encrypted on the client before being sent to the server,
and database results have to be decrypted on the client
before being used.