Installing PostgreSQL on Raspberry Pi

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.

Installing Nginx on Raspberry Pi

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

First steps

I like Nginx for the performances and the simple configuration file.

sudo apt update
sudo apt install nginx-light

Check the service is running.

systemctl status nginx
wget -O /dev/null http://127.0.0.1:80/

Add firewall rule to allow connection.

ufw allow 80/tcp
ufw allow 443/tcp

Self signed configuration

As the Deffie-Hellman generation is quite long on a Raspberry Pi, I generally download my dhparam.pem from the Public Deffie-Hellman Parameter Service.

curl https://2ton.com.au/getprimes/random/dhparam/4096 > dhparam.pem
sudo cp dhparam.pem /etc/nginx/
rm dhparam.pem

Let’s generate a self-signed certificate to get started.

openssl req -newkey rsa:2048 -nodes -keyout self-signed.key -x509 -days 3650 -out self-signed.pem
sudo cp self-signed.* /etc/nginx
sudo chmod g-rwx,o-rwx /etc/nginx/*.key
rm self-signed.*

Put your site configuration file in /etc/nginx/sites-available and enable it by setting a symbolic link in /etc/nginx/sites-enabled.

# Port 80/HTTP will redirect all traffic to 443/HTTPS
server {
listen 80 default_server;
listen [::]:80 default_server;

server_name <website host>;
server_tokens off;

return 301 https://$host$request_uri;
}

# Main server on port 443/HTTPS
server {
listen 443 ssl http2;
listen [::]:443 ssl http2;

server_name <website host>;
server_tokens off;

ssl_certificate /etc/nginx/self-signed.pem;
ssl_certificate_key /etc/nginx/self-signed.key;
ssl_trusted_certificate /etc/nginx/self-signed.pem;

ssl_session_timeout 1d;
ssl_session_cache shared:MozSSL:10m;
ssl_session_tickets off;

ssl_dhparam /etc/nginx/dhparam.pem;

ssl_protocols TLSv1.2 TLSv1.3;
ssl_ciphers 'ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256';
ssl_prefer_server_ciphers on;

ssl_stapling on;
ssl_stapling_verify on;

index index.php index.html index.htm;
error_page 404 /404.html;
error_page 500 502 503 504 /50x.html;

root /var/www/html/<website folder>;

location / {
# First attempt to serve request as file, then
# as directory, then fall back to displaying a 404.
try_files $uri $uri/ =404;

# Security HTTP headers
add_header Content-Security-Policy "default-src 'self'";
add_header Permissions-Policy "camera=(),fullscreen=(),microphone=()";
add_header Referrer-Policy "no-referrer";
add_header Strict-Transport-Security "max-age=31536000; includeSubDomains";
add_header X-Content-Type-Options "nosniff";
add_header X-Frame-Options "sameorigin";
add_header X-XSS-Protection "1; mode=block";
}

}

When you are happy with your config, restart your Nginx server and check everything is ok.

sudo systemctl restart nginx
systemctl status nginx

The configuration file is mostly generated from Mozila SSL Config Generator.

I use the following sites to verify my SSL configuration: Qualys SSL Labs, Immuniweb SSL and cryptcheck.fr.

For the HTTP security, I check using Mozilla Observatory, Immuniweb WebSec and securityheaders.com.

Installing Let’s Encrypt

Let’s Encrypt certbot will generate valid certificate for you.

sudo apt install certbot python3-certbot-nginx

Running certbot.

sudo certbot --nginx

Test for a renewal

sudo certbot renew --dry-run

Edit /etc/crontab and add the following line for automatic renewal of the certificates.

0 0,12 * * * root sleep 1486 && certbot renew -q

Installing MariaDB on Raspberry Pi

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.