I have added this to backend/libpq/README.SSL to be integrated into our main docs later.
--------------------------------------------------------------------------- Bear Giles wrote: > A second cut at SSL documentation.... > > > > SSL Support in PostgreSQL > ========================= > > Who needs it? > ============= > > The sites that require SSL fall into one (or more) of several broad > categories. > > *) They have insecure networks. > > Examples of insecure networks are anyone in a "corporate hotel," > any network with 802.11b wireless access points (WAP) (in 2002, > this protocol has many well-known security weaknesses and even > 'gold' connections can be broken within 8 hours), or anyone > accessing their database over the internet. > > These sites need a Virtual Private Network (VPN), and either > SSH tunnels or direct SSL connections can be used. > > *) They are storing extremely sensitive information. > > An example of extremely sensitive information is logs from > network intrusion detection systems. This information *must* > be fully encrypted between front- and back-end since an attacker > is presumably sniffing all traffic within the VPN, and if they > learn that you know what they are doing they may attempt to > cover their tracks with a quick 'rm -rf /' and 'dropdb' > > In the extreme case, the contents of the database itself may > be encrypted with either the crypt package (which provides > symmetrical encryption of the records) or the PKIX package > (which provides public-key encryption of the records). > > *) They are storing information which is considered confidential > by custom, law or regulation. > > This includes all records held by your doctor, lawyer, accountant, > etc. In these cases, the motivation for using encryption is not > a conscious evaulation of risk, but the fear of liability for > 'failure to perform due diligence' if encryption is available but > unused and an attacker gains unauthorized access to the harm of > others. > > *) They have 'road warriors.' > > This includes all sites where people need to have direct access > to the database (not through a proxy such as a secure web page) > from changing remote addresses. Client certificates provide a > clean way to grant this access without opening up the database > to the world. > > Who does not need it? > --------------------- > > It's at least as important to know who does not need SSL as it > is to know who does. Sites that do not need SSL fall into several > broad categories. > > *) Access is limited to the Unix socket. > > *) Access is limited to a physically secure network. > > "Physically secure" networks are common in the clusters and > colocation sites - all database traffic is restricted to dedicated > NIC cards and hubs, and all servers and cabling are maintained in > locked cabinets. > > > Using SSH/OpenSSH as a Virtual Private Network (VPN) > ==================================================== > > SSH and OpenSSH can be used to construct a Virtual Private Network > (VPN) to provide confidentiality of PostgreSQL communications. > These tunnels are widely available and fairly well understood, but > do not provide any application-level authentication information. > > To set up a SSH/OpenSSH tunnel, a shell account for each > user should be set up on the database server. It is acceptable > for the shell program to be bogus (e.g., /bin/false), if the > tunnel is set up in to avoid launching a remote shell. > > On each client system the $HOME/.ssh/config file should contain > an additional line similiar to > > LocalForward 5555 psql.example.com:5432 > > (replacing psql.example.com with the name of your database server). > By putting this line in the configuration file, instead of specifying > it on the command line, the tunnel will be created whenever a > connection is made to the remote system. > > The psql(1) client (or any client) should be wrapped with a script > that establishes an SSH tunnel when the program is launched: > > #!/bin/sh > HOST=psql.example.com > IDENTITY=$HOME/.ssh/identity.psql > /usr/bin/ssh -1 -i $IDENTITY -n $HOST 'sleep 60' & \ > /usr/bin/psql -h $HOST -p 5555 $1 > > Alternately, the system could run a daemon that establishes and maintains > the tunnel. This is preferrable when multiple users need to establish > similar tunnels to the same remote site. > > Unfortunately, there are many potential drawbacks to SSL tunnels: > > *) the SSH implementation or protocol may be flawed. Serious problems > are discovered about once every 18- to 24- months. > > *) the systems may be misconfigured by accident. > > *) the database server must provide shell accounts for all users > needing access. This can be a chore to maintain, esp. in if > all other user access should be denied. > > *) neither the front- or back-end can determine the level of > encryption provided by the SSH tunnel - or even whether an > SSH tunnel is in use. This prevents security-aware clients > from refusing any connection with unacceptly weak encryption. > > *) neither the front- or back-end can get any authentication > information pertaining to the SSH tunnel. > > Bottom line: if you just need a VPN, SSH tunnels are a good solution. > But if you explicitly need a secure connection they're inadequate. > > > Direct SSL Support > ================== > > Insecure Channel: ANONYMOUS DH Server > ------------------------------------- > > "ANONYMOUS DH" is the most basic SSL implementation. It does > not require a server certificate, but it is vulnerable to > "man-in-the-middle" attacks. > > The PostgreSQL backend does not support ANONYMOUS DH sessions. > > > Secure Channel: Server Authentication > ------------------------------------- > > Server Authentication requires that the server authenticate itself > to clients (via certificates), but clients can remain anonymous. > This protects clients from "man-in-the-middle" attacks (where a > bogus server either captures all data or provides bogus data), > but does not protect the server from bad data injected by false > clients. > > The community has established a set of criteria for secure > communications: > > *) the server must provide a certificate identifying itself > via its own fully qualified domain name (FDQN) in the > certificate's Common Name (CN) field. > > *) the FQDN in the server certificate must resolve to the > IP address used in the connection. > > *) the certificate must be valid. (The current date must be > no earlier than the 'notBefore' date, and no later than the > 'notAfter' date.) > > *) the server certificate must be signed by an issuer certificate > known to the clients. > > This issuer can be a known public CA (e.g., Verisign), a locally > generated root cert installed with the database client, or the > self-signed server cert installed with the database client. > > Another approach (used by SSH and most web clients) is for the > client to prompt the user whether to accept a new root cert when > it is encountered for the first time. psql(1) does not currently > support this mechanism. > > *) the client *should* check the issuer's Certificate Revocation > List (CRL) to verify that the server's certificate hasn't been > revoked for some reason, but in practice this step is often > skipped. > > *) the server private key must be owned by the database process > and not world-accessible. It is recommended that the server > key be encrypted, but it is not required if necessary for the > operation of the system. (Primarily to allow automatic restarts > after the system is rebooted.) > > The 'mkcert.sh' script can be used to generate and install > suitable certificates > > Finally, the client library can have one or more trusted root > certificates compiled into it. This allows clients to verify > certificates without the need for local copies. To do this, > the source file src/interfaces/libpq/fe-ssl.c must be edited > and the database recompiled. > > Secure Channel: Mutual Authentication > ------------------------------------- > > Mutual authentication requires that servers and clients each > authenticate to the other. This protects the server from > false clients in addition to protecting the clients from false > servers. > > The community has established a set of criteria for client > authentication similar to the list above. > > *) the client must provide a certificate identifying itself. > The certificate's Common Name (CN) field should contain the > client's usual name. > > *) the client certificate must be signed by a certificate known > to the server. > > If a local root cert was used to sign the server's cert, the > client certs can be signed by the issuer. > > *) the certificate must be valid. (The current date must be > no earlier than the 'notBefore' date, and no later than the > 'notAfter' date.) > > *) the server *should* check the issuer's Certificate Revocation > List (CRL) to verify that the clients's certificate hasn't been > revoked for some reason, but in practice this step is often > skipped. > > *) the client's private key must be owned by the client process > and not world-accessible. It is recommended that the client > key be encrypted, but because of technical reasons in the > architecture of the client library this is not yet supported. > > PostgreSQL can generate client certificates via a four-step process. > > 1. The "client.conf" file must be copied from the server. Certificates > can be highly localizable, and this file contains information that > will be needed later. > > The client.conf file is normally installed in /etc/postgresql/root.crt. > The client should also copy the server's root.crt file to > $HOME/.postgresql/root.crt. > > 2. If the user has the OpenSSL applications installed, they can > run pgkeygen.sh. (An equivalent compiled program will be available > in the future.) They should provide a copy of the > $HOME/.postgresql/postgresql.pem file to their DBA. > > 3. The DBA should sign this file the OpenSSL applications: > > $ openssl ca -config root.conf -ss_cert .... > > and return the signed cert (postgresql.crt) to the user. > > 4. The user should install this file in $HOME/.postgresql/postgresql.crt. > > The server will log every time a client certificate has been > used, but there is not yet a mechanism provided for using client > certificates as PostgreSQL authentication at the application level. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly