Étiquette : database

I assume you started from my guide for hardened Raspberry Pi.

First steps

PostgreSQL is a powerful open-source relational database.

sudo apt update
sudo apt install postgresql

Check if the service is running.

systemctl status postgresq

Add firewall rule to allow connection.

ufw allow 5432/tcp

Hardening users and network configuration

Connect with user postgres and create new accounts.

sudo su postgres
createuser -P --interactive <new user login>
exit

Connect with the new user to control.

psql -d postgres -U vrampal -W

Remove the shell of user postgres.

sudo chsh -s /usr/sbin/nologin postgres

By default PostgreSQL is only available on loopback (localhost/127.0.0.1). Either you use an SSH tunnel to connect or change the configuration.

sudo vim /etc/postgresql/15/main/postgresql.conf
# change listen_addresses = 'localhost'
# into listen_addresses = '*'

sudo vim /etc/postgresql/15/main/pg_hba.conf
# add the following line
# host all <new user login> 0.0.0.0/0 md5

sudo systemctl restart postgresql

I generally continue configuring the database with graphical client like DBeaver.

Where are the data stored?

MariaDB will store the data in the folder /var/lib/postgresql and it’s a good idea to backup these data regularly.

I assume you started from my guide for hardened Raspberry Pi.

First steps

MariaDB is a simple, popular and open-source relational database.

sudo apt update
sudo apt install mariadb-server

Run the following script to secure your installation and define root password.

sudo mariadb-secure-installation

Check if the service is running.

systemctl status mariadb

Add firewall rule to allow connection.

ufw allow 3306/tcp

Connect for the first time to control everything is ok.

mysql -u root -p

Hardening users and network configuration

Create a user with full privilege. Choose a meaningful username, a strong password and you can choose to connect only from local host or from a local network like 192.168.1.%

GRANT ALL PRIVILEGES ON *.* TO '<new user login>'@'%' IDENTIFIED BY '<new user password>' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

Connect with the new user to control.

mysql -u <new user login> -p

Remove default users.

DROP USER 'mysql'@'localhost';
DROP USER 'root'@'localhost';
EXIT;

Remove the shell of user mysql.

sudo chsh -s /usr/sbin/nologin mysql

By default MariaDB is only available on loopback (localhost/127.0.0.1). Either you use an SSH tunnel to connect or change the configuration.

sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
# replace bind-adress = 127.0.0.1
# by bind-adress = 0.0.0.0

sudo systemctl restart mariadb

I generally continue configuring the database with graphical client like DBeaver.

Where are the data stored?

MariaDB will store the data in the folder /var/lib/mysql and it’s a good idea to backup these data regularly.