Re: 2nd cut at SSL documentation - Mailing list pgsql-hackers

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 pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073