MariaDB Security and Configuration Setup

[MickLesk]

Copied from original post: tteck/Proxmox#2915

MariaDB Security and Configuration Setup

The next step involves running the security script, which modifies certain default options to enhance security. Our purpose here is to prevent remote root logins and remove unnecessary database users.

Run the Security Script

mariadb-secure-installation

Follow the prompts:

1. Enter current password for root (enter for none): Press Enter
2. Switch to unix_socket authentication [Y/n]: y
3. Change the root password? [Y/n]: n
4. Remove anonymous users? [Y/n]: y
5. Disallow root login remotely? [Y/n]: y
6. Remove test database and access to it? [Y/n]: y
7. Reload privilege tables now? [Y/n]: y

---

Create an Admin User

We'll create a new account named admin with root-level capabilities, but configured for password authentication.

mariadb

The prompt will change to MariaDB [(none)]>. Now, create a new local admin user (adjust the username and password to your preference):

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';

Grant the admin user root privileges (adjust username and password as above):

GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

To allow the admin user password-based access from your local area network (LAN) within the subnet 192.168.100.0/24, use the following (adjust the username, password, and subnet as needed):

GRANT ALL ON *.* TO 'admin'@'192.168.100.%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Flush the privileges to ensure they are saved and available in the current session:

FLUSH PRIVILEGES;

Exit the MariaDB shell:

exit

---

Log in as the New Admin User

Log in as the new database user you just created:

mariadb -u admin -p

Create a new database:

CREATE DATABASE homeassistant;

Exit the MariaDB shell:

exit

---

Additional Configuration

• ⚠️ Reboot the LXC container.

• Check the MariaDB status:

  systemctl status mariadb

• Update the db_url in your configuration.yaml for Home Assistant:

  recorder:
    db_url: mysql://admin:[email protected]:3306/homeassistant?charset=utf8mb4

• Access the phpMyAdmin interface at: IP/phpMyAdmin