9

To connect and set up properly with a command line, I "simply" have to do:

  1. ssh <MY_USERNAME>@<URL> -L 1139:arcade:139
  2. Enter <MY_PASSWORD>
  3. psql -h postgres
  4. \c ift2935
  5. set search_path to inscriptions_devoir;

However, I've been tempted to use a more complex tool than the command line to explore the database and try out different SQL commands. Being a fan of JetBrains, I've decided to try out DataGrip.

I have indeed read the actual help page but it doesn't provide me with the information I need.

I lack the understanding of the jargon to know the correspondance, in terms, between the different lines I had to enter and the actual field-names that I am supposed to enter in DataGrip.

DataGrip connection

My question is quite simple: what and where do I type the stuff ?

payne
  • 4,691
  • 8
  • 37
  • 85
  • 1
    Seems like there is an issue in DataGrip tracker. The second port is chosen randomly now: https://youtrack.jetbrains.com/issue/DBE-6668 – moscas Feb 20 '19 at 10:13
  • What second port? – payne Feb 20 '19 at 13:58
  • I meant forward port. – moscas Feb 21 '19 at 09:30
  • After having everything configured correctly I was still getting an incorrect password message. Later, I realized that I need to set the database user password. ```alter user postgres password 'postgres'``` Things magically worked after that – taari Nov 15 '22 at 12:33

2 Answers2

20

I faced this issue too. So want to explain others how i managed to do it. lets say for example your remote server ip address is 192.128.11.120. Most of the time while tunneling the localhost/127.0.0.1 is used as the host ip address as in such scenarios the localhost ip is set as the bind address in the mysql config.

Here is the step by step guide on setup to connect to MySQL using SSH tunnel.

1 - Go to SSH/SSL tab and select 'Use SSH tunnel'

enter image description here

2 - Click and do shift+enter or click three dots [...] to open SSH Configurations. From there click + sign to add a new configurations. Fill the form with your remote server ssh configurations.

  • If you are using just one ssh key pair select 'OpenSSH config and authentication agent' as Authentication type.ssh key pair select following
  • If you are using more than one key pair or want to use specific file select 'Key pair (OpenSSH or Putty)' as the Authentication type and select the private key file you used to connect to the remote server as the Private key file.

After that click 'Test Connection' to test whether SSH configuration is correct. If it works Click 'Apply' and 'OK' to save it.

enter image description here

Now SSH is configured for DataGrip.

enter image description here

3 - Go to 'General' Tab to add the MySQL related entries. One thing to keep in mind is that most of the time the bind address is set to localhost/127.0.0.1 in MySql to make it secure on the remote servers to enable only ssh connections. In such cases the remote ip(in this case 192.128.11.120) wont work. So, use 127.0.0.1. Click test connection to check whether the MySQL connection works over SSH.

enter image description here

4 - If it works, Click 'Apply' and 'OK' to save it.

aimme
  • 6,385
  • 7
  • 48
  • 65
  • 1
    Thanks for that simple solution – Ismail Sahin Jun 24 '21 at 22:55
  • How can I add the unix socket connection to SSH Tunnel ? – Chris Sum Jul 28 '21 at 05:31
  • My problem with the setup is that or mysql users are set to localhost. And even if I try the above with localhost as host, it seems to take 127.0.0.1 on the server side. Which MySQL treats differently than localhost (apparently localhost uses a socket conn. and 127.0.0.1 a tcp/ip conn.). Any solution to this, except changing users on mysql side? – Bachi Jul 15 '22 at 09:26
7

Figured it out!

The SSH stuff was properly set up. The Name field at the very top was wrong.

For a MySQL database called <DB_NAME>, this would be the input (first image is for the SSH infos, the second one is for the General infos):

SSH inputs

General inputs

(Disregard the fact that the initial question was about PostGreSQL. The general idea stays the same.)

payne
  • 4,691
  • 8
  • 37
  • 85
  • 1
    doesn't seeem like the solution, you screenshot had different hosts, idk why the name would matter. – exts Apr 07 '20 at 18:54
  • @exts It's the same host listed in the "SSH/SSL" tab. You're looking at the "General" tab. – payne Apr 09 '20 at 15:24