0614 - Bloc 2: Servidors d'aplicacions web

Sistema Gestor de Bases de Dades - MariaDB i connexió amb PHP

Treballarem amb una pila LAMP, que significa:

Linux – Sistema operatiu base.
Apache – Servidor web.
MariaDB – Sistema gestor de bases de dades.
PHP – Llenguatge de programació del costat del servidor.

Aquesta configuració està orientada exclusivament a servidors web Apache en entorns Linux (Ubuntu).
En altres combinacions tecnològiques (per exemple: Windows amb WAMP, Nginx, PostgreSQL, o altres llenguatges com Perl o Python), el procés d’instal·lació i configuració pot variar, i caldrà consultar la documentació específica corresponent.

El servidor de bases de dades que instal·larem serà MariaDB.

Implementació

Repositori

Abans d’instal·lar MariaDB, és recomanable assegurar-nos que la llista de paquets del sistema està actualitzada. Això garanteix que obtindrem les versions més recents i compatibles dels programes que instal·larem.

sudo apt-get update

Se suposa que tenim iniciat el nostre servidor ubuntu i iniciarem una shell per a poder fer la instal·lació del que calgui. A partir d'aquí:

Instal·lem client i Servidor de Base de Dades

MariaDB és un sistema gestor de bases de dades relacional, totalment compatible amb MySQL.
Per instal·lar tant el servidor com el client, executem la següent comanda:

sudo apt install -y mariadb-server mariadb-client

Aquesta ordre instal·la:

mariadb-server: el servei principal que gestiona les bases de dades.
mariadb-client: l’eina de línia d’ordres que permet connectar-nos i administrar la base de dades des del terminal.

Iniciem el servidor de base de dades

Un cop finalitzada la instal·lació, cal iniciar el servei perquè el servidor de bases de dades comenci a funcionar:

sudo systemctl start mariadb

Pots comprovar l’estat del servei amb:

sudo systemctl status mariadb

Si tot està correcte, veuràs un missatge indicant que MariaDB està actiu (active) i en execució (running).

posar exemple de sortida

O simplement podem veure els processos relacionats amb l'apache i el mariaDB amb la comanda:

root@xxxxx:~$ ps -ef | grep -e apache -e maria | grep -v grep
root      3432     1  0 09:50 ?        00:00:00 /usr/sbin/apache2 -k start
www-data  3435  3432  0 09:50 ?        00:00:00 /usr/sbin/apache2 -k start
www-data  3436  3432  0 09:50 ?        00:00:00 /usr/sbin/apache2 -k start
root      5095     1  0 10:08 pts/0    00:00:00 /bin/sh /usr/bin/mariadbd-safe
mysql     5220  5095  1 10:08 pts/0    00:00:00 /usr/sbin/mariadbd --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-log-error --pid-file=/run/mysqld/mysqld.pid --socket=/run/mysqld/mysqld.sock

Configuració inicial i seguretat del servidor MariaDB

Un cop hem instal·lat i iniciat el servei MariaDB, ja podríem accedir-hi directament amb la comanda:

sudo mysql

No obstant això, per defecte el servidor pot no tenir credencials configurades o pot utilitzar el mètode d’autenticació unix_socket (que permet accedir com a root només des del sistema).

Després d’instal·lar MariaDB, es convenient executar l’script de configuració de seguretat per establir contrasenya al compte root i desactivar opcions insegures:

Per instal·lar les credencials per a poder entrar ens cal executar la comanda mysql_secure_installation:

sudo mysql_secure_installation

Aquesta comanda ens farà diverses preguntes. Sempre es recomana tenir el mínim de permisos.

Segueix les instruccions que apareixen a la pantalla per:

Definir una contrasenya per a l’usuari root.
Eliminar usuaris anònims.
Desactivar l’accés remot al root (per seguretat).
Esborrar la base de dades de proves.
Recarregar les taules de privilegis.

Si volem accedir des de la màquina local amb alguna eina client, recomano, ja que tenim habilitat el protocol ssh, deshabilitar l'accés remot de root i accedir obrint el port de mariadb, 3306, amb la connexió ssh. Caldrà no tenir autenticació unix_socket.

sudo mysql_secure_installation
* * *
Switch to unix_socket authentication [Y/n] n
* * *
Disallow root login remotely? [Y/n] y
* * *

Possible sortida i respostes

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
    SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Accés al servidor MariaDB via SSH

Un cop hem configurat MariaDB i establert la contrasenya per a l’usuari root, ja podem accedir al servidor localment mitjançant la comanda:

mysql -u root -p

Aquesta comanda ens demanarà la contrasenya configurada durant l’assistent mysql_secure_installation i ens permetrà treballar directament amb la shell de MariaDB.

Accés remot segur mitjançant SSH

Si volem connectar-nos al servidor MariaDB des d’una altra màquina (per exemple, amb una eina gràfica com DBeaver, HeidiSQL o MySQL Workbench), és recomanable fer-ho de manera segura a través d’un túnel SSH.
Això ens permet connectar-nos com si fos una connexió local (localhost), sense exposar el port 3306 a Internet.

Primer, assegurem-nos que el servidor té instal·lat i actiu el servei SSH:

sudo apt install ssh
sudo systemctl enable ssh
sudo systemctl start ssh

Podem crear un túnel SSH amb la següent comanda:

Client SSH
`ssh usuari@ipDelServidor -L 3306:127.0.0.1:3306`

Això redirigeix el port local 3306 (MariaDB) a través d’una connexió segura amb el servidor.

No podem connectar-nos per ssh amb l'usuari root?

Correcte: per motius de seguretat, l’usuari root del sistema no pot accedir per SSH. Per tant, el més recomanable és crear un nou usuari local amb contrasenya pròpia i utilitzar-lo per establir el túnel SSH.

Crear un nou usuari local amb password.

Creem un usuari

sudo useradd -m -s /bin/bash jordi
echo "jordi:daw" | sudo chpasswd

Ara ja podem connectar-nos amb aquest usuari:
Connectem per ssh
```
ssh jordi@ipDelServidor
```

Ara podem accedir al servidor obrint un tunel ssh cap al servidor pel port 3306. podem fer-ho amb la comanda ssh o bé amb l'eina putty:

Tunnelling amb la comanda ssh

ssh usuari@ipDelServidor -L 3306:127.0.0.1:3306

També podem establir el túnel SSH mitjançant eines com Termius o directament des de DBeaver:

Tunnelling amb PuTTy	DBeaver

Podem mapejar tants ports necessitem

A més del port 3306 de MariaDB, podem redirigir altres ports útils (com el 22, 80 o 443) per treballar de manera remota i segura.

Comprovació d’accés a MariaDB

Un cop creat el túnel SSH o des de la mateixa màquina, podem accedir al servidor de bases de dades amb:

$ mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 69
Server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> exit;

usuari root sense password?

Assegura't que l'usuari root del servidor MariaDB tingui password ja que en cas contrari pot causar problemes de seguretat o d'accés a través de phpMyAdmin, o altres eines, més endavant.

Connectem PHP i MariaDB

Ens falta fer la connexió del php amb el mariadb, és a dir, instal·lar els mòduls que connecten php amb mariadb. Cal fer-ho a la versió de php on necessitem l'accés al servidor de base de dades.

Connexió a la BD amb PDO

En aquest projecte, i en tots els exemples del curs, farem la connexió a MariaDB utilitzant PDO (PHP Data Objects). PDO és l’opció recomanada per la seva seguretat (Prepared Statements), portabilitat i traducció automàtica d’errors. Documentació oficial de PDO

Afegirem un fitxer PHP bdd.php a l’arrel del servidor web (on apunta el DocumentRoot, habitualment /var/www/html/) amb el codi següent:

Fitxer /var/www/html/bdd.php

<?php
    $servername = "ipdelservidor";
    $username = "root";
    $password = "passwordQueTinguiElRoot";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=mysql", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        echo "Connected successfully";
    } catch(PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
?>

Accedim a la pàgina web http://ipdelservidor/bdd.php

Si el mòdul de connexió no està instal·lat, el missatge que obtindrem serà:

    Connection failed: could not find driver
    ```

Això indica que PHP no troba el connector PDO–MySQL/MariaDB i cal instal·lar-lo com hem vist abans (php-mysql).

Si no tinguéssim el codi protegit per un `try` caldria procedir de la següent forma:

* Abans d'accedir a la pàgina web [http://ipdelservidor/bdd.php](http://ipdelservidor/bdd.php){target="_blank"} podem executar la comanda `tail` al nostre servidor per a veure si es produeix algun error.

   `tail -n 0 -f /var/log/apache2/error.log`

   L'error que ens donaria si no tinguéssim el codi dins el `try` seria: `PHP Fatal error:  Uncaught PDOException: could not find driver in /var/www/html/bdd.php`

La qual cosa significa que ens falta instal·lar el mòdul de connexió del llenguatge *php* amb la base de dades *mariadb*.

#### Instal·lem els mòduls d'accés del php al servidor de base de dades

Cal instal·lar el mòdul `php-mysql`. Segons la versió de php que tinguem, ens instal·larà els mòduls `libapache2-mod-phpX.y phpX.y-cli phpX.y-common phpX.y-opcache phpX.y-readline`. Cal fixar-nos bé amb la versió de *php*. Si en volem una altra, el millor serà instal·lar el mòdul `php7.4-mysql` per exemple.

* `libapache2-mod-phpX.y`
* `phpX.y-cli`
* `phpX.y-common`
* `phpX.y-opcache`
* `phpX.y-readline`

``` bash hl_lines="1"
sudo apt install php-mysql

COMPTE a l'hora de la instal·lació. Cal que sigui la mateixa versió de php que tenim instal·lada i habilitada a l'apache.

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libapache2-mod-phpX.y phpX.y-cli phpX.y-common phpX.y-opcache phpX.y-readline
Suggested packages:
  php-pear
The following NEW packages will be installed:
  phpX.y-mysql
The following packages will be upgraded:
  libapache2-mod-phpX.y phpX.y-cli phpX.y-common phpX.y-opcache phpX.y-readline
5 upgraded, 1 newly installed, 0 to remove and 23 not upgraded.
Need to get 4,853 kB of archives.
After this operation, 138 kB of additional disk space will be used.
Do you want to continue? [Y/n]

Ara només ens cal reiniciar el servidor web, si no ens ho ha demanat la instal·lació,

sudo systemctl restart apache2

per tal que l'apache s'executi havent carregat el php amb el mòdul d'accés al servidor de bases de dades mariaDB.

Si tornem a accedir a http://ipdelservidor/bdd.php veurem que intenta connectar-se; una altra cosa és que tinguem permisos per a fer-ho -
Connection failed: SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'.

Es recomana crear sempre un usuari i una base de dades per cadascuna de les aplicacions que vulguem desenvolupar o instal·lar. No utilitzeu mai l'usuari root.

Eina d'administració del MariaDB: phpmyadmin

Instal·larem el paquet phpmyadmin per administrar remotament el servidor web, en cas necessari.

sudo apt install -y phpmyadmin php-mbstring php-zip php-gd php-json php-curl

Altres extensions

Hem instal·lat algunes extensions de php però segons els CMS que instal·lem és possible que calgui instal·lar-ne d'altres.

Si no podem executar la instal·lació per no existir algun dels mòduls, podem treure'l de la llista.

I si no s'ha activat automàticament el phpmyadmin?

Ara ens cal activar el paquet phpmyadmin ja que en les proves fetes no el teniem activat. Per això caldrà afegir el fitxer /etc/phpmyadmin/apache.conf dins el directori de configuracions disponibles de l'apache /etc/apache2/conf-available per a posteriorment habilitar-la a2enconf. Ho farem amb les següents instruccions:

ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
a2enconf phpmyadmin
sudo systemctl reload apache2

Fixeu-vos que al fitxer de configuració l'hem anomenat phpmyadmin.conf

Provem-ho

Per a poder treballar amb el phpmyadmin, caldrà iniciar sessió a http://ipdelservidor/phpmyadmin però, per a no treballar amb l'usuari root, crearem un usuari administrador admin amb password daw. Això ho fem amb les següents comandes:

mysql -u root -p -e "CREATE USER 'admin'@'localhost' IDENTIFIED BY 'daw'; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES;"

Després crearem un usuari i una base de dades per a provar que som capaços, des de php, d'accedir al servidor web. Creem un usuari m8 amb password m8 i una base on tingui tots els permisos anomenada, també, m8

mysql -u admin -pdaw -e "CREATE USER 'm8'@'localhost' IDENTIFIED BY 'm8'; CREATE DATABASE m8; GRANT ALL PRIVILEGES ON m8.* TO 'm8'@'localhost';"

mysql -u m8 -pm8 m8 -e "CREATE TABLE t1(ct1 INT AUTO_INCREMENT PRIMARY KEY, dt1 VARCHAR(50)); INSERT INTO t1(dt1) VALUES ('Un'),('Dos'),('Tres'),('Quatre'),('Cinc'),('Sis'),('Set');"

Puc connectar-me des d'eines externes com ara el dBeaver amb un usuari `usuari@%`?

Aquesta NO ÉS una configuració recomanda sota cap concepte

La configuració que tenim, per defecte, al servidor mariadb només permet la connexió al servidor de bases de dades des de l'adreça 127.0.0.1.

Per a poder accedir des d'altres hosts cal canviar la configuració del servidor mariadb, tenint en compte que això provoca una menor seguretat, per tal que escolti les peticions des de qualsevol IP, o la que ens interessi.

Si volem fer-ho, però, cal canviar el fitxer /etc/mysql/mariadb.conf.d/50-server.cnf, modificant la línia on especifiquem que el servidor només escolta per la ip ipdelservidor i posant-li que cal escoltar per la ip 0.0.0.0. Aquesta IP indica que escolta les peticions per totes les IPs que té el contenidor.

Així doncs, cal buscar la línia

bind-address            = 127.0.0.1

i modificar-hi l'adreça així

bind-address            = 0.0.0.0

A l'haver modificat el fitxer de configuració del mariadb cal reiniciar-ne el servei.

sudo systemctl restart mariadb

Amb quin usuari accedeixo?

Per accedir des d'una màquina externa, cal crear un usuari que no sigui amb host localhost. La següent comanda crea un usuari anomenat admin@'%' amb password daw que pot accedir, per tant, des de qualsevol host amb permisos de DBA.

mysql -u admin -pdaw -e "CREATE USER 'admin'@'%' IDENTIFIED BY 'daw'; GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;"