Problem with SSL certificate setup

From: Robert Bruccoleri <bruc(at)stone(dot)congenomics(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Problem with SSL certificate setup
Date: 2012-05-18 21:31:49
Message-ID: 4FB6BFC5.3040007@stone.congenomics.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Dear Colleagues,
I'm trying to setup a PostgreSQL server (9.1.0) that will use SSL
for I/O and for authenticating the clients. I've been able to create
certificates for both server and clients that pass signature
verification using 'openssl verify', but I get invalid certificate
errors from psql when I try to use them. I've run out of ideas of what
to try next, and I hoping that someone on this list can offer suggestions.
I'm running on RHEL 5.4 with a copy of PostgreSQL 9.1.0 that I built
myself. Here's the configure command for PostgreSQL:

./configure --prefix=${POSTGRES_HOME} \
--with-pgport=${PGPORT} \
--enable-cassert \
--disable-debug \
--cache-file=config.cache \
--enable-integer-datetimes \
--enable-thread-safety \
--enable-nls \
--with-pam \
--with-ldap \
--with-openssl \
--with-gssapi \
--with-perl \
--without-python \
--with-libxml \
--with-libxslt

The certificates were built according to a web page from thebrain.ca
<http://www.howtoforge.com/postgresql-ssl-certificates>: Here's the script:
#!/bin/sh -x
# Source: http://www.howtoforge.com/postgresql-ssl-certificates

# Make a key.

openssl genrsa \
-passout pass:mypassword \
-des3 \
-out server.key \
1024

# Remove the passphrase.

openssl rsa \
-passin pass:mypassword \
-in server.key \
-out server.key

chmod 400 server.key

# Create the server certificate.
# -subj is a shortcut to avoid prompting for the info.
# -x509 produces a self signed certificate rather than a certificate
request.

openssl req \
-new \
-key server.key \
-days 3650 \
-out server.crt \
-x509 \
-subj '/C=US/ST=Connecticut/L=Glastonbury/O=Congenomics
LLC/CN=liberty.congen.com/emailAddress=bruc(at)acm(dot)org'

# Since we are self-signing, we use the server certificate as the
trusted root certificate.

cp server.crt root.crt

# On the client, we need three files. For Linux ~/.postgresql/
# directory. root.crt (trusted root certificate) postgresql.crt (client
# certificate) postgresql.key (private key)

# First create the private key postgresql.key for the client machine,
and remove the passphrase.

openssl genrsa \
-passout pass:mypassword \
-des3 \
-out postgresql.key \
1024

openssl rsa \
-in postgresql.key \
-out postgresql.key \
-passin pass:mypassword

# Then create the certificate postgresql.crt. It must be signed by our
# trusted root (which is using the private key file on the server
# machine). Also, the certificate common name (CN) must be set to the
# database user name we'll connect as.

openssl req \
-new \
-key postgresql.key \
-out postgresql.csr \
-subj '/C=US/ST=Connecticut/L=Glastonbury/O=Congenomics
LLC/CN=postgres/emailAddress=bruc(at)acm(dot)org'

openssl x509 \
-req \
-in postgresql.csr \
-CA root.crt \
-CAkey server.key \
-out postgresql.crt \
-CAcreateserial

openssl verify -CAfile root.crt postgresql.crt
openssl verify -CAfile root.crt server.crt

# Copy the trusted root certificate root.crt from the server machine
# to the client machine (for Windows pgadmin %appdata%\postgresql\ or
# for Linux pgadmin ~/.postgresql/). Change the file permission of
# postgresql.key to restrict access to just you.

Note that the script does a openssl verify. The files were copied into
their places using this script:

#!/bin/sh

d=/pg/postgresql-9.1.0/data

cp server.crt server.key root.crt $d
chmod 600 $d/server.key

cp root.crt postgresql.{crt,key} ~/.postgresql
chmod 600 ~/.postgresql/postgresql.key

In my pg_hba.conf file, I have these entries:

# IPv4 local connections:
hostssl all all 127.0.0.1/32 cert
hostssl all all liberty.congen.com cert

BTW, my system, liberty.congen.com has an IP address of 127.0.0.1 in the
/etc/hosts file.

Finally, the relevent SSL entries in $PGDATA/postgresql.conf are

ssl = on # (change requires restart)
ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL
ciphers
# (change requires restart)
ssl_renegotiation_limit = 512MB # amount of data between renegotiations

When I attempt a 'psql -l' command from the Postgres superuser account,
I get this:

psql: SSL error: certificate verify failed
FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres",
database "postgres", SSL off

I turned on as much debugging logging as I could, and the postmaster.log
file has these entries around the connection:

LOG: 00000: database system is ready to accept connections
LOCATION: reaper, postmaster.c:2435
LOG: 00000: connection received: host=127.0.0.1 port=35224
LOCATION: BackendInitialize, postmaster.c:3457
DEBUG: 00000: forked new backend, pid=29591 socket=7
LOCATION: BackendStartup, postmaster.c:3307
LOG: 08P01: could not accept SSL connection: tlsv1 alert unknown ca
LOCATION: open_server_SSL, be-secure.c:947
DEBUG: 00000: shmem_exit(0): 0 callbacks to make
LOCATION: shmem_exit, ipc.c:211
DEBUG: 00000: proc_exit(0): 1 callbacks to make
LOCATION: proc_exit_prepare, ipc.c:183
DEBUG: 00000: exit(0)
LOCATION: proc_exit, ipc.c:135
DEBUG: 00000: shmem_exit(-1): 0 callbacks to make
LOCATION: shmem_exit, ipc.c:211
DEBUG: 00000: proc_exit(-1): 0 callbacks to make
LOCATION: proc_exit_prepare, ipc.c:183
DEBUG: 00000: forked new backend, pid=29592 socket=7
LOCATION: BackendStartup, postmaster.c:3307
LOG: 00000: connection received: host=127.0.0.1 port=35225
LOCATION: BackendInitialize, postmaster.c:3457
DEBUG: 00000: postgres child[29592]: starting with (
LOCATION: BackendRun, postmaster.c:3587
DEBUG: 00000: postgres
LOCATION: BackendRun, postmaster.c:3590
DEBUG: 00000: postgres
LOCATION: BackendRun, postmaster.c:3590
DEBUG: 00000: )
LOCATION: BackendRun, postmaster.c:3592
DEBUG: 00000: InitPostgres
LOCATION: InitPostgres, postinit.c:472
DEBUG: 00000: my backend ID is 2
LOCATION: SharedInvalBackendInit, sinvaladt.c:326
DEBUG: 00000: reaping dead processes
LOCATION: reaper, postmaster.c:2353
DEBUG: 00000: server process (PID 29591) exited with exit code 0
LOCATION: LogChildExit, postmaster.c:2861

So, the big question in my mind is where is the system getting its root
certificates from? I've provided the same file in both possible places,
and openssl says the server and client certificates are OK.

Any suggestions as to what to try next would be most welcome.

Thanks. --Bob

Attachment Content-Type Size
bruc.vcf text/x-vcard 181 bytes

Browse pgsql-admin by date

  From Date Subject
Next Message Robert Bruccoleri 2012-05-18 22:29:43 Problem with SSL certificate setup; please disregard -- solution found.
Previous Message Scott Marlowe 2012-05-18 17:01:06 Re: Transaction ID overrun problem on greenplum