Salta el contingut

PR02 — Pràctica: Gestió d'accés amb Docker

Informació general

Camp Detall
Mòdul M0377 — Administració de SGBD
RA RA3 — Control d'accés
Durada estimada 4 hores
Modalitat Individual
Lliurament Script SQL + captures de pantalla + memòria breu

Objectius

Al finalitzar aquesta pràctica haureu de ser capaços de:

  • Crear una jerarquia de rols RBAC per a una empresa fictícia.
  • Crear usuaris de base de dades i assignar-los rols adequats.
  • Crear taules i concedir privilegis granulars als rols correctes.
  • Crear una vista de seguretat per restringir l'accés a dades sensibles.
  • Verificar l'accés de cada rol connectant-se com a cada usuari.
  • Habilitar pgaudit i interpretar els registres d'auditoria.
  • Revocar un privilegi i comprovar-ne l'efecte.

Prerequisits

Abans de començar, assegureu-vos que teniu:

  • Docker Desktop instal·lat i en execució.
  • Haver completat la pràctica practica_postgresql.md (instància PostgreSQL activa amb Docker).
  • Accés a psql des del contenidor o des de l'amfitrió.
  • (Opcional) pgAdmin 4 o DBeaver per visualitzar l'estructura.

Escenari: empresa TecnoSapa SL

TecnoSapa SL és una empresa de venda de material informàtic. La seva base de dades (empresa) conté informació de clients, productes, comandes i empleats.

Heu de configurar el control d'accés per als rols següents:

Rol Descripció Privilegis
rol_admin Administrador de la BD Tots els privilegis
rol_analista Analista de dades SELECT sobre totes les taules
rol_vendes Equip comercial SELECT + INSERT + UPDATE sobre comandes i clients
rol_lectura Accés de lectura bàsic SELECT sobre productes i clients

Usuaris que caldrà crear:

Usuari Rol assignat Descripció
admin_[vostre_nom] rol_admin Vostre compte d'administrador
ana_vendes rol_vendes Comercial d'exemple
pau_analista rol_analista Analista de dades
guest_lectura rol_lectura Usuari de lectura pública

Pas 1 — Preparació de l'entorn Docker

1.1 Iniciar el contenidor PostgreSQL

Si no el teniu en marxa, inicieu el contenidor de la pràctica anterior:

# Iniciar el contenidor PostgreSQL
docker start postgres-empresa

# Verificar que està en marxa
docker ps | grep postgres-empresa

# Connectar-se com a superusuari
docker exec -it postgres-empresa psql -U postgres

Si no teniu el contenidor creat, creeu-lo ara:

docker run -d \
  --name postgres-empresa \
  -e POSTGRES_PASSWORD=AdminPass#2024! \
  -e POSTGRES_DB=empresa \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  postgres:17

1.2 Crear l'estructura de taules

Connecteu-vos a la base de dades empresa i creeu les taules:

-- Connectar-se a la BD empresa
\c empresa

-- Crear les taules de l'escenari
CREATE TABLE IF NOT EXISTS clients (
    id          SERIAL PRIMARY KEY,
    nom         VARCHAR(100) NOT NULL,
    cognoms     VARCHAR(100),
    empresa     VARCHAR(150),
    correu      VARCHAR(100) UNIQUE,
    telefon     VARCHAR(20),
    adreca      TEXT,
    nif         VARCHAR(15),
    data_alta   DATE DEFAULT CURRENT_DATE,
    actiu       BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS productes (
    id           SERIAL PRIMARY KEY,
    codi         VARCHAR(20) UNIQUE NOT NULL,
    nom          VARCHAR(200) NOT NULL,
    descripcio   TEXT,
    categoria    VARCHAR(50),
    preu_cost    NUMERIC(10, 2),
    preu_venda   NUMERIC(10, 2) NOT NULL,
    estoc        INT DEFAULT 0,
    actiu        BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS comandes (
    id              SERIAL PRIMARY KEY,
    num_comanda     VARCHAR(20) UNIQUE NOT NULL,
    client_id       INT REFERENCES clients(id),
    comercial       VARCHAR(100),  -- nom d'usuari de la BD
    data_comanda    DATE DEFAULT CURRENT_DATE,
    data_entrega    DATE,
    estat           VARCHAR(20) DEFAULT 'pendent'
                    CHECK (estat IN ('pendent','confirmada','enviat','completada','cancel·lada')),
    total           NUMERIC(12, 2),
    notes           TEXT
);

CREATE TABLE IF NOT EXISTS linies_comanda (
    id          SERIAL PRIMARY KEY,
    comanda_id  INT REFERENCES comandes(id) ON DELETE CASCADE,
    producte_id INT REFERENCES productes(id),
    quantitat   INT NOT NULL CHECK (quantitat > 0),
    preu_unit   NUMERIC(10, 2) NOT NULL,
    descompte   NUMERIC(5, 2) DEFAULT 0
);

CREATE TABLE IF NOT EXISTS empleats (
    id              SERIAL PRIMARY KEY,
    nom             VARCHAR(100) NOT NULL,
    cognoms         VARCHAR(100) NOT NULL,
    departament     VARCHAR(50),
    lloc_treball    VARCHAR(100),
    correu          VARCHAR(100),
    telefon         VARCHAR(20),
    salari          NUMERIC(10, 2),
    nif             VARCHAR(15),
    data_alta       DATE DEFAULT CURRENT_DATE,
    actiu           BOOLEAN DEFAULT TRUE
);

-- Inserir dades de mostra
INSERT INTO clients (nom, cognoms, empresa, correu, telefon) VALUES
('Maria', 'Puig Ros', 'Consultoria TIC SL', 'maria.puig@consultoria.cat', '972100001'),
('Joan', 'Ferrer Costa', 'Distribucions Nord SA', 'joan.ferrer@nord.es', '972100002'),
('Laia', 'Mas Bosch', NULL, 'laia.mas@gmail.com', '972100003'),
('Pere', 'Vila Sala', 'Empresa Beta SL', 'pere.vila@beta.cat', '972100004');

INSERT INTO productes (codi, nom, categoria, preu_cost, preu_venda, estoc) VALUES
('LAP-001', 'Portàtil Pro 15', 'Portàtils', 450.00, 799.99, 25),
('MON-001', 'Monitor 27" 4K', 'Monitors', 180.00, 349.99, 40),
('TEC-001', 'Teclat mecànic RGB', 'Perifèrics', 45.00, 89.99, 100),
('RAT-001', 'Ratolí ergonòmic', 'Perifèrics', 20.00, 39.99, 150),
('SSD-001', 'SSD NVMe 1TB', 'Emmagatzematge', 60.00, 119.99, 80);

INSERT INTO comandes (num_comanda, client_id, comercial, estat, total) VALUES
('COM-2024-001', 1, 'ana_vendes', 'completada', 1149.98),
('COM-2024-002', 2, 'ana_vendes', 'pendent', 349.99),
('COM-2024-003', 3, 'ana_vendes', 'confirmada', 89.99),
('COM-2024-004', 4, 'ana_vendes', 'pendent', 799.99);

INSERT INTO empleats (nom, cognoms, departament, lloc_treball, correu, salari, nif) VALUES
('Francesc', 'Barragán', 'IT', 'Administrador SGBD', 'fbarragan@tecnosapa.cat', 42000.00, '12345678A'),
('Anna', 'García', 'Vendes', 'Comercial', 'agarcia@tecnosapa.cat', 28000.00, '23456789B'),
('Pau', 'Martí', 'Anàlisi', 'Analista de dades', 'pmarti@tecnosapa.cat', 35000.00, '34567890C'),
('Marta', 'López', 'RRHH', 'Responsable RRHH', 'mlopez@tecnosapa.cat', 38000.00, '45678901D');

Pas 2 — Creació de la jerarquia de rols

Substituïu [vostre_nom] pel vostre nom o inicials en tots els exemples.

-- Connectats com a postgres (superusuari)

-- 2.1 Crear els rols (sense capacitat de login)
CREATE ROLE rol_lectura;
CREATE ROLE rol_vendes;
CREATE ROLE rol_analista;
CREATE ROLE rol_admin;

-- 2.2 Concedir privilegis de connexió a tots els rols
GRANT CONNECT ON DATABASE empresa TO rol_lectura;
GRANT USAGE ON SCHEMA public TO rol_lectura;

-- 2.3 Privilegis del rol base (rol_lectura)
GRANT SELECT ON productes TO rol_lectura;
GRANT SELECT ON clients TO rol_lectura;

-- 2.4 rol_vendes hereta rol_lectura i afegeix DML sobre comandes/clients
GRANT rol_lectura TO rol_vendes;
GRANT SELECT, INSERT, UPDATE ON comandes TO rol_vendes;
GRANT SELECT, INSERT, UPDATE ON clients TO rol_vendes;
GRANT USAGE ON SEQUENCE comandes_id_seq TO rol_vendes;
GRANT USAGE ON SEQUENCE clients_id_seq TO rol_vendes;

-- 2.5 rol_analista hereta rol_lectura i té SELECT sobre tot
GRANT rol_lectura TO rol_analista;
GRANT SELECT ON empleats TO rol_analista;
GRANT SELECT ON comandes TO rol_analista;
GRANT SELECT ON linies_comanda TO rol_analista;

-- 2.6 rol_admin té tots els privilegis (herència i privilegis directes)
GRANT rol_analista TO rol_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rol_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO rol_admin;

-- Verificar l'estructura de rols
SELECT r.rolname AS rol, m.rolname AS hereta_de
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
WHERE r.rolname LIKE 'rol_%'
ORDER BY r.rolname;

Resultat esperat:

    rol         | hereta_de
----------------+------------
 rol_vendes     | rol_lectura
 rol_analista   | rol_lectura
 rol_admin      | rol_analista

Pas 3 — Creació d'usuaris i assignació de rols

-- 3.1 Crear els usuaris
-- IMPORTANT: substituïu [vostre_nom] pel vostre nom real
CREATE USER admin_[vostre_nom] WITH PASSWORD 'AdminPass#2024!' LOGIN;
CREATE USER ana_vendes WITH PASSWORD 'VendesPass#2024!' LOGIN;
CREATE USER pau_analista WITH PASSWORD 'AnalistaPass#2024!' LOGIN;
CREATE USER guest_lectura WITH PASSWORD 'LecturaPass#2024!' LOGIN;

-- 3.2 Assignar rols als usuaris
GRANT rol_admin TO admin_[vostre_nom];
GRANT rol_vendes TO ana_vendes;
GRANT rol_analista TO pau_analista;
GRANT rol_lectura TO guest_lectura;

-- 3.3 Verificar la configuració
\du

-- O més detallat:
SELECT
    u.usename AS usuari,
    u.usesuper AS superuser,
    u.usecreatedb AS createdb,
    array_to_string(ARRAY(
        SELECT b.rolname
        FROM pg_auth_members m
        JOIN pg_roles b ON m.roleid = b.oid
        WHERE m.member = u.usesysid
    ), ', ') AS rols_assignats
FROM pg_user u
WHERE u.usename IN ('ana_vendes', 'pau_analista', 'guest_lectura', 'admin_[vostre_nom]')
ORDER BY u.usename;

Pas 4 — Creació de la vista de seguretat per a vendes

El rol rol_vendes necessita consultar dades dels empleats per conèixer qui és el comercial responsable de cada comanda, però no pot veure els salaris ni el NIF.

-- 4.1 Crear la vista de seguretat
CREATE VIEW empleats_vendes AS
SELECT
    id,
    nom,
    cognoms,
    departament,
    lloc_treball,
    correu,
    telefon
FROM empleats
WHERE actiu = TRUE;
-- Les columnes 'salari' i 'nif' queden ocultes

-- 4.2 Concedir accés a la vista al rol vendes
GRANT SELECT ON empleats_vendes TO rol_vendes;

-- 4.3 Assegurar que el rol vendes NO té accés directe a la taula base
REVOKE ALL ON empleats FROM rol_vendes;

-- 4.4 Vista de les pròpies comandes per al rol vendes
CREATE VIEW les_meves_comandes AS
SELECT
    c.id,
    c.num_comanda,
    c.data_comanda,
    c.data_entrega,
    c.estat,
    c.total,
    cl.nom || ' ' || COALESCE(cl.cognoms, '') AS client,
    cl.correu AS correu_client,
    cl.telefon AS telefon_client
FROM comandes c
JOIN clients cl ON c.client_id = cl.id
WHERE c.comercial = current_user;

GRANT SELECT ON les_meves_comandes TO rol_vendes;

-- 4.5 Verificar la vista
\d empleats_vendes
SELECT * FROM empleats_vendes;

Pas 5 — Verificació d'accés per rol

Ara comprovarem que cada usuari pot fer el que ha de poder fer i no pot fer el que no ha de poder fer. Heu de connectar-vos com a cada usuari i executar les consultes.

Verificació del rol de lectura (guest_lectura)

# Connectar-se com a guest_lectura
docker exec -it postgres-empresa psql -U guest_lectura -d empresa

-- Hauria de funcionar:
SELECT id, nom, preu_venda FROM productes LIMIT 5;
SELECT id, nom, correu FROM clients LIMIT 5;

-- Hauria de FALLAR (sense permís):
SELECT * FROM comandes;
-- ERROR: permission denied for table comandes

SELECT * FROM empleats;
-- ERROR: permission denied for table empleats

INSERT INTO clients (nom) VALUES ('Test');
-- ERROR: permission denied for table clients

Verificació del rol de vendes (ana_vendes)

docker exec -it postgres-empresa psql -U ana_vendes -d empresa

-- Hauria de funcionar:
SELECT * FROM clients LIMIT 5;
SELECT * FROM productes LIMIT 5;
SELECT * FROM comandes WHERE comercial = current_user;
SELECT * FROM les_meves_comandes;
SELECT * FROM empleats_vendes;

INSERT INTO clients (nom, cognoms, correu)
VALUES ('Nou', 'Client', 'nou.client@test.cat');

UPDATE comandes SET estat = 'confirmada'
WHERE id = 2 AND comercial = current_user;

-- Hauria de FALLAR:
SELECT * FROM empleats;
-- ERROR: permission denied for table empleats

SELECT salari FROM empleats_vendes;
-- ERROR: column "salari" does not exist (no és a la vista!)

DELETE FROM clients WHERE id = 1;
-- ERROR: permission denied for table clients

Verificació del rol analista (pau_analista)

docker exec -it postgres-empresa psql -U pau_analista -d empresa

-- Hauria de funcionar:
SELECT * FROM clients;
SELECT * FROM comandes;
SELECT * FROM empleats;
SELECT * FROM productes;
SELECT * FROM linies_comanda;

-- Consultes analítiques
SELECT
    DATE_TRUNC('month', data_comanda) AS mes,
    COUNT(*) AS num_comandes,
    SUM(total) AS total_vendes
FROM comandes
GROUP BY DATE_TRUNC('month', data_comanda)
ORDER BY mes;

-- Hauria de FALLAR:
INSERT INTO comandes (num_comanda, client_id) VALUES ('TEST-001', 1);
-- ERROR: permission denied for table comandes

DROP TABLE productes;
-- ERROR: must be owner of table productes

Verificació del rol admin (admin_[vostre_nom])

docker exec -it postgres-empresa psql -U admin_[vostre_nom] -d empresa

-- Hauria de poder fer tot:
SELECT * FROM empleats;
INSERT INTO productes (codi, nom, preu_venda) VALUES ('TEST-001', 'Producte Test', 9.99);
UPDATE empleats SET salari = salari * 1.03 WHERE id = 2;
DELETE FROM productes WHERE codi = 'TEST-001';
CREATE TABLE log_activitat (id SERIAL, ts TIMESTAMP, accio TEXT);
DROP TABLE log_activitat;

Pas 6 — Habilitació de pgaudit

6.1 Instal·lar pgaudit al contenidor

# Entrar al contenidor com a root
docker exec -it -u root postgres-empresa bash

# Instal·lar l'extensió
apt-get update && apt-get install -y postgresql-16-pgaudit

# Sortir del contenidor
exit

6.2 Configurar postgresql.conf

# Editar postgresql.conf dins el contenidor
docker exec -it postgres-empresa bash -c \
  "echo \"shared_preload_libraries = 'pgaudit'\" >> /var/lib/postgresql/data/postgresql.conf"

docker exec -it postgres-empresa bash -c \
  "echo \"pgaudit.log = 'read,write,ddl,role,connection'\" >> /var/lib/postgresql/data/postgresql.conf"

docker exec -it postgres-empresa bash -c \
  "echo \"pgaudit.log_catalog = on\" >> /var/lib/postgresql/data/postgresql.conf"

docker exec -it postgres-empresa bash -c \
  "echo \"pgaudit.log_parameter = on\" >> /var/lib/postgresql/data/postgresql.conf"

# Reiniciar el contenidor per aplicar canvis
docker restart postgres-empresa

# Esperar uns segons i reconnectar
sleep 5
docker exec -it postgres-empresa psql -U postgres -d empresa

6.3 Crear l'extensió i verificar

-- Connectats com a postgres a la BD empresa
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Verificar que pgaudit és actiu
SHOW pgaudit.log;
SHOW shared_preload_libraries;

SELECT name, setting, short_desc
FROM pg_settings
WHERE name LIKE 'pgaudit%'
ORDER BY name;

6.4 Generar events i revisar els logs

-- Des de la sessió de postgres, executar algunes operacions per generar logs:
SELECT * FROM empleats;
INSERT INTO clients (nom) VALUES ('Client Auditoria Test');
GRANT SELECT ON productes TO guest_lectura;
REVOKE SELECT ON productes FROM guest_lectura;

# Revisar el log de PostgreSQL
docker exec -it postgres-empresa bash -c \
  "tail -50 \$(ls -t /var/log/postgresql/*.log 2>/dev/null | head -1 || \
   find /var/lib/postgresql/data/log -name '*.log' | head -1)"

# Alternativa: mirar directament el stdout del contenidor
docker logs --tail 100 postgres-empresa | grep AUDIT

Exemple de sortida esperada:

LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,public.empleats,
      "SELECT * FROM empleats",<not logged>
LOG:  AUDIT: SESSION,2,1,WRITE,INSERT,TABLE,public.clients,
      "INSERT INTO clients (nom) VALUES ('Client Auditoria Test')",<not logged>
LOG:  AUDIT: SESSION,3,1,ROLE,GRANT,,,
      "GRANT SELECT ON productes TO guest_lectura",<not logged>

Pas 7 — Revocació de privilegis i verificació

7.1 Revocar el privilegi UPDATE d'ana_vendes sobre clients

-- Connectats com a postgres

-- Revocar UPDATE sobre clients del rol vendes
REVOKE UPDATE ON clients FROM rol_vendes;

-- Verificar els privilegis restants
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'rol_vendes'
ORDER BY table_name, privilege_type;

7.2 Verificar l'efecte de la revocació

docker exec -it postgres-empresa psql -U ana_vendes -d empresa

-- Hauria de funcionar (SELECT i INSERT continuen):
SELECT id, nom FROM clients LIMIT 3;
INSERT INTO clients (nom, correu) VALUES ('Nou Client 2', 'nou2@test.cat');

-- Hauria de FALLAR (UPDATE ha estat revocat):
UPDATE clients SET telefon = '972000000' WHERE id = 1;
-- ERROR: permission denied for table clients

7.3 Restaurar el privilegi

-- Tornar a concedir el privilegi
GRANT UPDATE ON clients TO rol_vendes;

-- Verificar que funciona de nou (connectar-se com ana_vendes)
UPDATE clients SET telefon = '972000001' WHERE id = 1;
-- UPDATE 1  ← Funciona!

Resum de l'escenari implementat

Un cop completada la pràctica, la vostra base de dades hauria de tenir la configuració següent:

graph TD
    subgraph "Usuaris"
        U1["admin_[nom]"]
        U2[ana_vendes]
        U3[pau_analista]
        U4[guest_lectura]
    end

    subgraph "Rols"
        R1[rol_lectura]
        R2[rol_vendes]
        R3[rol_analista]
        R4[rol_admin]
    end

    subgraph "Objectes"
        T1[clients]
        T2[productes]
        T3[comandes]
        T4[empleats]
        V1[empleats_vendes\nVISTA]
        V2[les_meves_comandes\nVISTA]
    end

    U1 --> R4
    U2 --> R2
    U3 --> R3
    U4 --> R1

    R4 --> R3
    R3 --> R1
    R2 --> R1

    R1 -->|"SELECT"| T1
    R1 -->|"SELECT"| T2
    R2 -->|"SELECT,INSERT\nUPDATE"| T3
    R2 -->|"SELECT"| V1
    R2 -->|"SELECT"| V2
    R3 -->|"SELECT"| T3
    R3 -->|"SELECT"| T4
    R4 -->|"ALL"| T1
    R4 -->|"ALL"| T2
    R4 -->|"ALL"| T3
    R4 -->|"ALL"| T4

Preguntes de reflexió

Preguntes de reflexió

Responeu les preguntes següents a la memòria de la pràctica (5-10 línies cadascuna):

  1. Principi del mínim privilegi: Expliqueu com heu aplicat aquest principi en el disseny dels rols. Quins privilegis heu decidit NO concedir a cada rol i per qué?

  2. Jerarquia de rols: Quins avantatges té que rol_vendes hereti de rol_lectura en lloc de tenir els privilegis de lectura concedits directament? Qué passa si modifiqueu els privilegis de rol_lectura?

  3. Vistes de seguretat: Per qué és millor crear una vista empleats_vendes i concedir-hi accés en lloc de concedir accés a columnes concretes de la taula empleats directament?

  4. Auditoria: Quins events heu vist als logs d'auditoria que us han sorprès? Hi ha algun event que creieu que s'hauria d'auditar i que no heu configurat?

  5. Escenari real: Si fóssiu l'administrador/a de SGBD d'una empresa real, quins passos addicionals de seguretat afegiries a la configuració que heu implementat? Penseu en aspectes com SSL, rotació de contrasenyes, monitoratge continu, etc.

Criteris d'avaluació

Criteri Pes Descripció
Creació correcta de rols i jerarquia 20% Tots els rols creats, herència correcta
Creació d'usuaris i assignació 15% Usuaris creats amb contrasenyes segures i rols correctes
Privilegis correctes per rol 20% Cada rol té exactament els privilegis especificats
Vista de seguretat funcional 15% Vista creada, accessible al rol correcte, amaga salari i NIF
Verificació d'accés documentada 15% Captures de pantalla de cada prova positiva i negativa
Configuració de pgaudit 10% pgaudit instal·lat, configurat i amb logs generats
Reflexió i qualitat de la memòria 5% Respostes ben argumentades, clares i en Catalan

Lliurament

Heu de lliurar els arxius següents comprimits en un fitxer .zip amb el nom practica_acces_[vostre_nom].zip:

practica_acces_[vostre_nom]/
├── 01_estructura_taules.sql       # Script creació taules i dades
├── 02_rols_i_usuaris.sql          # Script creació de rols i usuaris
├── 03_privilegis.sql              # Script GRANTs i REVOKEs
├── 04_vistes_seguretat.sql        # Script creació de vistes
├── 05_verificacio.sql             # Consultes de verificació d'accés
├── 06_auditoria.sh                # Comandes de configuració pgaudit
├── captures/
│   ├── rol_lectura_ok.png         # Captures de proves d'accés
│   ├── rol_lectura_fail.png
│   ├── rol_vendes_ok.png
│   ├── rol_vendes_fail.png
│   ├── rol_analista_ok.png
│   ├── rol_admin_ok.png
│   ├── pgaudit_log.png            # Captures dels logs d'auditoria
│   └── revocacio_fail.png         # Captura de la revocació
└── memoria.pdf                    # Memòria amb respostes a la reflexió

Consell per a la memòria

La memòria no cal que sigui llarga, però sí que demostri que heu entès el que heu fet. Expliqueu les decisions de disseny (per qué heu triat una certa estructura de rols, per qué heu creat la vista d'aquella manera, etc.), no simplement què heu executat.