How to connect to windows postgres Database from WSL
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
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:
- Allow Windows and Postgres to accept connections from the WSL2 IP address range (not allowed by default)
- From WSL2, determine the Windows/Postgresql host's IP address (which is dynamic) when connecting via
psql
. We'll make this convenient via.bashrc
andalias
.
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:
- Open
Windows Defender Firewall with Advanced Security
- Click
New Rule...
- Select
Port
for rule type - Select
TCP
and forSpecific local ports
enter5432
- Select
Allow the connection
. Connecting from WSL2 won't be secure so don't select the secure option - Select at least
Public
. Can selectDomain
andPrivate
as well. I could only connect ifPublic
was selected - Name the rule e.g.
Postgres - connect from WSL2
and create it - Right click newly created rule and select
Properties
then click on theScope
tab - Under
Remote IP address
, selectThese IP addresses
then clickAdd...
and enter range172.0.0.1
to172.254.254.254
- Repeat step 9 for IP address range
192.0.0.1
to192.254.254.254
- Click
Apply
thenOK
- 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\PostgresSQL\$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 viaControl 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!
Specify your host, port, and username explicitly For example:
psql -h 127.0.0.1 -p 5432 -U postgres