Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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: bruc.vcf
Description: text/x-vcard (181 bytes)

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group