SSL Certificates in Postgres 9.3 and Windows 7

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 9 November 2014 05:07, harpagornis <shenlong@runbox.com> 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.

Postgres on Windows will not start if it's running on account which belongs
to Administrators or PowerUsers groups.
LocalSystem belongs to Administrators.

On most cases you would like to use for example NetworkService account,
however you need to setup proper permissions on cluster data directory
(full access), postgres installation directory (read+execute mostly), and
in some cases also for root drive of cluster data directory (read access).

Best regards,
Krystian Bigaj

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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 /messages/by-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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

David J.

On Sunday, November 9, 2014, Magnus Hagander <magnus@hagander.net> wrote:

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/10/2014 11:56 AM, Magnus Hagander wrote:

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

this is a test, purely a test I'm attaching a couple small small
pictures in jpg format.

The USB stick picture is inline, above.

The picture of the binoculars on the patio is an actual attachment.

apologies for crowding your email boxes with noise.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general