Quedaven dues preguntes de la regidoria de Vallmar sense respondre des del primer dia d'aquest mòdul, i cap de les dues no s'arregla amb un índex.

La primera la va fer la regidora de Cultura a la reunió de seguiment: "qui pot consultar els telèfons i els correus dels dotze mil socis?". Ningú no va saber respondre, que és la pitjor resposta possible. La segona la va fer l'interventor: "què passaria si el disc del servidor morís aquesta nit?". Algú va dir "tenim còpies", i en preguntar quan s'havia provat l'última restauració, es va fer el silenci.

Aquesta lliçó respon les dues, i tanca el mòdul i el bloc teòric del curs.

Hi ha una idea que convé posar per davant, perquè ordena tot el que segueix: la base de dades és l'última frontera. Si algú travessa el tallafoc, enganya l'aplicació o troba una credencial en un repositori, l'única cosa que queda entre aquella persona i les dades de dotze mil veïns de Vallmar són els permisos de la base de dades. I si l'edifici s'incendia, l'única cosa que queda entre la biblioteca i començar de zero són les còpies de seguretat —provades, no simplement existents—.

Veurem autenticació i autorització amb rols i GRANT; el principi de mínim privilegi aplicat a tres rols concrets de BiblioRed; vistes i seguretat a nivell de fila perquè cada sucursal vegi només el seu; la injecció SQL i per què les consultes parametritzades són l'única defensa que funciona; el xifratge en trànsit i en repòs; l'auditoria; el tractament de les dades personals; i el bloc complet de còpies de seguretat, on el registre WAL de 06-01 reapareix convertit en l'eina que permet recuperar la base de dades a l'instant anterior al desastre.

1. Per què la seguretat de la base de dades és diferent de la de l'aplicació
2. Autenticació: rols, contrasenyes i pg_hba.conf
3. Autorització: GRANT, REVOKE i el catàleg de privilegis
4. Mínim privilegi: els tres rols de BiblioRed
5. El perill de connectar-se com a superusuari
6. Vistes per amagar columnes sensibles
7. Seguretat a nivell de fila: cada sucursal veu el seu
8. Injecció SQL: com es produeix i com s'impedeix
9. Xifratge en trànsit, en repòs i el cas especial de les contrasenyes
10. Auditoria: registre d'accessos i taula de canvis
11. Dades personals: minimització, pseudonimització i retenció
12. Còpies de seguretat: lògiques i físiques
13. Completa, diferencial i incremental
14. Arxivat del WAL i recuperació a un instant concret
15. La regla 3-2-1 i la verificació de les còpies
16. RPO i RTO aplicats a BiblioRed
17. El pla de còpies de BiblioRed, comentat
18. Els primers minuts després d'un esborrat accidental
19. L'equivalent mínim a SQLite

És habitual pensar que la seguretat es resol a la capa d'aplicació: si la pantalla de llistat de socis només la veu el personal autoritzat, les dades estan protegides. És una idea perillosa, i n'hi ha prou d'enumerar els camins que arriben a la base de dades sense passar per l'aplicació per veure-ho:

Sis camins, i cinc no veuen ni una sola línia del codi de l'aplicació. D'aquí el principi:

Els controls d'accés han d'estar on són les dades. L'aplicació pot afegir comoditat i context, però la garantia ha de viure a la base de dades.

És exactament el mateix argument que hem fet servir a 04-04 per a les restriccions i a 06-02 per a l'aforament del club de lectura: una regla que no es pot trencar ha d'estar on no es pugui saltar. Aquí la regla és "el personal de taulell no pot exportar els correus dels socis".

Les capes de seguretat d'un sistema, de fora cap endins:

Les dues preguntes centrals d'aquesta lliçó són la tercera i la quarta caixa: autenticació (qui ets?) i autorització (què pots fer?). Es confonen constantment i són coses diferents.

PostgreSQL no distingeix entre "usuari" i "grup": té rols. Un rol amb l'atribut LOGIN es pot connectar; un rol sense ell serveix com a grup de permisos. CREATE USER és simplement una drecera de CREATE ROLE ... LOGIN.

Atributs que convé conèixer:

Inspeccionar el que hi ha:

Abans de comprovar la contrasenya, PostgreSQL consulta el fitxer pg_hba.conf (host-based authentication). És una llista de regles que s'avalua de dalt a baix, i s'aplica la primera que encaixa. Si no n'encaixa cap, la connexió es rebutja.

Els mètodes, amb la seva valoració:

Sobre trust. Significa literalment "qualsevol que arribi per aquesta via entra com l'usuari que digui ser, sense contrasenya". El seu únic ús legítim és una instància local de desenvolupament al teu propi equip, sense dades reals i sense port obert a l'exterior. Una línia host all all 0.0.0.0/0 trust en un servidor equival a publicar la base de dades a internet sense porta. Apareix amb més freqüència de la que ningú voldria admetre, gairebé sempre perquè algú la va posar "un moment, per provar" i ningú no la va treure.

Fixa't en la línia hostssl per a direcció: obliga que la connexió vingui xifrada. I l'última línia, reject, converteix la llista en una política de denegar per omissió, que és com s'han d'escriure totes les llistes de control d'accés.

Després d'editar el fitxer:

Comprovar quines regles estan actives:

Autenticat el rol, la pregunta següent és què pot fer. El model de PostgreSQL és jeràrquic: per arribar a una taula cal travessar la base de dades i l'esquema.

És l'error número u en configurar permisos: donar SELECT sobre les taules i oblidar el USAGE sobre l'esquema. Sense USAGE, el rol no pot ni veure que la taula existeix.

Dues formes avançades imprescindibles:

Privilegis a nivell de columna. Es pot autoritzar la lectura d'unes columnes i no d'unes altres:

Aquell rol podrà llegir el nom d'un soci però no el seu email. Si ho intenta:

Privilegis per omissió per a objectes futurs. Un GRANT ON ALL TABLES afecta les taules que existeixen avui. La taula que algú creï demà no hi estarà inclosa, i aquesta és una fallada silenciosa clàssica:

PostgreSQL té un pseudo-rol anomenat PUBLIC al qual pertanyen tots. Històricament, PUBLIC tenia CREATE sobre l'esquema public, cosa que permetia a qualsevol usuari crear-hi taules. Des de PostgreSQL 15 ja no és així, però en instal·lacions més antigues —o migrades— convé comprovar-ho i corregir-ho:

Inspeccionar els permisos concedits:

Les lletres: r = SELECT, a = INSERT, w = UPDATE, d = DELETE, x = REFERENCES, U = USAGE.

Principi de mínim privilegi. Cada rol rep exactament els permisos que necessita per a la seva funció, ni un més, i només mentre els necessita.

BiblioRed necessita tres perfils. Els definim primer en llenguatge planer, perquè un permís que no es pot explicar en una frase sol estar mal pensat:

Un permís que no s'ha provat és una suposició. La comprovació es fa suplantant el rol:

Dos errors esperats i una consulta correcta: els permisos fan el que diu el paper.

Existeix a més una funció per consultar-ho sense executar res:

És la mala pràctica més estesa i la més fàcil de corregir.

Quan una aplicació es connecta amb un rol superusuari —postgres a la majoria d'instal·lacions—, tota la feina dels apartats anteriors queda anul·lada. Un superusuari se salta totes les comprovacions de permisos, inclosa la seguretat a nivell de fila de l'apartat 7.

Què pot fer un atacant que aconsegueixi la connexió de l'aplicació:

Aquella última fila converteix una injecció SQL de gravetat mitjana en un compromís total del servidor.

La llista de comprovació:

• La cadena de connexió de l'aplicació mai no usa postgres ni cap rol amb SUPERUSER.
• El propietari de les taules és un rol d'administració diferent del que usa l'aplicació.
• Les contrasenyes viuen en un gestor de secrets o en variables d'entorn del servei, mai al repositori de codi.
• Cada persona té el seu propi rol de connexió. Un compte compartit fa impossible l'auditoria de l'apartat 10.

Verificar que no hi ha superusuaris de més:

Un. Si n'apareixen tres o quatre, hi ha feina a fer.

Els privilegis de columna de l'apartat 3 funcionen bé, però tenen un inconvenient pràctic: SELECT * falla, i moltes eines d'informes l'usen. L'alternativa elegant és una vista.

Una vista s'executa amb els permisos de qui la va crear, no de qui la consulta. Això permet donar accés a un subconjunt de dades sense donar accés a la taula subjacent.

Comprovació:

SELECT * funciona, i els correus i telèfons són inabastables.

Altres vistes útils del mateix tipus a BiblioRed:

Un advertiment sobre l'emmascarament: serveix perquè el personal de suport pugui verificar un correu que el soci dicta per telèfon, no per anonimitzar. Un emmascarament no és una anonimització, i a l'apartat 11 veurem la diferència.

Nota tècnica: per omissió les vistes són SECURITY INVOKER quant a la seguretat a nivell de fila de l'apartat següent, però s'executen amb els permisos del propietari respecte a les taules. Si necessites que la vista apliqui les polítiques de fila de l'usuari que consulta, declara-les amb WITH (security_invoker = true), disponible des de PostgreSQL 15.

Les vistes amaguen columnes. Per amagar files —que el personal de la sucursal Nord no vegi els préstecs de la sucursal Sud— PostgreSQL ofereix la seguretat a nivell de fila (row level security, RLS).

Amb RLS, cada taula pot portar polítiques que actuen com un WHERE implícit i obligatori, aplicat pel gestor a tota consulta dels rols afectats.

Atenció: amb RLS activada i sense cap política, ningú no veu res. El comportament per omissió és denegar, que és el correcte.

La política necessita saber a quina sucursal és l'usuari. L'aplicació ho comunica amb un paràmetre de sessió en obrir la connexió:

Les clàusules d'una política:

Sense WITH CHECK, un rol podria inserir files que després no pot veure, cosa que sol ser un error. La política completa per a socis:

La mateixa consulta, el mateix rol, resultats diferents: el gestor està aplicant el filtre pel seu compte. I és impossible saltar-se'l des de SQL:

1. Els superusuaris i els propietaris de la taula se salten RLS. Si l'aplicació es connecta com a propietària de les taules, les polítiques no s'apliquen. Cal forçar-ho amb ALTER TABLE socis FORCE ROW LEVEL SECURITY;.
2. Té cost de rendiment. La política es converteix en una condició afegida a cada consulta, i les polítiques amb subconsultes com la de prestecs poden canviar el pla d'execució. Comprova-ho amb EXPLAIN ANALYZE (06-03).
3. El paràmetre de sessió l'ha de fixar l'aplicació de manera fiable i no l'ha de poder alterar l'usuari final. Si l'usuari pot influir en app.sucursal_actual, la política no protegeix de res.

És la vulnerabilitat més antiga i més coneguda de les aplicacions amb base de dades, i continua apareixent cada any a les llistes d'incidents. La seva causa és sempre la mateixa: barrejar codi i dades a la mateixa cadena de text.

El cercador del catàleg de BiblioRed construeix la consulta concatenant el que l'usuari escriu:

Amb una cerca normal, q = mapa, la consulta resultant és correcta:

Ara un visitant escriu al cercador:

La consulta que arriba al servidor és:

Els dotze mil correus dels socis de Vallmar, al cercador públic del catàleg. Sense contrasenya, sense eines i sense deixar més rastre que una línia al registre de consultes.

I això és només la lectura. El que permet una injecció, en general:

Aquella última fila explica per què els apartats 4 i 5 són part de la defensa contra la injecció: amb bibliored_app ben limitat, la mateixa injecció no pot llegir multes, ni esborrar res, ni tocar el sistema operatiu. El dany d'una injecció és exactament igual als permisos de la connexió.

Una consulta parametritzada envia el text SQL i els valors per camins separats. El gestor rep primer l'estructura de la consulta i després les dades, així que és impossible que una dada s'interpreti com a codi. No hi ha res a escapar perquè no hi ha res a barrejar.

Amb el mateix atac, el resultat és:

La base de dades ha buscat, literalment, materials el títol dels quals contingui la cadena ' UNION SELECT soci_id, email FROM socis --. No n'hi ha cap. L'atac s'ha convertit en una cerca sense resultats, que és exactament el que ha de ser.

En SQL directe, dins de psql o d'una funció, l'equivalent és PREPARE:

És temptador pensar que n'hi ha prou de "treure les cometes". No n'hi ha prou, per cinc raons:

Una consulta parametritzada no té cap d'aquests problemes perquè no escapa res: separa els camins.

De vegades la part variable és el nom d'una columna d'ordenació, i això no es pot parametritzar. La solució no és escapar, és validar contra una llista blanca:

L'entrada de l'usuari mai no arriba al SQL: només s'usa com a clau per triar entre valors fixos escrits per tu.

Cap no substitueix la parametrització; totes redueixen el dany:

• Mínim privilegi (apartat 4): que la connexió no pugui llegir el que no li toca.
• Validació d'entrada: comprovar que un identificador és un enter, que una data és una data.
• Mai no mostris el missatge d'error de la base de dades a l'usuari final. Un ERROR: column "socis.email" does not exist és un mapa de l'esquema regalat.
• Registrar els errors de SQL: una ràfega d'errors de sintaxi des d'una mateixa adreça és un atac en curs.

Sense xifratge, les consultes i els seus resultats viatgen en clar per la xarxa. Qualsevol amb accés al tram pot llegir els correus dels socis a mesura que es transmeten.

I a pg_hba.conf, hostssl en lloc de host per obligar que la connexió vingui xifrada. Del costat del client:

Els modes de sslmode, que importen més del que sembla:

require xifra però no comprova amb qui parla, així que no protegeix d'un intermediari. La configuració correcta en producció és verify-full.

Comprovar l'estat d'una connexió:

Dos nivells, amb propòsits diferents:

El xifratge de disc és barat, transparent i ha d'estar activat sempre. El xifratge per columna és una eina quirúrgica: a BiblioRed no hi ha res que ho justifiqui —no es desen números de targeta ni dades de salut—, i aplicar-lo a email faria impossible buscar per correu i obligaria a custodiar una clau la pèrdua de la qual equivaldria a perdre les dades.

Regla: xifra el disc sempre; xifra columnes només quan puguis anomenar la dada exacta, el risc exacte i qui custodia la clau.

Les contrasenyes dels socis per al portal web mereixen un apartat propi perquè l'error aquí és greu i freqüent.

Una contrasenya no es xifra: es transforma amb una funció d'un sol sentit, deliberadament lenta i amb sal. No cal poder-la recuperar; només cal poder-la comprovar. Per això els sistemes seriosos ofereixen "restablir la contrasenya" i mai "recordar-li la seva contrasenya".

Si la contrasenya és incorrecta, la consulta torna zero files. El paràmetre 12 de gen_salt('bf', 12) és el cost: cada unitat duplica el temps de càlcul, cosa que alenteix els atacs per força bruta sense molestar l'usuari legítim.

Nota important: l'anterior val per a les contrasenyes dels socis a l'aplicació. Les contrasenyes dels rols de PostgreSQL les gestiona el mateix servidor amb SCRAM-SHA-256, i no cal fer res especial més enllà d'usar aquell mètode a pg_hba.conf.

Autenticació, autorització i xifratge responen a "qui pot?". L'auditoria respon a "qui ha fet què, i quan?", que és la pregunta que es fa després d'un incident, i també la que exigeix qualsevol revisió seriosa de tractament de dades personals.

Aquell últim punt mereix atenció: activar log_statement = 'all' en una base amb dades personals significa que els correus i telèfons dels socis acaben escrits als fitxers de registre, que sovint tenen menys protecció i més còpies que la mateixa base de dades. És un cas clar d'una mesura de seguretat que crea un problema de privacitat.

Per saber qui va modificar quina fila i quan, el patró estàndard és una taula d'auditoria alimentada per un activador —els TRIGGER que vam presentar a 05-04—.

Provem-ho:

Quatre decisions de disseny que convé entendre:

• SECURITY DEFINER fa que la funció s'executi amb els permisos de qui la va crear, de manera que el rol de taulell pot generar registres d'auditoria sense tenir permís d'escriptura sobre la taula d'auditoria. És just el que es vol: poder escriure al registre però no poder-lo esborrar.
• current_user identifica el rol de la base de dades. Per això importa que cada persona tingui el seu propi rol de connexió (apartat 5): amb un compte compartit, aquesta columna no diu res útil.
• JSONB per a l'abans i el després evita haver de refer la taula d'auditoria cada vegada que canvia l'esquema de socis. És un ús legítim de jsonb dels que 04-04 anomenava "escapatòries controlades".
• El cost: l'auditoria duplica les escriptures i fa créixer una taula que ningú no consulta cada dia. S'aplica a les taules amb dades sensibles —socis, multes, pagaments—, no a totes.

I un advertiment final: la taula d'auditoria conté també dades personals, incloses les que s'hagin esborrat de la taula original. Necessita els mateixos permisos restrictius i la mateixa política de retenció que les dades que audita.

⚠️ Advertiment important. El que segueix descriu mecanismes tècnics per tractar dades personals en una base de dades. No és assessorament legal. El compliment del Reglament General de Protecció de Dades (RGPD) i de la legislació aplicable a cada jurisdicció —incloses les obligacions específiques d'una administració pública com l'ajuntament de Vallmar— l'ha de revisar un professional de compliance o el delegat de protecció de dades de l'organització. Les decisions sobre quines dades es poden recollir, amb quina base jurídica, durant quant de temps i amb quines mesures, són decisions jurídiques i organitzatives, no tècniques. Aquesta lliçó ensenya com implementar el que es decideixi, no què s'ha de decidir.

Dit això, hi ha quatre tècniques que tot professional de bases de dades hauria de conèixer.

La mesura més eficaç és no tenir la dada.

Repassant l'esquema de BiblioRed amb aquella pregunta:

Cada dada que no hi és no es pot filtrar, no s'ha de xifrar, no s'ha d'auditar i no s'ha d'esborrar.

Pseudonimitzar és substituir els identificadors directes per una referència que no identifica per si sola, conservant en un altre lloc i amb altres proteccions la correspondència. És reversible amb la informació addicional.

A BiblioRed, la taula d'estadístiques d'ús no necessita saber qui és cada soci:

La clau és a la sal (app.sal_estadistica): sense ella, un sha256 d'un identificador numèric s'inverteix provant els 12.000 valors possibles en menys d'un segon. Un pseudònim sense sal no és un pseudònim.

Anonimitzar és transformar les dades de manera que ja no sigui possible identificar la persona, ni tan sols amb informació addicional. A diferència de la pseudonimització, és irreversible.

El cas pràctic és l'entorn de desenvolupament. Copiar la base de producció al portàtil d'un desenvolupador —que és el que es fa a la meitat de les organitzacions— significa distribuir les dades de dotze mil veïns per equips sense control, sense xifratge de disc garantit i sense auditoria.

Un guió d'anonimització per a l'entorn de proves de BiblioRed:

Tres regles perquè una anonimització serveixi d'alguna cosa:

1. S'executa com a part del procés de restauració, automàticament, mai a mà. Un pas manual s'oblidarà.
2. Es conserva la forma de les dades —longituds, distribucions, volum— o l'entorn de proves deixarà d'assemblar-se a producció i els plans d'execució de 06-03 no seran comparables.
3. Es comprova que no queda cap dada identificable en taules secundàries: auditoria, registres, informes_esdeveniment, camps de text lliure on algú va anotar "trucar al 6XX del fill".

Aquell últim punt és el que més falla. Els camps d'observacions en text lliure són un pou de dades personals que cap anonimització per columnes no detecta.

Una dada desada per sempre és un risc desat per sempre.

Fixa't que no s'esborra el préstec: es desvincula del soci. La biblioteca conserva l'estadística de circulació —quantes vegades es va prestar cada material, en quina sucursal, en quin mes—, que és el que necessita per a la seva gestió, i deixa de conservar qui va llegir què. És el tipus de solució que la tècnica sí que pot aportar a una decisió de retenció.

Això exigeix, és clar, que prestecs.soci_id admeti nuls i que les claus foranes ho permetin, cosa que és una decisió de disseny que convé prendre abans, al mòdul 4, i no quan arriba la política de retenció.

Passem a la segona pregunta de la regidoria. Comencem per l'afirmació que ordena tot el bloc:

Una còpia de seguretat no provada no és una còpia de seguretat: és una esperança.

PostgreSQL ofereix dues famílies de còpia, i no competeixen: es complementen.

Genera un fitxer amb les instruccions necessàries per reconstruir les dades: CREATE TABLE, COPY, CREATE INDEX.

En cas d'èxit, pg_dump no imprimeix res: només parla si hi ha problemes. Per veure el progrés en bases grans s'usa l'opció -v.

Restaurar:

Important: pg_dump no copia els rols ni les contrasenyes, que són globals del servidor. Calen a part:

Oblidar aquest fitxer és un clàssic: es restauren les dades i no es pot entrar perquè no existeix cap rol.

Copia els fitxers del directori de dades tal qual, a nivell de bloc.

La resposta correcta per a BiblioRed és "les dues", i l'apartat 17 concreta el pla.

La classificació clàssica, aplicable a qualsevol sistema de còpies:

El compromís és sempre el mateix: espai i temps de còpia enfront de temps i complexitat de restauració. I hi ha un factor que decideix més que els números:

Com més fitxers necessiti una restauració, més probable és que un falli. Una cadena incremental de trenta baules es trenca si falta la número disset.

A PostgreSQL, la traducció pràctica d'aquest esquema és:

• La completa és el pg_basebackup.
• El paper de les incrementals el compleix l'arxivat del WAL, que és continu en lloc de periòdic. I és una solució millor que les incrementals clàssiques, perquè permet restaurar no només al moment d'una còpia, sinó a qualsevol instant.

(PostgreSQL 17 va afegir a més còpies incrementals natives amb pg_basebackup --incremental, útils en bases molt grans; l'arxivat del WAL continua sent la base de l'esquema.)

Aquí reapareix el registre d'escriptura anticipada de la lliçó 06-01, i la seva segona vida és tan important com la primera.

Recordaràs el mecanisme: abans de modificar una pàgina de dades, PostgreSQL escriu al WAL una anotació que descriu el canvi. Aquell registre conté, per tant, la història completa de totes les modificacions des del moment en què es va fer la còpia base.

Arxivat del WAL. Si conservem una còpia base i tots els segments de WAL generats des d'aleshores, podem reconstruir la base de dades en qualsevol instant posterior a la còpia base, aplicant el registre fins al punt desitjat. És la recuperació a un instant concret (point-in-time recovery, PITR).

Aquell archive_timeout mereix atenció perquè és el que fixa el pitjor cas: sense ell, un segment de 16 MB a mig omplir podria no arxivar-se en hores, i aquella feina es perdria. Amb 300 segons, la pèrdua màxima està acotada a cinc minuts.

I un avís operatiu important: si archive_command falla, PostgreSQL conserva els segments i el disc s'omple. Un archive_command que apunta a una destinació inaccessible és una manera segura d'aturar el servidor en unes hores. Vigila'l:

failed_count = 0 i last_archived_time recent: l'arxivat funciona. Aquestes dues columnes haurien de ser al tauler de supervisió.

Escenari real: a les 11:47 algú executa en producció, creient que és a proves:

A les 11:52 es detecta. Els passos:

Al registre del servidor:

Fixa't en la línia decisiva: recovery stopping before commit of transaction 90412. Aquella transacció 90412 és el DELETE. La recuperació s'atura just abans de confirmar-la.

Els dotze mil socis són de tornada. S'han perdut només les operacions d'aquells cinc minuts entre les 11:47 i les 11:52, que es reconstrueixen a mà des del directori apartat al pas 2 i des dels comprovants en paper del taulell.

Altres destinacions de recuperació possibles, a més de recovery_target_time:

Aquell últim és or pur abans d'una migració d'esquema:

Fer tot això a mà és viable, però en producció s'usen eines que gestionen retenció, verificació i catàleg de còpies: pgBackRest, Barman i WAL-G són les tres habituals. Totes implementen el mateix mecanisme de fons que acabem de veure. Conèixer el mecanisme és el que permet entendre l'eina, i no a l'inrevés.

3 còpies de les dades · en 2 tipus de suport diferents · amb 1 còpia fora de l'emplaçament.

Aplicada a BiblioRed:

Afegits moderns a la regla, que l'experiència amb segrest de dades ha fet imprescindibles:

• 1 còpia immutable: emmagatzematge que no admet modificació ni esborrat durant un termini. Sense això, un atacant amb accés al servidor xifra o esborra també les còpies, que és exactament el que passa als incidents de segrest de dades.
• 0 errors a la verificació: cap còpia no compta fins que s'ha restaurat amb èxit.

Aquest és el punt on falla la majoria de les organitzacions. El procés de còpia es configura, es veu que genera fitxers, i ningú no restaura mai fins al dia del desastre, que és el pitjor moment per descobrir que el fitxer estava truncat, que faltaven els rols, o que el procés portava set mesos copiant una base de dades buida.

Un guió de verificació automàtica, executat setmanalment:

Les comprovacions de volum i de data són el cor del guió. Una còpia que restaura sense errors però conté una base de dades buida restaura perfectament, i no serveix per a res. L'error clàssic —copiar la base de dades equivocada, o una que es va deixar d'usar— només es detecta comptant files.

I un cop l'any, un simulacre complet: restaurar en un servidor diferent, arrencar l'aplicació contra la còpia restaurada, i cronometrar quant es triga des de la trucada fins al servei funcionant. Aquell cronòmetre és el RTO real, i gairebé sempre és el triple del que s'havia estimat.

Dues sigles que ordenen qualsevol conversa sobre continuïtat, perquè converteixen "volem estar segurs" en dos números que es poden dissenyar i pressupostar.

Aplicats a les operacions de BiblioRed:

De la taula surten directament les decisions tècniques:

Fixa't en la lògica: primer es decideix quant es pot perdre i quant es pot esperar; després es tria la tecnologia. Fer-ho a l'inrevés —muntar la infraestructura i veure quin RPO surt— és com dissenyar l'esquema sense conèixer el domini.

Amb tot l'anterior, aquest és el pla concret:

I les decisions que ho justifiquen, una per una:

Per què còpia lògica i física. La física dóna el RTO baix (restaurar és copiar fitxers) i és l'única base possible per al PITR. La lògica permet restaurar una sola taula —que és el que cal el 90 % de les vegades, perquè el desastre típic no és un disc mort, és un DELETE mal escrit— i és l'única que serveix per migrar a una versió major diferent.

Per què el WAL es reté 35 dies i la base setmanal 8 setmanes. El WAL només serveix si existeix la còpia base corresponent. Retenir 35 dies de WAL sense bases de més de 35 dies d'antiguitat seria inútil; a l'inrevés, retenir bases sense el seu WAL impedeix el PITR. La retenció del WAL ha de cobrir amb marge la de la còpia base més antiga que es vulgui usar per a PITR.

Per què una còpia immutable. Perquè un atacant que compromet el servidor i té les credencials de còpia esborrarà o xifrarà també les còpies. La còpia mensual immutable és l'últim recurs, i el seu RPO d'un mes és dolentíssim però infinitament millor que res.

Per què la verificació és setmanal i no mensual. Perquè la fallada típica del procés de còpia és silenciosa —un permís que va canviar, un disc ple, una ruta que va deixar d'existir— i una setmana és el temps màxim acceptable per detectar-la.

Per què el simulacre anual, si ja hi ha verificació setmanal. Perquè són coses diferents. La verificació comprova que el fitxer de còpia és bo; el simulacre comprova que el procediment funciona amb persones pel mig: que algú sap on és el document, que les credencials de l'emmagatzematge extern continuen sent vàlides, que la persona que el va escriure no és l'única que sap fer-ho, i quant es triga de debò.

Què falta en aquest pla. Convé dir-ho també: no hi ha rèplica de reserva. Amb el RTO de 2 hores de l'apartat 16 no és imprescindible, però és la primera inversió que caldria si la biblioteca decidís baixar aquell objectiu. La replicació primari-secundaris de 03-01 és exactament aquella peça.

El moment en què més mal es fa és el que segueix immediatament a l'error, quan algú intenta arreglar-lo de pressa. Aquest és el procediment, i convé tenir-lo imprès.

Minut 0 — Parar. No executis res més. No intentis "tornar a inserir les dades". Cada escriptura posterior complica la reconciliació i pot sobreescriure informació recuperable.

Minut 1 — Encara està oberta la transacció? Si el DELETE es va executar dins d'un BEGIN sense confirmar, la solució és un ROLLBACK i prou (06-01). Comprovar qui té què obert:

Aquí està: la transacció continua oberta. Un ROLLBACK en aquella sessió resol l'incident sencer. Val la pena comprovar-ho abans que res.

Minut 2 — Aïllar. Si ja està confirmat, impedir noves escriptures mentre es decideix:

Minut 3 — Determinar l'abast exacte. Quina taula, quantes files, a quina hora, amb quin usuari. La taula d'auditoria de l'apartat 10 respon tot això:

Minut 5 — Decidir la via de recuperació:

L'opció de la taula d'auditoria, quan aplica, és la més quirúrgica:

Minut 30 — Després. Restablir els permisos, comprovar la coherència amb les consultes de control de 05-03, i escriure què ha passat. L'informe posterior no busca culpables: busca per què va ser possible. Gairebé sempre la resposta és alguna d'aquestes tres: la connexió tenia més permisos dels necessaris, no hi havia manera de distingir l'entorn de producció del de proves, o no existia procediment per a operacions massives.

Tres mesures preventives que surten d'aquí:

Per tancar, la versió reduïda del mateix problema.

SQLite no té usuaris, ni rols, ni GRANT: els permisos són els del fitxer al sistema operatiu. Qui pot llegir el fitxer, ho llegeix tot; qui el pot escriure, ho escriu tot. Tota la primera meitat d'aquesta lliçó no té equivalent, i aquesta és una raó important per no usar SQLite en un sistema multiusuari amb dades personals.

Les còpies sí que tenen equivalent, i és senzill:

I l'advertiment central:

Copiar el fitxer .db amb cp mentre la base està en ús produeix una còpia corrupta, perquè pot capturar el fitxer a mitja transacció, i en mode WAL deixa fora el fitxer -wal amb els canvis recents. Copiar el fitxer directament només és segur amb la base tancada i sense cap connexió oberta.

.backup sí que és segur en calent: usa el mecanisme intern de còpia de SQLite, que garanteix una imatge coherent. És una diferència d'una línia que separa una còpia vàlida d'una inservible.

Connectar l'aplicació com a superusuari. Anul·la tota la configuració de permisos i converteix qualsevol injecció en un compromís del servidor. És l'error més greu i el més fàcil de corregir.

Donar SELECT sobre les taules i oblidar USAGE sobre l'esquema. El rol no veurà res i rebràs un informe d'error confús. És la fallada número u en configurar permisos per primera vegada.

Usar GRANT ... ON ALL TABLES i creure que cobreix el futur. Només afecta les taules existents. Sense ALTER DEFAULT PRIVILEGES, la taula que es creï demà quedarà inaccessible o —pitjor, segons com estigui muntat— accessible a qui no ha de ser.

Deixar trust a pg_hba.conf. Encara que sigui "un moment, per provar". Aquell moment dura tres anys.

Usar sslmode=require i creure que és segur. Xifra, però no verifica amb qui parla. Usa verify-full.

Escapar cometes a mà en lloc de parametritzar. N'hi ha prou amb un lloc oblidat de dos-cents. Les consultes parametritzades no tenen aquell mode de fallada.

Mostrar l'error de la base de dades a l'usuari final. Regala l'esquema. Registra l'error complet internament i mostra un missatge genèric.

Copiar la base de producció al portàtil de desenvolupament sense anonimitzar. És la fuita de dades més comuna i la que menys es percep com a tal. Anonimitza com a part automàtica del procés de restauració.

Activar log_statement = 'all' en una base amb dades personals. Els correus i telèfons acaben en fitxers de registre amb menys protecció i més còpies que la base de dades.

Tenir còpies i no haver restaurat mai. És l'error d'aquesta lliçó. Una còpia no provada no és una còpia. Verifica setmanalment de manera automàtica i fes un simulacre complet a l'any.

No copiar els rols. pg_dump no els inclou. Sense pg_dumpall --globals-only, restauraràs les dades i no podràs entrar.

No vigilar pg_stat_archiver. Si archive_command falla, el WAL s'acumula, el disc s'omple i el servidor s'atura. failed_count ha d'estar en un tauler.

Confondre emmascarar amb anonimitzar. m***@example.org continua sent una dada personal en un registre que conté el nom i la sucursal. Anonimitzar és substituir, no tapar.

Consell final: escriu el procediment de recuperació i guarda'l fora del sistema que protegeix. Un document de restauració allotjat únicament al servidor que s'ha incendiat és un acudit que només fa gràcia abans de l'incendi. En paper, a l'emmagatzematge extern, i amb les credencials necessàries custodiades per almenys dues persones.

La regidoria de Cultura de Vallmar contracta una empresa externa per analitzar l'ús de les biblioteques durant sis mesos. L'empresa necessita: llegir els préstecs, exemplars, materials, sucursals i esdeveniments; no ha de poder identificar cap soci; no ha de poder escriure res; i el seu accés ha de caducar automàticament el 31 de desembre de 2026.

Escriu el SQL complet: el rol, els permisos, qualsevol vista que necessitis, i la comprovació que no pot arribar a les dades personals.

Un tècnic ha deixat aquesta configuració al servidor de producció de BiblioRed. Identifica tots els problemes de seguretat, ordenats per gravetat, i escriu la correcció de cadascun.

La xarxa de biblioteques de Vallmar afegeix un servei de préstec d'instruments musicals, amb la seva pròpia base de dades instruments. Les seves característiques: 400 instruments, 900 usuaris, unes 30 operacions al dia, i els imports de les fiances (fins a 300 € per instrument) es registren en una taula fiances.

Defineix el RPO i el RTO de cada tipus d'operació, justifica'ls, i escriu el pla de còpies que se'n deriva. Explica en què es diferencia del pla de BiblioRed de l'apartat 17 i per què.

Solució 1

I a pg_hba.conf, restringint l'origen i obligant a TLS:

Comprovació:

Punts clau de la solució:

• Es revoca prestecs i es dóna accés només a la vista. Donar SELECT sobre prestecs hauria lliurat soci_id en clar, que combinat amb les dates permet reidentificar persones concretes amb poc esforç.
• VALID UNTIL fa que l'accés caduqui sol. Confiar que algú se'n recordi de revocar-lo al desembre és confiar massa.
• CONNECTION LIMIT 3 evita que una eina d'anàlisi mal configurada obri dues-centes connexions i afecti el servei del taulell.
• hostssl amb l'adreça concreta limita l'accés a la xarxa de la consultora i obliga al xifratge.
• Nota de compliance: el pseudònim fa més difícil la reidentificació, però un conjunt amb dates, sucursal i materials pot continuar sent reidentificable per combinació. Que aquest tractament sigui suficient i amb quina base jurídica se cedeix a un tercer és una qüestió que ha de validar el delegat de protecció de dades de l'ajuntament, no l'equip tècnic.

Solució 2

Problema 1 (crític) — SUPERUSER a la connexió de l'aplicació.

Anul·la tots els permisos i totes les polítiques de fila, i converteix qualsevol injecció en execució d'ordres del sistema operatiu mitjançant COPY ... FROM PROGRAM.

Problema 2 (crític) — GRANT ALL ... TO PUBLIC.

PUBLIC inclou tots els rols presents i futurs. Qualsevol que aconsegueixi connectar-se té control total sobre totes les taules, inclòs DELETE i TRUNCATE.

Problema 3 (crític) — Injecció SQL per concatenació.

A més, s'elimina el SELECT *: la consulta ha de demanar només les columnes que usa, cosa que evita exposar email per accident i facilita l'Index Only Scan de 06-03.

Problema 4 (crític) — local all all trust.

Qualsevol usuari del sistema operatiu del servidor entra com a qualsevol rol, inclòs postgres, sense contrasenya.

Problema 5 (greu) — host all all 0.0.0.0/0.

La base de dades accepta connexions des de qualsevol lloc d'internet, a qualsevol base i amb qualsevol usuari.

Problema 6 (greu) — md5 en lloc de scram-sha-256.

Mètode amb debilitats conegudes. Es canvia el mètode i es reassignen les contrasenyes, perquè el canvi de mètode no reconverteix sol les existents:

Problema 7 (greu) — Sense TLS.

Totes les línies eren host, no hostssl: el trànsit viatja en clar. Corregit al problema 5, més ssl = on a postgresql.conf.

Problema 8 (moderat) — Contrasenya feble i previsible.

bibliored2026 s'endevina al primer intent d'un atac dirigit. Contrasenya llarga generada aleatòriament, guardada en un gestor de secrets, mai al repositori de codi, i amb rotació planificada.

Ordre d'intervenció recomanat: primer tancar pg_hba.conf (problemes 4, 5, 6 i 7), que atura l'accés indegut de seguida; després treure SUPERUSER i PUBLIC (1 i 2), que limiten el dany; i en paral·lel corregir el codi (3), que requereix desplegament. I tan bon punt es tanqui l'urgent: revisar els registres per determinar si aquesta configuració va ser aprofitada, cosa que és un incident que s'ha de comunicar pels canals de l'organització.

Solució 3

RPO i RTO per operació:

Pla de còpies derivat:

Diferències amb el pla de BiblioRed i per què:

1. archive_timeout = 600 en lloc de 300. Amb 30 operacions al dia, un segment de WAL cada 10 minuts és més que suficient i genera molts menys fitxers per arxivar i retenir. El RPO efectiu de 10 minuts continua sent millor que el requisit d'1 hora del préstec.

2. Retenció de la còpia lògica de 30 dies en lloc de 14. La base és diminuta —900 usuaris i 400 instruments—, així que 30 còpies diàries comprimides ocupen uns pocs megabytes. Quan l'espai no és una restricció, es reté més, perquè l'error que més triga a detectar-se és la corrupció lògica silenciosa: algú va modificar malament unes dades fa tres setmanes i ningú no ho va veure.

3. RTO més folgat (4-8 hores enfront de 2). El servei d'instruments no és crític per al funcionament diari de les biblioteques i admet operar en paper durant una jornada. Això fa innecessària qualsevol inversió en rèplica de reserva.

4. El RPO de 0 en fiances no obliga aquí a rèplica síncrona. A diferència dels pagaments de multes de BiblioRed —que arriben per datàfon i no deixen comprovant a la biblioteca—, la fiança d'un instrument es cobra amb un resguard en paper signat per l'usuari. Aquell resguard és el respatller del RPO 0, i és molt més barat que la infraestructura equivalent. És un bon exemple que un requisit de continuïtat no sempre es resol amb tecnologia.

5. El mateix règim de verificació i simulacre. Aquest punt no es relaxa per la mida de la base. La probabilitat que un procés de còpies falli silenciosament no depèn de quantes files hi hagi, i una base petita sol tenir menys supervisió, no més. És exactament on més falta fa la verificació automàtica.

Les dues preguntes de la regidoria de Vallmar ja tenen resposta, i totes dues són respostes concretes.

Qui pot consultar els telèfons i els correus dels socis? El rol bibliored_app, perquè l'aplicació web necessita enviar els avisos de venciment, i ningú més. El personal de taulell pot modificar un correu però no llegir-lo en un llistat. La direcció accedeix a v_socis_public, on aquelles columnes no existeixen. La consultora externa treballa sobre v_prestecs_analitica amb pseudònims amb sal. I tot canvi sobre socis queda registrat a auditoria_socis amb el rol, l'adreça d'origen i el valor anterior. La resposta ja no és "no ho sé": és un script de GRANT que es pot llegir, comprovar amb has_column_privilege i auditar.

Què passaria si el disc morís aquesta nit? Es restauraria l'últim pg_basebackup, s'aplicaria el WAL arxivat fins a l'últim segment disponible, i el servei tornaria amb una pèrdua màxima de cinc minuts i en menys de dues hores. I aquella afirmació no és una esperança: és un procediment escrit, verificat automàticament cada dilluns amb un guió que compta files i comprova dates, i assajat sencer una vegada l'any amb cronòmetre.

Pel camí hem recorregut les capes completes. L'autenticació, amb rols que són alhora usuaris i grups, scram-sha-256 com a mètode i aquell pg_hba.conf que es llegeix de dalt a baix i ha d'acabar en reject. L'autorització, amb la seva jerarquia de base, esquema i taula —i el USAGE que tothom oblida—, els privilegis a nivell de columna, ALTER DEFAULT PRIVILEGES per als objectes que encara no existeixen, i el principi de mínim privilegi convertit en tres rols concrets i comprovables. Les vistes per amagar columnes i la seguretat a nivell de fila per amagar files, amb l'advertiment que ni l'una ni l'altra no protegeixen d'un superusuari. La injecció SQL, amb el cercador del catàleg tornant dotze mil correus i la consulta parametritzada convertint el mateix atac en una cerca sense resultats, perquè no escapa res: separa els camins. El xifratge en trànsit amb verify-full i no amb require, en repòs a nivell de disc, i les contrasenyes que no es xifren sinó que es deriven amb sal i amb cost. L'auditoria amb SECURITY DEFINER, que permet escriure al registre sense poder-lo esborrar. I les dades personals, amb la minimització que és l'única mesura que no pot fallar, la pseudonimització que sense sal no és pseudonimització, l'anonimització que ha de ser un pas automàtic del procés de restauració, i el recordatori —que convé repetir— que aquests són mecanismes tècnics i que el compliment normatiu el decideix un professional de compliance, no l'equip de desenvolupament.

I el bloc de còpies, on el WAL de 06-01 ha tingut la seva segona vida: el que en aquella lliçó era el mecanisme que garantia la durabilitat d'un COMMIT davant d'un tall de llum, aquí s'ha convertit en la història completa de les modificacions que permet aturar la recuperació just abans de la transacció 90412, aquella que va esborrar dotze mil socis a les 11:47:03. Hem vist la còpia lògica i la física amb els seus papers diferents i complementaris, la regla 3-2-1 amb els seus afegits moderns d'immutabilitat, el RPO i el RTO com els dos números que converteixen "volem estar segurs" en decisions d'enginyeria, un pla de còpies comentat decisió per decisió, i el procediment dels primers minuts, que comença per allò més contraintuïtiu i més eficaç: parar i comprovar si la transacció continua oberta.

Amb això es tanca el mòdul 6 i, amb ell, el bloc teòric del curs. Val la pena mirar enrere el recorregut complet. Vam començar al mòdul 1 amb un full de càlcul desbordat i vam obrir el SGBD per veure'n les peces per dins. Al 2 vam aprendre el model relacional i SQL fins als agregats i la integritat referencial. Al 3 vam sortir del món relacional per entendre NoSQL, el teorema CAP i la consistència eventual, i vam tornar sabent què es guanya i què es paga a cada costat. Al 4 vam dissenyar l'esquema de BiblioRed des del diagrama entitat-relació fins a l'últim CHECK. Al 5 el vam sotmetre a un examen formal, vam trobar una fallada real a multes i vam justificar per escrit cada desnormalització. I al 6 hem deixat de mirar l'esquema per mirar el sistema en funcionament: les transaccions que passen senceres o no passen, la concurrència que trenca el codi correcte tan bon punt hi ha dues persones, els índexs que van convertir catorze segons en quaranta mil·lisegons, i la seguretat i les còpies que separen una base de dades d'un accident esperant a passar.

El que queda del curs ja no és teoria nova: és fer-ho amb les mans. El mòdul 7, Exercicis Pràctics, és on tot l'anterior es converteix en destresa, i està organitzat seguint el mateix recorregut: exercicis de SQL sobre l'esquema de BiblioRed que ja coneixes pam a pam (07-01); exercicis de disseny d'esquemes, amb dominis nous que hauràs de modelar des de zero (07-02); exercicis de normalització, amb taules que amaguen dependències funcionals que ara saps detectar (07-03); i exercicis de consultes avançades i transaccions, on tornaran les dues sessions psql en paral·lel, els nivells d'aïllament i els plans d'execució d'aquest mòdul (07-04). Després, el mòdul 8 recorrerà tres casos d'estudi complets —relacional, no relacional i de persistència poliglota— i el mòdul 9 reunirà els llibres, cursos i eines amb què continuar pel teu compte. Obre els dos terminals, tingues l'esquema de BiblioRed a mà, i ens veiem al primer exercici.