Bug 1202507

+++ This bug was initially created as a clone of Bug #1191181 +++

Description of problem:

I cannot create a secured communication from PgAdmin III locally to my Postgresql 9.2 database instance hosted on OpenShift. This could be a bug or a lack of documentation issue.

This issue follows https://bugzilla.redhat.com/show_bug.cgi?id=1121727 where existing configuration was erased after a reboot. It is now unclear where the certificates should be loaded/created, and whether secured communications can be established. 

A question has also been opened on StackOverflow: http://stackoverflow.com/questions/28431114/where-to-load-certificates-for-secured-postgresql-connections-on-openshift

Version-Release number of selected component (if applicable):

Unknown


How reproducible/Steps to Reproduce:
1. Create an openshift application, together with a postgresql 9.2 instance.
2. rhc ssh to the application.
3. Go to ./app_root/data.
4. Create the required certificates as described here: http://www.postgresql.org/docs/9.2/static/ssl-tcp.html
5. On your local PC, create port forwarding with rhc port-forward
6. Create certificates for PgAdmin III locally
7. Open PgAdmin III and create a connection to the remote database, using the forwarded port number and other required information. Make sure you select 'required' in the SSL tab.
8. PgAdmin III fails to connect to the database.

Actual results:

"Error connecting to the server: server does not support SSL, but SSL was required"

Expected results:

A secured connection and no error message.

Additional info:

-) If a connection using PgAdmin III cannot be set, it cannot be set with a node.js application too.

-) The documentation available here is obsolete (and should probably be removed): https://help.openshift.com/hc/en-us/articles/202535570-How-do-I-change-PostgreSQL-configuration-on-OpenShift-

-) There is no documentation available about OPENSHIFT_POSTGRESQL_SSL_ENABLED. Some documentation explaining how to configure secured communications with Postgresql on Openshift should be made available.

--- Additional comment from JVerstry on 2015-02-10 12:21:56 EST ---

Suggestion:

A solution to this issue might be storing those certificates into the git repository of the application (in a predefined ./postgresql directory for example).

--- Additional comment from JVerstry on 2015-02-11 11:00:01 EST ---

For the records, I have also encountered a:

 Failed to execute: 'control start' for /var/lib/openshift/54db753de0b8cdd7a300008a/postgresql

message when I tried to restart my application or database. After several attempts, I though my database was broken and created a new instance. It failed with the same message.

I finally figured out I still had OPENSHIFT_POSTGRESQL_SSL_ENABLED set to true in the environment. I removed it and the issue disappeared.

I could replicate the issue:

i) Create a node.js application (for example), but without a database.
ii) Set the environment variable OPENSHIFT_POSTGRESQL_SSL_ENABLED to true.
iii) Add a Postgresql 9.2 instance to the application.

--- Additional comment from Maciej Szulik on 2015-02-16 14:42:19 EST ---

The problem you've had is related to bad location of the cert file, it should be $PGDATA/data according to docs [1] you've pointed, which is postgresql/data on your gear. It's definitely not app-root/data, the later is application directory.

Further more the problem you described in Comment #2 was related to that bad location as well. Postgresql server checks for those files during start (see [1]), if SSL is turned on and if it does not find them in $PGDATA/data dir (server.key and server.crt are required) it fails o start, which was the problem you were experiencing every time, even when adding postgresql cartridge afterwards. This is the only thing I can fix here, I've added check for those two files if they exist ssl will be turned on, otherwise it will not, which will lead you to properly running postgresql but without ssl turned on [2]. 

As for your suggestion from Comment #1: unfortunately postgresql, nor any other non-primary cartridge does not have access to git repo, so there's no option by now to do it that way.

[1] http://www.postgresql.org/docs/9.2/static/ssl-tcp.html
[2] https://github.com/openshift/origin-server/pull/6075

--- Additional comment from openshift-github-bot on 2015-02-16 15:12:04 EST ---

Commits pushed to master at https://github.com/openshift/origin-server

https://github.com/openshift/origin-server/commit/c49ffba782b10912ab93650e26df9c39fe3af587
Bug 1191181 - Added checking server certs existence when turning on SSL.

https://github.com/openshift/origin-server/commit/75bb2de1e2f25b604b9b694069ade1eedee6d7b8
Merge pull request #6075 from soltysh/bug1191181

Merged by openshift-bot

--- Additional comment from JVerstry on 2015-02-22 08:33:39 EST ---

This works when the application is created as non-scalable. However, when the application is created as scalable, the $PGDATA structure is not there.

I have created an extra issue: https://bugzilla.redhat.com/show_bug.cgi?id=1194986

--- Additional comment from zhou ying on 2015-02-25 03:10:02 EST ---

Verified on devenv_5449.