How to easily and safely connect to postgres or mysql remotely?

Question

I would like to know how can you connect to postgresql in these conditions:

allow you to access them from any location (do IP filtering)
safe connection (no risk on having your password captured)
easy to setup, preferably having to configure only the server for that.

I know that the recommended approach is to used SSH port forwarding, but this requires you to start the port forwarding before trying to connect to these databases.

What is the easiest method to acquire a good enough security without having to do complex setup on the client.

Is there a way to auto enable the port forwarding stuff on demand?

Why the MySQL reference/tag? There doesn't seem to be anything about MySQL in your question. — liquorvicar, Apr 15 '12 at 11:31
It has to do, as long mysql can be configured to access secured connections. — sorin, Apr 15 '12 at 12:09

score 2 · Answer 1 · answered Apr 15 '12 at 14:54

For PostgreSQL you would start by making sure you are using an SSL-enabled build. (I think that is the default for most installers.)

Then you would need to allow the server to accept remote connections by setting listen_addresses (which specifies which IP addresses the server will listen on): http://www.postgresql.org/docs/9.1/interactive/runtime-config-connection.html

The pg_hba.conf file allows you to specify which users can connect to which databases from which IP addresses using which authentication methods. There are a lot of authentication methods from which to choose: http://www.postgresql.org/docs/9.1/interactive/client-authentication.html

Regarding what needs to be done on the client side, the details will depend on what connector you are using from which environment; as an example, the PostgreSQL JDBC driver uses an SSL connection by default if available. To tell the JDBC driver not to accept a connection unless it can use SSL, you set a JDBC connection property: ssl=true. http://jdbc.postgresql.org/documentation/head/ssl-client.html

Sorry, but I don't know how MySQL manages any of this.

score 1 · Answer 2 · edited May 23 '17 at 12:07

I am myself trying to find the answer for Postgre, but here is what you can do for MySQL.

First, you need to enable remote access to your database. You can create a user with remote access ability as follows.

GRANT ALL ON *.* to user@address IDENTIFIED BY 'password'; 
flush privileges;

More details here.

To add security to this, you can add a 'REQUIRE SSL' to the GRANT command as follows

GRANT ALL ON *.* to user@address IDENTIFIED BY 'password' REQUIRE SSL;

All this needs to be done on the server side. On the client, you just need to provide the required certificates that it will need to connect.

For details on creating certificates, the MySQL site has a step by step guide here

How to easily and safely connect to postgres or mysql remotely?

2 Answers2