Connecting to a Postgres database, hosted within a Windows Subsystem for Linux, from a separate device

Question

I have two devices:

• HOST: a Windows device, on which I have installed a Windows Subsystem for Linux.
• CLIENT: a Linux device.

I have then taken the following steps.

1. I have installed Postgres within the HOST subsystem.
2. I have created a database on HOST, with some test data.
3. I have made this database remotely accessible with a script. (See below.)
4. I have installed PG Admin 4 on CLIENT.
5. I have attempted to connect to the database on CLIENT, using PG Admin and the usual IP address of the HOST device. This gives me the error: failed: FATAL: role "postgis_user" does not exist. (The string postgis_user is the username corresponding to the database in question.)
6. I have also tried connecting with some other IP addresses, such as the one which HOST spits out in response to the command wsl hostname -i. For each address I've tried, PG Admin gives me a timeout error.

Some other things to note:

• If I try the steps 1-5, but with two Linux devices, I can connect.
• I have made sure that the PostgreSQL service is up on the HOST subsystem by running sudo service postgresql start.
• I'm running WSL2, and my Linux distribution is Ubuntu (on both the subsystem and on CLIENT).

Any ideas as to where I'm going wrong?

---

The script I used for making my database remotely accessible:

PG_HBA_CONF_STRING_TO_ADD="host all all 0.0.0.0/0 md5"
PG_CONF_STRING_TO_ADD="# Allow remote connections.\n listen_addresses = '*'"

path_to_pg_hba_conf="/etc/postgresql/$pgres_version/main/pg_hba.conf"
path_to_pg_conf="/etc/postgresql/$pgres_version/main/postgresql.conf"

if ! sudo grep -q "$PG_HBA_CONF_STRING_TO_ADD" $path_to_pg_hba_conf; then
    echo $PG_HBA_CONF_STRING_TO_ADD | sudo tee -a $path_to_pg_hba_conf
fi
if ! sudo grep -q "$PG_CONF_STRING_TO_ADD" $path_to_pg_conf; then
    echo $PG_CONF_STRING_TO_ADD | sudo tee -a $path_to_pg_conf
fi

Answer 1

While I can't speak to any additional Postgres configuration needed, the core issue is that WSL2 runs under a virtual network inside Hyper-V that is NAT'd behind the Windows host itself. By default, other devices on the network cannot see WSL2.

This is most easily seen by simply running the following in WSL:

python3 -m http.server

You will be able to access http://localhost:8000 from your Windows browser, but you won't be able to access it from the Linux client machine.

There are multiple solutions for this, but the easiest is typically just to run under WSL1. As far as I know, Postgres will run just fine in WSL1, and the networking configuration there does allow access from other devices on the network, since the WSL1 network adapter is not virtualized (it uses syscall translation to provide direct access to the Windows host network).

If you do need or prefer to run as WSL2, other alternatives can be found in detail in this question. I'm of course partial to my ssh reverse tunnel answer there (that I just posted today). But the netsh forwarding option works as well.

It looks like the Postgres port that would need to be forwarded is 5432.

Again, there may be more Postgres configuration that is needed. I know from experience that MySQL requires a different user configuration for "remote" users vs. local, but it's been too long since I used Postgres to recall if this is the case there. Regardless, I'm hoping once you get past the "network" issue, the rest you'll be able to handle.