0

I have installed OpenGeo Suite 4.0.1 on my Windows 7 OS and created a spatially database. Now i want to connect to my database remotely but then it seems like i have to generate a self-signed key. i read about SSL connection on http://suite.opengeo.org/opengeo-docs/dataadmin/pgDBAdmin/security.html but i have no idea where to write the codes for generating a self-signed key since my psql is not active anymore because i stoped the postgres service. If i write it in the command line it says openssl is not recognised as an internal or external command or batch file. Where can i write those codes to generate a self-signed key.

  • Are you sure you are unable to connect to your database remotely because of the problem described above OR because the postgresql in opengeo suite can be accessed just from localhost in default? – Tamas Kosa Jun 26 '15 at 8:25
1

To connect to your database remotely, you do not need SSL or self-signed key. All you need is to enable it in postgresql.conf pg_hba.conf:

In postgresql.conf, find the line that read:

listen_addresses='localhost'

and change it to:

listen_addresses='*'

[[Technical details]]

In pg_hba.conf: Add the following line at the end of the file to allow connection from all users to all databases in the server from all IP addresses with md5 method (username/password)

host all all 0.0.0.0/0 md5

After connected, all users are still subjected to restrictions from GRANT/REVOKE privileges in the database(s).
[[Technical details]]

The problem is your data (including username and password) can be eavesdropped by some one on the network between you and the database.


If you want to encrypt your data so that they can not be eavesdropped, then, in addition to the steps above, you'll have to buy a certificate from a certificate authority or generate one yourself.

The steps to generate your own certificate are well described in the part about SSL connection you mentioned.
One problem is: because you're on a Windows machine, OpenSSL is not installed by default, so you'll have to install it yourself, and after that, follow the steps mentioned - just google install openssl on windows.
I tested those steps successfully in Windows command line (cmd) after installing OpenSSL in Cygwin.

If you generate your own certificate, you are still vulnerable to "man in the middle" attack the first time you connect to the database. To protect against it, there are no other way than buying certificate from a trusted authority and create your connection in PgAdmin or Psql with ssl-mode verify-ca or verify-full

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.