Connecting IntelliJ (Windows environment) to PostgreSQL (Ubuntu for Windows: WSL 2 environment)

How to connect them?

Create user and DB for PostgreSQL

If the user you want to use has not been configured yet:

# add user in ubuntu
sudo adduser myuser
# now add a role in postgres
sudo -u postgres createuser myuser
# now create a db for user 
sudo -u postgres createdb -O myuser mydb

To connect to a database mydb as user myuser: sudo -u myuser psql mydb

Get network addresses

In Ubuntu:

$ ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.27.36.133  netmask 255.255.240.0  broadcast 172.27.47.255
        inet6 fe80::215:5dff:feea:24da  prefixlen 64  scopeid 0x20<link>
        ether 00:15:5d:ea:24:da  txqueuelen 1000  (Ethernet)
        RX packets 2734  bytes 281375 (281.3 KB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 191  bytes 10810 (10.8 KB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 911  bytes 307501 (307.5 KB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 911  bytes 307501 (307.5 KB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

We note the address 172.27.36.133, this will be the address that we will connect to in IntelliJ.

$ cat /etc/resolv.conf
# This file was automatically generated by WSL. To stop automatic generation of this file, add the following entry to /etc/wsl.conf:
# [network]
# generateResolvConf = false
nameserver 172.27.32.1

The address of the server for the VM is 172.27.32.1. Note this address too.

Locate the files configurations locations

$ sudo -u postgres psql
postgres=# show hba_file;
              hba_file
-------------------------------------
 /etc/postgresql/12/main/pg_hba.conf
(1 row)
postgres=# show config_file;
               config_file
-----------------------------------------
 /etc/postgresql/12/main/postgresql.conf
(1 row)

To get the port of the database (if not 5432)

$ sudo -u postgres psql
postgres=# select * from pg_settings where name='port'; 

I got 5432.

Add the following configurations

$ sudo nano /etc/postgresql/12/main/pg_hba.conf
# add a line 
host	all		all		172.27.32.1/0		md5

$ sudo nano /etc/postgresql/12/main/postgresql.conf
# change listen_addresses to 
listen_addresses = '*'