I am trying to implement SSL certificates with postgres 9.3 locally in Windows 7. In Windows Component Services / Local Services, postrgres is configured to start automatically, with Log On as a local system account.
Using my Windows administrator account, in a command prompt inside my data folder, when I execute postgres -D . , I get the message, "Redirecting logging output to the logging collector service." I get this error message in my log file:
2014-11-09 03:05:13 GMT LOG: client certificates can only be checked if a root certificate store is available
2014-11-09 03:05:13 GMT HINT: Make sure the configuration parameter "ssl_ca_file" is set.
2014-11-09 03:05:13 GMT CONTEXT: line 2 of configuration file "D:/PostgresDat/pg_hba.conf"
2014-11-09 03:05:13 GMT FATAL: could not load pg_hba.conf
When I try to connect in PgAdminIII I get the error message, "Server isn't listening"
What am I doing wrong? Right now, just for development purposes, do I need to have a root certificate? I tried unsuccessfully to create one with makecert but couldn't get the flags and options right.
I think I correctly followed the postgres & openssl documentation for creating the privkey.pem, server.req, server.key and server.crt files, ie.:
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 127.0.0.1/32 cert clientcert=1
hostssl postgres postgres ::1/128 trust
#hostssl all all ::1/128 cert clientcert=1
I am not sure which of those last two lines in the pg_hba.conf file should I be using to require SSL certificates for all postgres accounts? Is it even possible to require a SSL certificate for the postgres account?
This the entire postgresql.conf file:
listen_addresses = '*'
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
# - Security and Authentication -
ssl = on # (change requires restart)
ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
ssl_renegotiation_limit = 512MB # amount of data between renegotiations
ssl_cert_file = 'server.crt' # (change requires restart)
ssl_key_file = 'server.key' # (change requires restart)
#ssl_ca_file = 'root.crt'
password_encryption = on
shared_buffers = 128MB # min 128kB
# ERROR REPORTING AND LOGGING
# - Where to Log -
log_destination = 'stderr'
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# (change requires restart)
log_line_prefix = '%t ' # special values:
# - Locale and Formatting -
datestyle = 'iso, mdy'
timezone = 'US/Central'
lc_messages = 'English_United States.1252' # locale for system error message
lc_monetary = 'English_United States.1252' # locale for monetary formatting
lc_numeric = 'English_United States.1252' # locale for number formatting
lc_time = 'English_United States.1252' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
I also tried changing the data folder attribute from Read Only to allow Read / Write (I was already logged in as Administrator), but the errors are the same. Anyway, Windows automatically changes the data folder attribute back to Read Only. The only Windows groups that have full permission of the data folder are SYSTEM, Administrators and my administrator /user account.
If I remove the SSL-related lines in pg_hba.conf and postgresql.conf, and use the following lines instead in pg_hba.conf, I am able to connect to the database using PgAdminIII:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
However, even then, after doing that, and setting ssl=off in postgresql.conf, when I run the command prompt and execute "postgres -D ." in the data folder, I get these errors in the command prompt console:
could not bind Ipv6 socket. No error. Is another postmaster running on port 5432?
could not bind Ipv4 socket. No. error. Is another postmaster running on port 5432?
Could not create any listen sockets for "*"
Could not create any TCP / IP sockets
With that, there are no entries in the postgres log file.