Web Dev setup in WSL2 Kali Linux 2022 Edition - Part 1: Connecting MariaDB and PostgreSQL database across Windows and WSL2
Tags: webdev, beginners, postgres, mariadb
Published: 2022-06-01 18:27:39

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o6ndb9ktrbljdgbdh4hy.png)

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:

```bash
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](https://dev.to/alia5/the-ultimate-wsl2-setup-4m08) has it covered well. For Postgres vs MySQL comparison, you can check this [blog](https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison-syntax-performance-scalability-and-features) 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](https://www.pgadmin.org/) from their download [page](https://www.enterprisedb.com/downloads/postgres-postgresql-downloads). Simply install them and all add their `bin` directories to path folder if necessary.

For MariaDB, I recommend using [XAMPP](https://www.apachefriends.org/index.html) 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](https://mariadb.org/). You can use both [MySQL Workbench](https://www.mysql.com/products/workbench/) and [phpmyadmin](https://www.phpmyadmin.net/) 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.

```bash
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ebl6u2f0z9glod52yb8j.png)

This is the expected output after proper installation.

To fix connection refused issue, here's the [workaround](https://stackoverflow.com/questions/45707319/pgadmin-on-windows-10-with-postgres-when-installed-via-bash-on-ubuntu-on-windows) for it:

```bash
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:

```txt
#newline
host    all             all             all                     trust
```

Here's the detail [article](https://www.cybertec-postgresql.com/en/postgresql-on-wsl2-for-windows-install-and-setup/) if you like to dig deeper into this. Also, check [this](https://www.rosehosting.com/blog/how-to-install-phppgadmin-on-ubuntu-20-04/) for better insights.

#### MariaDB Server in WSL

Here's the command enough to setup MariaDB server in WSL:

```bash
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](https://stackoverflow.com/questions/64320136/error-2002-hy000-cant-connect-to-mysql-server-on-192-168-1-15-115) 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:

```bash
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.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vhhihi1wu991360zse0j.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/72irt2qvv13wocd3hfe9.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4h7jqnrhd6z217a7zypl.png)

### 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:

```bash
psql -h [HOST ADDRESS] -U [USER NAME]
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sl8mr9movxq29ixtj3p7.png)

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


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jyebgwkmplasz4vyd2oi.png)

To make things simpler, simply create new user and access through it:

```sql
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](https://askubuntu.com/questions/32730/how-to-remove-postgres-from-my-installation) in detail for the process:

- Remove Postgres from apt (or from other installation if any):

  ```bash
  sudo apt-get --purge remove postgresql postgresql-*
  ```

- Verify if packages are uninstalled:

  ```bash
  dpkg -l | grep postgres
  ```

Here's the [solution](https://askubuntu.com/questions/32730/how-to-remove-postgres-from-my-installation) in detail for the process:

- Remove Postgres from apt (or from other installation if any):

  ```bash
  sudo apt-get --purge remove postgresql postgresql-*
  ```

- Verify if packages are uninstalled:

  ```bash
  dpkg -l | grep postgres
  ```

  Here, you must find this to be empty. If not, remove them manually.

- Remove the configuration folders below:

  ```bash
  sudo rm -rf /var/lib/postgresql/
  sudo rm -rf /var/log/postgresql/
  sudo rm -rf /etc/postgresql/
  ```

- Finally, remove `postgres` user:

  ```bash
  sudo deluser postgres
  ```

### Work-around for error in `phpmyadmin`

Grant all permissions to `root` and `pma` and remove password too:

```sql
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