Thread: SSL Certificates in Postgres 9.3 and Windows 7

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 also get this error
message in my log file:



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 followed the postgres & openssl documentation for creating the
privkey.pem, server.req, server.key and server.crt files, ie.:


This is the entire pg_hba.conf file:



Also, 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:



Thank you for all comments and suggestions.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/08/2014 08:07 PM, harpagornis wrote:
> 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 also get this error
> message in my log file:


?????

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

Would seem either Postgres has not started or is listening on an
interface different from what you are trying to connect to.

>
>
> I followed the postgres & openssl documentation for creating the
> privkey.pem, server.req, server.key and server.crt files, ie.:
>

?????

>
> This is the entire pg_hba.conf file:



?????

>
>
>
> Also, 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:


?????
>
>
>
> Thank you for all comments and suggestions.

More comments/suggestions will have to wait until the missing pieces are
filled in.

>
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver-4 wrote
>> Thank you for all comments and suggestions.
>
> More comments/suggestions will have to wait until the missing pieces are
> filled in.

I read most of these mailing list emails via Nabble and the pieces you show
as missing are present in what I am reading.  If I go to reply and quote the
original message the missing sections are sour rounded by "raw" tags.

Looking at the official mailing list archive these sections are missing
there.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826246.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Quoting original message to try and show file contents...


harpagornis wrote
> 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 also 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 followed the postgres & openssl documentation for creating the
> privkey.pem, server.req, server.key and server.crt files, ie.:
>
> 1.     openssl genrsa –out privkey.pem 2048
> 2.     openssl req -new -key privkey.pem -out server.req –config
> "D:\openssl\v9.8\openssl.cnf”
> 3.     openssl rsa -in privkey.pem -out server.key openssl req -x509 -in
> server.req -text -key server.key -out server.crt  -config
> "D:\openssl\v9.8\openssl.cnf”
>
>
> This is the entire pg_hba.conf file:
>
>
> # 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
>
> Also, 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
>                     # csvlogs.
>                     # (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'
>
> Thank you for all comments and suggestions.

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826247.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

As suggested, I changed the data folder permissions from Read Only to allow
Read / Write (I was already logged in as Administrator),  but the errors are
the same.

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:



But, 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:


With that, there are no entries in the postgres log file.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826249.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Windows automatically changed the data folder attribute back to Read Only.
The only Windows groups that have full permission are SYSTEM, Administrators
and my administrator /user account.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826251.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 11/09/2014 10:14 AM, David G Johnston wrote:
> Adrian Klaver-4 wrote
>>> Thank you for all comments and suggestions.
>>
>> More comments/suggestions will have to wait until the missing pieces are
>> filled in.
>
> I read most of these mailing list emails via Nabble and the pieces you show
> as missing are present in what I am reading.  If I go to reply and quote the
> original message the missing sections are sour rounded by "raw" tags.

Hmm, is there a way to make Nabble aware of this and fix it?

>
> Looking at the official mailing list archive these sections are missing
> there.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/09/2014 10:17 AM, David G Johnston wrote:
> Quoting original message to try and show file contents...
>
>
> harpagornis wrote
>> 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 also 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.

My suggestion would be to read:

http://www.postgresql.org/docs/9.3/interactive/ssl-tcp.html

The short version:

If you want a client to supply a certificate then you need a valid
ssl_ca_file. If you do not want that to happen do not set cert
clientcert=1 in pg_hba.conf



>
> David J.
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826247.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/09/2014 10:14 AM, David G Johnston wrote:

Adrian Klaver-4 wrote

Thank you for all comments and suggestions.

More comments/suggestions will have to wait until the missing pieces are
filled in.

I read most of these mailing list emails via Nabble and the pieces you show
as missing are present in what I am reading.  If I go to reply and quote the
original message the missing sections are sour rounded by "raw" tags.

Hmm, is there a way to make Nabble aware of this and fix it?

Looking at the official mailing list archive these sections are missing
there.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sun, Nov 9, 2014 at 11:37 PM, David Johnston
<david.g.johnston@gmail.com> wrote:
>
>
> On Sunday, November 9, 2014, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>>
>> On 11/09/2014 10:14 AM, David G Johnston wrote:
>>>
>>> Adrian Klaver-4 wrote
>>>>>
>>>>> Thank you for all comments and suggestions.
>>>>
>>>>
>>>> More comments/suggestions will have to wait until the missing pieces are
>>>> filled in.
>>>
>>>
>>> I read most of these mailing list emails via Nabble and the pieces you
>>> show
>>> as missing are present in what I am reading.  If I go to reply and quote
>>> the
>>> original message the missing sections are sour rounded by "raw" tags.
>>
>>
>> Hmm, is there a way to make Nabble aware of this and fix it?
>>
>>>
>>> Looking at the official mailing list archive these sections are missing
>>> there.
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>
>
> I don't know how the OP sent the original e-mail but since I could read the
> problem areas the question is why other e-mail clients aren't seeing them...

I'd be more interested in how *you* could see them - unless you are
just referring to seeing them on nabble.com?

The original as delivered through the mailinglist is in it's raw form
at http://www.postgresql.org/message-id/raw/1415506067738-5826230.post@n5.nabble.com
- which does not contain those parts. And it wasn't event sent as
multipart, so there is not much of ways to misparse it.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Mon, Nov 10, 2014 at 1:45 AM, David Johnston
<david.g.johnston@gmail.com> wrote:
> Yes, that is what I was referring to.  The Nabble.com website showed them.
>
> http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-td5826230.html

Then it's more logical. Nabble simply did not include that when the
email was posted. I would call that either a bug or at least a
limitation in Nabble - I think only they can figure out what's wrong
and if it's fixable. Until then, we'll have to recommend people just
post plaintext from there (which seems to work) or use a more capable
MUA.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 11/10/2014 11:13 AM, Magnus Hagander wrote:
> Then it's more logical. Nabble simply did not include that when the
> email was posted. I would call that either a bug or at least a
> limitation in Nabble - I think only they can figure out what's wrong
> and if it's fixable. Until then, we'll have to recommend people just
> post plaintext from there (which seems to work) or use a more capable
> MUA.

I suspect its the centos mail list server thats stripping attachments.
I know most of my personal email lists do so.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Mon, Nov 10, 2014 at 8:53 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 11/10/2014 11:13 AM, Magnus Hagander wrote:
>>
>> Then it's more logical. Nabble simply did not include that when the
>> email was posted. I would call that either a bug or at least a
>> limitation in Nabble - I think only they can figure out what's wrong
>> and if it's fixable. Until then, we'll have to recommend people just
>> post plaintext from there (which seems to work) or use a more capable
>> MUA.
>
>
> I suspect its the centos mail list server thats stripping attachments.  I
> know most of my personal email lists do so.

The post was made to the pgsql-general list AFAICT, and I'm pretty
sure it doesn't strip any attachments. I would be hard to deal with
patches if it did...


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

The post was made to the pgsql-general list AFAICT, and I'm pretty
sure it doesn't strip any attachments. I would be hard to deal with
patches if it did...

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Since we are testing...test using Nabble.com and the more->raw option...

"begin quote"
I also get this error message in my log file:

"begin raw"

"end raw"
"end quote"

Following the sentence above should be 4 lines of log file content...

Sent using the Nabble.com interface

Dave




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826396.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.