DEV Community

Yuil Tripathee
Yuil Tripathee

Posted on

Web Dev setup in WSL2 Kali Linux 2022 Edition - Part 1: Connecting MariaDB and PostgreSQL database across Windows and WSL2

#webdev #beginners #postgres #mariadb

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

Image description

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
Enter fullscreen mode Exit fullscreen mode

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.msc in 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)
Enter fullscreen mode Exit fullscreen mode

TIP: can check the status of your services in Linux using sudo service --status-all in the shell.

Image description

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

In pgAdmin mention host IP address from ip route and add other credentials to access WSL based Postgres server.

Image description

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.

Image description

Here's a sample showing access in Command Line (Database server is in Windows):

Image description

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]
Enter fullscreen mode Exit fullscreen mode

Image description

For MariaDB client access, it is simple as mysql like mysql -u wsl_root -p -h 172.24.xxx.xxx:

Image description

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;
Enter fullscreen mode Exit fullscreen mode

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-*
Enter fullscreen mode Exit fullscreen mode
  • Verify if packages are uninstalled: 
  dpkg -l | grep postgres
Enter fullscreen mode Exit fullscreen mode

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-*
Enter fullscreen mode Exit fullscreen mode
  • Verify if packages are uninstalled: 
  dpkg -l | grep postgres
Enter fullscreen mode Exit fullscreen mode

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/
Enter fullscreen mode Exit fullscreen mode
  • Finally, remove postgres user: 
  sudo deluser postgres
Enter fullscreen mode Exit fullscreen mode

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='';
Enter fullscreen mode Exit fullscreen mode

References

Top comments (0)

Read next

maame-codes profile image

PYTHON LOGICAL OPERATORS

Maame Afia Fordjour -

dejetem profile image

Rendering( or How to Render) Animation in JSON format with LottieFiles animation in React application

JONATHAN SIMON -

1hamzabek profile image

Everything About Blazor p1

Hamza -

om0509 profile image

Docker Puppeeteer Node Railway

Om Bharti -