Aquesta lliçó tanca les promeses que el curs fa des del mòdul 5. Tracta dues coses que semblen diferents i són la mateixa: qui decideix què s'executa i qui pot veure què. La primera meitat és la injecció SQL, la fallada de seguretat més vella i més cara de les aplicacions amb base de dades, explicada des del costat que importa: com s'evita. La segona és el sistema de permisos i rols de PostgreSQL, inclosa la seguretat a nivell de fila, amb un disseny concret per a BotigaVerda.

Un avís des del principi: això és una lliçó defensiva. Els exemples d'atac que veuràs són mínims i inofensius —una condició que retorna de més— i són aquí només perquè entenguis per què falla l'enfocament vulnerable. El pes de la lliçó és a les defenses. I una advertència que va de debò: abans d'exposar a Internet un sistema amb dades reals, encarrega una revisió a un professional de seguretat i consulta el tractament de les dades personals amb el responsable legal o de protecció de dades de la teva organització. El que segueix és el mínim que has de saber, no un substitut d'aquesta revisió.

1. Què és una injecció SQL
2. Per què passa: dades i codi a la mateixa cadena
3. La defensa: consultes parametritzades
4. El que no és una defensa suficient
5. El cas especial: identificadors dinàmics
6. Defensa en profunditat
7. Permisos i rols a PostgreSQL
8. Un disseny de rols per a BotigaVerda
9. Row Level Security
10. SECURITY DEFINER i el search_path
11. Dades personals
12. Checklist de seguretat
13. Errors habituals i consells
14. Exercicis
15. Conclusió

El cercador de la botiga rep un text i busca productes. Escrit de la pitjor manera possible —concatenant— queda així:

Amb l'entrada normal oli, el que arriba al servidor és SELECT id, nom, preu FROM productes WHERE actiu AND nom ILIKE '%oli%';, que és correcte i retorna l'esperat:

Dos productes. Ara, l'entrada ' OR '1'='1 — un text perfectament escrivible en qualsevol caixa de cerca. En concatenar-lo, la consulta que s'executa és:

Retorna 19 files: el catàleg actiu sencer. La cometa de l'usuari va tancar la cadena que el programador havia obert, i el que venia al darrere va deixar de ser text de cerca per convertir-se en estructura de la consulta: un OR que ningú no va escriure. Amb la variant ' OR '1'='1' --, el doble guió comenta la resta de la línia, inclòs el %' pendent:

20 files. Una més: hi apareixen també les càpsules d'espirulina, el producte descatalogat (actiu = FALSE) que la botiga no hauria de mostrar mai. És un exemple diminut i sense dany, i precisament per això és útil: l'atacant no ha fet res estrany; ha escrit text en una caixa de text, i ha aconseguit que el filtre de negoci deixi d'aplicar-se. Si en lloc d'un catàleg públic la consulta hagués estat la de "les meves comandes" o la d'"empleats", el resultat hauria estat el mateix mecanisme sobre dades que sí que importen.

I una conseqüència menys òbvia però igual de greu: l'entrada O'Connor —un cognom perfectament normal— trenca la consulta amb un error de sintaxi. La mateixa esquerda que permet l'atac fa que el programa falli amb dades legítimes. Un codi vulnerable a injecció és també un codi que no funciona bé.

La causa cap en una frase, i convé aprendre-se-la:

La injecció SQL passa perquè el codi i les dades viatgen barrejats a la mateixa cadena de text, i el motor no té manera de saber quina part vas escriure tu i quina va escriure l'usuari.

El servidor rep un text i l'analitza sencer. Per a ell, OR '1'='1' és llenguatge SQL igual que el SELECT que el precedeix: no hi ha cap marca que digui "això venia d'un formulari". Tot el que se'n deriva —afegir condicions, comentar la resta, encadenar sentències si el connector ho permet— és conseqüència d'aquesta barreja. D'aquí surt l'única defensa que funciona de debò: separar el codi de les dades, i que sigui el motor qui els ajunti sabent quin és quin.

Una consulta parametritzada (o sentència preparada) envia al servidor dues coses separades: el text amb marcadors, i els valors. El servidor analitza i planifica abans de veure els valors; quan arriben, ja no hi ha res a analitzar i un valor no es pot convertir en codi passi el que passi. Ni tan sols cal escapar cometes: el valor no s'insereix al text. El mateix cercador, ben escrit, en quatre entorns:

Amb l'entrada maliciosa ' OR '1'='1, les quatre versions retornen 0 files: busquen, literalment, productes el nom dels quals contingui el text ' OR '1'='1. Que no n'existeixi cap és exactament el correcte. L'atac deixa de ser un atac i passa a ser una cerca sense resultats.

I el mecanisme també existeix en SQL pur, útil per veure'l sense llenguatge pel mig:

Dos matisos pràctics. El comodí % va dins del valor, mai al text SQL: ILIKE '%' || $1 || '%' també funciona, però llavors convé escapar els % i _ que porti l'usuari, o una cerca de 100% es convertirà en un comodí. I si vols filtrar per una llista de valors, no muntis la llista concatenant: fes servir WHERE id = ANY($1) passant-hi un array, que és el que suporten tots els connectors moderns.

Nota de dialecte. El marcador canvia segons el connector: $1, $2 a PostgreSQL natiu i node-postgres, %s a psycopg, ? a JDBC, PDO i molts altres, :nom per a paràmetres amb nom a PDO, SQLAlchemy o JDBI. El que no canvia és la garantia: mentre el valor viatgi pel canal de paràmetres, no es pot convertir en codi. Compte amb PDO: per omissió emula les preparades escapant al client; desactiva-ho amb ATTR_EMULATE_PREPARES => false per fer servir les de debò.

La llista deixa una conclusió: no hi ha graus. O el valor viatja pel canal de paràmetres, o no està protegit.

Hi ha un cas que les consultes parametritzades no resolen, i és per on es cola la injecció en aplicacions per la resta correctes: un paràmetre no pot ser el nom d'una taula, d'una columna, ni el sentit d'un ORDER BY.

La idea clau: l'usuari no aporta text, tria una opció d'un conjunt que tu controles. El que es concatena mai no surt de l'entrada; surt del teu diccionari. I com a efecte secundari, el valor per defecte converteix un paràmetre brossa en un ordre raonable en lloc d'un error.

Quan el SQL dinàmic es construeix dins de PostgreSQL —en una funció PL/pgSQL (10-04)— l'eina és format() amb els seus marcadors de tipus:

%I aplica quote_ident: entrecometa l'identificador i neutralitza qualsevol cosa estranya que porti. No facis servir mai %s amb entrada de l'usuari; %s és concatenació amb un altre nom. I fins i tot amb %I, el correcte és validar abans contra la llista de taules permeses o contra information_schema: %I impedeix la injecció, però no impedeix que algú compti les files d'una taula que no li correspon.

Cap capa no basta tota sola; la bona notícia és que se sumen.

Sobre els missatges d'error: en producció, l'error complet de PostgreSQL li diu a l'usuari el nom de les taules, de les columnes i de les restriccions. Registra el detall al servidor, retorna un text genèric i un identificador que permeti a suport trobar la traça; en desenvolupament, al revés.

Aquí es tanquen les promeses de 05-04, 09-05, 10-01 i 10-04. El model és més simple del que sembla, amb una idea central:

A PostgreSQL no hi ha "usuaris" i "grups": només hi ha rols. Un rol amb LOGIN es comporta com un usuari; un rol sense LOGIN al qual es concedeixen altres rols es comporta com un grup. És el mateix objecte.

Els privilegis es concedeixen amb GRANT, es retiren amb REVOKE i s'apliquen a diferents nivells:

Dos paranys que enxampen tothom:

• USAGE sobre l'esquema és imprescindible. Sense ell, GRANT SELECT ON comandes no serveix de res: el rol té permís sobre una taula que no pot assolir. L'error és permission denied for schema public i desconcerta perquè el GRANT de la taula existeix.
• ALTER DEFAULT PRIVILEGES, o els objectes nous no hereten res. Un GRANT SELECT ON ALL TABLES IN SCHEMA public afecta les taules que existeixen en aquell moment. La taula que creïs demà no hi estarà inclosa, i la fallada apareixerà en producció després del desplegament:

Compte: ALTER DEFAULT PRIVILEGES s'aplica als objectes que creï el rol que executa l'ordre. Si les migracions les llança bv_admin, cal executar-ho com a bv_admin (o amb FOR ROLE bv_admin).

Són dues coses diferents amb el mateix nom, i confondre-les és el clàssic de la lliçó. PUBLIC és un pseudorol que significa "tots els rols que existeixin i existiran". public és l'esquema per omissió on viuen les nou taules de BotigaVerda.

Històricament, PUBLIC tenia CREATE sobre l'esquema public, de manera que qualsevol usuari hi podia crear objectes. PostgreSQL 15 ho va corregir, però en bases creades abans, o migrades, convé comprovar-ho i endurir-ho:

I recorda del mòdul 10 que una vista només protegeix si treus el permís sobre la taula: GRANT SELECT ON v_clients_public no serveix de res mentre el rol conservi SELECT ON clients.

Tres rols de grup, tres perfils reals, i ningú que es connecti com a propietari de les taules:

Comprovar què té cada rol és tan important com concedir-ho:

A psql, \dp comandes dona la mateixa informació de manera compacta. La regla d'or: l'aplicació no es connecta mai com a superusuari ni com a propietari de les taules. Si web_prod és propietari, tots els REVOKE del món són decoratius: el propietari s'ho pot tornar a concedir tot.

Els permisos anteriors arriben fins a la taula i la columna. Row Level Security (RLS) arriba fins a la fila: permet que dos usuaris executin SELECT * FROM comandes i obtinguin resultats diferents. El cas clàssic: que cada comercial vegi només les seves comandes.

Amb SET app.empleat_id = '4' a l'inici de la sessió, el comercial Óscar (empleat 4) veu 4 comandes —les números 2, 6, 10 i 16— i cap més, encara que escrigui SELECT * FROM comandes sense WHERE. Les deu comandes web, amb empleat_id NULL, no les veu ningú amb aquesta política, perquè NULL = 4 no és cert: si les han de veure tots, la condició seria empleat_id = ... OR empleat_id IS NULL, i aquesta decisió cal prendre-la explícitament.

Tres avisos. El propietari de la taula i els superusuaris se salten RLS per omissió (cal FORCE ROW LEVEL SECURITY). Les polítiques s'escriuen per operació —FOR SELECT, FOR INSERT amb WITH CHECK, FOR UPDATE, FOR ALL— i si no defineixes la d'INSERT, no s'hi pot inserir res. I el cost de rendiment és real: la condició entra en totes les consultes, així que la columna de la política ha d'estar indexada. RLS brilla en multiinquilí (empresa_id); per a tres pantalles i un rol, sol bastar una vista.

Tanca 10-04. Per omissió una funció s'executa amb els permisos de qui la crida (SECURITY INVOKER). Amb SECURITY DEFINER s'executa amb els de qui la va crear, cosa que permet donar accés controlat a dades que l'usuari no pot llegir: per exemple, que un analista obtingui la mitjana salarial sense poder llegir empleats.

La mitjana salarial canònica del curs, 35.037,50 €, servida sense donar accés a la taula. I ara el risc, que és seriós: una funció SECURITY DEFINER corre amb privilegis aliens, així que si l'atacant controla quins objectes resol a dins, executa codi amb aquells privilegis. El vector és el search_path: si la funció diu FROM empleats sense qualificar i qui la crida ha posat al davant un esquema seu amb una taula empleats pròpia, la funció llegirà la de l'atacant.

Les quatre regles, i no són opcionals: (1) fixa sempre SET search_path = public, pg_temp en tota funció SECURITY DEFINER (o qualifica cada objecte: public.empleats); (2) REVOKE EXECUTE ... FROM PUBLIC i concedeix només a qui pertoqui, perquè per omissió EXECUTE es concedeix a PUBLIC; (3) fes la funció tan petita com sigui possible i sense SQL dinàmic a dins —i si n'hi ha, format('%I'/'%L') i llista blanca—; (4) fes-la servir només quan calgui: SECURITY INVOKER és el valor per omissió per una bona raó.

BotigaVerda és fictícia, però el seu esquema té exactament les dades que en un sistema real estan regulades:

Les mesures que cal conèixer, sense entrar en matèria jurídica:

• Xifratge en trànsit: TLS obligatori a la connexió (sslmode=require o superior); sense ell, credencials i dades viatgen en clar. En repòs: xifratge de disc o de volum, i de les còpies de seguretat, que és el que més s'oblida.
• Privilegi mínim, que és el de l'apartat 8: la majoria dels analistes no necessita veure correus. I registre d'accessos a les dades sensibles, no només dels canvis.
• Pseudonimització per a entorns de prova: substituir noms i correus, desplaçar dates, alterar imports — amb l'advertiment que una pseudonimització mal feta és reversible (un "hash de l'email" es reverteix provant correus).
• Retenció: les dades personals no es desen per sempre. Cal decidir quant de temps i esborrar-les o anonimitzar-les després (05-04).

⚠️ Abans d'exposar un sistema real: encarrega una revisió de seguretat a un professional (revisió de codi, configuració i proves d'intrusió) i contrasta el tractament de les dades personals amb el responsable de protecció de dades o l'assessoria jurídica de la teva organització. Aquesta lliçó et dona el vocabulari i les pràctiques mínimes; no substitueix ni una auditoria ni una anàlisi de compliment.

Consultes. 1. Tots els valors de l'usuari van com a paràmetres? Busca concatenacions i f-strings amb SQL a dins. 2. Hi ha identificadors dinàmics (ORDER BY, nom de taula)? Estan resolts amb llista blanca o %I? 3. S'escapen % i _ a les cerques amb LIKE/ILIKE?

Permisos. 4. L'aplicació es connecta amb un rol que no és superusuari ni propietari? 5. Aquell rol té només el que necessita, sense DELETE ni TRUNCATE gratuïts? 6. Està posat ALTER DEFAULT PRIVILEGES per a les taules futures? 7. S'ha revocat CREATE sobre l'esquema public a PUBLIC? 8. Les vistes "de seguretat" tenen revocat el permís sobre la taula base? 9. Les funcions SECURITY DEFINER fixen search_path i tenen REVOKE ... FROM PUBLIC?

Dades i operació. 10. La connexió fa servir TLS i estan xifrades les còpies de seguretat? 11. Els entorns de prova tenen dades fictícies o pseudonimitzades? 12. Els missatges d'error de producció són genèrics, amb el detall al registre? 13. Es registren i es revisen els accessos a dades sensibles i els errors de sintaxi repetits? 14. Hi ha data per a la revisió externa abans de sortir a producció?

• Creure que una dada numèrica no és injectable. WHERE id = " + entrada és injectable sense fer servir ni una cometa. Parametritza sempre, no només els textos.
• Fer servir un ORM i donar-lo per segur. Tan bon punt apareix raw(), text() o createNativeQuery() amb una f-string, la protecció desapareix (11-05). I deixar PDO amb l'emulació de preparades activada: escapa al client en lloc de fer servir preparades reals.
• Concedir SELECT sense USAGE sobre l'esquema. permission denied for schema public amb el GRANT de la taula ja fet: falta GRANT USAGE ON SCHEMA. I oblidar ALTER DEFAULT PRIVILEGES: tot funciona fins que una migració afegeix una taula i l'aplicació deixa de veure-la en producció.
• Confondre PUBLIC (el pseudorol "tots") amb l'esquema public. Un REVOKE ... FROM PUBLIC afecta tots els rols, presents i futurs.
• Crear una vista "de seguretat" i deixar el GRANT sobre la taula. No protegeix res. I SECURITY DEFINER sense search_path fixat: és una escalada de privilegis de manual.
• Consell: busca execute( seguit d'una f-string o d'un + a tot el repositori. És la revisió de seguretat més barata i troba la majoria dels casos. I prova cada camp del formulari amb ' i amb --: si alguna cosa es trenca amb una cometa, hi ha concatenació al darrere.
• Consell: escriu els GRANT en una migració, no a mà. Els permisos són part de l'esquema i han de poder reconstruir-se des del repositori (05-06).

Aquest endpoint retorna les comandes d'un client:

(1) Assenyala les tres vies d'injecció. (2) Reescriu-lo correctament. (3) Per què la tercera no s'arregla igual que les altres dues?

Dissenya els permisos per a dos perfils nous de BotigaVerda: bv_magatzem (Irene, operària) necessita veure les comandes pagades i les seves línies, i actualitzar l'estat de la comanda a enviat; bv_suport (Marc, atenció al client) necessita veure tot el d'un client, inclòs el seu correu, i crear devolucions. (1) Escriu els GRANT. (2) Com impediries que bv_magatzem canviï l'estat a cancellat? (3) Què li falta a bv_suport per poder inserir a devolucions?

Un company proposa: "Donem SELECT sobre tot als analistes, que són de confiança, i així no molesten demanant permisos". Rebat la proposta amb quatre arguments concrets referits a BotigaVerda, i proposa una alternativa que resolgui el seu problema real.

Solució 1

1. Les tres: client_id interpolat sense cometes —injectable amb 1 OR 1=1, i la que més es passa per alt precisament per "ser un número"—; estat interpolat dins de cometes, injectable tancant-les; i ordre, que és un identificador i no pot ser un paràmetre.

3. Perquè un paràmetre és sempre un valor, mai estructura. ORDER BY $1 ordenaria per la constant $1, no per la columna el nom de la qual conté: el motor ja ha planificat la consulta quan rep el valor, i l'ordre és part del pla. Per això els identificadors es resolen amb llista blanca (o amb %I si el SQL es construeix dins del servidor), que és una tècnica diferent amb el mateix objectiu: que l'entrada de l'usuari no aporti mai text SQL.

Solució 2

2. Un GRANT UPDATE (estat) deixa canviar la columna, però no controla a quin valor. Això és una regla de negoci i va a la base: un disparador BEFORE UPDATE (10-05) que rebutgi transicions no permeses, o un procediment sp_marcar_enviat al qual es concedeix EXECUTE mentre es retira l'UPDATE directe — la segona opció és més neta, perquè exposa l'operació i no la columna. 3. Li falta el permís sobre la seqüència —devolucions_id_seq—, sense el qual un INSERT que deixa generar l'id falla amb permission denied for sequence. És l'oblit més habitual en concedir INSERT, i per això a l'apartat 8 hi apareix un GRANT USAGE ON ALL SEQUENCES.

Solució 3

Els quatre arguments. (a) clients.email i empleats.salari no són de confiança ni de desconfiança: són dades personals l'accés a les quals ha d'estar limitat a qui les necessiti per a la seva feina, i un analista de vendes no les necessita. (b) La confiança no protegeix de l'accident: una exportació a CSV, un portàtil perdut o un panell compartit converteixen un SELECT legítim en una fuita. (c) La confiança no protegeix del compte compromès: si roben les credencials del Daniel, l'atacant hereta exactament el que el Daniel tenia. (d) Sense ALTER DEFAULT PRIVILEGES i amb "accés a tot", cada taula nova —inclosa una futura nomines— queda exposada per omissió, que és just el contrari del que ha de passar.

L'alternativa, que a més resol el seu problema real (deixar de molestar demanant permisos): donar-los bv_lectura sobre les vistes d'anàlisi, no sobre les taules. Una capa de vistes v_* que exclogui les columnes sensibles, amb ALTER DEFAULT PRIVILEGES perquè les vistes noves es concedeixin soles, i un SECURITY DEFINER per als pocs agregats que necessitin dades restringides —com fn_salari_mitja—. L'analista guanya autonomia i l'exposició és molt menor.

Aquesta lliçó tanca la meitat menys visible del curs i la que més car es paga si falta:

• La injecció SQL passa perquè el codi i les dades viatgen a la mateixa cadena. Amb l'entrada ' OR '1'='1, un cercador concatenat passa de retornar 2 productes a retornar-ne els 19 actius; amb -- pel mig, els 20, inclòs el descatalogat. I la mateixa fallada fa que O'Connor trenqui la consulta. La defensa és una de sola i és total: consultes parametritzades. El servidor analitza i planifica abans de veure els valors, així que un valor no es pot convertir en codi. Igual a psycopg, JDBC, PDO i node-postgres, i en SQL pur amb PREPARE/EXECUTE.
• No són defenses: escapar a mà, les llistes negres, amagar els errors, validar al navegador o "fer servir un ORM". I els identificadors no es parametritzen: van amb llista blanca, o amb format('%I') / quote_ident dins del servidor. Mai %s.
• Permisos: a PostgreSQL tot són rols; GRANT/REVOKE sobre base, esquema, taula, columna, seqüència i funció; USAGE sobre l'esquema és imprescindible i ALTER DEFAULT PRIVILEGES és el que fa que els objectes futurs heretin. PUBLIC (tots els rols) no és l'esquema public, i una vista només protegeix si es revoca el permís sobre la taula.
• El disseny de BotigaVerda: bv_lectura per a analistes sense email ni empleats, bv_app sense DELETE ni DDL, bv_admin només per a migracions. I la regla d'or: l'aplicació no es connecta mai com a superusuari ni com a propietari.
• RLS filtra per fila —el comercial 4 veu les seves 4 comandes i cap més— a canvi d'una condició en totes les consultes i d'una depuració més difícil; SECURITY DEFINER presta privilegis i exigeix SET search_path i REVOKE EXECUTE FROM PUBLIC. I les dades personals: identificar quines columnes ho són, TLS en trànsit, xifratge en repòs i de les còpies, pseudonimització en proves, retenció — amb la revisió per part d'un professional de seguretat i pel responsable legal abans d'exposar res real.

Amb això el sistema està protegit i és mantenible. Toca treure'n valor. A la lliçó següent, SQL per a l'anàlisi de dades, veuràs l'ofici de l'analista: el flux que va de la pregunta de negoci a la mètrica ben definida —i per què la meitat dels errors d'anàlisi són de definició i no de SQL: "vendes" inclou els ports?, i la comanda cancel·lada?—; les mètriques fonamentals de BotigaVerda calculades una a una; l'anàlisi temporal amb acumulats i mitjanes mòbils; la segmentació i el Pareto de clients i productes; les cohorts amb el seu advertiment honest sobre la mida de la mostra; la presentació amb CASE i amb crosstab, tancant la promesa de 06-05; i on encaixa SQL enfront de Python i de les eines de BI.