Vous n'êtes pas identifié(e).
Merci, maintenant ça fonctionne comme prévu (j'ai même compris pourquoi:)
J'avoue que j'en chibave avec SQL, notamment avec la syntaxe (où mettre et ne pas mettre un ';' par exemple) et je comprends mieux pourquoi un pote programmeur m'avait dit il-y-a qq années que dans un projet important on utilisait des spécialistes différents pour les tâches PGM/DB.
Au sujet du dernier point, je ne peux faire autrement parce que mon système de sécurité est basé sur les droits accordés sur les colonnes; si j'utilise '*', la requête plantera automatiquement puisqu'elle est appelée avec les droits de l'utilisateur qui n'a pas spécialement accès à toutes les colonnes d'une table.
J'aimerai d'ailleurs avoir ton avis sur la première idée que j'avais:
Je pensais bloquer les accès utilisateurs aux schémas et n'utiliser que des fonctions de type SECURITY DEFINER; le PB avec ce type d'architecture c'est que la fonction doit, à chaque appel, d'abord vérifier les droits de l'appelant; ça m'a parut plutôt lourd et peu rapide et j'ai abandonné - maintenant si tu juges que c'est une bonne solution, je peux tout à fait revenir à cette solution (d'ailleurs, j'aimerai autant, parce que c'est imparable question sécurité des données puisque l'utilisateur ne peut QUE passer par les fonctions pour atteindre les données.)
Merci d'avance.
JY
Salut forumers,
J'en bave pômal avec ma fonction (enfin plutôt avec PlPgSQL) qui devrait me renvoyer une suite de rows uniquement composée des colonnes voulues.
Si je demande un retour de type "SETOF tst" et que j'utilise "rowdata tst%ROWTYPE", ça me renvoie un résultat correct, mais tous les champs dans une seule string :-(, et si j'utilise la fonction telle que suit:
CREATE OR REPLACE FUNCTION tsttst(colorder TEXT, -- Ordering column
sensorder BOOLEAN, -- TRUE=ASC / FALSE=DESC
lim INTEGER, -- LIMIT
off BIGINT, -- OFFSET
cols TEXT[]) -- Columns' names array
RETURNS SETOF RECORD AS $$
DECLARE
ord TEXT;
collist TEXT;
qry TEXT;
-- rowdata tst%ROWTYPE;
rowdata RECORD;
BEGIN
IF sensorder THEN
ord := 'ASC';
ELSE
ord := 'DESC';
END IF;
-- Construct columns full list
collist := array_to_string(cols, ',');
-- Get row
qry = 'SELECT (' || collist || ') FROM public.tst ORDER BY ' || colorder || ' ' || ord || ' LIMIT ' || lim || ' OFFSET ' || off;
RAISE NOTICE 'String de query = %', qry;
FOR rowdata IN
EXECUTE qry
LOOP
RETURN NEXT rowdata;
END LOOP;
END;
$$ LANGUAGE plpgsql STABLE STRICT;là, ça ne marche plus du tout, et ça crache:
SELECT tsttst('id', FALSE, 1000, 1, variadic array['id', 'a', 'b']);
NOTICE: String de query = SELECT (id,a,b) FROM public.tst ORDER BY id DESC LIMIT 1000 OFFSET 1
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "tsttst" line 21 at RETURN NEXTQu'ai je bien pu louper sur ce coup-là?
Pour les récriminations voir SQLpro et son article: http://sqlpro.developpez.com/cours/mode … tadonnees/; en général j'ai pu constater qu'il sait ce qu'il dit...
Ha, Ok, merci.
Arf, je n'avais pas vu la signature, mais le pseudo me disait qq chose!
En fait, c'est pour une implémentation (simplifiée) de ton papier sur les méta-données (sur developpez.net.)
Oops: Qu'est-ce qu'une UDF?
Pour le trigger, je ne vois toujours pas quelle string de CHECK stocker 
Ouii: une table des tables, une pour les caractéristiques de la colonne ajoutée et une pour toutes les données de toutes les colonnes ajoutées (+ les index qui vont bien, oeuf corse.)
L'intérêt de ce système n'est bien évidemment pas de pouvoir modifier la DB à volonté, mais de l'utiliser pour personnaliser certaines colonnes, ou plutôt internationaliser certaines colonnes peu utilisées - eg: N° d'identification salarié (fr: SS), N° d'identification entreprise (fr: SIREN/SIRET), etc. - sans avoir à modifier les tables de base qui ne contiennent que les colonnes communes à tous les pays.
Tout simplement parce que cela fait partie d'un système d'ajouts de champs à la DB.
Étant donné que toutes les valeurs des champs ajoutés se retrouvent dans une seule table, en dehors du NOT NULL (implicite, puisque NULL == absence de row dans la table), on ne peut préjuger des contraintes applicables, d'où l'externalisation desdites contraintes.
Salut forumers,
Je dois stocker des constraints sous forme de texte puis les appliquer; le PB c'est que je ne trouve pas comment faire pour certaines.
Voila ce que je stocke par exemple:
1 | ^(1|2)\d{2}[0-1][0-2]([0][1-9]|[1-8][0-9]|[1-9]([0-7]|9)|[2][A-B])([0][0][1-9]|[0][1-9][0-9]|[1-9][0-9][0-9])([0][0][1-9]|[0][1-9][0-9]|[1-9][0-9][0-9])$Qui fonctionne farpaitement:
SELECT '199112B008403' ~ (SELECT const FROM tst WHERE id=1);
?column?
----------
t
(1 row)Par contre ça fait un paquet d'heures que je bute sur ce type de constraint:
2 | CHECK(char_length(VALUE) = 13)Qui me renvoie systématiquement FALSE:
SELECT '1234567890123' ~ (SELECT const FROM tst WHERE id=2);;
?column?
----------
f
(1 row)Ça m'étonnerait que ça ne soit pas possible, mais je dois louper un truc qq part et/ou taper bien à côté de la plaque (mais pas encore dans les gamelles:)
JY
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.
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.)
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.
Merci,
je pense que comme mes définitions de tables sont dans un tableur, je vais utiliser ses fonctions
histoire de remplacer les domains par leurs valeurs réelles afin de faciliter la récupération des
types de champs.
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.
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)
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.
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)
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:)
Salut,
je suis à la recherche de bouts de codes pour récupérer toutes les infos et contraintes des colonnes d'une base afin de les réutiliser dans l'application qui va au-dessus et éviter une éventuelle désynchronisation en manuel:
* Type du champs (int4, varchar(n), ...), également si le descripteur est un domain (c'est surtout là que ça coince),
* Contraintes "directes" (not null, >5, ...),
* Contraintes "indirectes" (celles du domain).
je n'ai pas trop le temps de m'y atteler étant donné que le dev de l'application prend tout mon temps dispo.
Merci d'avance.
Et pourquoi pas une Màj par porteur spécial?
Sérieusement, un simple tunnel SSH entre les 2 sites permettra l'accès de tout le monde à une seule base (bureau|entrepôt, aucune importance) et son exploitation temps réel.
Une dernière pour la route: Est-ce qu'un REVOQUE ALL sur chaque objet est suffisant, ou bien faut-t'il rentrer dans le détail?
Merci, je vois mieux le tableau d'ensemble maintenant et j'incluerai les REVOQUE au moment de la création des objets (mais c'est galère comme fonctionnement, tout bloquer de base aurait été plus logique question sécurité.)
Ok pour l'analogie avec les répertoires, c'est très parlant 
Donc, si j'ai bien suivi et vu qu'il n'y-a pas propagation des droits, cela veut dire que je vais être obligé de révoquer *tous* les droits sur *tous les "objets"*;
soit: REVOQUE ALL ON (DB, TABLESPACE, LANGUAGE, SCHEMA, FUNCTION, SEQUENCE, TABLES, CHAMPS, VIEW, TRIGGER, RULE, ) FROM public ? (en ai-je oublié?)
Ou bien seulement certains REVOQUE vont-ils suffire à tous interdire au méta-user 'public'?
Bon, je me réponds tout seul: NON!
Apparemment il faut aussi "REVOQUE ALL ON SCHEMA xxxx FROM public", ce qui n'est absolument pas logique, la DB étant censée chapeauter tout le reste (mais bon, je dois me tromper qq part)
Juste une dernière question, histoire d'être absolument sur de ne faire aucune connerie: "REVOQUE ALL ON DATABASE mydb FROM public" est-il suffisant ?