Vous n'êtes pas identifié(e).
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à?
Hors ligne
Il y a plusieurs choses qui ne vont pas:
* Pourquoi mettre entre parenthèses, dans le SELECT, la liste des colonnes. En faisant ça, postgres vous fabrique un type composite, il ne garde pas à chaque colonne son identité.
* Vous utilisez du RECORD partout. Je comprends bien pourquoi, vous ne pouvez pas connaître les types au moment de l'écriture de la fonction. Le problème, c'est que lui non plus ne les connait pas au moment de l'exécution.
Vous devez donc lui préciser, par quelque chose comme:
SELECT * FROM tsttst('id', FALSE, 1000, 1, variadic array['id', 'a', 'b']) AS t(id int, a text, b text);
* Dernier point: si vous ne mettez pas * dans le SELECT appelant, la fonction ne vous retourne qu'un attribut, qui est un composite des colonnes retournées par la fonction.
Ensuite, si vous retournez de toutes façons tous les enregistrements de qry, utilisez directement «RETURN QUERY EXECUTY qry», ça sera plus simple que de faire vous même la boucle.
Marc.
Hors ligne
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
Hors ligne
Bon, je me réponds à moi-même personnellement tout seul à la dernière question: les tests montrent ~ 20% d'overhead avec le contrôle des droits intégré à la fonction (table de taille moyenne: 15 colonnes.)
Ça n'est pas beaucoup, et c'est même acceptable SI on prend en compte le contexte des opérations: HD PATA 7200 RPM standard sur lequel cohabite l'OS, mono CPU assez vieux: AthlonXP-2600+, machine non-dédiée, 1.5GB de RAM - on passe de 760ns (sans contrôle des droits) à 950ns de temps d'exécution, mais ça reste... des ns.)
Donc, vu le trafic prévisible (léger: ERP), la pénalité de retard ne grève pas le budget (et surtout question sécurité je ne vois pas mieux.)
JE SUIS LE POUCE DU MONDE!! (ben wai, parce c'est pô encore le pied et que je suis très très loin d'en être le mètre:)
Hors ligne
Si vous n'avez besoin que des droits sur les colonnes, pourquoi ne pas tout faire directement avec soit des grant par colonnes sur des groupes d'utilisateur, soit des vues ? Ça me semble très compliqué, ce que vous êtes en train de faire. Sans même parler du fait que la fonction au dessus est très facile à détourner avec une injection SQL.
Marc.
Hors ligne
C'est justement ce que je fais: un groupe Pal qui contient tous les autres groupes et users, des autorisations par colonnes et par groupes et un grant du/des groupes aux users qui n'ont aucun accès direct aux schémas.
Sinon, dans la fonction, j'ai juste ajouté le nom de la table et la vérification des droits sur les colonnes demandées, avec génération d'une exception si un droit n'est pas bon (maintenant, il va falloir que je trouve comment aller alimenter ma table de surveillance sans qu'elle ne soit affectée par le rollback subséquent; je sens que je vais m'amuser un certain temps...)
Au sujet de l'injection SQL, je suis preneur de toutes infos & conseils, également sites/articles, étant donné que je ne connais malheureusement que de nom.
Hors ligne
Ok. Je dois être un peu obtus, ou peut-être que je me suis levé trop tôt, mais je ne vois pas l'intérêt de la fonction par rapport à une requête simple, dans ce cas.
Pour l'injection SQL, c'est assez simple: que se passe t'il à l'heure actuelle si quelqu'un, dans l'appel de la fonction, vous passe par exemple un nom de colonne qui contient «';SELECT * FROM ma_table_super_critique» ? (ça ne va pas marcher exactement comme ça, ça dépendra de ce qui se trouve avant dans l'ordre SQL généré, mais on peut finir par trouver quelque chose qui passe).
Pour être tranquille vis-à-vis de ça, vous avez deux stratégies simples:
- protéger les chaînes de caractères que vous concaténez dans l'ordre SQL, ça se fait avec quote_ident, quote_litteral, quote_nullable : http://docs.postgresql.fr/9.0/plpgsql-s … al-example
- passer les chaînes de caractères en paramètres. Comme ce sont des paramètres de l'exécution de la requête, et plus le code lui-même de la requête, aucun risque que quelqu'un y mette quelque chose qui permette une injection. Ça se fait avec EXECUTE query USING (c'est sur la même page de manuel qu'au dessus).
Marc.
Hors ligne
Ben moi, je ne me suis pas couché, on est à égalité 
Si je comprends bien: pas la peine de passer par des fonctions appartenant à l'admin ni d'interdire l'accès aux schémas, juste avoir des droits sur les colonnes et émettre les requêtes à partir du client ? (mais dans ce cas, comment éviter une injection puisque plus rien ne lui fait barrage ??)
Sinon, à quel type de process dois-je réserver l'utilisation de fonctions, en dehors des triggers et éventuelles rules; les inserts/updates/deletes?
Merci pour le lien; ça ne semble effectivement ni trop compliqué ni trop contraignant; je digère ça et je RE avec qq chose qui prenne tout ça en compte.
Hors ligne
En émettant les requêtes à partir du client, on peut aussi y avoir des injections SQL… ça dépend évidemment de comment est codé le client, et de ce qui s'y passe (se méfier de tous les champs que peuvent remplir les utilisateurs). Mais elles ne contourneront pas les privilèges de l'utilisateur. Si une colonne est interdite d'accès, elle reste interdite d'accès, injection SQL ou pas. Cela n'est évidemment possible que si chaque client se connecte avec un utilisateur différent. Si tout le monde utilise le même compte, c'est impossible.
L'autre solution, c'est d'interdire l'accès aux tables aux utilisateurs, leur faire des vues qui n'affichent que ce qu'ils doivent voir, et leur donner à chacun des droits sur ces vues. Mais ça laisse le problème de l'écriture dans les tables (on ne peut pas écrire dans les vues simplement pour le moment dans Postgres, il va y avoir une amélioration de ce point de vue en 9.1).
L'utilisation de fonctions, c'est assez compliqué de vous répondre, et sujet à débat. Ce qui suit n'est donc que mon point de vue.
Préférez le SQL quand vous pouvez, puis les fonctions en SQL si nécessaire, et les fonctions dans un autre langage (PLPgSQL si possible, c'est le plus simple) après. C'est vraiment fait pour quand les traitements en SQL deviennent trop compliqués, ou impossibles (encore que c'est de plus en plus rare de ne pas pouvoir faire un traitement en SQL pur). Je m'en sers aussi pas mal pour les modifications de données, c'est souvent plus simple (du côté du client, et de mon point de vue) de faire un appel à une fonction qui fait son insertion dans plusieurs tables que de faire plusieurs insertions consécutives (sans parler des latences entre le client et le serveur pour chaque requête). Ça aussi va pas mal changer avec les writable CTEs de la 9.1.
Pour ce qui est des rules, honnêtement, ne vous en servez qu'en cas de dernière extrémité. C'est très vicieux, il y a pas mal de pièges, et ça rend le débogage et la maintenance très pénibles.
Marc.
Hors ligne
Ok, c'est bien ce qui me semblait question droits.
Pour les vues, c'est ingérable vu le nombre de profils users susceptibles d'exister, à moins de les régénérer automatiquement dès qu'une modif de droits a lieu; et ça me semble assez dangereux parce qu'une vue peut donner l'accès à une colonne interdite par ailleurs.
C'était ma première idée, mais j'ai vite laissé tomber.
En fait, ce que je veux faire est une coopération entre client (en python) & server: à la connexion, le client lit tous ses droits et s'auto-configure en fonction de ceux-ci; les requêtes ou appels aux fonctions sont donc conformes aux droits.
Si qqun "bricole", il génèrera fatalement tôt ou tard une exception qui sera logguée.
Niveau fonction je me suis effectivement aperçu que les discussions étaient nombreuses et animées.
Pour ce qui est des modifs je suis farpaitement d'accord: 1 seul appel et le multi-processing se fait au niveau server.
Pour l'instant je n'ai pas identifié de cas où une rule serait nécessaire, et j'espère que ça restera comme ça: j'ai lu plusieurs papiers sur les interactions & effets de bord possibles avec les triggers et ça n'encourage pas à s'en servir.
Hors ligne
Une vue et le grant associé est pourtant la meilleure solution en terme d'évolutivité, et de performances.
En principe on devrait toujours développer les applications clientes à partie des vues et jamais en accès direct aux tables.
C'est le MED (Modèle Externe de Données), composé de vues, procédures et trigger, généralement le grand oublié des développeurs !
Quelques références sur le sujet :
http://www.cybermed.jussieu.fr/Broussai … Chap4.html
http://errachidiaista.co.cc/cc/fichiers … rique2.doc
A +
Dernière modification par SQLpro (21/06/2011 09:51:59)
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Merci pour les liens.
J'ai déjà lu cela plusieurs fois dans tes articles et posts.
Par contre, ça veut aussi (sans doute) dire que je vais être obligé d'ajouter des tables spécifiques, telle que les champs utilisés ou non par vue et par groupe d'utilisateur, pour pouvoir régénérer automatiquement mes vues lors d'une modification de(s) vue(s) (?)
Et si je veux pouvoir garder le côté auto-adaptatif de mon pgm client, il devra lui aussi avoir accès à ces tables (ou plutôt à des vues de ces tables.)
Et donc, puisqu'une vue donne un accès indépendant des droits des tables/colonnes, je suppose que ne touche plus à ces droits?
Hors ligne
Oui, car toucher aux privilèges (et non pas droits) pose des problèmes de "recompilation" donc de perf.... Mieux vaut que le schéma soit le plus stable possible
Pour les tables spécifiques, ça n'est pas une obligation. De plus tu peut jouer sur les schéma en ventilant tes vues sur des schémas différents et donner des privilèges au niveau du schéma. Ainsu qune même vue peut exister dans deux schémas différents avec des colonnes différentes et des privilèges spécifiques.
Enfin, une bonne chose aurait été que PG implémente les trigger DDL comme c'est le cas de SQL Server, car tu aurait pu gérer automatiquement par du SQL dynamique l'attribution des privilèges en cas d'évolution des structures des tables et vues
A +
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES, Expert langage SQL
Le site sur les SGBD relationnel et langage SQL : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA, ISEN Toulon, CESI Aix en Provence * * * * *
Hors ligne
Ah, en fait après réflexion, je ne pense pas que je pourrai couvrir tous les cas avec uniquement des vues (par ex. pour afficher les familles & s/s familles d'article je me vois mal créer une vue par cas de figure; donc ça ne peut se faire que par une fonction.)
Et je ne comprends pas le PB de "recompilation".
Oui, quand j'ai commencé à vraiment m'interesser aux DBs il-y-a 1 an, je me demandais à quoi pouvait bien servir les schémas - quand j'ai commencé à analyser mon projet d'ERP, j'ai compris tout de suite l'intérêt. Mais je l'ai loupé pour les vues.
Sur le dernier point je suis dubitatif: comme on ne sait jamais quel colonne risque d'être éventuellement ajoutée, l'intervention humaine semble de rigueur; et si on reconstruit automatiquement les vues à la fin des modifications les concernant, ça fait l'affaire. (mais là, jsuis pas sur d'être sur la même longueur d'onde que toi...)
Hors ligne
@Marc: Au sujet de: "EXECUTE query USING", je suppose que tu fais allusion aux seules 2 pages qui en parle: ecpg-...
Le seul soucis, c'est qu'avec cette syntaxe, si je veux mettre les noms des colonnes en parms externes, je dois savoir combien il-y-a en a ('SELECT ?, ?, ?, ?' etc); sauf que justement ça n'est pas le cas: le nombre de parms est inconnu.
OU bien, tu veux dire que je construire la string de query par itération, en ajoutant justement un ', ?' par colonne voulue (pour rester dans le même exemple, évidemment.)
Peux-tu me préciser cela, STP?
Hors ligne
Il semble, d'après le lien que Marc m'as fourni, qu'il-y-ait plus facile que d'utiliser les 'quote_xxxxx':
n'utiliser que la description des parms (int4, text, etc...) et donc utiliser $n dans le corps de la fonction - cette doc précise justement qu'escape & quote de telles valeurs sont automatiques.
J'ai quand même demandé la confirmation sur la ML Pg.
SI c'est bien ça, ça permettrait d'utiliser mon idée de fonctions polymorphes en lieu et place de vues.
Évidemment les vues sont plus rapides puisque prepared et plus sécurisées de base, mais (IMHO) l'overhead introduit par mon système est relativement négligeable par rapport à la création/modification de centaines de vues/fonctions par groupe d'utilisateurs; à la condition expresse que mes fonctions soient inattaquables pour une injection.
L'intérêt de ma méthode par rapport à celle de Fredéric est que la seule modif d'un privilège sur une colonne est immédiatement répercutée, sans avoir à modifier des tas de vues/fonctions dans la foulée.
Mais bon, jsuis pas un hyper-spécialiste (même pas un spécialiste tout court, d'ailleurs): je recherche juste le moyen de faire secure, effficace & surtout simple - fusse au prix d'une latence légèrement supérieure.
Je dis ça dans l'optique de CE projet: un ERP - parce qu'il est évident que si j'avais des centaines de requêtes à la seconde, la problématique de latence serait toute autre et considérée beaucoup plus du point de vue de Frédéric que de celui que j'axprime maintenant:)
Hors ligne