Lesson: MySQL/MariaDB & PostgreSQL

What you'll learn

• What a relational database (RDBMS) is and the core vocabulary (table, row, SQL).
• How to install and connect to MySQL/MariaDB and PostgreSQL.
• How to create a database, a user, and grant that user access.
• The practical differences between MySQL, MariaDB, and PostgreSQL.
• The lab's per-app credentials rule and why it matters.

By the end you can stand up a database, create an isolated app account, and connect to it at a recognise-and-operate level.

The lesson

1. What is a relational database?

A relational database management system (RDBMS) stores data in tables — think spreadsheets with named columns and rows. Tables can relate to each other (an orders table references a customers table), which is where "relational" comes from.

You talk to an RDBMS using SQL (Structured Query Language): SELECT, INSERT, UPDATE, DELETE, plus CREATE/ALTER/DROP for structure. A defining feature is the schema: you declare the shape of your data (columns and types) up front, and the database enforces it.

The three you'll meet everywhere:

• MySQL — the classic open-source RDBMS, now owned by Oracle.
• MariaDB — a community fork of MySQL; mostly a drop-in replacement.
• PostgreSQL ("Postgres") — a feature-rich, standards-focused RDBMS.

Our lab gives each its own VM:

   +---------------------------+   +--------------------------+   +-------------------------+
   | PostgreSQL-Server         |   | MariaDB-Server           |   | MySQL-Server            |
   | 10.100.100.13   (PG)      |   | 10.100.100.14 (MariaDB)  |   | 10.100.100.15 (MySQL)   |
   +---------------------------+   +--------------------------+   +-------------------------+
              ^                              ^                              ^
              |   apps connect from the lab subnet, each with its OWN db+user
              +------------------------------+------------------------------+

2. Install

MySQL / MariaDB (Debian/Ubuntu):

sudo apt update
sudo apt install -y mariadb-server      # or: mysql-server
sudo systemctl enable --now mariadb     # or: mysql
sudo mysql_secure_installation          # set root password, remove test db

PostgreSQL:

sudo apt install -y postgresql
sudo systemctl enable --now postgresql

The CLI clients are mysql (works for both MySQL and MariaDB) and psql (Postgres).

3. Connect

MySQL/MariaDB — local admin login uses the root system account:

sudo mysql                          # admin shell, prompt becomes MariaDB [(none)]>

Connect to a specific server over the network:

mysql -h 10.100.100.15 -u appuser -p appdb     # -p prompts for password

PostgreSQL — Postgres ships a postgres OS user that maps to the DB superuser:

sudo -u postgres psql               # admin shell, prompt becomes postgres=#

Connect to a specific server:

psql -h 10.100.100.13 -U appuser -d appdb       # then enter password

Useful in-shell commands: in psql, \l lists databases, \du lists users, \c dbname switches, \q quits. In mysql, SHOW DATABASES;, SELECT user,host FROM mysql.user;, USE dbname;, \q or exit.

4. Create a database + user + grant (MySQL/MariaDB)

Run inside sudo mysql:

CREATE DATABASE appdb CHARACTER SET utf8mb4;

-- user that may connect from anywhere on the lab subnet:
CREATE USER 'appuser'@'10.100.100.%' IDENTIFIED BY '<REDACTED>';

GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'10.100.100.%';
FLUSH PRIVILEGES;

Note the 'user'@'host' form — MySQL identifies a user by both name and the host they connect from. appdb.* means "all tables in appdb only" — this user cannot touch other apps' databases.

5. Create a database + user + grant (PostgreSQL)

Run inside sudo -u postgres psql:

CREATE DATABASE appdb;
CREATE USER appuser WITH PASSWORD '<REDACTED>';
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser;

-- Postgres also needs schema rights (run after \c appdb):
\c appdb
GRANT ALL ON SCHEMA public TO appuser;

For Postgres to accept network logins you also edit two files (admin task): postgresql.conf (listen_addresses = '*') and pg_hba.conf (add a line allowing the lab subnet with scram-sha-256 auth), then sudo systemctl reload postgresql.

6. MySQL vs MariaDB vs PostgreSQL

At your level, "which one" is usually decided by the app's documentation. Recognise these traits:

Trait	MySQL / MariaDB	PostgreSQL
Style	Simple, fast, hugely common in PHP/web stacks	Standards-strict, feature-rich
MySQL vs MariaDB	MariaDB is a fork; commands are nearly identical	n/a
Admin login	sudo mysql (root via socket)	sudo -u postgres psql
User identity	user@host pair	role (user), host rules in pg_hba.conf
Advanced types	Basic JSON support	Rich: JSONB, arrays, custom types, extensions
Reputation	Easy to start, ubiquitous	Strong data integrity, complex queries

Rule of thumb: pick what the app expects. MariaDB and MySQL are interchangeable for most apps; Postgres is favored when data correctness and complex queries matter.

7. The per-app credentials rule (lab rule)

Every app gets its OWN database, its OWN user, and its OWN password. Never point an app at the root/admin account. Why:

• Blast radius — if one app's credentials leak, only that app's data is exposed.
• Isolation — apps can't read or corrupt each other's tables.
• Auditability — you can see which user/app is doing what, and revoke one without touching others.
• Least privilege — grant only on that app's database (appdb.*), not server-wide.

So the workflow for a new app is always: connect as admin once → CREATE DATABASE → CREATE USER with a unique password → GRANT only on that database → hand those app-specific credentials to the app. The admin/root login stays for administration only.

Dig deeper

• PostgreSQL official documentation
• MariaDB Knowledge Base
• DigitalOcean: How To Install MySQL on Ubuntu
• DigitalOcean: How To Install and Use PostgreSQL on Ubuntu
• DigitalOcean: SQLite vs MySQL vs PostgreSQL comparison

Search terms

• what is a relational database for beginners
• mysql create database user grant privileges
• postgresql create user grant database
• mariadb vs mysql differences
• postgresql pg_hba.conf allow remote connection
• principle of least privilege database accounts

Check yourself

1. What does "relational" mean, and what language do you use to query an RDBMS?
2. Which command gives you an admin shell on PostgreSQL, and which on MySQL/MariaDB?
3. In MySQL, what does 'appuser'@'10.100.100.%' specify beyond just the username?
4. Name two reasons each app should have its own database and user instead of using root.
5. Which lab VM/IP runs PostgreSQL, and which runs MySQL?