How to connect to database through SSH using DataGrip

Question

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.

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

Differentiable

Seems like there is an issue in DataGrip tracker. The second port is chosen randomly now: youtrack.jetbrains.com/issue/DBE-6668 – Jodee

What second port? – Differentiable

I meant forward port. – Jodee

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 – Polygynist

Answer 1

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):

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

Differentiable

doesn't seeem like the solution, you screenshot had different hosts, idk why the name would matter. – Midsummer

@Midsummer It's the same host listed in the "SSH/SSL" tab. You're looking at the "General" tab. – Differentiable

Answer 2

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'

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.

Now SSH is configured for DataGrip.

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.

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

Kirk

Thanks for that simple solution – Doublebank

How can I add the unix socket connection to SSH Tunnel ? – Selfrealization

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? – Knawel

Answer 3

Addition for Kubernetes remote database access

If you like to connect to a remote database that runs in a cluster you have to configure the host ip address differently (not localhost).

Kubernetes Database setup - Example Keycloak k8

Simple example setup of keycloak running in a kubernetes cluster.

• Database resource of type StatefulSet, since the db shall be persisted.
• Service resource, used by the StatefulSet (link).
• Keycloak instance (app), runs the actual keycloak application (Quarkus App)

Diagram of components inside cluster

+--------------------------+
|        Kubernetes        |
|       Cluster Nodes      |
|                          |
|  +--------------------+  |
|  | StatefulSet        |  |
|  | (Postgres Database)|  |
|  |                    |  |
|  | +---------------+  |  |
|  | | Pod (DB)      |  |  |
|  | +---------------+  |  |
|  +--------------------+  |
|                          |
|  +--------------------+  |
|  | Deployment         |  |
|  | (Keycloak App)     |  |
|  |                    |  |
|  | +---------------+  |  |
|  | | Pod (App)     |  |  |
|  | +---------------+  |  |
|  +--------------------+  |
|                          |
|  +--------------------+  |
|  | Service            |  |
|  | (keycloak-database)|  |
|  |                    |  |
|  | +---------------+  |  |
|  | | ClusterIP:    |  |  |
|  | | 10.152.183.65 |  |  |
|  | +---------------+  |  |
|  +--------------------+  |
|                          |
+--------------------------+

Traffic / comunication flow

Brief summary of the communication between keycloak and database with the service.

1. Kecloak (app): runs inside a pod in the cluster
2. Service (discovery): the service used to provide a stable IP address and DNS name for accesing pods, in this example keycloak uses it to gain access to the database
3. Database (stateful set): the actual instance of the database also running in a pod.

Communicaiton diagram - keycloak inside a cluster

+--------------------------+
|      Kubernetes Cluster  |
|        (Nodes)           |
|                          |
| +----------------------+ |
| | Keycloak Deployment  | |
| | +------------------+ | |
| | | Keycloak Pod     | | |
| | | (App)            | | |
| | +------------------+ | |
| +----------------------+ |
|          |               |
|          | Service Discovery
|          v               |
| +----------------------+ |
| | keycloak-database    | |
| | Service              | |
| | ClusterIP:           | |
| | 10.152.183.65:5432   | |
| +----------------------+ |
|          |               |
|          v               |
| +----------------------+ |
| | StatefulSet (DB)     | |
| | +------------------+ | |
| | | PostgreSQL Pod   | | |
| | | (DB)             | | |
| | +------------------+ | |
| +----------------------+ |
|                          |
+--------------------------+

Example communication summary:

1. Keycloak:

• pod runs applicaiton inside pod.
• Keycloak wants to connect to the postgres db to do stuff.

2. Service discovery:

• Kubernetes services provide stable IP for accessing pods.
• The keycloak-database service acts as an intermediary that directs traffic to the pgsql database pod.

3. Service to pod communication:

• When the keycloak pod needs to interact with the database it sends the request to the keycloak-database service.
• The keycloak-database service has a ClusterIP and is configured to forward requests on port 5432 to the pgsql database pod.

4. Service selector:

• The keycloak-database service uses a selector to match labels on the database pod. Ensures the service knows where to forward the traffic (to the correct pod only).

5. Database pod receives request:

• meaning the service forwards the request from keycloak to the statefulSet database pod and returns the reponse the same way back to the caller.

In this setup the Service: keycloak-database is your entrypoint for communicating to your database. In the example of @payne the MySQL Database runs on his server on localhost:.

Knowing your stuff

This is just an example setup as the important part here is merely the fact that knowing what and where to type stuff is explained now easier on an example.

Where you host your database determines the value of the host in DataGrip So only if your postgres database runs on your server directly you also would use localhost: in your configuration. But this is NOT the case when you do Cloud / Cluster db hosting.

How does the tunneling then work in a client to cluster scenario?

Remark: It doesn´t matter what tool you use, since the overall workflow is the same.

1. Establish a ssh tunnel:

to your server, where your database is running on (in this case the cluster the db is deployed to)

• DataGrip gives you this option as @payne showcased

2. Configure database connection in DataGrip

Host: needs to be Cluster Ip of your service that is used to communicate to your cluster database Port: is the actual port of the service, not the port of the database pod. The service defines whcih interal port is mapped to the service port. Username + password + database name: your actual credentials

Client to cluster database communication

+--------------------------+
|   Local Machine (Client) |
|                          |
| +--------------------+   |
| | DataGrip           |   |
| |                    |   |
| | Host: localhost    |   |
| | Port: 5432         |   |
| | Username:          |   |
| | Password:          |   |
| | Database: keycloak |   |
| +--------------------+   |
|         |                |
|         | SSH Tunnel     |
|         |                |
| +--------------------+   |
| | ssh command:       |   |
| | ssh -L 5432:       |   |
| | 10.152.183.65:5432 |   |
| | your-username@     |   |
| | your-ssh-server.com|   |
| +--------------------+   |
|         |                |
+---------|----------------+
          |
+---------|----------------+
|   Remote Server          |
| (Kubernetes Cluster)     |
|                          |
| +--------------------+   |
| | Kubernetes Node    |   |
| |                    |   |
| | +--------------+   |   |
| | | StatefulSet  |   |   |
| | | (Postgres DB)|   |   |
| | |              |   |   |
| | +--------------+   |   |
| |  ClusterIP:    |   |   |
| |  10.152.183.65 |   |   |
| +--------------------+   |
+--------------------------+

Putting it all together

---

TL;DR How to connect to a cluster (remote) database with DataGrip

General settings

Tunnel settings

Simply leave the Local port blank, so DataGrip can assign a free port itself for the tunnel. Or if you want a specific port choose one.

Simply enter your clusters host ip or domain name here, choose authentication type etc. Is the actual IP of the server that runs the cluster, or lets you enter the cluster from outside

Summary

Since this little detail was missing on how to connect to a remote cluster based database, here you go. This might be very obvious for seasoned folks, but can be usefull for beginners in cloud tech, since it´s way more comfortable to have access to your remote databases with a more suffisticated tool than the terminal.

Rebus