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.
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).
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.
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
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
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
Verified on devenv_5449.