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

Tue, 21 May 2002 14:27:00 -0600 (MDT)
Bear Giles <bgiles@coyotesong.com>
[HACKERS] 2nd cut at SSL documentation
On Tue, 21 May 2002 14:27:00 -0600 (MDT)
"Bear Giles" <bgiles@coyotesong.com> wrote:
> A second cut at SSL documentation....

I've pointed out some minor things I noticed while reading through.
Yeah, I was bored :-)

> 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,"

What's a corporate hotel?

> *) They have 'road warriors.'

This section title sounds confusingly similar to the 1st item.
Perhaps "They need to authentication clients securely" or something
similar? The need to use client certificates does not apply only to
"road warriors" -- I've seen situations where client-certs are used for
clients to connecting to a server over a LAN.

> *) Access is limited to the Unix socket.

"the" sounds wrong, there's more than just 1 :-)

> *) 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.

Perhaps add a note on the performance hit here?

> Using SSH/OpenSSH as a Virtual Private Network (VPN)

I'm unsure why you're bothering to differentiate between SSH
and OpenSSH.

> SSH and OpenSSH can be used to construct a Virtual Private Network
> (VPN)

No need to include the abbreviation for VPN here, you've explained
the term before.

> to provide confidentiality of PostgreSQL communications.  
> These tunnels are widely available and fairly well understood, but 
> do not provide any application-level authentication information.

You might want to clarify what "application-level authentication
information" means, or else leave out all discussion of drawbacks
until later.

> 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

"pgsql.example.com" strikes me as a better example hostname (I always
think that psql == DB client, postgres/postmaster/pgsql == DB server).

> Unfortunately, there are many potential drawbacks to SSL tunnels:

I think you mean SSH tunnels.

> *) the SSH implementation or protocol may be flawed.  Serious problems
>    are discovered about once every 18- to 24- months.

I'd be skeptical whether this weakness if specific to SSH -- there
can be security holes in OpenSSL, the SSL protocol, the SSL
implementation in PostgreSQL, etc.

> *) the database server must provide shell accounts for all users
>    needing access.  This can be a chore to maintain, esp. in if

Remove the "in".

> *) 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.

Spelling error.

> 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.

"PostgreSQL" recompiled -- database versus RDBMS can be ambiguous.

> 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.

"false" in this context?

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


--ELM1032744392-11773-0_--

Tue, 21 May 2002 19:50:38 -0400
Neil Conway <nconway@klamath.dyndns.org>
Re: [HACKERS] 2nd cut at SSL documentation

> On Tue, 21 May 2002 14:27:00 -0600 (MDT)
> "Bear Giles" <bgiles@coyotesong.com> wrote:
> > A second cut at SSL documentation....

> [snip]

> > 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

I'm coming to this party a bit late in that this is the first I've read the
documentation. I'm also a bit of a newbie when it comes to SSH and I've not
investigated ssh3 at all yet. However, isn't this assuming ssh1 only? I know
ssh2 will fallback to ssh1 compatibility but should there be something about
configuring for the later versions?


-- 
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants


Thu, 23 May 2002 02:50:41 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: 2nd cut at SSL documentation

With Bear disappearing, I am now unsure of our current SSL code.  He
submitted a bunch of patches that seemed to improve our SSL
capabilities, but I am unsure if it is done, and exactly how it was
improved.

Attached is the documentation Bear supplied.  I have removed the
interfaces/ssl directory because I couldn't figure out what to do with
it, but this documentation does help.

The files in interfaces/ssl where:

	client.conf   mkcert.sh*    pgkeygen.sh   root.conf     server.conf

Would someone who understands SSL coding please comment?

---------------------------------------------------------------------------

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, Pennsylvania 19073

Mon, 26 Aug 2002 14:42:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 2nd cut at SSL documentation

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, Pennsylvania 19073

Thu, 3 Oct 2002 13:26:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 2nd cut at SSL documentation