Bug 1202507 - Cannot create secured communication with Postgresql 9.2 database
Summary: Cannot create secured communication with Postgresql 9.2 database
Keywords:
Status: CLOSED ERRATA
Alias: None
Product: OpenShift Container Platform
Classification: Red Hat
Component: ImageStreams
Version: 2.2.0
Hardware: Unspecified
OS: Unspecified
high
unspecified
Target Milestone: ---
: ---
Assignee: Jason DeTiberus
QA Contact: libra bugs
URL:
Whiteboard:
Depends On: 1191181
Blocks:
TreeView+ depends on / blocked
 
Reported: 2015-03-16 18:52 UTC by Brenton Leanhardt
Modified: 2015-04-06 17:06 UTC (History)
9 users (show)

Fixed In Version: openshift-origin-cartridge-postgresql-1.32.4.1-1.el6op
Doc Type: Bug Fix
Doc Text:
Previously in applications with a PostgreSQL cartridge, the PostgreSQL server would fail to start if the OPENSHIFT_POSTGRESQL_SSL_ENABLED environment variable was set to "true" and the server.key and server.crt files were not located in the $PGDATA/data directory. This bug fix updates the PostgreSQL cartridge to check these file locations during start up if OPENSHIFT_POSTGRESQL_SSL_ENABLED is set to "true". If they exist, SSL is enabled. Otherwise, the PostgreSQL server starts up normally but SSL is not enabled. After applying this update, a cartridge upgrade is required.
Clone Of: 1191181
Environment:
Last Closed: 2015-04-06 17:06:42 UTC
Target Upstream Version:


Attachments (Terms of Use)


Links
System ID Private Priority Status Summary Last Updated
Red Hat Product Errata RHBA-2015:0779 0 normal SHIPPED_LIVE Red Hat OpenShift Enterprise 2.2.5 bug fix and enhancement update 2015-04-06 21:05:45 UTC

Description Brenton Leanhardt 2015-03-16 18:52:07 UTC
+++ 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.

Comment 3 Gaoyun Pei 2015-03-17 09:13:49 UTC
verify this bug with openshift-origin-cartridge-postgresql-1.32.4.1-1.el6op.noarch

1. Create an app with postgresql embedded.

2. Set the SSL env on
   rhc env-set OPENSHIFT_POSTGRESQL_SSL_ENABLED=true -a app1

3. Restart the app. No error happened and postgresql work well with SSL off.

4. Add server.key and server.crt to $PGDATA/data dir and restart the app again.
   No error happened.

5. Port-forward the postgresql to localhost. Connect to the postgres db.
   rhc port-forward app1
   ...
   postgresql 127.0.0.1:5432   =>  127.8.237.2:5432

   [root@broker ~]# psql app1 -h 127.0.0.1 --port 5432 --user xxxxxxx
   Password for user xxxxxxx: 
   psql (8.4.20, server 9.2.8)
   WARNING: psql version 8.4, server version 9.2.
         Some psql features might not work.
   SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
   Type "help" for help.

6. Set the SSL env off and restart the app
   rhc env-set OPENSHIFT_POSTGRESQL_SSL_ENABLED=false -a app1

7. Port-forward the postgresql to localhost. Connect to the postgres db.
   rhc port-forward app1
   ...
   postgresql 127.0.0.1:5432   =>  127.8.237.2:5432

   [root@broker ~]# psql app1 -h 127.0.0.1 --port 5432 --user xxxxxxx
   Password for user xxxxxxx: 
   psql (8.4.20, server 9.2.8)
   WARNING: psql version 8.4, server version 9.2.
         Some psql features might not work.
   Type "help" for help.

   app1=# ^C

Comment 5 errata-xmlrpc 2015-04-06 17:06:42 UTC
Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://rhn.redhat.com/errata/RHBA-2015-0779.html


Note You need to log in before you can comment on or make changes to this bug.