devsetup1-7  Exercise 3: MariaDB, a Limited User, and PDO
==========================================================

STEP 1: INSTALL AND CHECK
-------------------------
    sudo apt install mariadb-server
    systemctl status mariadb
    mariadb --version
    ss -tlnp | grep 3306

Expected: the service is active; the version begins 11.8 on Debian 13; and
port 3306 is listed on 127.0.0.1 only (for example 127.0.0.1:3306). If you
see 0.0.0.0:3306 or [::]:3306, the database is reachable from your network
and its bind-address setting needs correcting before you go any further.

STEP 2: A DATABASE AND A LIMITED USER
-------------------------------------
    sudo mariadb

    CREATE DATABASE devdb CHARACTER SET utf8mb4;
    CREATE USER 'dev'@'localhost' IDENTIFIED BY 'choose-a-password';
    GRANT ALL PRIVILEGES ON devdb.* TO 'dev'@'localhost';
    EXIT;

Check the limits:

    mariadb -u dev -p devdb -e "SHOW DATABASES;"

The dev user sees devdb (and the information schema) but not other
databases such as mysql.

STEP 3: A PDO SCRIPT (db-test.php)
----------------------------------
    <?php
    $pdo = new PDO(
        'mysql:host=localhost;dbname=devdb;charset=utf8mb4',
        'dev',
        'choose-a-password',
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );

    $pdo->exec('CREATE TABLE IF NOT EXISTS notes (id INT AUTO_INCREMENT PRIMARY KEY, body VARCHAR(200))');
    $pdo->prepare('INSERT INTO notes (body) VALUES (?)')->execute(['hello from devserver']);

    foreach ($pdo->query('SELECT id, body FROM notes') as $row) {
        echo $row['id'], ': ', $row['body'], PHP_EOL;
    }

Run: php db-test.php
Each run adds another row, so the output grows by one line each time.

If PHP reports "could not find driver", the php-mysql package is missing.
If it reports access denied, recheck the user name, password and host.

STEP 4: COMPARE WITH debserver
------------------------------
On debserver run:

    php -v
    mysql --version           # or: mariadb --version

Record both. Expected differences from devserver (which has PHP 8.4 and
MariaDB 11.8):
  - debserver runs an older Debian, so its PHP and MariaDB versions are older.
  - "mysql" on debserver may well be MariaDB; the version output says so.

How to deal with the differences:
  1. Write code that runs on both, and avoid features only present in the
     newer PHP.
  2. Test on debserver before deploying: run the same scripts there.
  3. Keep SQL to standard features; be careful with newer MariaDB-only
     syntax.
  4. When debserver is reinstalled with Debian 13 (planned), the versions
     will match and most of the caution disappears.

WHY THIS WORKS AS AN ANSWER
---------------------------
It verifies each claim (listening address, user limits, working query)
instead of assuming it. It also records the version gap between the two
machines as a risk to manage now, rather than a surprise on deployment day.