filtre et limitation d’accès sur une table¶

Il est parfois utile de limiter la visualisation d’une table pour un utilisateur particulier.

Un exemple simple est le suivant: un commercial ne souhaite voir que c’est client.

la table client est composé comme cela:

id
lib
commercial

Il y a trois possiblités:

faire un filtre à chaque requête sql la charge de travail est du côté du développeur
utiliser une RULE: la charge de travail est limité au concepteur de la base
utilisation de trigger

Filtre par requête¶

select id, lib from client where commercial = 'TUTU';

Utilisation de rule¶

CREATE TABLE client (
    id         serial CONSTRAINT key_clt PRIMARY KEY,
    lib        varchar(100) NOT NULL,
    commercial varchar(100) NOT NULL
);
CREATE VIEW clt_view AS SELECT * FROM client WHERE commercial=current_user;

CREATE OR REPLACE RULE example_1 AS
ON INSERT TO clt_view
DO INSTEAD INSERT INTO client (lib, commercial) VALUES (new.lib, current_user);

CREATE OR REPLACE RULE example_2 AS
ON UPDATE TO clt_view
DO INSTEAD UPDATE client  SET lib=new.lib,  commercial= current_user where id=new.id;

CREATE OR REPLACE RULE example_3 AS
    ON DELETE TO clt_view
    DO INSTEAD DELETE FROM client  where id=old.id and commercial= current_user;

INSERT INTO client(lib, commercial) VALUES ('client1','userA');
INSERT INTO client(lib, commercial) VALUES ('client2','userB');
INSERT INTO client(lib, commercial) VALUES ('client3','postgres');

Warning

notre filtre utilise comme utilisateur le user de connexion à la base il faut donc pour avoir un suivi fin avoir un user de connexion par utilisateur

Maintenant suivant l’utilisateur connecté le select sur la vue clt_view ramènera plus ou moins de lignes.

L’update et le delete ne prend en compte que l’utilisateur connecté (un commercial ne peut supprimer un client qui ne lui appartient pas).

Le developpeur utilise la vue clt_view et nom la table client.

INSERT INTO clt_view(lib) VALUES('fred');
UPDATE clt_view SET lib='fred2' where id=4;
DELETE FROM clt_view  where id=2;
DELETE FROM clt_view  where id=4;

Note

si on souhaite que l’update d’un client qui ne nous appertient pas retourne un erreur on peut utiliser le code suivant

CREATE FUNCTION updateclt_trigger(old clt, new clt) RETURNS VOID AS
$$
BEGIN
    UPDATE client  SET lib=new.lib,  commercial= current_user where id=new.id;
    IF found THEN
        RETURN;
    END IF;
    RAISE EXCEPTION 'Le client % n\'existe pas ou vous n\'avez pas les bonnes autorisations', new.id;
END;

CREATE OR REPLACE RULE example_2 AS
ON UPDATE TO clt_view
DO INSTEAD SELECT updateclt_trigger(OLD,NEW);

utilisation de trigger¶

Dans notre exemple seul l’utilisateur postgres peut modifier la table cpte

CREATE TABLE cpte (
    id        serial CONSTRAINT key_cpte PRIMARY KEY,
    lib       varchar(100) NOT NULL,
    actif     boolean DEFAULT true
);

CREATE OR REPLACE FUNCTION  maj_trigger() RETURNS TRIGGER AS
 $BODY$
DECLARE
    b boolean;
BEGIN
    select into b true where 'postgres'= current_user;
    IF b = true THEN
        RETURN NEW;
    END IF;
    RAISE EXCEPTION 'vous n\'avez pas les autorisations necessaire pour creer ou modifier % ', new.lib;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

CREATE TRIGGER insert BEFORE INSERT ON cpte
    FOR EACH ROW EXECUTE PROCEDURE maj_trigger();

Note

dans cette dernière utilisation la gestion du select doit être réalisé par une vue

on peut imaginer de faire des triggers sur la vue cpte_view pour gérer les insert / update et delete sur la table cpte