When I first began familiar with WSL 1.0 , the network interfacing was simpler. But since WSL2 uses dynamic virtual IP address for each VM, now the things has got little trickier. Here, we will learn to install Postgres and MariaDB server and client in both Windows and WSL2 Linux and access them across the virtual Ethernet connection. By the term using interchangeably we mean accessing database in Windows from WSL2 Linux and vice versa.
PROBLEM STATEMENT: WSL2 IPv4 has access to PostgreSQL from localhost in Linux but not in Windows requires virtual ethernet adaptor's assigned IP to communicate between each other and also other issue found was them requiring the firewall.
REQUIREMENTS: Database clients should be able to access database servers both in same OS and in other. WSL Client should access server in Windows and Windows clients should access database in WSL2.
To begin, one must need a good installation of Windows and WSL2 based distro in their machine.
Working with databases interchangeably
Source: https://dev.to/hymanzhan/setting-up-wsl-2-for-web-development-3202
Please be aware that you need to identify the IP address of the virtual machine (WSL) and the host machine you are working with, which might be different from the IP address of your local physical machine. You can find the IP address using Task Manager, and also using this command in WSL:
ip route # or
ip addr | grep eth0
You might also need to enable firewall on port 3306 for MariaDB and 5432 for Postgres for interconnectivity.
If you require port forwarding in WSL for reason, this article has it covered well. For Postgres vs MySQL comparison, you can check this blog from EDB, so you can choose the database system based on your need.
Database Servers
Database Servers in Windows
For Windows, you can get PostgreSQL from EDB and pgAdmin 4 from their download page. Simply install them and all add their bin directories to path folder if necessary.
For MariaDB, I recommend using XAMPP since it is robust and simple for beginners. But, if you like to install standalone version, you can download and install from official MariaDB site. You can use both MySQL Workbench and phpmyadmin to access such them as a client.
You must be able to access normally in Windows by psql and mysql commands.
Enabling/Disabling database servers in Windows is done through 'Windows Services'. It can be accessed by entering
service.mscin Windows Run (Win+R).
Database Servers in Linux
PostgreSQL Server in WSL
Most of the Linux distribution already have source for installing Postgres packages.
sudo apt update
sudo apt install postgresql postgresql-client postgresql-client-common postgresql-common postgresql-contrib -y
psql --version # to confirm installation
sudo service postgresql start # replace ending by stop to stop server, restart to restart server
sudo -u postgres psql # to access Postges via command line (both in Linux)
TIP: can check the status of your services in Linux using sudo service --status-all in the shell.
This is the expected output after proper installation.
To fix connection refused issue, here's the workaround for it:
sudo -u postgres psql -c 'SHOW config_file' # to locate config file
Once you find the file, change the listen_addresses to * in postgresql.conf, the restart Postgres by sudo service postgresql restart command.
Also, update this in pg_hba.conf to allow remote auth from Windows host.
...
host all all 0.0.0.0/0 scram-sha-256
host all all ::/0 scram-sha-256
This is a less recommended method (for security) you can try in case above methods still don't let you connect in Windows:
#newline
host all all all trust
Here's the detail article if you like to dig deeper into this. Also, check this for better insights.
MariaDB Server in WSL
Here's the command enough to setup MariaDB server in WSL:
sudo apt install mariadb-server
sudo service mariadb start
sudo mysql # to access the database CLI
To change port, edit the configuration file /etc/mysql/my.cnf that suits your needs.
This StackOverflow thread suggests to edit the file /etc/mysql/mariadb.conf.d/50-server.cnf, replace bind-address = 127.0.0.1 (localhost) by bind-address = 0.0.0.0 or * (all). After you restart the server by $ sudo service mariadb restart, you may be able to login your Linux database server in Windows client too with proper credentials (username, password) configuration.
Database Clients
Database Clients in Windows accessing database in both Windows & WSL
We need to enable firewall for both in and out bound in Windows (for working across). To access, we need to find IP of VM in which the database server is active, the we can get along by mentioning host, port, user and password from any database client. This process if enough for the well-configured server.
Here's the sample psql command to log in for dev user, to avoid conflict:
psql -U dev -d postgres -h 192.168.176.181 -p 5432 # server host IP
In pgAdmin mention host IP address from ip route and add other credentials to access WSL based Postgres server.
To access MariaDB in Windows using XAMPP phpmyadmin, create a user in WSL MariaDB server (say wsl_user with no password) and grant ALL PRIVELEDGES. Now, you can go to login portal for phpmyadmin and find Debian in server version.
Here, mysql accesses the server in Windows in the first one, and in the second one, the server is natively installed within WSL. Therefore, both side accessed.
In phpMyAdmin, you also need to change its config.inc.php on $cfg['Servers'][$i]['auth_type'] = 'config'; to cookie to get login page.
Here's a sample showing access in Command Line (Database server is in Windows):
Database Clients in WSL accessing database in both Windows and WSL
For psql in WSL to access Windows database, you need to find host IP (using ip route for example) and then hit the command below:
psql -h [HOST ADDRESS] -U [USER NAME]
For MariaDB client access, it is simple as mysql like mysql -u wsl_root -p -h 172.24.xxx.xxx:
To make things simpler, simply create new user and access through it:
CREATE USER 'wsl_root'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'wsl_root'@'localhost' WITH GRANT OPTION;
CREATE USER 'wsl_root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'wsl_root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
You can get more details in these blogs: https://dev.to/hymanzhan/setting-up-wsl-2-for-web-development-3202 and https://dev.to/qviper/connecting-mysql-server-in-windows-machine-from-wsl-4pf1
Tips
Complete Postgres removal
Here's the solution in detail for the process:
- Remove Postgres from apt (or from other installation if any):
sudo apt-get --purge remove postgresql postgresql-*
- Verify if packages are uninstalled:
dpkg -l | grep postgres
Here's the solution in detail for the process:
- Remove Postgres from apt (or from other installation if any):
sudo apt-get --purge remove postgresql postgresql-*
- Verify if packages are uninstalled:
dpkg -l | grep postgres
Here, you must find this to be empty. If not, remove them manually.
- Remove the configuration folders below:
sudo rm -rf /var/lib/postgresql/
sudo rm -rf /var/log/postgresql/
sudo rm -rf /etc/postgresql/
- Finally, remove
postgresuser:
sudo deluser postgres
Work-around for error in phpmyadmin
Grant all permissions to root and pma and remove password too:
CREATE USER 'pma'@'localhost' IDENTIFIED BY '';
SET PASSWORD FOR root@localhost='';
References
- https://docs.microsoft.com/en-us/windows/wsl/networking
- https://www.enterprisedb.com/postgres-tutorials/how-use-postgresql-django
- https://www.enterprisedb.com/blog/how-to-secure-postgresql-security-hardening-best-practices-checklist-tips-encryption-authentication-vulnerabilities
- https://www.enterprisedb.com/postgresql-tutorial-resources-training
- https://dev.to/hymanzhan/setting-up-wsl-2-for-web-development-3202#install-database-in-wsl
- https://stackoverflow.com/questions/56824788/how-to-connect-to-windows-postgres-database-from-wsl
Top comments (0)