Database Reference
In-Depth Information
Connecting using SSL
Here we demonstrate how to enable PostgreSQL to use SSL for protecting database connections
by encrypting all data passed over that connection. Using SSL makes it much harder to sniff
the database traffic including usernames, passwords, and sensitive data that are passed
between client and database by someone listening to a network somewhere between them. An
alternative to using SSL is running the connection over a VPN (Virtual private Network).
Using SSL makes the data transfer on the encrypted connection a little slower, so you may not
want to use it if you are sure your network is safe. The performance impact can be quite large
if you are doing lots of short connections, as setting up a SSL connection is quite CPU-heavy.
In this case, you may want to run a local spooling solution, such as PgBouncer, to which
you connect without encryption, and make the SSL-protected connection using stunnel as
described in PgBoun
cer FAQ at the following website:
http://pgbouncer.pr
ojects.postgresql.org/doc/faq.html
Getting ready
Get or generate an SSL server key and certificate pair for the server, and store these into the
data directory of current database instance as files
server.key
and
server.crt
.
It may already be done for you on some platforms. For example, on Ubuntu, postgres is set up
to support SSL connections by default.
How to do it...
Set
ssl
=
on
in
postgresql.conf
, and restart the database.
How it works...
If
ssl
=
on
is set, then postgresql listens to both plain and SSL connections on the same port
(5432 by default), and determines the type of connection from the first byte of a new connection.
Then, it proceeds to set up an SSL connection, if an incoming request asks for it.
There's more...
You can leave the choice of whether or not to use SSL to the client, or you can force SSL
usage from the server's side.
To let the client choose, use lines such as the following:
host database user IP-address IP-mask auth-method
