Forums PostgreSQL.fr

> quel est le risque (securité) si le owner de ma base reste postgres?

Simple: si le compte est compromis, le svr l'est aussi (et potentiellement la machine si ledit svr a une faille de sécurité importante (in)connue).

> Est-ce-que vous me conseillez de toujours modifier le owner de ma base?

En Gal, il t'en faut 2: le propriétaire de la DB et de ses objects, et l'"exploitant", ayant des droits réduits au strict minimum pour un fonctionnement correct de l'application.

Effectivement, en contrôlant le domain, il-y-a un gros PB:

SELECT D.typname as domname, format_type(b.oid,NULL) as dombasetype,
    D.typlen, D.typtypmod, D.typnotnull, D.typdefault, D.typndims, D.typdelim, C.consrc
    FROM pg_type D
    JOIN pg_type B ON B.oid = CASE WHEN B.typndims > 0 then D.typelem ELSE D.typbasetype END
    JOIN pg_namespace N ON N.oid = B.typnamespace
    JOIN pg_constraint C ON (D.typname || '_check') = C.conname
    WHERE D.typtype = 'd' AND D.typnamespace = 2200::oid
    ORDER BY D.typname;

da_label_s   | character varying |   -1 |     20 | t       |                 |        0 | ,      | (char_length((VALUE)::text) > 1)

Alors que ça devrait être >0.

Etant donné que justement le source est dans un tableur pour éviter toute redondance, après grattage j'ai droit au tirage: un typo a fait que 3 lignes plus bas le domain DA_LABEL_M se nomme aussi DA_LABEL_S (et lui, il compare >1).

Désolé pour le bruit et merci.

Donc elle est bien correcte (SHOW lc_collate renvoie fr_FR.utf8).

Wai, ça me rappelle une sortie ô combien vraie de Raymond Devos: "Mettez dix sages ensembles et vous obtenez un fou"

Ah, c'est effectivement un point qui m'avait échappé!
Je vais réserver ça à une future version étant donné que c'est un ERP pour PME et qu'il est rare qu'elles soient internationales, sauf en frontalier, mais ça devrait le faire dans un premier temps.
Merci pour tes éclaircissements.

Et là où j'y perd mon latin(-1:), c'est que j'ai toujours le même résultat en utilisant l'index case+accent insensitive:
SELECT *,lower(jyunaccent(note)) FROM tst1m ORDER BY lower(jyunaccent(note)) ; alors que théoriquement il devrait au moins me remonter ces ë et finir par les z.

@gleu: Non, il semble que tu te trompes qq part (ou bien c'est moi qui ai de la m..e dans les yeux, ce qui est tt à fait poss avec 5H de sommeil: ça me tracassait graâve).
@frost: YES, j'ai recréé la même Fn mais en utilisant VARCHAR au lieu de TEXT:

CREATE OR REPLACE FUNCTION jyunaccent(varchar) RETURNS varchar AS $$
   SELECT unaccent($1);
$$ LANGUAGE sql  IMMUTABLE;

Puis j'ai lû la doc des indexes, supprimé l'index original et recréé avec ce que tu m'as conseillé:

CREATE INDEX tst1m_name_lu_key ON tst1m(lower(jyunaccent(name)) varchar_pattern_ops);

Et là, le résultat à quelque peu changé...:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(jyunaccent(name)) LIKE 'ogvvatoie%';
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tst1m  (cost=17.90..1211.87 rows=500 width=100) (actual time=0.253..0.255 rows=1 loops=1)
   Filter: (lower((jyunaccent(name))::text) ~~ 'ogvvatoie%'::text)
   ->  Bitmap Index Scan on tst1m_name_lu_key  (cost=0.00..17.78 rows=500 width=0) (actual time=0.054..0.054 rows=1 loops=1)
         Index Cond: ((lower((jyunaccent(name))::text) ~>=~ 'ogvvatoie'::text) AND (lower((jyunaccent(name))::text) ~<~ 'ogvvatoif'::text))
 Total runtime: 0.337 ms
(5 lignes)

Ce qui n'est pas si mal sur une table de test de 10,000 rows et une colonne contenant une string aléatoire de longueur [14-32].

Merci beaucoup, c'est génial parce que ça m'emmerdait vraiment d'ajouter une colonne et un trigger rien que pour ce type de recherche (ET du coup je vais passer en revue tout le script de création de la DB parce que ça n'est sûrement pas un cas isolé, y'a du gain dans l'air:)!

Ok, mais est-ce que c'est (très) rapide?
Parce que cette nuit j'ai trouvé une solution en contournant le PB de l'immutabilité, non-satisfaisante parce que correcte pour une recherche sur le contenu entier de la colonne, mais très lente sur une recherche partielle:

CREATE FUNCTION erpunaccent(text) RETURNS text AS $$
    SELECT unaccent($1);
$$ LANGUAGE sql  IMMUTABLE;

Recherche partielle sur un index normal:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE lower(erpunaccent(name)) LIKE 'ogvvatoies%';
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Seq Scan on tst1m  (cost=0.00..28117.27 rows=500 width=100) (actual time=1.284..3569.742 rows=1 loops=1)
   Filter: (lower(erpunaccent((name)::text)) ~~ 'ogvvatoies%'::text)
 Total runtime: 3569.815 ms
(3 lignes)

Recherche partielle sur mon index case a accent insensitive:

EXPLAIN ANALYZE SELECT * FROM tst1m WHERE name LIKE 'oGvvÀtÖiÉ%';
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on tst1m  (cost=0.00..2867.01 rows=10 width=100) (actual time=0.071..140.336 rows=1 loops=1)
   Filter: ((name)::text ~~ 'oGvvÀtÖiÉ%'::text)
 Total runtime: 140.418 ms
(3 lignes)

Ce que je n'arrive pas à comprendre, c'est que pour une recherche partielle l'index ne soit pas utilisé (en recherche std, il l'est et on tourne à ~0.177ms) mais surtout que ça soit 3x plus lent avec le mien qu'avec un index standard (à moins que l'index ne soit recalculé à la volée à chaque accès?)

Donc ma seconde question est: est-ce que je risque d'avoir le même PB avec TRANSLATE, et si oui quelle est la meilleure façon d'intégrer l'une ou l'autre méthode pour alimenter une colonne supplémentaire, étant donné qu'on ne peut pas définir un default à partir d'une autre colonne lors de la création de la table (un trigger AFTER INSERT & UPDATE?)

YES!

Je savais  bien que ça devait être possible sans trigger, merci

Edit 20110721@0038:
J'ai fini par retrouver mes notes.
On peut aussi avoir un CHECK dans la ligne qui vérifie les autres colonnes en utilisant:
..... CHECK(CASE blah THEN blahblah AND blahblahblah ..... END),

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:)

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...)

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.

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.