PR0306 — Backup i seguretat amb Docker

Informació de la pràctica

Camp	Valor
Codi	PR0306
mòdul	M0372 — Gestió de Bases de Dades
RA	RA6 — Seguretat i còpia de seguretat
Durada estimada	3 hores
Modalitat	Individual
Lliurament	Scripts Bash/SQL + informe PDF a Moodle
Qualificació	10 punts (rúbrica adjunta)

Objectius

• Implementar una estratègia de backup per a PostgreSQL usant pg_dump.
• Realitzar una restauració completa i verificar la integritat de les dades.
• Crear usuaris amb privilegis diferenciats seguint el principi del mínim privilegi.
• Verificar que els permisos funcionen correctament.

---

Apartat 1 — Còpies de seguretat (4 punts)

1.1 Backup en múltiples formats

Executeu els seguents backups de la vostra BD de pràctiques:

# 1. Backup en format SQL plain
docker exec postgres-nom-cognom pg_dump -U gbd_user gbd_nom_cognom \
    > backup_sql_$(date +%Y%m%d).sql

# 2. Backup en format binari custom
docker exec postgres-nom-cognom pg_dump -U gbd_user -F c gbd_nom_cognom \
    > backup_custom_$(date +%Y%m%d).dump

# 3. Backup només de l'esquema (sense dades)
docker exec postgres-nom-cognom pg_dump -U gbd_user --schema-only gbd_nom_cognom \
    > schema_only_$(date +%Y%m%d).sql

# 4. Backup només de les dades (sense DDL)
docker exec postgres-nom-cognom pg_dump -U gbd_user --data-only gbd_nom_cognom \
    > data_only_$(date +%Y%m%d).sql

Tasca 1.1: Per a cada backup: - Documenteu la comanda executada. - Registreu la mida del fitxer generat. - Inspeccioneu el contingut del fitxer SQL: identifiqueu el CREATE TABLE i els INSERT d'una taula concreta.

1.2 Script de backup automatitzat

Creeu un script Bash backup_auto.sh que: 1. Faci un backup en format binari amb pg_dump. 2. Comprovi si el backup s'ha completat correctament (codi de retorn). 3. Elimini els backups de més de 7 dies. 4. Generi un fitxer de log amb la data, el nom del fitxer i la mida.

#!/bin/bash
CONTAINER="postgres-nom-cognom"
DB="gbd_nom_cognom"
USER="gbd_user"
BACKUP_DIR="./backups"
LOG_FILE="./backups/backup.log"
DATE=$(date +%Y%m%d_%H%M%S)

# Completeu l'script

---

Apartat 2 — Restauració (3 punts)

2.1 Restauració a una BD nova

# Crear una nova BD per a la restauració
docker exec postgres-nom-cognom createdb -U gbd_user gbd_nom_cognom_restore

# Restaurar el backup binari
docker exec -i postgres-nom-cognom pg_restore \
    -U gbd_user -d gbd_nom_cognom_restore < backup_custom_*.dump

Tasca 2.1: Verifiqueu la restauració:

-- Compareu el nombre de files de cada taula entre la BD original i la restaurada
SELECT tablename,
       (SELECT COUNT(*) FROM <taula>) AS files_original
FROM pg_tables WHERE schemaname = 'public';

2.2 Simulació de desastre i recuperació

1. Simuleu un desastre: Elimineu accidentalment una taula de la BD original:

   DROP TABLE usuaris CASCADE;
   -- Verifiqueu que la taula ja no existeix
   \dt
   

2. Recupereu la taula des del backup:

   # Restaurar només la taula 'usuaris'
   docker exec -i postgres-nom-cognom pg_restore \
       -U gbd_user -d gbd_nom_cognom --table=usuaris < backup_custom_*.dump
   

3. Verifiqueu la recuperació: SELECT COUNT(*) FROM usuaris;

Tasca 2.2: Documenteu cada pas amb captures de pantalla.

---

Apartat 3 — Seguretat i permisos (3 punts)

3.1 Creació d'usuaris

-- Connecteu-vos com a superusuari
docker exec -it postgres-nom-cognom psql -U postgres

-- Crear els usuaris
CREATE USER app_web_nom WITH PASSWORD 'web2025';
CREATE USER analista_nom WITH PASSWORD 'analista2025';
CREATE USER dba_nom WITH PASSWORD 'dba2025';

3.2 Creació de rols i assignació de privilegis

Creeu un fitxer seguretat.sql que implementi l'esquema de seguretat complet:

-- Crear rols
CREATE ROLE rol_lectura_nom;
CREATE ROLE rol_crud_nom;

-- Assignar privilegis al rol de lectura
GRANT CONNECT ON DATABASE gbd_nom_cognom TO rol_lectura_nom;
GRANT USAGE ON SCHEMA public TO rol_lectura_nom;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rol_lectura_nom;

-- Assignar privilegis al rol crud
GRANT rol_lectura_nom TO rol_crud_nom;
GRANT INSERT, UPDATE, DELETE ON TABLE reserves TO rol_crud_nom;
GRANT INSERT, UPDATE, DELETE ON TABLE usuaris TO rol_crud_nom;

-- Assignar rols als usuaris
GRANT rol_lectura_nom TO analista_nom;
GRANT rol_crud_nom TO app_web_nom;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dba_nom;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO dba_nom;

3.3 Verificació dels permisos

Connecteu-vos amb cada usuari i verifiqueu:

# Connexió com a app_web
docker exec -it postgres-nom-cognom psql -U app_web_nom -d gbd_nom_cognom

-- Prova 1: SELECT (hauria de funcionar per a app_web)
SELECT * FROM reserves LIMIT 5;

-- Prova 2: INSERT (hauria de funcionar per a app_web)
INSERT INTO usuaris (nom, cognoms, email, dni)
VALUES ('Test', 'User', 'test@test.com', '00000000T');

-- Prova 3: DROP TABLE (hauria de FALLAR per a app_web)
DROP TABLE reserves;

-- Connectar-se com a analista i provar:
-- SELECT (hauria de funcionar)
-- INSERT (hauria de FALLAR)

Tasca 3.3: Documenteu cada prova amb la comanda executada, l'usuari que l'executa i el resultat (exitós o error). Expliqueu per que cada resultat és l'esperat.

---

Entrega

• Scripts de backup: backup_auto.sh
• Fitxer seguretat.sql amb tots els GRANT/REVOKE/CREATE USER/CREATE ROLE.
• Informe PDF amb captures de pantalla de cada apartat i respostes de reflexió.
• Reflexió final: Quins riscos de seguretat existirien si tots els usuaris de l'aplicació accedissin amb l'usuari superusuari (root/postgres)? Quins principis de seguretat s'infringiran?