Using dBeaver to Access Postgres on WSL: A Developer Guide

TL;DR

While WSL and Windows share some resources, they maintain a level of separation. Network connections made on Windows might not be directly accessible by WSL applications like PostgreSQL.You need to adjust PostgreSQL's security settings to allow connections from your Windows machine.So, in your pg_hba.conf file, add bind address 0.0.0.0/0 to accept connections from anywhere.

The Transition

Switching from the development environment you're used to always comes with resistance. As developers, we often talk about change management and think only end users need that transition preparation. Little did I know that switching development environments also require similar preparation.

I have been using Linux-based distros like Debian and Ubuntu professionally for 3 years. We all know why we love this setup, so I won't go into that. My perfect configurations—like neovim, zsh, and a minimalist desktop environment etc—got me very attached to the Linux setup.

Change is inevitable, especially in the dynamic world of software engineering. As fate would have it, I moved to a new company with different policies. I was given a Windows 11 laptop and quickly needed to set up my development environment.

Fast forward, all the required software is installed. What's left is getting the Linux experience on Windows. Enter WSL. For starters, WSL is the Windows Subsystem for Linux, a feature that allows Linux enthusiasts to run a Linux-like environment on Windows without using slow, conventional virtual machines. But that's a topic for another day.

FATAL: Authentication Failed for user 'user'

Anyway, let me not bore you with my personal experience. After installing and setting up WSL, I needed a database GUI client. dBeaver and pgAdmin are my top choices. And here begins my troubles. Assuming things would work out of the box like they did on Linux, I tried to connect my database installed on WSL to a GUI client, but every time, I got a Connection refused: connect or FATAL: Authentication Failed for user 'user' error. I tried several recommended solutions, such as allowing port 5432 on my firewall with ufw allow 5432/tcp, but this did not resolve the issue.

After much trial and error, I had an aha moment when I applied my networking skills. You see, dBeaver is installed on Windows and my database is on WSL, so there is complete isolation. While WSL and Windows share some resources, they maintain a level of separation. Network connections made on Windows might not be directly accessible by WSL applications like PostgreSQL. By default, PostgreSQL listens for connections on the local loopback interface 127.0.0.1, which is only accessible from within the WSL environment itself. DBeaver running on Windows is treated as a remote connection.

To overcome these restrictions, this is what worked for me. I modified the pg_hba.conf file. This configuration file lets you specify which users and IP addresses can connect to the database. Since I needed this for local development, I allowed connections from any IP by adding this line: host all all 0.0.0.0/0 md5. Please note that this is not recommended due to security risks.

Implementing a secure connection

While modifying the above settings will work for most users, if your company policy does not allow opening bind ports, you can specify the database and IP address that PostgreSQL should listen to. On your WSL, check the host IP using ip addr show or ss -tnlp. From the output of these commands, focus on the eth0 interface, which is the network interface connected to your WSL environment's virtual network. Assuming the IP is 172.26.96.1, modify the pg_hba.conf file and add this line or modify the one with 127.0.0.1/32 to host admin db_name 172.26.96.1/32 md5. Once done, restart PostgreSQL to update the config with sudo service postgresql restart. Then, go to your GUI client and try connecting to the database, and voilà, the connection is successful!

That's it, I thought I should share my experience and what worked for me. I hope this helps anyone with similar encounters. Thanks for reading my rant on a simple problem. Happy hacking!