Salta el contingut
 

Exercici: Control d'accés

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Exercici: Control d'accés

Aquests exercicis estan dissenyats per ajudar-te a practicar la gestió d'usuaris, rols i privilegis en PostgreSQL.

Tots els exercicis treballen sobre el mateix escenari: la base de dades d'un institut, on accedeixen el professorat i la secretaria.

Preparació de l'entorn

Connectat com postgres, crea la base de dades institut i les taules següents:

CREATE DATABASE institut;
\c institut

CREATE TABLE alumnes (
    id       SERIAL PRIMARY KEY,
    nom      VARCHAR(100),
    dni      VARCHAR(9),
    telefon  VARCHAR(15)
);

CREATE TABLE notes (
    id         SERIAL PRIMARY KEY,
    alumne_id  INTEGER REFERENCES alumnes(id),
    assignatura VARCHAR(50),
    nota       NUMERIC(4,2)
);

INSERT INTO alumnes (nom, dni, telefon) VALUES
    ('Aina Soler', '12345678A', '600111222'),
    ('Pere Mas', '87654321B', '600333444');

Exercici 1: Creació d'usuaris i grups

  1. Crea els grups professorat i secretaria, que no han de poder iniciar sessió.

  2. Crea els usuaris següents:

    • laura: professora, amb una contrasenya que caduque el 30 de juny.
    • pau: professor, amb un màxim d'una connexió simultània.
    • marta: personal de secretaria, amb permís per crear bases de dades.

  3. Comprova amb \du i amb la vista pg_roles que els rols s'han creat amb els atributs correctes.

  4. Intenta iniciar sessió amb el grup professorat i explica l'error que obtens.

Exercici 2: Pertinença a grups

  1. Fes membres del grup professorat els usuaris laura i pau, i membre de secretaria l'usuària marta.

  2. Comprova la pertinença amb la metaordre \drg.

  3. Escriu una consulta sobre pg_auth_members que mostre els membres de cada grup.

  4. pau passa a ser també coordinador. Crea el grup coordinacio, fes-lo membre de professorat i afig-hi pau. Comprova que ara pau és membre directe de coordinacio i indirecte de professorat.

Exercici 3: Modificació de rols

  1. Canvia la contrasenya de laura i fes que les seues sessions mostren les dates en format ISO, DMY.

  2. pau està de baixa: impedeix que puga iniciar sessió sense eliminar el seu compte. Comprova que no es pot connectar.

Exercici 4: Assignació de privilegis a grups

  1. La secretaria gestiona les dades de l'alumnat: concedeix al grup secretaria tots els privilegis de consulta i modificació sobre la taula alumnes, incloent-hi la seqüència de la clau primària.

  2. El professorat ha de poder consultar el nom de l'alumnat, però no el DNI ni el telèfon. Concedeix al grup professorat els privilegis necessaris.

  3. El professorat ha de poder consultar, inserir i modificar notes, però no eliminar-les.

  4. Connectat com laura, comprova que:

    • Pot consultar el nom de l'alumnat, però no el DNI.
    • Pot inserir una nota, però no eliminar-la.

Exercici 5: Consulta dels privilegis

  1. Mostra els privilegis de les taules alumnes i notes amb la metaordre \dp i interpreta cada lletra de la columna Access privileges.

  2. Escriu una consulta sobre information_schema.role_table_grants que mostre els privilegis del grup professorat.

  3. Comprova amb has_table_privilege si pau pot eliminar notes. Explica el resultat tenint en compte que pau és membre de coordinacio.

Exercici 6: Revocació i privilegis per defecte

  1. La coordinació ha de poder eliminar notes. Concedeix-li el privilegi i comprova que ara pau sí que pot eliminar-ne, però laura no.

  2. Retira al professorat el privilegi de modificar notes. Comprova que tant laura com pau l'han perdut.

  3. Intenta retirar a laura el privilegi SELECT sobre notes amb REVOKE ... FROM laura. Pot continuar consultant les notes? Per què?

  4. Fes que la secretaria puga consultar automàticament totes les taules que es creen en el futur en l'esquema public. Crea una taula nova i comprova que marta la pot consultar.

Exercici 7: Eliminació de rols

  1. Connectat com marta, crea la taula matricules en la base de dades institut. Si no pots, concedeix-li abans el permís necessari.

  2. Elimina l'usuària marta sense perdre la taula matricules, que ha de passar a ser propietat de postgres. Explica per què no es pot eliminar directament.

Ampliacions

Si has acabat els exercicis anteriors, pots aprofundir en el control d'accés amb aquestes propostes:

  • Investiga el mòdul passwordcheck i explica quines polítiques de contrasenyes permet aplicar.
  • Investiga la seguretat a nivell de fila (Row Level Security) i fes que cada membre del professorat només veja les notes de les seues assignatures.
📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez