How to connect to windows postgres Database from WSL

WSL2 assigns IP address to the Windows host dynamically and the IP addresses can change without even rebooting Windows (see Notes below). So to reliably connect we'll need to:

1. Allow Windows and Postgres to accept connections from the WSL2 IP address range (not allowed by default)
2. From WSL2, determine the Windows/Postgresql host's IP address (which is dynamic) when connecting via psql. We'll make this convenient via .bashrc and alias.

Unfortunately I couldn't find the exact specification for the WSL2 IP address range. From several tests/reboots it appears that WSL2 is assigning IP addresses primarily in range of 172.*.*.* but I have occasionally been assigned 192.*.*.* so we'll use these when configuring the firewall and Postgres.

Add Windows Firewall Inbound Port Rule for WSL2 IP Addresses:

1. Open Windows Defender Firewall with Advanced Security
2. Click New Rule...
3. Select Port for rule type
4. Select TCP and for Specific local ports enter 5432
5. Select Allow the connection. Connecting from WSL2 won't be secure so don't select the secure option
6. Select at least Public. Can select Domain and Private as well. I could only connect if Public was selected
7. Name the rule e.g. Postgres - connect from WSL2 and create it
8. Right click newly created rule and select Properties then click on the Scope tab
9. Under Remote IP address, select These IP addresses then click Add... and enter range 172.0.0.1 to 172.254.254.254
10. Repeat step 9 for IP address range 192.0.0.1 to 192.254.254.254
11. Click Apply then OK
12. Make sure rule is enabled

Configure Postgres to Accept Connections from WSL2 IP Addresses

Assuming a default install/setup of Postgresql for Windows the following files are located under C:\Program Files\PostgreSQL\$VERSION\data

Verify that postgresql.conf has following set:

listen_addresses = '*'

This should already be set to '*' so nothing do here.

Update pg_hba.conf to allow connections from WSL2 range e.g. for Postgresl 12:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             172.0.0.0/8             md5
host    all             all             192.0.0.0/8             md5

For Postgresql 13+ you should use scram-sha-256 as the method.

Restart Postgres for changes to take effect. This can be done either from the Windows Services app or from cmd with Administrator privileges e.g. for Postgresql 12:

net stop postgresql-x64-12
net start postgresql-x64-12

WSL Shell Conveniences

In WSL, add following to your ~/.bashrc or similar:

# Add DNS entry for Windows host
if ! $(cat /etc/hosts | grep -q 'winhost'); then
  echo 'Adding DNS entry for Windows host in /etc/hosts'
  echo '\n# Windows host - added via ~/.bashhrc' | sudo tee -a /etc/hosts
  echo -e "$(grep nameserver /etc/resolv.conf | awk '{print $2, "   winhost"}')" | sudo tee -a /etc/hosts
fi

Then reload your .bashrc changes: source ~/.bashrc

Usage

psql -h winhost -p 5432 -U postgres

Notes:

• The IP address assigned to the Windows host by WSL2 is not the same as the IP address assigned to your physical Windows machine on your network. WSL2 uses vEthernet connections.
• You can inspect the vEthernet connections via Control Panel\Network and Internet\Network Connections
• Note that when looking at the IPv4 properties that the IP addresses will appear as if they are statically set but they aren't! Try rebooting and inspecting IPv4 properties again
• If one day you're unable to connect to Postgres, check that winhost is in the IP address range per firewall rules. Could be WSL has assigned an IP address that we weren't expecting!

In WSL2 you need to use host IP to connect

to get host IP

grep nameserver /etc/resolv.conf | awk '{print $2}'

then you need to allow TCP 5432 inbound Rules in 'Windows Defender Firewall with Advanced Security'

I made my self PS.you still need to allow TCP 5432 in Firewall

put this in ~/.bashrc

cat /etc/hosts | grep 172.; test $? -eq 0 && $1 || echo -e "$(grep nameserver /etc/resolv.conf | awk '{print $2, " host"}')\n$(cat /etc/hosts)" | sudo tee /etc/hosts

its append host IP to /etc/hosts if not exist before(usually happened when restart wsl or computer)

then

psql -h host -p 5432 -U postgres

For me what worked was to follow these steps:

• Change pg_hba.conf to listen on all interfaces: host all all 0.0.0.0/0 trust
• Open firewall for postgresql
• Use one of the hostnames in /etc/hosts that pointed to the ip of my host. This hostname for me was: host.docker.internal

In extension to the most awarded answer:

If, for some reason, the IP that you are seeing in your WSL terminal is not correct or giving a connection refused error, use the following script in your .bashrc file

# Map Windows host IP as winhost (based on default route, not /etc/resolv.conf)
if ! grep -q 'winhost' /etc/hosts; then
  echo 'Adding Windows host IP as winhost...'
  WINIP=$(ip route | grep default | awk '{print $3}')
  echo -e "\n# Windows host for PostgreSQL\n$WINIP   winhost" | sudo tee -a /etc/hosts
fi

Bonus Tip:

If you are in local env and tired of entering password again and again, then see below

Create the .pgpass file

nano ~/.pgpass

Then add this line (replace values with your actual credentials):

winhost:5432:*:postgres:your_password

Then secure the file (IMPORTANT)

chmod 600 ~/.pgpass

Test

psql -h winhost -U postgres

or simply add this entry into ~/.bashrc and source it

alias pgwin="psql -h winhost -U postgres -p 5432"

Then use it:

pgwin

This issue can be fixed in two possible ways

Specify the host and user name explicitly

psql -h localhost -U postgres

Or

Navigate to the runpsql.sh file and run the query

/Library/PostgreSQL/14/scripts/runpsql.sh

Now run the psql query by entering password (if needed)

I found that WSL 2 does give this error:

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections?

But WSL 1 does not.

I had two different WSL distros running of Ubuntu one on WSL 1 and one on WSL 2 and tested connectivity in WSL 1 and my connection there was successful.

I downgraded the other one and re-tested and it worked as well. I re-upgraded and I got the error.

WSL 2 is a more complete (see here... https://learn.microsoft.com/en-us/windows/wsl/compare-versions) and is a full Linux Kernel and Managed VM whereas WSL 1 is not.

If you are like me, and your requirements are not as stringent for testing (and switching to the admin user to take care of the firewall and postgres settings on your work laptop is a laborious process as it is for me) and you just need to test from Linux as I did, you can install another WSL and leave it at 1 or even downgrade. Of note though this then will not support Rancher Desktop as it needs WSL 2. Docker Desktop installs WSL 2 so that should not be a consideration.

More info here to see your WSL versions etc.

https://learn.microsoft.com/en-us/windows/wsl/compare-versions

HTH.

I managed to connect to my postgres-db on windows from my WSL2 after having the same problem.. Some of my steps might be redundant, but hopefully it leads the discussion towards greater insights. Here are my steps:

1. Make sure postgresql.conf had listen_addresses = '*'
2. Open firewall inbound rule to allow any ips for port 5432
3. Add host to pg_hba.conf by adding the following line:
   host all all 172.xx.xx.0/20 scram-sha-256

The ip-address "172.xx.xx.x" was attained through running ipconfig and taking the IPv4 Address under the Ethernet Adapter WSL section.

Now after all this, I am pretty sure it still wasn't working for me. However, I found an additional way of allowing inbound traffic from the WSL network.. Run the following in an elevated powershell (you can read more about it here):

New-NetFirewallRule -DisplayName "WSL" -Direction Inbound -InterfaceAlias "vEthernet (WSL (Hyper-V firewall))" -Action Allow

You might have to change interface alias to whatever you see at "Ethernet Adapter" when running ipconfig in windows cmd. These last two steps were most likely what did it for me. Hope this helps!

open file:

sudo vim /etc/postgresql/14/main/pg_hba.conf

Add the below line in it:

host    all             all             0.0.0.0/0               trust

Now restart the postgres:

sudo service postgresql restart

Now log into postgres via cli:

psql -h localhost -U postgres