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: