Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
#1 Re : Sécurité » Plein d'utilisateurs, mais un SU qui se connecte » 21/01/2017 23:27:16
Bon, comme on n'est jamais si bien servi que par soi-même, je me réponds avec une solution qui évite tout recours à un compte SU (trouvée sur stackoverflow.)
.
Créer le user devant accéder aux données:
CREATE USER machin LOGIN PASSWORD 'trucbiencompliqué';et lui donner tous les droits voulus sur les views et fonctions de la DB.
.
Créer le user qui va endosser toutes les connexions, puis prendre temporairement l'identité de l'utilisateur devant effectuer une/des opérations sur la DB:
CREATE USER access NOINHERIT LOGIN PASSWORD 'trucencorepluscompliqué';et lui accorder les droits du user normal (dont il n'héritera pas, puisque qu'il a NOINHERIT):
GRANT machin TO access;Créer les connexions PgBouncer à la DB avec le user 'access' (qui n'a donc aucun droit en dehors de la connexion - NB: dans mon cas particulier, elles restent ouvertes tant que le serveur est up.)
.
Quand le besoin s'en fait sentir, exécuter la substitution avec:
SET ROLE machin;puis bricoler tout ce que l'on veut sous le compte 'machin'. Pour revenir à l'état antérieur, faire:
RESET ROLE;Je n'ai pas trouvé plus simple, ni plus sécurisé.
#2 Sécurité » Plein d'utilisateurs, mais un SU qui se connecte » 11/01/2017 09:28:54
- Jiff
- Réponses : 1
Salut forumers (bonne année et tout plein de choses, surtout la santé),
.
NB: Les utilisateurs n'ont pas d'accès direct aux données ni même aux vues, seulement à certaines fonctions et encore, pas directement; seul le serveur (middleware) a accès à ces fonctions - Les requêtes des utilisateurs sont des RPC sur le serveur qui connaît tous les droits d'accès accordés aux utilisateurs ainsi que leur nom et mot de passe (hash ≠ md5).
.
Je me demande si la bonne solution pour éviter d'avoir à fermer les connections rapidement (à cause du nombre d'utilisateurs >> nombre de connexions) ne serait pas de passer par des connexions permanentes ouverte par un SU ?
.
Donc, disons, par exemple:
100 connections ouvertes par pgbouncer au nom d'un SU,
50 connections en réserve au cas où,
2 connexions réservées aux DBA et admin de la DB. Ensuite, quand le serveur reçoit une requête d'un client.
.
Dans cet exemple, le client est déjà connecté (login/pw ≠ de Pg).
* Client requête,
* Serveur vérifie si le client a le droit d'appeler la fonction, (on va dire que vi, sinon le client se fait jeter et logger)
* Serveur construit la requête PG (SQL) dans un bloc de transaction (niveau SERIALIZED),
* Serveur envoie: SET SESSION AUTHORIZATION <role> à pgbouncer,
* Serveur envoie le bloc de transaction à pgbouncer et papote avec le DBA en attendant la réponse,
* Serveur reçoit la réponse,
* Serveur la transmet au client,
* Serveur envoi RESET SESSION AUTHORIZATION à pgbouncer.
.
Q1: Est-ce une bonne façon de faire, et si non, pourquoi ?
.
Q2: Je vois le distinguo entre SET SESSION AUTHORIZATION <role> et SET ROLE <role> (SESSION_USER restant au role du SU dans le 2nd cas), mais quelle différence cela pourrait-il faire dans une configuration telle que la mienne si j'utilisai SET ROLE ?
.
Merci d'avance.
.
PS: Le tracking sur HTTP referer & user agent pour pouvoir poster, c'est l'enfer 
#3 Général » indexation fonctionnelle impossible sur timestamptz » 01/04/2015 21:46:30
- Jiff
- Réponses : 0
Salut Forumers,
.
Dans la plupart de mes tables, je garde trace de l'ID des utilisateurs de création et de modification, ainsi que des dates d'insertion et d'update - jusque-là, rien que de très classique.
Le hic, c'est qu'après différents tests, je ne peux bien évidemment pas faire une requête du style:
SELECT a,b,c,date_upd FROM matable WHERE date_upd = '2015-04-01';donc, je me renseigne et j'essaie:
CREATE INDEX test_ix ON matable(DATE(date_upd)); - OU BIEN (d°): …matable((date_upd::DATE));et je me retrouve avec un splendide: ERROR: functions in index expression must be marked IMMUTABLE 
.
La page de doc des functions-datetime ne rapporte aucune possibilité de conversion directe de timestamptz en date, et je ne vois que celle (un peu débile) d'extraire AN, MOIS et JOUR, puis de re-créer une date et de l'indexer; existe-t-il une façon plus simple de faire?
.
.
EDIT: Après une recherche exhaustive, je ne vois qu'une seule solution (mais qui laisse la gym mentale à l'utilisateur:(:
CREATE INDEX test_ix ON matable(TIMEZONE('UTC', date_upd));Quelqu'un aurait-il mieux?
.
Y'a des jours comme ça où je ferais mieux de tourner sept fois ma langue dans la bouche de ma voisine avant d'écrire… Évidemment que c'est la bonne solution, ainsi que de passer également le svr en UTC.
#4 Re : Général » Petit problème dans une fonction » 13/10/2014 00:08:56
Effectivement, ça parle mieux; voici donc la query originale et ma fonction.
.
Elle sert à récupérer les tables|views|matviews|indexes|sequences d'un schéma par son ptit nom.
.
Il y a deux choses que j'aimerais savoir:
0- Pourquoi ça ne marche pas (enfin, si, mais ça renvoie *tous* les types d'un seul coup) dans le cas précédemment évoqué (où 'rel_type' était une string conforme à l'original)?
1- Existe-t-il une façon plus simple de faire que la mienne?
2- Est-ce que cette function est injection-free ou non? (et si non, évidemment le pourquoi du comment;)
3- S'il est important de déclarer son type? (et pourquoi)
4- Si 3, quel est son type? (j'ai du mal à vraiment saisir le distingo entre STABLE et IMMUTABLE)
5- À tout hasard, si vous savez ce que peuvent bien être les tables|views|etc de types 'special' et ''?
6- Pourquoi ça plante si j'utilise '$BODY$' à la place de '$$'?
.
Query originale:
SELECT n.nspname AS "schema", c.relname AS "name", c.oid AS "oid",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'matview'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
WHEN 'f' THEN 'foreign table'
END AS "type",
pg_catalog.pg_get_userbyid(c.relowner) AS "owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) AS "size",
pg_catalog.obj_description(c.oid, 'pg_class') AS "description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND n.nspname !~ '^pg_toast'
AND n.nspname ~ '^(mon_schema_a_moi_qu_il_est_beau)$'
ORDER BY 1,2;La fonction que j'ai fini par trouver (celle qui ne fonctionnait pas était identique à l'exception des 'rel_type' tels que précédemment décrit):
CREATE FUNCTION admin.schtabviematvieindseq(IN schema_name TEXT, IN relation_type TEXT, OUT schema NAME, OUT name NAME, OUT oid OID, OUT type TEXT, OUT owner NAME, OUT size TEXT, OUT description TEXT)
RETURNS SETOF record AS $$
DECLARE
sch_name TEXT := '^(' || $1 || ')$';
rel_type TEXT;
BEGIN
IF $2 = 'tables' THEN rel_type = '^(r|s|'''')';
ELSIF $2 = 'views' THEN rel_type = '^(v|s|'''')';
ELSIF $2 = 'matviews' THEN rel_type = '^(m|s|'''')';
ELSIF $2 = 'indexes' THEN rel_type = '^(i|s|'''')';
ELSIF $2 = 'sequences' THEN rel_type = '^(S|s|'''')';
ELSE
RAISE EXCEPTION 'Function ILLEGAL CALL: admin.tabviematvieindseq(TEXT, TEXT) - wrong 2nd parameter – By User #%', common.whoami_id();
------ HINT 'Depending on his mood, the DBA might cut your balls off with a rusty chainsaw or have you fired.'; --Breaks @ creation
END IF;
------RAISE NOTICE 'HERE IS rel_type AFTER IF: %', rel_type;
RETURN QUERY
SELECT n.nspname AS "schema", c.relname AS "name", c.oid AS "oid",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'matview'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
WHEN 'f' THEN 'foreign table'
END AS "type",
pg_catalog.pg_get_userbyid(c.relowner) AS "owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) AS "size",
pg_catalog.obj_description(c.oid, 'pg_class') AS "description"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind ~ rel_type
AND n.nspname !~ '^pg_toast'
AND n.nspname ~ sch_name
ORDER BY 1,2;
END
$$ LANGUAGE plpgsql STRICT; #5 Re : Général » Petit problème dans une fonction » 12/10/2014 03:51:12
Bon, je me réponds à moi-même, personnellement, tout seul (pu**in, j'en ai chibavé quand même:(
.
Après un nombre respectable de tests, dont j'aurais pu éviter une bonne partie en utilisant RAISE pour afficher ma string finale de l'intérieur de la proc (@!#©<), et lu un nombre impressionnant de pages sur le web, voila comment j'ai résolu le PB.
.
En début de proc, j'ai ajouté un IF et modifié le WHERE d'origine:
…
IF $2 = 'tables' THEN rel_type = '^(r|s|'''')';
ELSIF $2 = 'views' THEN rel_type = '(''v'',''s'','''')';
ELSIF $2 = 'matviews' THEN rel_type = '(''m'',''s'','''')';
ELSIF $2 = 'indexes' THEN rel_type = '(''i'',''s'','''')';
ELSIF $2 = 'sequences' THEN rel_type = '(''S'',''s'','''')';
ELSE
RAISE EXCEPTION 'Function ILLEGAL CALL: admin.tabviematvieindseq(TEXT, TEXT) - Wrong 2nd Parm - By User #%', common.whoami_id();
END IF;
RAISE NOTICE 'VOICI rel_type APRÈS AFFECTATION DANS LE IF: %', rel_type;
RETURN QUERY
…
WHERE c.relkind ~ rel_type
…Quand le 2nd parm de ma proc est: 'tables', ça fonctionne nickel; par contre, ce que je m'explique absolument pas, c'est que le RAISE renvoie également "une bonne string" pour 'views': ('v','s',''), ce qui devrait normalement fonctionner aussi, non? (Pas sûr que j'ai correctement appréhendé toutes les subtilités des regexp…)
#6 Re : Général » Petit problème dans une fonction » 11/10/2014 22:14:54
? J'ai eu une discussion il y a un certain temps avec Tom Lane, et il me confirmait qu'utiliser: '$n' à la place de: nom_du_parm permettait justement d'éviter ça (d'après ce que j'avais compris, c'est équivalent à: quote_literal(nom_du_parm)).
.
Maintenant, j'ai ptêt ben compris de travers; auquel cas, d'après votre réponse, il suffit que je prépare une requête paramétrée et que je fasse l'appel par la proc?
.
Par ailleurs, j'ai un bis repetita (plpgsql, ça n'est _vraiment_ pas simple), je dois passer… j'sais pas trop quoi en fait… à une proc.
.
La proc d'origine utilise:
…
WHERE c.relkind IN ('r','s','')
…et là encore, je sèche
.
Après m'être battu un bon bout de temps avec un: IN parm CHAR[], qui n'a pas plus fonctionné, je suis revenu à une string (avec laquelle j'ai aussi bataillé un bout de temps):
-- SELECT * FROM admin.tst( '''r'',''s'',''''' );
CREATE FUNCTION admin.tst(my TEXT)
RETURNS BOOLEAN AS $$
DECLARE
az TEXT := '(' || $1 || ')';
ch CHAR := 'r';
BEGIN
-- RETURN az; --AVEC "RETURNS TEXT AS": Fonctionne et renvoie bien: ('r','s','')
-- RETURN ch ~ az; --Fonctionne mais renvoie FALSE:(
-- RETURN ch IN az; --Plante à la création: syntax error at or near “az”
RETURN ch IN ($1); --Fonctionne mais renvoie FALSE:(
END
$$ LANGUAGE plpgsql STRICT; Et là, je n'y comprend plus rien; je pensais qu'en appliquant la même recette que la précédente, ça irait, meuh nan (
#7 Re : Général » Petit problème dans une fonction » 11/10/2014 17:52:24
Ahhh, merci, ça fonctionne Tbien.
Mon erreur était de persister à vouloir rester en SQL (plPgSQL, céplukomplikékesicétéplusimp;-).
J'avais utilisé temporairement "n.nspname = $1" pour pouvoir tester mon client, mais ça n'était pas satisfaisant puisque, d'après ce que je comprend, la regexp permet d'éviter une injection.
#8 Général » Petit problème dans une fonction » 11/10/2014 05:34:01
- Jiff
- Réponses : 7
Salut forumers,
.
J'ai une query qui fonctionne très bien en ligne de Cde, mais qui me me renvoit aucun résultat quand elle est placée dans une fonction.
Je suspecte fortement le quoting automatique de PG, mais je ne trouve pas comment la réécrire pour qu'elle fonctionne correctement.
.
La ligne qui marche est:
WHERE n.nspname ~ '^(common)$'En l'occurence, common est le nom d'un schéma.
.
La ligne qui me plante est sa "traduction" dans ma fonction qui prend ce nom de schéma comme parm:
WHERE n.nspname ~ '^($1)$'Bon, je me doute que la regexp permet d'éviter une éventuelle injection (et c'est Tbien), mais en attendant çamorchpôéjtroufpôpourkoa.
.
J'ai d'ailleurs le même résultat que ci-dessus (une réponse vide de lignes) avec:
WHERE n.nspname ~ '^("common")$'.
Comment dois-je réécrire cela pour que ça fonctionne comme attendu? (et que la protection reste activée).
#9 Re : Général » VIEW ch'tit problème » 05/09/2014 16:42:38
Oui, je m'en suis aperçu en relisant attentivement les docs.
J'ai résolu ce PB à la tronçonneuse: comme indiqué plus haut, j'ai maintenant mes date et RI d'update qui sont fixées aux mêmes valeurs que celles d'insertion (le client fera la discrimination entre update "de confort" et réel). Certainement pas la solution la plus élégante, mais bon, ça roule.
.
<EDIT>
Je viens de faire un test à partir de ton précédent message, parce que je me demandais justement à quoi pouvait servir exactement les LEFT|RIGHT JOINT; ben maintenant je sais, mon problème est définitivement réglé (et d'une façon correcte).
.
Merci encore!
#10 Re : Général » VIEW ch'tit problème » 02/09/2014 16:28:53
Bon, étant donné que ça urge, j'ai fini par trouver un palliatif. Pas super génial, mais ça devrait fonctionner comme je le veux.
.
Dans mon 'footer' (où se trouvent les dates ins & upd ainsi que les RIs tier ins & upd), je remplace 'clock_timestamp()' par 'current_timestamp' afin de bloquer le timestamp à l'heure d'appel, et je modifie les defaults de façon à ce que date_ins=date_upd et ri_tier_upd=1 (soit le SU de la DB); c'est le client qui fera la discrimination par tests d'égalités.
.
Et si ça môrche pô, je sacrifie un développeur java (ça ne manquera à personne;) sur l'autel du DB (Dieu Base).
#11 Re : Général » VIEW ch'tit problème » 02/09/2014 00:22:44
Ok, d'abord les views renvoyant firstname & name pour un tier:
View "common.vtier_ri_tier_ins"
Column | Type | Modifiers | Storage | Description
-------------+----------+-----------+----------+-------------
id | integer | | plain |
ri_tier_ins | integer | | plain |
firstname | da_label | | extended |
name | da_label | | extended |
View definition:
SELECT t.id,
t.ri_tier_ins,
vtf.firstname,
vtn.name
FROM tier t
JOIN vtier_firstname vtf ON vtf.id = t.ri_tier_ins
JOIN vtier_name vtn ON vtn.id = t.ri_tier_ins; View "common.vtier_ri_tier_upd"
Column | Type | Modifiers | Storage | Description
-------------+-------------------+-----------+----------+-------------
id | integer | | plain |
ri_tier_upd | integer | | plain |
firstname | character varying | | extended |
name | character varying | | extended |
View definition:
SELECT t.id,
COALESCE(t.ri_tier_upd, NULL::integer) AS ri_tier_upd,
COALESCE(vtf.firstname::character varying, NULL::character varying) AS firstname,
COALESCE(vtn.name::character varying, NULL::character varying) AS name
FROM tier t
JOIN vtier_firstname vtf ON vtf.id = t.ri_tier_upd
JOIN vtier_name vtn ON vtn.id = t.ri_tier_upd;Celle sur un fichier (en l'occurrence, celui des utilisateurs):
View "common.vtier_naminsupd"
Column | Type | Modifiers | Storage | Description
---------------+--------------------------+-----------+----------+-------------
id | integer | | plain |
civility | da_label | | extended |
name | da_label | | extended |
login | character varying(127) | | extended |
ins_firstname | da_label | | extended |
ins_name | da_label | | extended |
ins_date | dd_tstz | | plain |
upd_firstname | character varying | | extended |
upd_name | character varying | | extended |
upd_date | timestamp with time zone | | plain |
View definition:
SELECT t.id,
c.label AS civility,
n.label AS name,
t.login,
vtif.firstname AS ins_firstname,
vtin.name AS ins_name,
t.date_ins AS ins_date,
COALESCE(vtuf.firstname::character varying, NULL::character varying) AS upd_firstname,
COALESCE(vtun.name::character varying, NULL::character varying) AS upd_name,
COALESCE(t.date_upd::timestamp with time zone, NULL::timestamp with time zone) AS upd_date
FROM tier t
JOIN civility c ON c.id = t.ri_civility
JOIN name n ON n.id = t.ri_name
JOIN vtier_firstname vtif ON vtif.id = t.ri_tier_ins
JOIN vtier_name vtin ON vtin.id = t.ri_tier_ins
JOIN vtier_firstname vtuf ON vtuf.id = t.ri_tier_upd
JOIN vtier_name vtun ON vtun.id = t.ri_tier_upd;Le contenu actuel de la table common.tier:
SELECT * FROM common.tier;
id | ri_civility | ri_firstname | ri_name | ri_name_commercial | login | date_ins | date_upd | ri_tier_ins | ri_tier_upd
----+-------------+--------------+---------+--------------------+-------+-------------------------------+-------------------------------+-------------+-------------
1 | 1 | 1 | 2 | | admin | 2014-08-30 03:52:56.777909+02 | 2014-09-01 21:38:42.464494+02 | 1 | 1
3 | 1 | 1 | 2 | | test | 2014-09-01 21:41:12.365147+02 | | 1 |
(2 rows)Le select sur la view censée tout afficher:
SELECT * FROM common.vtier_naminsupd ;
id | civility | name | login | ins_firstname | ins_name | ins_date | upd_firstname | upd_name | upd_date
----+----------+------+-------+---------------+----------+-------------------------------+---------------+----------+-------------------------------
1 | Mister | USER | admin | SUPER | USER | 2014-08-30 03:52:56.777909+02 | SUPER | USER | 2014-09-01 21:38:42.464494+02
(1 row)Hm, j'ai oublié le résultat des 2 premières views:
SELECT * FROM common.vtier_ri_tier_ins ;
id | ri_tier_ins | firstname | name
----+-------------+-----------+------
1 | 1 | SUPER | USER
3 | 1 | SUPER | USER
(2 rows)SELECT * FROM common.vtier_ri_tier_upd ;
id | ri_tier_upd | firstname | name
----+-------------+-----------+------
1 | 1 | SUPER | USER
(1 row)En fait, ce que je voudrais comme retour, c'est la même chose que le SELECT * FROM common.tier avec noms et prénoms en plus, ce que j'ai pour l'instant uniquement si le row de common.tier a subit un update (avec évidemment des NULL à la place des de firstname & name quand ça n'est pas le cas).
.
Le résultat est logique (puisque vtier_ri_tier_upd ne renvoie qu'un seul row), et c'est certainement évident; mais j'ai le nez dans le guidon depuis trop longtemps pour voir ce qui merdouille
.
Le but final étant d'avoir toujours le même format de retour, et surtout éviter: requête-1, test ri_tier_upd IS NULL?, éventuelle requête-2.
.
Hm, en relisant les docs, je vois une partie de mon erreur (?) et j'ai réécrit 2 views:
CREATE VIEW common.vtier_ri_tier_upd AS
SELECT
T.id,
COALESCE(T.ri_tier_upd, 1) AS ri_tier_upd,
COALESCE(VTF.firstname, '') AS firstname,
COALESCE(VTN.name, '') AS name
FROM common.tier T
INNER JOIN common.vtier_firstname VTF ON VTF.id = ri_tier_upd
INNER JOIN common.vtier_name VTN ON VTN.id = ri_tier_upd;
et:
CREATE VIEW common.vtier_naminsupd AS
SELECT
t.id,
c.label AS civility,
n.label AS name,
t.login,
vtif.firstname AS ins_firstname,
vtin.name AS ins_name,
t.date_ins AS ins_date,
COALESCE(t.ri_tier_upd, 1) AS ri_tier_upd,
COALESCE(vtuf.firstname, '') AS upd_firstname,
COALESCE(vtun.name, '') AS upd_name,
COALESCE(t.date_upd, NULL) AS upd_date
FROM tier t
JOIN civility c ON c.id = t.ri_civility
JOIN name n ON n.id = t.ri_name
JOIN vtier_firstname vtif ON vtif.id = t.ri_tier_ins
JOIN vtier_name vtin ON vtin.id = t.ri_tier_ins
JOIN vtier_firstname vtuf ON vtuf.id = ri_tier_upd
JOIN vtier_name vtun ON vtun.id = ri_tier_upd;mais ça ne fonctionne toujours pas:(
Déjà, je ne comprends â pourquoi un select sur la Nlle view ne renvoie toujours qu'un seul row au lieu de 2:
SELECT * FROM common.vtier_ri_tier_upd;
id | ri_tier_upd | firstname | name
----+-------------+-----------+------
1 | 1 | SUPER | USER
(1 row)puisque COALESCE est censé remplacer ri_tier_upd (NULL) trouvé dans le row #3 de common.tier par 1; donc, je m'attendais à ce que la requête renvoie qq chose comme ça:
SELECT * FROM common.vtier_ri_tier_upd;
id | ri_tier_upd | firstname | name
----+-------------+-----------+------
1 | 1 | SUPER | USER
3 | 1 | |#12 Général » VIEW ch'tit problème » 01/09/2014 22:57:41
- Jiff
- Réponses : 5
Salut forumers,
.
Existe-t-il un moyen de renvoyer NULL à la place de colonnes inexistantes dans une VIEW?
.
Je m'explique: dans pratiquement chacune de mes tables, j'ai: ri_usr_ins & ri_usr_upd; jusque-là, rien de zarb.
…
ri_user_ins INTEGER NOT NULL REFERENCES usr(id) ON DELETE RESTRICT,
ri_user_upd INTEGER NOT NULL REFERENCES usr(id) ON DELETE RESTRICT,
…Dans ma table des utilisateurs, j'ai pour chacun: ri_firstname & ri_name.
…
ri_firstname INTEGER NOT NULL REFERENCES name(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
ri_name INTEGER NOT NULL REFERENCES name(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
…J'ai une première VIEW qui renvoie: usr(id), usr(firstname) (littéral) & usr(name) (littéral).
id | firstname | name
------+----------+------
1 | JEAN | TY
.
Une seconde VIEW renvoie les colonnes significatives de la table cible, plus: ins_firstname, ins_name; jusque-là, ça va, mais elle doit aussi renvoyer: upd_firstname & upd_name.
Évidemment, quand le row n'a pas subit d'update, ri_user_upd est NULL.
.
Le problème est que je pensais naïvement que ma 2nde VIEW allait me renvoyer des NULL, mais elle ne renvoie pas les rows n'ayant pas subit d'update.
.
Comment puis-je sortir de cela? Existe-t-il une possibilité de faire en sorte que des NULL soient renvoyés ou bien d'établir une VIEW conditionnelle qui soit ne renverrait pas les colonnes à NULL, soit les renverrait remplies de NULL?
.
Ou bien dois-je me résoudre à insérer en même temps les mêmes ri_usr_ins & ri_usr_upd dans la table d'origine, afin de ne plus avoir de NULL pour ri_usr_upd?
#13 Général » Question design » 01/09/2014 16:26:04
- Jiff
- Réponses : 0
Salut forumers,
.
Question sur le design d'une table contenant des n° de série.
.
Étant donné les contraintes (notamment une traçabilité totale), J'aurais tendance à penser que mon design n'est pas mauvais; cependant entre lectures et questions, j'ai appris que SQL comporte des virages parfois dangereux et que "ma logique" n'était souvent qu'une mauvaise conseillère 
.
Mon design:
CREATE TABLE item.serialnb (
id BIGSERIAL NOT NULL PRIMARY KEY,
ri_item INTEGER NOT NULL REFERENCES item.item(id) ON DELETE RESTRICT,
ri_stock_place INTEGER DEFAULT NULL REFERENCES item.stock_place(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
serialnb DA_SN DEFAULT NULL,
qty DN_QTY DEFAULT 1.0,
output BOOLEAN NOT NULL DEFAULT FALSE,
consumption BOOLEAN NOT NULL DEFAULT FALSE,
ri_comnoml INTEGER DEFAULT NULL REFERENCES item.comnoml(id) ON DELETE RESTRICT,
ri_sup_dlol BIGINT DEFAULT NULL REFERENCES supplier.dlol(id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
ri_sup_invl BIGINT DEFAULT NULL REFERENCES supplier.invl(id) ON DELETE RESTRICT,
ri_sup_rmal BIGINT DEFAULT NULL REFERENCES supplier.rmal(id) ON DELETE RESTRICT,
ri_sup_cdnl BIGINT DEFAULT NULL REFERENCES supplier.cdnl(id) ON DELETE RESTRICT,
ri_cli_prel BIGINT DEFAULT NULL REFERENCES client.prel(id) ON DELETE RESTRICT,
ri_cli_fabl BIGINT DEFAULT NULL REFERENCES client.fabl(id) ON DELETE RESTRICT,
ri_cli_dlol BIGINT DEFAULT NULL REFERENCES client.dlol(id) ON DELETE RESTRICT,
ri_cli_invl BIGINT DEFAULT NULL REFERENCES client.invl(id) ON DELETE RESTRICT,
ri_cli_rmal BIGINT DEFAULT NULL REFERENCES client.rmal(id) ON DELETE RESTRICT,
ri_cli_excl BIGINT DEFAULT NULL REFERENCES client.excl(id) ON DELETE RESTRICT,
ri_cli_cdnl BIGINT DEFAULT NULL REFERENCES client.cdnl(id) ON DELETE RESTRICT,
ri_serialnb BIGINT DEFAULT NULL REFERENCES item.serialnb(id) ON DELETE RESTRICT,
LIKE tpl_table_footer INCLUDING DEFAULTS
) WITH (OIDS=FALSE, FILLFACTOR=70);
NB: Toutes les colonnes sont indexées, avec séparation TRUE/FALSE pour les indexes des BOOLEAN
(qui seront Tpeu utilisés et correspondent aux sorties de stock (Galmt démarque) et consommation
interne.Normalement, je n'aurais du chaîner qu'avec un seul doc fournisseur (dlo: delivery order: bon de réception, soit le premier de la chaîne) puis, lors d'une recherche, faire le chemin inverse pour retrouver tous les autres types de docs (mais au prix d'un nombre élevé de requêtes et join) et pareil pour les clients.
C'est donc une dérogation au modèle standard, mais qui me parait permettre de gagner beaucoup en terme de requêtes (et d'occupation serveur). Est-ce bon ?
.
Second point: normalement, j'aurais du joindre les s/n aux divers docs fournisseurs et clients avec une table de liaison par type de doc
du type "type_de_doc_id---serialnb_id" (par conséquent, j'ai certaines RIs ici qui ne serviront pas et resteront donc définitivement à NULL); mais là aussi je pense gagner du temps et de l'occupation serveur en ayant tout sous la main; le tout au prix d'une occupation disque supérieure, mais qui n'a qu'une importance marginale aujourd'hui. Sans compter le nombre d'indexes, puisque celui sur serialnb(id) serait répété à chaque table de liaison.
.
Mes impératifs sont: traçabilité totale, dont éléments de remplacement des RMA (retours matériels), d'où la self-RI, rapidité (+sieurs dizaines de milliards de rows minimum, accès à partir de n'importe quel doc fournisseur|client, reset facile en cas d'annulation|suppression de doc client, requêtes ultra-rapides lors de préparations/fabrications de commandes clients, recherches de S/N, etc.
.
Merci d'avance (PAS sur la tête!;)
#14 Re : Général » PK composite et indexes » 31/08/2014 20:47:30
Ok, merci rjuju.
#15 Général » PK composite et indexes » 31/08/2014 15:51:53
- Jiff
- Réponses : 2
Salutations dominicales, forumers,
.
Ma question est: lorsqu'une table a une PK composite, est-ce que Pg crée/utilise des indexes unitaires sur chaque partie de cette PK; ou bien faut-il les créer manuellement?
#16 Re : Général » SELECT * sur une VIEW » 21/08/2014 19:34:55
Cébenkeskejpensè mais bon, comme j'ai des fois le neurone qui grippe, mieux valait poser la question 
Merci.
#17 Général » SELECT * sur une VIEW » 21/08/2014 17:09:08
- Jiff
- Réponses : 2
Salut forumers,
.
Est-il correct de faire un SELECT * sur une VIEW, étant donné que l'ordre des colonnes est fixé (et reste fixe) par la VIEW ?
.
Ou bien vaut-il mieux, par sécurité, la considérer au même titre qu'une table, et donc nommer toutes les colonnes dans le SELECT ?
#18 Sécurité » Empêcher les injections SQL » 10/08/2014 02:48:06
- Jiff
- Réponses : 1
Salut forumers,
.
Est-ce qu'en utilisant un PREPARE appelé à partir d'une PROCÉDURE STOCKÉE de la façon suivante, ma DB ne risque rien ?
.
PREPARE:
PREPARE fooplan (int, text, bool, numeric) AS
INSERT INTO foo VALUES($1, $2, $3, $4);PROCÉDURE STOCKÉE:
CREATE FUNCTION machtiteps(INTEGER, TEXT, BOOL, NUMERIC, INTEGER, VARCHAR(48)) AS $$
…
EXECUTE fooplan($1, $2, $3, $4);
…Ou bien, suis-je _obligé_ d'utiliser les différents "quote_…()" sur $1, $2, $3 & $4 avant l'EXECUTE ?
.
Ah, merdouille, yauntrukaukeljavèplupensé
Je me réponds sur la question ci-dessus: elle est sans objet, puisque les utilisateurs n'ont aucun accès direct aux schémas ni aux tables, rien que des droits d'exécution, ou pas, sur des procédures (toutes SECURITY DEFINER).
J'en déduis donc qu'il _faut_ que j'utilise quote_…() sur tous les parms IN de mes procédures, puisque je n'aurais pas de requêtes préparées.
.
Reste la 2nde question: est-ce que cela protège également des injections n'utilisant pas les quotes, telles que décrites dans http://www.blackhatlibrary.net/SQL_inje … y_measures (par exemple; celle utilisant des "select chr(NN)||chr(XX)…) ?
#19 Re : Général » Quel type de backup/réplication choisir? + question de structure » 11/03/2014 14:50:44
Je serais très intéressé d'avoir la référence de cette Loi.... En effet à ma connaissance elle n'existe pas.
Oops, my very bad!
Effectivement, aucune trace sur le web d'une telle législation…
.
Et voici l'explication de ma méprise: vers 2006 (IIRC), un de mes amis utilisant EBP sur 2 portables et un poste bureau, abonné aux "services" d'EBP et transférant régulièrement ses factures par export/import des 2 portables vers le desktop (numéros décalés de +1M pour chaque portable pour qu'il n'y ait pas de collisions) a "déduit" de ses déboires que la version suivante ne le lui permettrait plus.
.
Donc, très bêtement (et en râlant parce que ça me semblait tellement idiot), j'ai pris cela pour argent comptant et entériné ce fait sans le vérifier.
.
Merci pour avoir pointé cette erreur! (mais ça ne change pas mon opinion sur le reste: les parlementaires débiles, ça reste une spécialité bien de chez nous ;-p )
#20 Re : Général » Quel type de backup/réplication choisir? + question de structure » 09/03/2014 21:51:25
… ce qui peut avoir des problèmes avec l'utilisation de fonctions non déterministes telles que random() par exemple. Il peut également servir de surcouche à la réplication native de postgres (par envoi des modifications binaires donc) un peu comme le ferait linux ha, ce qui nécessite de fournir des scripts de failover etc, et gérer de la balance de charge.
C'est ce que j'avais déduit de la doc; ça n'a pas d'incidence dans mon cas cependant, car je n'utilise pas de telles fonctions (exception faite du timestamp d'INSERT et de celui d'UPDATE - qui peuvent tout à fait souffrir un léger décalage puisqu'ils ne sont quasiment uniquement utilisés que pour la date).
Donc, pas de PB Pgpool-II pour le synchrone.
La réplication PG a des attraits, cependant il *faut* que tout cela reste transparent parce que dans mes interlocuteurs j'ai plus souvent à faire avec des utilisateurs qu'à des admins (ou pire: ceux qui se disent admins et commencent les conneries avant même que je n'ai le dos tourné).
Il faut aussi que ça ne vire pas usine à gaz pour moi, de façon à ce que je puisse intervenir rapidement et efficacement le cas échéant, même après plusieurs mois et sans préparation préalable.
Donc, je suis prévenu par email &| SMS du basculement et eux ne voient rien - c'est exactement ce que je veux.
.
… il vous faut utiliser une réplication par trigger comme slony…. Ce type de réplication n'est pas possible de manière synchrone.
Oui, c'est la conclusion à laquelle j'étais arrivé, slony est systématiquement conseillé dans un tel cas.
.
Merci, je pense avoir mes réponses.
#21 Re : Général » Quel type de backup/réplication choisir? + question de structure » 09/03/2014 17:25:23
Qu'entendez-vous par "réplication synchrone intégrée dans pgpool-II" ?Quelque chose comme: http://docs.postgresqlfr.org/9.3/high-availability.html, où le tableau indique bien une réplication synchrone avec Pgpool-II.
Maintenant, je ne me suis pas encore vraiment penché dessus (le choix s'est fait naturellement, vu la quantité d'article touchant Pgpool-II) et peut-être qu'il utilise la réplication synchrone intrinsèque de PG et n'a pas lui-même de mécanismes dédiés à cela.
Mais si c'était juste pour me faire dire ça, il me semble qu'on touche à la sécabilité quaternaire d'une capillarité, non?
.
A ma connaissance il n'existe pas de réplication permettant de compresser toutes les données sauf les bytea.Tant pis.
Dans ce cas, je vais sortir les BYTEA des tables Pales, et créer des tables liées qui ne seront pas répliquées en asynchrone; (enfin, si ça n'est pas un problème pour le soft de réplication de laisser une partie des tables de côté).
En effet, la présence physique des plus gros BYTEA n'est indispensable qu'au siège, et les autres (docs clients) peuvent très êtres générés localement mais non-intégrés dans la DB, et très bien aussi être re-générés pendant la nuit au siège.
.
Et pour l'asynchrone, que me conseillez-vous comme solution? (étant donné que Pgpool-II fait aussi ce type de réplication, j'aurais tendance à penser qu'avoir du tout-en-un est tentant).
#22 Général » Quel type de backup/réplication choisir? + question de structure » 08/03/2014 21:20:33
- Jiff
- Réponses : 7
Salut forumers,
.
* Cas de figure: ERP d'entreprise (très gros volume; stockage d'innombrables docs sous forme de PDF, donc backup TRÈS lent assez rapidement), la mise en réseau entreprise/points de vente se fait par VPN chiffré.
* Dispo DB heures ouvrables: ASAP
* Dispo DB heures fermées: OSEF
* Points de vente (Nb: 5): connectés par ADSL (donc, poss. de débits pas terr., voire noster;)) - Les PDV *doivent* pouvoir continuer à fonctionner lors d'une interruption de l'Internet
.
* Besoin entreprise Pale: réplication synchrone + HA.
* Besoin points de vente: réplication asynchrone la plus rapide possible.
.
Supputation: Vu les besoins de l'entreprise Pale, la réplication synchrone intégrée dans Pgpool-II semble farpaitement correspondre aux specs, les temps d'accès ne sont pas (si) critiques (que ça), c'est apparemment très stable et surtout ça intègre son propre mécanisme de failover (évitant les jongleries habituelles avec le HA).
Évidemment, s'il y a mieux, je suis preneur.
.
Par contre pour les points de vente, vu la pléthore d'offre, je ne sais pas trop vers quel programme me tourner; sachant qu'il faut une compression maximale (pour économiser la B.P) mais aussi si possible "intelligente": ne compressant pas les BYTEA puisqu'on risque de se retrouver avec plus de bytes à la sortie qu'à l'entrée.
.
NB: on peut imaginer que le point d'accès VPN soit une machine différente du SVR Pgpool-II, mais je préférerais éviter cela.
Précision: chaque PDV a son propre stock et donc, les ventes ne peuvent interférer entre PDV ni avec l'entreprise (sauf en cas de transfert &| I/O de stock locaux par l'entreprise, mais ça sera interdit si les liaisons sont coupées).
.
Problématique des coupures entre SVR & PDV: étant donné qu'un parlementaire débile (pléonasme local) a fait passer une loi interdisant la recopie de données d'une base commerciale dans une autre, comment gérer de telles coupures?
.
1)- Violer la loi - ce qui ne me pose pas de PB moral (sauf si la compta risquait d'en être déclarée non-recevable), mais par contre des PBs de synchro: dans tous les cas, que les docs ne soient numérotés qu'à partir de l'entreprise ou bien que les PDV aient chacun leurs séquences décalées (disons de +100.10e6), comment assurer la re-synchronisation (dans le sens inverse: PDV vers SVR) lors du rétablissement de la liaison? - D'autant que si seul le SVR contient des séquences, on va se retrouver avec des numéros de docs en doublon
2)- Considérer qu'aujourd'hui les coupures/accidents sont très rares, et donc facturer manuellement en attendant le rétablissement, puis saisir les factures définitives, sortir les PDF et les envoyer par e-mail aux clients? - Ce qui veut dire créer un PDF de tarif tous les matins, et c'est facile à faire.
3)- Obiwan Kenobi (parce que je ne vois pas comment faire autrement que ci-dessus)
.
Merci d'avance.
.
.
PS: La déconnexion est trop rapide, j'ai mis plus d'une plombe à mettre en forme mon post et heureusement que j'avais fait une copie, parce que j'avais été déconnecté entre-temps
#23 Re : Général » Questions sur le type BYTEA » 07/03/2014 00:53:39
1+2. Donc juste des strings en I/O; c'est beaucoup plus simple que je ne pensais.
.
3. Tant que vous ne faites pas de SELECT * ou que vous ne réclamez pas explicitement ce champ, non, en effet.Ça existe des gens qui font un 'SELECT *' en dehors des vérifications manuelles en ligne de Cde???
.
4. Flûte, j'utilise la même chose pour certains BOOLEAN et je n 'ai même pas pensé à le faire là
.
Merci pour ces éclaircissements.
#24 Général » Questions sur le type BYTEA » 06/03/2014 05:21:41
- Jiff
- Réponses : 3
Salut forumers,
.
n'ayant jamais utilisé ce type, j'ai quelques questions dessus. Le but étant de stocker/restituer des PDF:
.
1- Existe-t-il une/des fonctions PG permettant de directement passer d'une string à un BYTEA et inversement dans les différents formats?
.
2- Sachant que mes PDF sont très efficacement re-compressés avant stockage (avec gs), quel format de HEX ou ESCAPE est celui qui prendra le moins de place sur le disque? (au vu de la doc je dirais ESCAPE, mais comme rien n'est dit là-dessus…)
Précision, la colonne BYTEA sera logiquement altérée de la sorte: SET STORAGE EXTERNAL.
.
3- Vu ce qui est expliqué dans la doc sur les types de stockage, les PDF ne devraient normalement pas pénaliser les requêtes sur cette table, non? (il n'y a pas que le BYTEA, il y a aussi au moins une 20aine d'autres colonnes).
.
4- Je viens de m'apercevoir que l'on peut indexer une colonne de type BYTEA et ça m'intéresse (NULL|NOT NULL == yapa|ya un PDF stocké dans la colonne), cependant est-ce que l'index utilise tout le contenu du BYTEA ou pas? (ne risque-t-il pas de grossir démesurément, auquel cas un BOOLEAN de présence ou pas du PDF serait nécessaire?)
.
Merci d'avance.
#25 Re : Général » Attaquer un svr XML-RPC à partir de LISTEN/NOTIFY ou d'1 proc stockée? » 09/02/2014 16:42:54
Ok, la majorité de l'appli étant en Python, ça ne devrait pas poser de PB;
merci.