PostgreSQL on Ubuntu – external database connection

External connection to PostgreSQLwhich is configured on the server Ubuntu via PhpStorm, PyCharm or DataGrip programs.

This article assumes that you have an initial server setup on Ubuntu. This means, among other things, that you have disabled password login to the server and configured to login to the server via the SSH protocol.

Connecting to PostgreSQL in PhpStorm, PyCharm or DataGrip

Configuring SSH

Open the database connection tab, select PostgreSQL and go to the tab SSH / SSL:

We put a tick next to the item Use SSH tunnel and go to the button to further configuration:

Here:
Host – enter the ip address of your server
User name – Linux system user
Authentication type – choose OpenSSH config and authentication agent

Next, click on the button Test Connection… Must see the inscription Successfully connected!

After successfully connecting to the server via SSH, we will configure the connection to the database itself.

Database connection

Open the database connection tab and select PostgreSQL:

Host – enter the ip address of your server
User – user in the database
Password – the password of this user in the database
Database – database name
Url – will be formed by itself

How to create a database, user and password to it is in the PostgreSQL article.

In the basic PostgreSQL configuration, the external connection is closed. You will receive errors when trying to connect. Let’s analyze each of them and open external access.

postgresql.conf

To fix the error:

[08001] The connection attempt failed.
java.net.SocketException: Connection reset.

Let’s fix the configuration in the file postgresql.conf

The remote server is now reporting that the connection was not recognized. The most likely reason is that access is denied.

Let’s find the postgresql.conf file:

find / -name postgresql.conf 2> /dev/null

Shows one or more files. Open the following file (instead of version 12, you may have a different one):

sudo nano /etc/postgresql/12/main/postgresql.conf

We find the line:

#listen_addresses = ‘localhost’ # what IP address (es) to listen on;

We change it to this:

listen_addresses = ‘*’ # what IP address (es) to listen on;

Restarting PostgreSQL:

sudo service postgresql restart

pg_hba.conf

To fix the following error:

The specified database user / password combination is rejected: [28000] FATAL: no pg_hba.conf entry for host “111.111.111.111”, user “ploshadka”, database “ploshadka”, SSL on

Let’s fix the configuration in the file pg_hba.conf

Let’s find the configuration file:

find / -name pg_hba.conf 2> /dev/null

Let’s open it:

sudo nano /etc/postgresql/12/main/pg_hba.conf

We change this line:

# host all all 127.0.0.1/32 md5

On this one:

host all all 0.0.0.0/0 md5

Restarting PostgreSQL:

sudo service postgresql restart

After all the settings, click Test Connection… The following message should appear:

Similar Posts

Leave a Reply