Connect PostgreSQL Database with DBeaverPostgreSQL with DBeaver. Connect PostgreSQL Database with DBeaverPostgreSQL with DBeaver. About DBeaver DBeaver is a universal database management tool for everyone who needs to work with data in a professional way. With DBeaver you are able to manipulate with your data like in a regular spreadsheet, create analytical reports based on records from different data storages, export information in an appropriate format.
Unlike psql and other libpq based programs the JDBC driver does server certificate validation by default. This means that when establishing a SSL connection the JDBC driver will validate the server's identity preventing 'man in the middle' attacks. It does this by checking that the server certificate is signed by a trusted authority. If you have a certificate signed by a global certificate authority (CA), there is nothing further to do because Java comes with copies of the most common CA's certificates. If you are dealing with a self-signed certificate though, you need to make this available to the Java client to enable it to validate the server's certificate.
Note
Only the JDBC 3 driver supports SSL. The 1.4 JDK was the first version to come bundled with SSL support. Previous JDK versions that wanted to use SSL could make use of the additional JSSE library, but it does not support the full range of features utilized by the PostgreSQL™ JDBC driver.
To make the server certificate available to Java, the first step is to convert it to a form Java understands. From here the easiest thing to do is import this certificate into Java's system truststore. The default password for the cacerts keystore is changeit. The alias to postgesql is not important and you may select any name you desire.
If you do not have access to the system cacerts truststore you can create your own truststore. When starting your Java application you must specify this keystore and password to use. In the event of problems extra debugging information is available by adding -Djavax.net.debug=ssl to your command line.
To instruct the JDBC driver to try and establish a SSL connection you must add the connection URL parameter ssl=true.
In some situations it may not be possible to configure your Java environment to make the server certificate available, for example in an applet. For a large scale deployment it would be best to get a certificate signed by recognized certificate authority, but that is not always an option. The JDBC driver provides an option to establish a SSL connection without doing any validation, but please understand the risk involved before enabling this option.
A non-validating connection is established via a custom SSLSocketFactory class that is provided with the driver. Setting the connection URL parameter sslfactory=org.postgresql.ssl.NonValidatingFactory will turn off all SSL validation.
DBeaver is an awesome SQL client and database management tool.
It comes with drivers for the most popular databases, including MySQL, PostgreSQL, SQLite and many others, meaning you can learn one tool and use it across projects built on different technology stacks. How awesome is that?
Bellow are the steps you can use to connect your DBeaver client to a remote database server via SSH.
Note that you only need to do these steps once. The connection configurations will be saved and you can re-use them by right-click + connect.
This guide is created using DBeaver version 6.3.
Method 1: DBeaver via SSH with plain password
1. Create a new connection
Create a new connection by right clicking in your Database Navigator area.
2. Fill SSH details
Fill details under the SSH tab, and click Test tunnel configuration.
You should see a success message if the details were correct.
3. Fill database’s details
Postgresql Client Windows Dbeaver
Now that DBeaver can connect via SSH, let’s go back to the General tab and fill in the database credentials:
Clicking Test Connection, you should see a success message. Click Finish.
Method 2: DBeaver with encrypted openssh private key file
If your server requires an openssh private key to login, follow the bellow steps:
1. Add SSHJ to DBeaver
You can do it by opening Help/Install New Software… and typing the following link: https://dbeaver.io/update/sshj/latest/
2. Connect
Use the same steps as Method 1, but adjust as follows: Unturned - permanent gold upgrade download.
- On the SSH details menu (step 2), provide the password for your encrypted private key file.
Select SSHJ, under Implementation.
- Change Authentication Method to Public Key.
Select your Private Key
- Click Test tunnel configuration and Finish.
Resources
Dbeaver Postgresql Client Download
If you need additional help, this link may be very useful.