TP TIW4 2020-2021 "autorisation" : rapport
==========================================

Introduction
------------

- Numéro de binôme
* Molinares Diogenes 12019196
* Bunel Maxime 11914012
- Nom DNS de la VM fournie
* 192.168.237.138

Prise en main
-------------

### Identification

#### Indiquer quelle sont les machines qui peuvent se connecter au serveur

Les machines sur sous-reseaux 192.168.237.128/27

#### Quel est le mode d'authentification imposé par PostgreSQL
Authentification avec utilisateur / mot de passe

### Création de bases et utilisateurs

#### Donner les requêtes SQL pour vérifier que les utilisateurs sont créés

-- ici les définitions  
create user lambda1 with encrypted password 'lambda1';  
create user lambda2 with encrypted password 'lambda2';  
create user adjoint with encrypted password 'adjoint';  
create user responsable with encrypted password 'responsable';  

-- ici les vérifications  
SELECT * FROM pg_roles;  
SELECT 1 FROM pg_roles WHERE rolname='lambda1'; 
SELECT 1 FROM pg_roles WHERE rolname='lambda2';  
SELECT 1 FROM pg_roles WHERE rolname='adjoint';  
SELECT 1 FROM pg_roles WHERE rolname='responsable';  

#### Montrer que vous avez vérifié que ces utilisateurs peuvent s'authentifier depuis une autre VM

Avec ces commandes noux pouvons nous connecter au utilisateur

psql -U lambda1 -W -p 5432 -h 192.168.237.146 -d test
psql -U lambda2 -W -p 5432 -h 192.168.237.146 -d test
psql -U responsable -W -p 5432 -h 192.168.237.146 -d test
psql -U adjoint -W -p 5432 -h 192.168.237.146 -d test

Le modèle de contrôle d'accès _applicatif_
------------------------------------------

### Schéma de départ

#### Commenter chacune des lignes du début du script ci-dessous

--Creation d'un schéma nommé rbac si il n'existe pas déjà
CREATE SCHEMA IF NOT EXISTS rbac;  
--Défini le chemin de recherche de la table test 
ALTER DATABASE "test" SET search_path TO "$user", rbac, public; 

-- Défini le chemin de recherche de la session actuelle
SET search_path TO "$user", rbac, public;  
  
-- Supprime le type de données http_method si il existe
DROP TYPE IF EXISTS http_method;  

-- Créer le type de données http_method qui est un enum qui peut avoir comme valeur (get,post,put,delete)
CREATE TYPE http_method AS ENUM ('GET', 'POST', 'PUT', 'DELETE', 'PATCH');  

#### Expliquez ce qui change sur la hiérarchie de rôle selont qu'on prenne pour PK de `rbac.inherits` une des 4 possibilités suivantes

-- Evite les doublons dans la table, un enfant peut avoir plusieurs parents et un parent peut avoir plusieurs enfants
  PRIMARY KEY (role_id_child, role_id_parent) --celle du script
-- un enfant ne peux avoir qu'un seul parent et un parent peut avoir plusieurs enfants
  PRIMARY KEY (role_id_child)
-- un parent ne peux avoir qu'un seul enfant et un enfant peut avoir plusieurs parents
  PRIMARY KEY (role_id_parent)
-- Possibilité d'avoir des doublons
  -- pas de PK déclarée

### Calcul des décisions d'accès

#### Donner la définition et les tests de la vue `rbac.rbac_inherits_trans`

CREATE OR REPLACE RECURSIVE VIEW rbac.inherits_trans(role_id_child, role_id_parent) AS ( 
	SELECT inherits.role_id_child, inherits.role_id_parent FROM rbac.inherits inherits 
	UNION SELECT role_id, role_id FROM rbac.role 
	UNION SELECT inherits.role_id_child, trans.role_id_parent FROM rbac.inherits inherits 
	INNER JOIN inherits_trans trans ON inherits.role_id_parent = trans.role_id_child
);

-- ici les tests sur le jeu d'essai
SELECT * FROM rbac.inherits_trans;

#### Donner la définition et les tests de la vue `rbac.rbac_matrix`

CREATE OR REPLACE VIEW rbac.rbac_matrix(usr_id, obj_id, act_id) AS(
    SELECT usr_id, obj_id, act_id
    FROM rbac.ura ura
    INNER JOIN rbac.pra pra ON ura.role_id = pra.role_id
);

-- ici les tests sur le jeu d'essai  
SELECT * FROM rbac.rbac_matrix;

#### Donner la définition et les tests de la fonction `rbac.authorized_actions`

CREATE OR REPLACE FUNCTION rbac.authorized_actions(u_name text, o_name text)
RETURNS TABLE(action_id http_method) AS 
  $$
    SELECT DISTINCT act_id
    FROM rbac.rbac_matrix
    INNER JOIN rbac.usr usr ON usr.usr_name = u_name and usr.usr_id = rbac.rbac_matrix.usr_id
    INNER JOIN rbac.obj obj ON obj.obj_name = o_name and obj.obj_id = rbac.rbac_matrix.obj_id
  $$
  LANGUAGE SQL
  STABLE
  RETURNS NULL ON NULL INPUT;

-- ici les tests sur le jeu d'essai  
-- Cette commande va retourner que l'utilisateur 0 sur l'objet 0 à l'authorisation GET
select * from rbac.authorized_actions('U0', 'O0');

  action_id 
═══════════
 GET
(1 row)

-- Cette commande retour aucun resultat
select * from rbac.authorized_actions('U0', 'O1');

#### Donner la définition et les tests de la fonction `rbac.count_auth_users`

CREATE OR REPLACE FUNCTION rbac.count_auth_users(r_id integer) 
RETURNS bigint AS 
$$ 
	SELECT count(*) 
	FROM ( -- roles fils de r_id 
		SELECT DISTINCT trans.role_id_child 
		FROM rbac.inherits_trans trans 
		WHERE trans.role_id_parent = r_id 
		UNION 
		SELECT DISTINCT ura.role_id 
		FROM rbac.ura ura 
		WHERE ura.role_id = r_id 
	) 
	childs 
	INNER JOIN rbac.ura ura ON ura.role_id = childs.role_id_child 
$$ 
LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;

-- ici les tests sur le jeu d'essai  
-- On applique le fonction sur l'utilisateur 3
SELECT * FROM rbac.count_auth_users(3);

  count_auth_users 
══════════════════
                1
(1 row)

### Extension aux sessions

#### Donner les définitions des nouvelles tables et le trigger

-- ici les définitions  

CREATE TABLE IF NOT EXISTS rbac.session(
  usr_id integer REFERENCES rbac.usr(usr_id),
  role_id integer REFERENCES rbac.role(role_id),
  PRIMARY KEY (usr_id, role_id)
);

CREATE FUNCTION session_trigger() RETURNS trigger AS $session_trigger$
    BEGIN
        IF NEW.usr_id IS NULL  THEN
                RAISE EXCEPTION 'usr_id ne peut pas etre NULL.';
        END IF;
        IF NEW.role_id IS NULL  THEN
            RAISE EXCEPTION 'role_id ne peut pas etre NULL.';
        END IF;
        IF NEW.usr_id NOT IN (SELECT DISTINCT usr_id FROM rbac.usr) THEN
            RAISE EXCEPTION 'utilisateur nexiste pas';
        END IF;
        IF NEW.role_id NOT IN (SELECT DISTINCT role_id FROM rbac.role) THEN
            RAISE EXCEPTION 'Le role nexiste pas.';
        END IF;
        IF (NEW.usr_id, NEW.role_id) NOT IN (SELECT DISTINCT usr_id, role_id FROM rbac.ura) THEN
            RAISE EXCEPTION 'utilisateur ne peut pas posséder ce role.';
        END IF;
        RETURN NEW;
    END;
$session_trigger$ LANGUAGE plpgsql;


CREATE TRIGGER session_trigger BEFORE INSERT OR UPDATE ON rbac.session
    FOR EACH ROW EXECUTE PROCEDURE session_trigger();
    
    CREATE OR REPLACE FUNCTION rbac.authorized_actions_session(u_name text, o_name text)
RETURNS TABLE(action_id http_method) AS 
  $$
    select distinct act_id
    from rbac.rbac_matrix
    INNER JOIN rbac.usr usr ON usr.usr_name = u_name and usr.usr_id = rbac.rbac_matrix.usr_id
    INNER JOIN rbac.obj obj ON obj.obj_name = o_name and obj.obj_id = rbac.rbac_matrix.obj_id
    INNER JOIN rbac.session session ON session.usr_id = usr.usr_id
  $$
  LANGUAGE SQL
  STABLE
  RETURNS NULL ON NULL INPUT;

#### Donner les tests du trigger

-- ici les tests sur le jeu d'essai
insert into rbac.session values (0,0);
-- INSERT 0 1

insert into rbac.session values (0,3);
ERROR:  P0001: utilisateur ne peut pas posséder ce role.
CONTEXT:  PL/pgSQL function session_trigger() line 16 at RAISE

insert into rbac.session values (NULL, NULL);
ERROR:  P0001: usr_id ne peut pas etre NULL.
CONTEXT:  PL/pgSQL function session_trigger() line 4 at RAISE

insert into rbac.session values (0, NULL);
ERROR:  P0001: role_id ne peut pas etre NULL.

-- l'utilisateur 1 n'existe pas sur la table session, meme s'il a le droit d'acceder sur l'objet 1
select * from authorized_actions_session('U1', 'O1');
 action_id
═══════════
(0 rows)

-- l'utilisateur 0 apparait sur la table session
select * from authorized_actions_session('U0', 'O0')
test-# ;
 action_id
═══════════
 GET
(1 row)

Gestion des droits sur le modèle
--------------------------------

### Modèle d'administration, base

#### Donner les `GRANT` et `REVOKE`

-- connection sur la bdd test
GRANT CONNECT ON DATABASE test TO public 

REVOKE ALL PRIVILEGES ON SCHEMA RBAC from PUBLIC ;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA RBAC from PUBLIC ;
GRANT USAGE ON SCHEMA RBAC to public;
GRANT SELECT ON ALL TABLES IN SCHEMA RBAC to public;

### Délégation à l'adjoint

#### Modification de la table

ALTER TABLE rbac.role ADD delegate BOOL DEFAULT 0 NOT NULL;

#### Donner le ROW LEVEL SECURITY pour `rbac.pra`

-- ici la définition de la politique RLS 
   
CREATE POLICY policy_adjoint_insert ON rbac.pra
    FOR INSERT
    TO adjoint
    WITH check (rbac.pra.role_id IN (
        SELECT role_id  FROM rbac.role WHERE delegate is true)
    );

CREATE POLICY policy_adjoint_update ON rbac.pra
    FOR UPDATE
    TO adjoint
    USING (rbac.pra.role_id IN (
        SELECT role_id  FROM rbac.role WHERE delegate is true)
    );


CREATE POLICY policy_adjoint_delete ON rbac.pra
    FOR DELETE
    TO adjoint
    USING (rbac.pra.role_id IN (
        SELECT role_id  FROM rbac.role WHERE delegate is true)
    );

-- ici les tests sur le jeu d'essai  
insert into rbac.pra values (5,1,'POST');

#### Donner le ROW LEVEL SECURITY pour `rbac.inherits`

-- ici la définition de la politique RLS  
CREATE POLICY policy_adjoint_in_insert ON rbac.inherits
    FOR INSERT
    TO adjoint
    WITH CHECK (rbac.inherits.role_id_parent IN (
        SELECT role_id FROM rbac.role WHERE delegate is true
    ));

CREATE POLICY policy_adjoint_in_update ON rbac.inherits
    FOR UPDATE
    TO adjoint
    WITH CHECK (rbac.inherits.role_id_parent IN (
        SELECT role_id FROM rbac.role WHERE delegate is true
    ));

CREATE POLICY policy_adjoint_in_delete ON rbac.inherits
    FOR DELETE
    TO adjoint
    USING (rbac.inherits.role_id_parent IN (
        SELECT role_id FROM rbac.role WHERE delegate is true
    ));

-- ici les tests sur le jeu d'essai 
select * from rbac.inherits;