1
>From the servers perspective:
7
(Is SSL_NEGOTIATE_CODE?) ----------- Normal startup
13
(Server compiled with USE_SSL?) ------- Send 'N'
31
>From the clients perspective (v6.6 client _with_ SSL):
37
Send packet with SSL_NEGOTIATE_CODE
40
Receive single char ------- 'S' -------- Establish SSL
46
Is it 'E' for error ------------------- Retry connection
50
Is it 'N' for normal ------------------- Normal startup
55
---------------------------------------------------------------------------
58
COMMENTS FROM BEAR GILES
60
On a related note, I had mentioned this before but it's a subtle point
61
and I'm sure that it's slipped everyone's mind...
63
- if you need to have confidence in the identity of the database
64
server, e.g., you're storing sensitive information and you absolutely
65
must prevent any "man in the middle" attacks, use the SSL code I
66
provided with server-side certs. To many users, the key issue is not
67
whether the data is encrypted, it's whether the other party can be
68
trusted to be who they claim to be.
70
- if you just need confidentiality, but you don't need to verify the
71
identity of the database server (e.g., because you trust the IP address,
72
but worry about packet sniffers), SSH tunnels are much easier to set up
73
and maintain than the embedded SSL code. You can set up the database
74
server so it doesn't require a certificate (hell, you can hard code a
75
fallback certificate into the server!), *but that violates the common
76
practice of SSL-enabled servers.* I cannot overemphasize this - every
77
other SSL-enabled server requires a certificate, and most provide
78
installation scripts to create a "snake oil" temporary certificate. I
79
can't think of any server (apache+mod_ssl, courier-imap, postfix(+tls),
80
etc.) that uses anonymous servers.
82
- if you don't need confidentiality, e.g., you're on a trusted network
83
segment, then use direct access to the server port.
85
---------------------------------------------------------------------------
87
EMAIL ABOUT DOCUMENTATION
89
From: Bear Giles <bgiles@coyotesong.com>
90
Subject: [HACKERS] 2nd cut at SSL documentation
91
To: pgsql-hackers@postgresql.org
92
Date: Tue, 21 May 2002 14:27:00 -0600 (MDT)
94
A second cut at SSL documentation....
98
SSL Support in PostgreSQL
99
=========================
104
The sites that require SSL fall into one (or more) of several broad
107
*) They have insecure networks.
109
Examples of insecure networks are anyone in a "corporate hotel,"
110
any network with 802.11b wireless access points (WAP) (in 2002,
111
this protocol has many well-known security weaknesses and even
112
'gold' connections can be broken within 8 hours), or anyone
113
accessing their database over the internet.
115
These sites need a Virtual Private Network (VPN), and either
116
SSH tunnels or direct SSL connections can be used.
118
*) They are storing extremely sensitive information.
120
An example of extremely sensitive information is logs from
121
network intrusion detection systems. This information *must*
122
be fully encrypted between front- and back-end since an attacker
123
is presumably sniffing all traffic within the VPN, and if they
124
learn that you know what they are doing they may attempt to
125
cover their tracks with a quick 'rm -rf /' and 'dropdb'
127
In the extreme case, the contents of the database itself may
128
be encrypted with either the crypt package (which provides
129
symmetrical encryption of the records) or the PKIX package
130
(which provides public-key encryption of the records).
132
*) They are storing information which is considered confidential
133
by custom, law or regulation.
135
This includes all records held by your doctor, lawyer, accountant,
136
etc. In these cases, the motivation for using encryption is not
137
a conscious evaulation of risk, but the fear of liability for
138
'failure to perform due diligence' if encryption is available but
139
unused and an attacker gains unauthorized access to the harm of
142
*) They have 'road warriors.'
144
This includes all sites where people need to have direct access
145
to the database (not through a proxy such as a secure web page)
146
from changing remote addresses. Client certificates provide a
147
clean way to grant this access without opening up the database
150
Who does not need it?
151
---------------------
153
It's at least as important to know who does not need SSL as it
154
is to know who does. Sites that do not need SSL fall into several
157
*) Access is limited to the Unix socket.
159
*) Access is limited to a physically secure network.
161
"Physically secure" networks are common in the clusters and
162
colocation sites - all database traffic is restricted to dedicated
163
NIC cards and hubs, and all servers and cabling are maintained in
167
Using SSH/OpenSSH as a Virtual Private Network (VPN)
168
====================================================
170
SSH and OpenSSH can be used to construct a Virtual Private Network
171
(VPN) to provide confidentiality of PostgreSQL communications.
172
These tunnels are widely available and fairly well understood, but
173
do not provide any application-level authentication information.
175
To set up a SSH/OpenSSH tunnel, a shell account for each
176
user should be set up on the database server. It is acceptable
177
for the shell program to be bogus (e.g., /bin/false), if the
178
tunnel is set up in to avoid launching a remote shell.
180
On each client system the ~/.ssh/config file should contain
181
an additional line similiar to
183
LocalForward 5555 psql.example.com:5432
185
(replacing psql.example.com with the name of your database server).
186
By putting this line in the configuration file, instead of specifying
187
it on the command line, the tunnel will be created whenever a
188
connection is made to the remote system.
190
The psql(1) client (or any client) should be wrapped with a script
191
that establishes an SSH tunnel when the program is launched:
194
HOST=psql.example.com
195
IDENTITY=~/.ssh/identity.psql
196
/usr/bin/ssh -1 -i $IDENTITY -n $HOST 'sleep 60' & \
197
/usr/bin/psql -h $HOST -p 5555 $1
199
Alternately, the system could run a daemon that establishes and maintains
200
the tunnel. This is preferrable when multiple users need to establish
201
similar tunnels to the same remote site.
203
Unfortunately, there are many potential drawbacks to SSL tunnels:
205
*) the SSH implementation or protocol may be flawed. Serious problems
206
are discovered about once every 18- to 24- months.
208
*) the systems may be misconfigured by accident.
210
*) the database server must provide shell accounts for all users
211
needing access. This can be a chore to maintain, esp. in if
212
all other user access should be denied.
214
*) neither the front- or back-end can determine the level of
215
encryption provided by the SSH tunnel - or even whether an
216
SSH tunnel is in use. This prevents security-aware clients
217
from refusing any connection with unacceptly weak encryption.
219
*) neither the front- or back-end can get any authentication
220
information pertaining to the SSH tunnel.
222
Bottom line: if you just need a VPN, SSH tunnels are a good solution.
223
But if you explicitly need a secure connection they're inadequate.
229
Insecure Channel: ANONYMOUS DH Server
230
-------------------------------------
232
"ANONYMOUS DH" is the most basic SSL implementation. It does
233
not require a server certificate, but it is vulnerable to
234
"man-in-the-middle" attacks.
236
The PostgreSQL backend does not support ANONYMOUS DH sessions.
239
Secure Channel: Server Authentication
240
-------------------------------------
242
Server Authentication requires that the server authenticate itself
243
to clients (via certificates), but clients can remain anonymous.
244
This protects clients from "man-in-the-middle" attacks (where a
245
bogus server either captures all data or provides bogus data),
246
but does not protect the server from bad data injected by false
249
The community has established a set of criteria for secure
252
*) the server must provide a certificate identifying itself
253
via its own fully qualified domain name (FDQN) in the
254
certificate's Common Name (CN) field.
256
*) the FQDN in the server certificate must resolve to the
257
IP address used in the connection.
259
*) the certificate must be valid. (The current date must be
260
no earlier than the 'notBefore' date, and no later than the
263
*) the server certificate must be signed by an issuer certificate
264
known to the clients.
266
This issuer can be a known public CA (e.g., Verisign), a locally
267
generated root cert installed with the database client, or the
268
self-signed server cert installed with the database client.
270
Another approach (used by SSH and most web clients) is for the
271
client to prompt the user whether to accept a new root cert when
272
it is encountered for the first time. psql(1) does not currently
273
support this mechanism.
275
*) the client *should* check the issuer's Certificate Revocation
276
List (CRL) to verify that the server's certificate hasn't been
277
revoked for some reason, but in practice this step is often
280
*) the server private key must be owned by the database process
281
and not world-accessible. It is recommended that the server
282
key be encrypted, but it is not required if necessary for the
283
operation of the system. (Primarily to allow automatic restarts
284
after the system is rebooted.)
286
The 'mkcert.sh' script can be used to generate and install
287
suitable certificates
289
Finally, the client library can have one or more trusted root
290
certificates compiled into it. This allows clients to verify
291
certificates without the need for local copies. To do this,
292
the source file src/interfaces/libpq/fe-ssl.c must be edited
293
and the database recompiled.
295
Secure Channel: Mutual Authentication
296
-------------------------------------
298
Mutual authentication requires that servers and clients each
299
authenticate to the other. This protects the server from
300
false clients in addition to protecting the clients from false
303
The community has established a set of criteria for client
304
authentication similar to the list above.
306
*) the client must provide a certificate identifying itself.
307
The certificate's Common Name (CN) field should contain the
310
*) the client certificate must be signed by a certificate known
313
If a local root cert was used to sign the server's cert, the
314
client certs can be signed by the issuer.
316
*) the certificate must be valid. (The current date must be
317
no earlier than the 'notBefore' date, and no later than the
320
*) the server *should* check the issuer's Certificate Revocation
321
List (CRL) to verify that the clients's certificate hasn't been
322
revoked for some reason, but in practice this step is often
325
*) the client's private key must be owned by the client process
326
and not world-accessible. It is recommended that the client
327
key be encrypted, but because of technical reasons in the
328
architecture of the client library this is not yet supported.
330
PostgreSQL can generate client certificates via a four-step process.
332
1. The "client.conf" file must be copied from the server. Certificates
333
can be highly localizable, and this file contains information that
334
will be needed later.
336
The client.conf file is normally installed in /etc/postgresql/root.crt.
337
The client should also copy the server's root.crt file to
338
~/.postgresql/root.crt.
340
2. If the user has the OpenSSL applications installed, they can
341
run pgkeygen.sh. (An equivalent compiled program will be available
342
in the future.) They should provide a copy of the
343
~/.postgresql/postgresql.pem file to their DBA.
345
3. The DBA should sign this file the OpenSSL applications:
347
$ openssl ca -config root.conf -ss_cert ....
349
and return the signed cert (postgresql.crt) to the user.
351
4. The user should install this file in ~/.postgresql/postgresql.crt.
353
The server will log every time a client certificate has been
354
used, but there is not yet a mechanism provided for using client
355
certificates as PostgreSQL authentication at the application level.
358
---------------------------(end of broadcast)---------------------------
359
TIP 5: Have you checked our extensive FAQ?
361
http://www.postgresql.org/users-lounge/docs/faq.html
363
------------------------------------------------------------------------------
365
Date: Tue, 21 May 2002 19:50:38 -0400
366
From: Neil Conway <nconway@klamath.dyndns.org>
367
To: "Bear Giles" <bgiles@coyotesong.com>
368
cc: pgsql-hackers@postgresql.org
369
Subject: Re: [HACKERS] 2nd cut at SSL documentation
371
On Tue, 21 May 2002 14:27:00 -0600 (MDT)
372
"Bear Giles" <bgiles@coyotesong.com> wrote:
373
> A second cut at SSL documentation....
375
I've pointed out some minor things I noticed while reading through.
376
Yeah, I was bored :-)
378
> The sites that require SSL fall into one (or more) of several broad
381
> *) They have insecure networks.
383
> Examples of insecure networks are anyone in a "corporate hotel,"
385
What's a corporate hotel?
387
> *) They have 'road warriors.'
389
This section title sounds confusingly similar to the 1st item.
390
Perhaps "They need to authentication clients securely" or something
391
similar? The need to use client certificates does not apply only to
392
"road warriors" -- I've seen situations where client-certs are used for
393
clients to connecting to a server over a LAN.
395
> *) Access is limited to the Unix socket.
397
"the" sounds wrong, there's more than just 1 :-)
399
> *) Access is limited to a physically secure network.
401
> "Physically secure" networks are common in the clusters and
402
> colocation sites - all database traffic is restricted to dedicated
403
> NIC cards and hubs, and all servers and cabling are maintained in
406
Perhaps add a note on the performance hit here?
408
> Using SSH/OpenSSH as a Virtual Private Network (VPN)
410
I'm unsure why you're bothering to differentiate between SSH
413
> SSH and OpenSSH can be used to construct a Virtual Private Network
416
No need to include the abbreviation for VPN here, you've explained
419
> to provide confidentiality of PostgreSQL communications.
420
> These tunnels are widely available and fairly well understood, but
421
> do not provide any application-level authentication information.
423
You might want to clarify what "application-level authentication
424
information" means, or else leave out all discussion of drawbacks
427
> To set up a SSH/OpenSSH tunnel, a shell account for each
428
> user should be set up on the database server. It is acceptable
429
> for the shell program to be bogus (e.g., /bin/false), if the
430
> tunnel is set up in to avoid launching a remote shell.
432
> On each client system the ~/.ssh/config file should contain
433
> an additional line similiar to
435
> LocalForward 5555 psql.example.com:5432
437
"pgsql.example.com" strikes me as a better example hostname (I always
438
think that psql == DB client, postgres/postmaster/pgsql == DB server).
440
> Unfortunately, there are many potential drawbacks to SSL tunnels:
442
I think you mean SSH tunnels.
444
> *) the SSH implementation or protocol may be flawed. Serious problems
445
> are discovered about once every 18- to 24- months.
447
I'd be skeptical whether this weakness if specific to SSH -- there
448
can be security holes in OpenSSL, the SSL protocol, the SSL
449
implementation in PostgreSQL, etc.
451
> *) the database server must provide shell accounts for all users
452
> needing access. This can be a chore to maintain, esp. in if
456
> *) neither the front- or back-end can determine the level of
457
> encryption provided by the SSH tunnel - or even whether an
458
> SSH tunnel is in use. This prevents security-aware clients
459
> from refusing any connection with unacceptly weak encryption.
463
> Finally, the client library can have one or more trusted root
464
> certificates compiled into it. This allows clients to verify
465
> certificates without the need for local copies. To do this,
466
> the source file src/interfaces/libpq/fe-ssl.c must be edited
467
> and the database recompiled.
469
"PostgreSQL" recompiled -- database versus RDBMS can be ambiguous.
471
> Mutual authentication requires that servers and clients each
472
> authenticate to the other. This protects the server from
473
> false clients in addition to protecting the clients from false
476
"false" in this context?
483
Neil Conway <neilconway@rogers.com>
added
removed
src/backend/libpq/README.SSL
