Forums PostgreSQL.fr

Jiff

Membre

dicriminer un insert direct d'un insert fait par une proc

Salut,

Je sèche sur ce PB; je voudrais discriminer un insert normal de celui fait par une proc stockée, de façon à logger le 1er (et ne rien faire), est-ce possible?

Le mieux serait sans doute de pouvoir récupérer le type du caller, mais je n'ai rien trouvé à ce sujet, et je tourne en rond (si je puis dire:)

gleu

Administrateur

Re : dicriminer un insert direct d'un insert fait par une proc

Pas à ma connaissance. Qu'est-ce que vous ententez par « ne rien faire avec » ?

---

Guillaume.

Jiff

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

Ne laisser passer que les requêtes transitant par ma proc, et écarter celles passant par la voie normale (genre: INSERT INTO... => rien ne se passe, mais l'ID et l'heure sont loggés)

Marc Cousin

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

Si vous voulez ne permettre l'insertion à la table que par la procédure, vous pouvez très bien faire que la procédure appartienne à un utilisateur que vous mettrez comme le seul ayant le droit d'insert sur la table. Définissez cette fonction en 'SECURITY DEFINER' afin qu'elle s'exécute avec les droits de son créateur (évidemment, attention à avoir un code propre qui ne soit pas sujet à des injections SQL par exemple).

Pour logguer les INSERT, cela risque d'être difficile, vous n'aurez pas de moyen de savoir d'où vient la session voulant faire l'opération (c'est une session normale). Si vous essayez d'intercepter l'insert, vous intercepterez aussi celui de la procédure. Vous devez par contre pouvoir logguer (ça sera un ordre SQL en erreur). http://docs.postgresqlfr.org/8.4/runtim … gging.html

http://docs.postgresql.fr/8.4/sql-createfunction.html

Dernière modification par Marc Cousin (20/11/2009 16:13:06)

---

Marc.

Jiff

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

Oui, je viens de trouver cela dans http://www.postgres.cz/index.php/PL/pgSQL_(en), mais dans ce cas là, je ne peux plus logger l'Id de l'appelant (puisqu'elle sera tjrs = DB owner).
Les logs system, c'est un peu galère (pléthoriques.)
Je suis en train de tester un chtit truc tordu, et je reviens en parler si ça fonctionne correctement.

Marc Cousin

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

Êtes vous sûr pour l'id de l'appelant ? Parce que ce qui est écrit dans les docs, c'est que la procédure s'exécute avec les privilèges de l'appelant, pas sous son identité. Ça mérite donc un test.

---

Marc.

Jiff

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

Résultat des 2 tests, avec 20' perdues, le temps de m'apercevoir que:
1)- S'il-y-a un commentaire entre le 'END;' final et la dernière ligne de la proc ($$ LANG....), ça plante la proc
2)- varchar(N) <=> N *inclue* le terminateur de string

-- Détection INSERT direct
CREATE OR REPLACE FUNCTION civility_ins_trick() RETURNS TRIGGER AS $$
BEGIN
        IF TG_OP = 'INSERT' THEN
                RAISE NOTICE 'INSERT direct détecté sur la table %)', TG_RELNAME;
                INSERT INTO security.alert(id,label,usesysid,date_alert)
                        VALUES(default,'INSERT common.civility',common.whoami(),default);
                RETURN NEW;
        ELSE
                RETURN NEW;
        END IF;
END;
$$ LANGUAGE 'plpgsql';

-- Trigger lançant la détection d'INSERT direct
CREATE TRIGGER civility_ins_t BEFORE INSERT ON common.civility
        FOR EACH ROW EXECUTE PROCEDURE common.civility_ins_trick();

* Ça fonctionne.

BEGIN
        INSERT INTO common.civility(id,label,front,date_cre,date_mod,mri_users_cre,mri_users_mod)
                VALUES(default,quote_literal(Plabel),Pfront,default,default,common.whoami(),default);
        RAISE NOTICE 'Id de l''appelant = %',common.whoami();
END;
$$ LANGUAGE 'plpgsql' VOLATILE STRICT SECURITY DEFINER;

* Renvoie bien l'Id du caller, et pas celle du owner

Mais le PB s'est juste déplacé puisqu'un user normal n'a pas les droits pour accéder au schéma 'security' (Raaaahhhh)

Jiff

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

Bon, en fait j'ai circonvenu le "problème" en m'apercevant que j'avais fais une erreur de conception
(jsuis pô'core DBA, loin'd'là:)

Donc maintenant, les users n'ont plus aucun droits sur les schémas contenant les tables (ex: clients),
au lieu de cela ils ont accès à des schémas différents (ex: uclients) dans lesquels se trouvent des vues
des tables ainsi que les rules afférentes => Plus aucun risque d'insertion par INSERT en direct dans
une table (et j'ai enfin compris ce que F. Brouard voulait dire quand il parlait de ne permettre laisser
les accès users que par des views.)

Merci pour vos réponses.

Jiff

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

Voila la solution que j'ai mis au point, j'aimerais avoir votre avis:

Table « common.civility »
    Colonne    |    Type    |                       Modificateurs                       
---------------+------------+-----------------------------------------------------------
id            | integer    | non NULL Par défaut, nextval('civility_id_seq'::regclass)
label         | da_label_s |
front         | boolean    | non NULL Par défaut, true
date_cre      | dd_ts      | non NULL Par défaut, now()
date_mod      | dd_ts      | Par défaut, NULL::timestamp without time zone
mri_users_cre | integer    |
mri_users_mod | integer    |

Fonction de récupération de l'OID du user:

CREATE OR REPLACE FUNCTION ucommon.whoami() RETURNS oid AS $$
    SELECT usesysid FROM pg_user WHERE usename = session_user;
$$ LANGUAGE SQL STRICT;

View

CREATE OR REPLACE VIEW ucommon.civility AS
        SELECT C.id, C.label, C.front, C.date_cre, C.date_mod, C.mri_users_cre, C.mri_users_mod FROM common.civility C;
COMMENT ON VIEW ucommon.civility IS 'common.civility view';
REVOKE ALL ON TABLE ucommon.civility FROM public;

Fonctions d'INSERT & UPDATE

CREATE OR REPLACE FUNCTION ucommon.civility_ins(Plabel varchar,Pfront boolean,Puserid oid) RETURNS void AS $$
BEGIN
        INSERT INTO common.civility(id,label,front,date_cre,date_mod,mri_users_cre,mri_users_mod)
                VALUES(default,Plabel,Pfront,default,NULL,Puserid,NULL);
END
$$ LANGUAGE PLPGSQL VOLATILE STRICT SECURITY DEFINER;
COMMENT ON FUNCTION ucommon.civility_ins(varchar,boolean,oid) IS 'INSERTs a row into common.civility';
REVOKE ALL ON FUNCTION ucommon.civility_ins(varchar,boolean,oid) FROM public;
-- *NO* COMMENT BETWEEN 'END;' AND '$$ LANGUAGE .....' OTHERWISE IT THROW AN ERROR
--------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION ucommon.civility_upd(Pid integer,Plabel varchar,Pfront boolean,Puserid oid) RETURNS void AS $$
BEGIN
        UPDATE common.civility SET label = Plabel, front = Pfront, date_mod = current_timestamp, mri_users_mod = Puserid WHERE id = Pid;
END
$$ LANGUAGE PLPGSQL VOLATILE STRICT SECURITY DEFINER;
COMMENT ON FUNCTION ucommon.civility_upd(integer,varchar,boolean,oid) IS 'UPDATEs a row into common.civility';
REVOKE ALL ON FUNCTION ucommon.civility_upd(integer,varchar,boolean,oid) FROM public;
--------------------------------------------------------------------------------------------------
-- ###TODO: Add a logging (Table: security.survey) to see whose user has deleted this row
CREATE OR REPLACE FUNCTION ucommon.civility_del(Pid integer) RETURNS void AS $$
BEGIN
        DELETE FROM common.civility WHERE id = Pid;
END
$$ LANGUAGE PLPGSQL VOLATILE STRICT SECURITY DEFINER;
COMMENT ON FUNCTION ucommon.civility_del(integer) IS 'DELETEs a row from common.civility';
REVOKE ALL ON FUNCTION ucommon.civility_del(integer) FROM public;

Ca fonctionne comme je le souhaite mais il reste qu'un user mal intentionné peut appeler directement les fonctions d'insert/update (dans ce cas, l'ID en insert est celle de postgres (?? le owner est un autre DBA!), et en update celui du owner).
Je n'ai pas de solution à ce risque, étant donné que le switch SYSID de CREATE ROLE est maintenant ignoré; sinon j'aurais créé les users avec des SYSID aléatoires.

gleu

Administrateur

Re : dicriminer un insert direct d'un insert fait par une proc

-- *NO* COMMENT BETWEEN 'END;' AND '$$ LANGUAGE .....' OTHERWISE IT THROW AN ERROR

J'ai aucune erreur en le faisant...

Ca fonctionne comme je le souhaite mais il reste qu'un user mal intentionné peut appeler directement les fonctions d'insert/update

Dans ce cas, il ne faut donner le droit d'exécution qu'aux seuls utilisateurs qui ont le droit de le faire.

dans ce cas, l'ID en insert est celle de postgres (?? le owner est un autre DBA!), et en update celui du owner).

Le propriétaire de quel objet ?

En fait, il serait beaucoup plus simple de dire exactement le résultat que vous souhaitez obtenir. Sinon on ne peut vous donner que des bouts de réponses.

---

Guillaume.

Jiff

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

J'ai aucune erreur en le faisant...

Tiens, moi non plus; sans doute une erreur au moment où j'ai noté ça, ou une mauvaise lecture.

Dans ce cas, il ne faut donner le droit d'exécution qu'aux seuls utilisateurs qui ont le droit de le faire.

CQFD, c'est évident, mais parmi eux...

Le propriétaire de quel objet ?

De la table, du trigger et de sa proc (c'est mon SU <> postgres.)

En fait, il serait beaucoup plus simple de dire exactement le résultat que vous souhaitez obtenir. Sinon on ne peut vous donner que des bouts de réponses.

wai, j'ai des fois du mal à décrire le tableau (pensée par images.)
* Pas d'accès user direct aux tables (interdiction de donner un ordre direct tel qu'INSERT),
* INSERT: Date de création = now() (pas de PB, ça peut passer en default) et PG Id du créateur du row (là, ça bloque: le trigger renvoyant l'Id de postgres),
* UPDATE: Date de modif = now() (pas de PB: dans le trigger), et PG Id du modificateur du row (là, ça rebloque: le trigger renvoie l'Id de mon SU.)

gleu

Administrateur

Re : dicriminer un insert direct d'un insert fait par une proc

Désolé mais je ne comprends pas ce dont vous parlez. Le premier point peut se comprendre. Le reste est incompréhensible sans plus de détails. Il faudrait avoir une vue plus complète du schéma et de ce que vous cherchez à faire. Par exemple : qui est le propriétaire des différents objets, ce que vous entendez par le « créateur de la ligne » (concept inexistant sur PostgreSQL), sans parler du code du trigger, etc, etc.

---

Guillaume.

Jiff

Membre

Re : dicriminer un insert direct d'un insert fait par une proc

J'ai fini par trouver

J'ai laissé tomber les triggers et gardé les functions (SECURITY DEFINED) ET changé ma fonction de renvoi de l'Id (current_user remplacé par session_user), et là tout est bon sans avoir à fournir l'Id lors d'un INSERT ou d'un UPDATE.

Merci.