|
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: 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@xxxxxxx' # 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@xxxxxxx' 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 |
begin:vcard fn:Robert Bruccoleri n:Bruccoleri;Robert org:Audacious Energy, LLC and Congenomics, LLC adr:;;;;;;USA email;internet:bruc@xxxxxxx title:President version:2.1 end:vcard
-- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
