LFCS Database Services: Start, Secure, and Back Up MariaDB and PostgreSQL

Intermediate30 min7 steps

What you'll learn

Install a database server, start it, and make it survive a reboot — including the PostgreSQL init step that trips everyone up
Lock a fresh server down: run mysql_secure_installation and bind the listener to localhost
Create a per-app database with a least-privilege user in both MariaDB and PostgreSQL
Explain how pg_hba.conf decides who may log in, and why 'psql -U appuser' fails locally
Back up a live server safely with mysqldump / pg_dump, automate it, and test the restore

Why 'install' is only step zero
Frame database operations as a four-part job — install, secure, scope, back up — not just 'apt install'.
Installing a database server is the easy part. The package manager drops the binaries and a systemd unit, and you're done in one command. But a database holds the one thing on the box you can't recreate from a template: your data. So the LFCS exam — and real life — judge you on what comes after the install.
Think of a fresh database like a brand-new shop with the front door wide open and the till empty. Anyone can wander in, and there's nothing organized inside yet. Your job is four moves, in order:
- Start it so it's actually open for business (and stays open after a reboot).
- Secure it — set a lock on the door before you put anything valuable inside.
- Scope it — give each application its own stockroom and a key that opens only that room.
- Back it up — and prove the backup actually restores, because a backup you've never tested is a rumor.
We'll do all four for the two engines LFCS covers: MariaDB (the MySQL-compatible default on most distros) and PostgreSQL.
Why it matters
The exam phrases this as 'configure a database service', but the grading checks the outcome: is the service enabled, is a scoped user present, does a dump exist? Learn the four-move habit and you satisfy the objective without memorizing a script.
Install, start, and persist the server
Get each engine running and enabled — and meet the PostgreSQL init trap before it bites you.
Installing the server package gives you a systemd unit, so persistence is the same enable --now you learned in the systemctl lesson: start it now and mark it to start at boot. The two engines differ in one critical way, which we'll hit in a moment.
Example — not runnable on your lab box
```
$ sudo dnf install -y mariadb-server
```
```
Installed:
  mariadb-server-10.11.6-1.el9.x86_64

Complete!
```
install
Fetch and install the named package and its dependencies.
-y
Assume 'yes' to prompts — needed for non-interactive/automated runs.
On Debian/Ubuntu the equivalent is sudo apt install mariadb-server. The package name to remember is *-server: the bare mariadb package is only the client, and installing it alone leaves you with no daemon to manage.
```
$ sudo systemctl enable --now mariadb
```
```
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
```
enable
Create the boot symlink so the service returns after a reboot.
--now
Also start it immediately, in the same command.
MariaDB starts cleanly the moment it's installed — its data directory is initialized for you by the package. Verify with systemctl is-active mariadb, which should print active. PostgreSQL does not behave this way, which is the next block.
Now PostgreSQL. The package installs the binaries but deliberately leaves the data directory empty. The server cannot start against an empty data directory, so you must initialize it first with postgresql-setup --initdb. Skip this and systemctl start fails — the single most common PostgreSQL stumble.
Example — not runnable on your lab box
```
$ sudo dnf install -y postgresql-server && sudo postgresql-setup --initdb
```
```
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
```
--initdb
Create the cluster's data directory — system catalogs, the default 'postgres' database, and the config files (postgresql.conf, pg_hba.conf).
--initdb builds /var/lib/pgsql/data from nothing. Only after this exists can the daemon start. On Debian/Ubuntu the package runs initdb for you automatically, but on RHEL-family distros (what LFCS targets) it's a manual step you must not forget.
```
$ sudo systemctl enable --now postgresql
```
```
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
```
This succeeds because the data directory now exists. Run the same line before --initdb and you'd get Job for postgresql.service failed — a failure that confuses people because the install itself reported success.
PostgreSQL won't start before --initdb
If systemctl start postgresql fails on a fresh install, your first check is the data directory. sudo journalctl -u postgresql will mention an empty or missing PGDATA. The fix is sudo postgresql-setup --initdb, then start. MariaDB never needs this — only PostgreSQL leaves the directory uninitialized.
Check yourself
You install postgresql-server and run 'systemctl enable --now postgresql', but it fails to start. What step did you most likely skip?
Secure the fresh server
Close the open front door before any data goes in — set credentials and limit who can reach the listener.
A freshly installed MariaDB ships wide open: a blank-password root account, anonymous logins, and a throwaway test database. MariaDB bundles a guided cleanup for exactly this — mysql_secure_installation, an interactive script that walks you through hardening in one pass.
Example — not runnable on your lab box
```
$ sudo mysql_secure_installation
```
```
Enter current password for root (enter for none):
Switch to unix_socket authentication [Y/n]
Set root password? [Y/n] Y
Remove anonymous users? [Y/n] Y
Disallow root login remotely? [Y/n] Y
Remove test database and access to it? [Y/n] Y
Reload privilege tables now? [Y/n] Y
```
Answer Y to every prompt on a real server. Each one closes a specific hole: a root password stops blank-password logins, removing anonymous users stops unauthenticated access, disallowing remote root forces admin access to come from the box itself, and dropping test removes a database any user could write to. The final reload makes the changes take effect immediately.
Hardening the accounts isn't enough if the server is also reachable from the whole network. By default you want the database to listen on localhost only, so the only way in is from an app running on the same machine (or through an SSH tunnel). Open it to the network only when a genuinely remote client needs it.
Example — not runnable on your lab box
```
$ grep -E '^bind-address|^listen_addresses' /etc/my.cnf.d/*.cnf /var/lib/pgsql/data/postgresql.conf
```
```
/etc/my.cnf.d/mariadb-server.cnf:bind-address=127.0.0.1
/var/lib/pgsql/data/postgresql.conf:listen_addresses = 'localhost'
```
-E
Use extended regex so the '^a|^b' alternation works without backslashes.
MariaDB's bind-address=127.0.0.1 and PostgreSQL's listen_addresses = 'localhost' both mean: accept connections only from this host. To grade a remote-access task you might set listen_addresses = '*' and add a pg_hba.conf rule for the client's IP — but the secure default, and the right answer when the task doesn't demand remote access, is localhost.
Binding to 0.0.0.0 exposes the database
Setting bind-address=0.0.0.0 or listen_addresses = '*' makes the server answer on every interface — including any public one. Combined with a weak password that's a direct path to data theft. Only widen the bind address when a specific remote client requires it, and pair it with a firewall rule and a tightly-scoped account.
Check yourself
Which one-shot interactive script hardens a fresh MariaDB by setting the root password and removing anonymous users and the test database?
Scope: a per-app database and a least-privilege user
Give each application its own database and a login that can touch only that database — nothing else.
Never let an application connect as the database superuser. If that app is compromised, the attacker inherits the keys to every database on the server. The principle is least privilege: one database per app, one login per app, and that login may touch only its own database.
The analogy: don't hand a tenant the building master key when all they need is the key to their own apartment. We'll create an appdb database and an appuser who can work inside it and nowhere else — in both engines.
Example — not runnable on your lab box
```
$ sudo mariadb
```
```
Welcome to the MariaDB monitor.  Commands end with ; or \g.
MariaDB [(none)]>
```
After mysql_secure_installation switched root to unix_socket auth, sudo mariadb logs the admin in with no password — the OS identity is the credential. From this prompt you issue SQL to create the app's database and user.
Example — not runnable on your lab box
```
$ CREATE DATABASE appdb;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'S3cure!pass';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
```
```
Query OK, 1 row affected (0.001 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.001 sec)
```
The scoping lives in *`ON appdb.** — it grants privileges on every table in appdb and *only* appdb. The **'appuser'@'localhost'** host part restricts this login to connections from the local machine. appuser cannot see, let alone touch, any other database on the server. FLUSH PRIVILEGES` reloads the grant tables so the change takes effect.
PostgreSQL expresses the same idea with slightly different vocabulary. A login is a role with the LOGIN attribute (a 'user' is just a role that can log in), and you grant privileges on the database object. Run these as the postgres superuser.
```
$ sudo -u postgres psql -c "CREATE ROLE appuser LOGIN PASSWORD 'S3cure!pass';" -c "CREATE DATABASE appdb OWNER appuser;"
```
```
CREATE ROLE
CREATE DATABASE
```
-u postgres
Run psql as the OS 'postgres' user, who is the database superuser under peer authentication.
-c
Run one SQL command and exit; repeat -c to run several in order.
CREATE ROLE appuser LOGIN makes a login; without LOGIN it would be a group role that can't connect. Making appuser the OWNER of appdb gives it full control of that database and nothing else — the Postgres equivalent of GRANT ALL ON appdb.*. For a non-owner you'd instead GRANT CONNECT ON DATABASE appdb TO appuser plus table grants.
MariaDB host part vs Postgres pg_hba
Notice where the 'who can connect from where' rule lives. In MariaDB it's baked into the account: 'appuser'@'localhost'. In PostgreSQL the account has no host attached — connection origin is decided separately, in pg_hba.conf. That separation is exactly what the next step is about.
Check yourself
In MariaDB, which part of 'GRANT ALL PRIVILEGES ON appdb.* TO appuser' is what limits the user to a single database?
PostgreSQL authentication: pg_hba.conf
Understand the rule file that decides who may connect — and why 'psql -U appuser' fails locally by default.
PostgreSQL decides who is allowed to connect, from where, and how they must prove it in one file: `pg_hba.conf` (the 'HBA' stands for host-based authentication). Postgres reads it top to bottom and uses the first matching line — like a firewall rule list. Each line names a connection type, a database, a user, an origin, and an auth method. The methods you must know:
- `peer` — for local socket connections, trust the OS username: the database role you ask for must match your Linux username. No password.
- `password` / `md5` / `scram-sha-256` — prompt for a password and verify it (scram is the modern, strongest hash; md5 is the legacy one).
Think of peer like a members' club where the doorman knows your face — no card needed, but you can only be let in under your own name.
Example — not runnable on your lab box
```
$ sudo grep -vE '^\s*#|^\s*$' /var/lib/pgsql/data/pg_hba.conf
```
```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
```
-v
Invert the match — print lines that do NOT match the pattern.
-E
Extended regex, so '^\s#|^\s$' filters comment lines and blank lines in one expression.
Read the columns: the first line says local socket connections to all databases for all users use `peer`. So a local psql succeeds only if your OS user matches the role you request. The host lines cover TCP connections from localhost (127.0.0.1 and the IPv6 ::1) and require a scram-sha-256 password — that's the path a password-based app login takes.
Why 'psql -U appuser' fails locally
You created appuser with a password, then as your own Linux user you run psql -U appuser and get FATAL: Peer authentication failed for user "appuser". Nothing is wrong with the password — the local line uses `peer`, which demands your OS user be appuser. Two fixes: connect over TCP so a host line with a password method matches — psql -h 127.0.0.1 -U appuser -d appdb — or change the relevant pg_hba.conf line's method to a password method and reload. This is the single most common PostgreSQL login confusion.
```
$ sudo systemctl reload postgresql
```
After editing pg_hba.conf, the running server hasn't re-read it yet. `reload` (silent on success) tells Postgres to re-read its config without dropping existing connections — preferable to restart. Forgetting this reload is why people edit the file 'correctly' and still get the old behavior.
Check yourself
Your pg_hba.conf has 'local all all peer'. As Linux user 'deploy' you run 'psql -U appuser' and it fails. Which auth method is rejecting you?
Back up a live server — and never copy the data directory
Take a consistent backup from a running server with mysqldump / pg_dump, automate it, and prove the restore.
The instinct of a new admin is to cp -r the data directory and call it a backup. Do not. A running database keeps files mid-write, holds changes in memory, and updates many files in a sequence — copying them while the server is live gives you an inconsistent, often unrestorable snapshot. It's like photographing a chef mid-chop: you freeze one hand on the knife and the other reaching for an onion, and the picture can never be 'un-paused' into a working kitchen.
The right tool asks the running server for a consistent logical dump — the SQL needed to rebuild everything — so the export reflects a single coherent moment. mysqldump for MariaDB, pg_dump for PostgreSQL.
Example — not runnable on your lab box
```
$ mysqldump -u root --single-transaction --all-databases > /backups/mariadb-$(date +%F).sql
```
--single-transaction
Take the dump inside one transaction so all tables reflect the same instant — a consistent snapshot without locking writers out.
--all-databases
Dump every database; swap for a single db name (e.g. 'appdb') to back up just one app.
This writes a plain-SQL file the server produced while running. --single-transaction is what makes it consistent on InnoDB tables. The $(date +%F) stamps the filename with today's date (YYYY-MM-DD), so successive runs don't overwrite each other. Restore is simply mysql < the-file.sql.
Example — not runnable on your lab box
```
$ pg_dump -U postgres -Fc appdb > /backups/appdb-$(date +%F).dump
```
-Fc
Custom compressed format — smaller, and restorable selectively with pg_restore.
appdb
The database to dump; use pg_dumpall instead to also capture roles and all databases.
pg_dump likewise reads from the live server and produces a consistent snapshot of appdb. The custom format (-Fc) is restored with pg_restore -d appdb the-file.dump (not psql), and lets you restore individual tables. For a full-cluster backup including roles, use pg_dumpall.
A backup you run by hand once is worthless when the disk dies at 3am. Automate it with cron (or a systemd timer) and make sure the output lands somewhere off the database disk.
```
$ echo '15 2 * * * postgres pg_dump -Fc appdb > /backups/appdb-$(date +\%F).dump' | sudo tee /etc/cron.d/appdb-backup
```
```
15 2 * * * postgres pg_dump -Fc appdb > /backups/appdb-$(date +%F).dump
```
This cron line runs the dump every day at 02:15 as the postgres user. In a crontab the % is special, so it's escaped as \%. A scheduled dump is only half the job — you also need monitoring that the file appears and is non-empty, or a silent failure leaves you with no backups and no warning.
An untested backup is not a backup
The only proof a backup works is restoring it. Periodically load the dump into a throwaway database — pg_restore -d testrestore appdb-2026-06-23.dump or mysql testrestore < dump.sql — and confirm the row counts. Plenty of teams discover their backups were empty or truncated during the disaster, which is the worst possible time.
Copying the live datadir = corrupt backup
cp -r /var/lib/pgsql/data /backups/ or tarring /var/lib/mysql on a running server captures files mid-write and gives an inconsistent, frequently unrestorable copy. A raw file-level copy is only valid if the server is stopped (or you use a purpose-built physical-backup tool like Percona XtraBackup / pg_basebackup that coordinates with the running server). For LFCS, reach for the logical dump (mysqldump / pg_dump).
Check yourself
Why is 'cp -r /var/lib/mysql /backups/' on a running MariaDB server a bad backup?
Try it in your live terminal
Inspect what's installed and running on the real sandbox to your right — read-only, no changes made.
You have a real shell. These commands only inspect — they install nothing and change nothing, so they're safe to run even on a box where no database is present. The point is to recognize the moves: is a client installed, and is a server active? On this sandbox the databases may well be absent; treat any 'not found' as a finding, not an error.
```
$ mysql --version 2>/dev/null || mariadb --version 2>/dev/null || echo 'no mariadb'
```
Checks whether a MariaDB/MySQL client exists. The 2>/dev/null hides the error if the binary is missing, and || falls through to the next attempt — so you get a version string if installed, or a clean 'no mariadb' if not. A missing client usually means the server isn't installed either.
```
$ psql --version 2>/dev/null || echo 'no postgres'
```
Same idea for PostgreSQL's client. psql ships with the client package, so its presence tells you Postgres tooling is installed; its absence prints 'no postgres'.
```
$ systemctl is-active mariadb postgresql 2>/dev/null
```
Asks systemd whether each service is running right now. Expect active, inactive, or unknown (the latter when the unit doesn't exist). This is the same is-active check from the systemctl lesson — confirming the 'start it' move actually took hold.

Recap

Install is step zero. The real work is start → secure → scope → back up, in that order.
MariaDB initializes its data dir on install; PostgreSQL needs `postgresql-setup --initdb` first or it won't start.
Give every app its own database and a least-privilege login (ON appdb.* / role owning one db), and bind to localhost unless remote access is truly required.
PostgreSQL connection rights live in `pg_hba.conf`: peer means your OS user must match the role, which is why psql -U appuser fails locally.
Back up a running server with mysqldump / pg_dump, automate it, and test the restore — never copy a live data directory.

MariaDB ↔ PostgreSQL equivalents
Task	MariaDB	PostgreSQL
Install server	`dnf install mariadb-server`	`dnf install postgresql-server`
First-run init	automatic on install	`postgresql-setup --initdb` (required)
Enable + start	`systemctl enable --now mariadb`	`systemctl enable --now postgresql`
Harden a fresh server	`mysql_secure_installation`	edit `pg_hba.conf` + set role passwords
Admin login (local)	`sudo mariadb` (unix_socket)	`sudo -u postgres psql` (peer)
Create app database	`CREATE DATABASE appdb;`	`CREATE DATABASE appdb OWNER appuser;`
Create scoped login	`CREATE USER 'appuser'@'localhost' …`	`CREATE ROLE appuser LOGIN PASSWORD …`
Grant on one db only	`GRANT ALL ON appdb.* TO appuser`	ownership, or `GRANT CONNECT ON DATABASE appdb`
Who-can-connect rules	host part of the account	`pg_hba.conf` (peer / scram / md5)
Bind to localhost	`bind-address=127.0.0.1`	`listen_addresses = 'localhost'`
Logical backup (live)	`mysqldump --single-transaction`	`pg_dump -Fc`
Restore	`mysql < dump.sql`	`pg_restore -d appdb dump`

LFCS Database Services: Start, Secure, and Back Up MariaDB and PostgreSQL

Why 'install' is only step zero

Install, start, and persist the server

Secure the fresh server

Scope: a per-app database and a least-privilege user

PostgreSQL authentication: pg_hba.conf

Back up a live server — and never copy the data directory

Try it in your live terminal