Forums PostgreSQL.fr

Morby

Membre

Prb d'insertion sur une table avec un SERIAL PRIMARY KEY

Bonjour à tous et meilleurs voeux

j'ai un projet à réaliser : un mini-wiki sans application, juste les tables à créer, faire les vues et les grants

voici ma table "comptes utilisateurs" créée avec le compte administrateur, sachant qu'après elle est sensée être utilisée par 3 comptes userA, userB et userC :

DROP TABLE LGPUTILS CASCADE;

CREATE TABLE LGPUTILS
       (NUMUSER SERIAL PRIMARY KEY,
        NOM VARCHAR(25) NOT NULL,
    PASSWORD VARCHAR(25) NOT NULL,
    NBARTICLECREE INTEGER DEFAULT '0'
    );

-- droits d'accès
grant select on LGPUTILS to public;
grant insert on LGPUTILS to public;
grant update on LGPUTILS to public;

--création d'une vue qui permet de voir tous les attributs de la table
DROP VIEW LGPUSERNEW;
CREATE VIEW LGPUSERNEW AS SELECT * FROM LGPUTILS;
select * from LGPUSERNEW;

grant select on LGPUSERNEW to public;
grant insert on LGPUSERNEW to public;
grant update on LGPUSERNEW to public;

-- EDIT : doublon de règles supprimé suite à l'intervention de dverite
-- création d'une règle sur la vue "LGPUSERNEW" permettant l'insertion de nouveaux utilisateurs
-- avec attribut "nom" automatique en fonction du compte utilisateur qui fait l'insertion
DROP RULE usernew_insert2 on LGPUSERNEW CASCADE;
CREATE RULE usernew_insert2 AS ON INSERT TO LGPUSERNEW DO INSTEAD
    INSERT INTO LGPUTILS VALUES
    (default,
    current_user,
    NEW.PASSWORD);

**********************

EDIT : problème 1 résolu
Probleme 1:
avec le compte admin, pas de souci pour insérer dans la table les nouveaux "utilisateurs", du moment que je précise la colonne "nom"
ex : INSERT INTO LGPUTILS (nom,password) VALUES ('user1','PASS');

par contre ma règle usernew_insert2 refuse de fonctionner. pourtant la règle a bien été crée sans erreur.
ex : INSERT INTO LGPUSERNEW (password) VALUES ('pass');
>> ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « nom »

**********************

Probleme 2:
à partir du compte utilisateur "userA", je parviens sans soucis à faire un select sur la table LGPUTILS et la vue LGPUSERNEW.
par contre quand j'essaye de faire un INSERT dans la table LGPUTILS ou dans la vue LGPUSERNEW ça ne marche qu'à moitié :

- si j'insère dans la table LGPUTILS en précisant le "numuser" ça marche :
INSERT INTO LGPUTILS VALUES (1,'userA','PASS');

- si j'insère dans la vue LGPUSERNEW  en précisant le "numuser" ça ne marche pas :
INSERT INTO LGPUSERNEW VALUES (1,'userA','PASS');
ERREUR:  droit refusé pour la séquence lgputils_numuser_seq

- si je ne précise pas le numuser (il est sensé se mettre tout seul avec la fonction SERIAL), ça ne marche pas non plus :
INSERT INTO LGPUTILS (nom,password) VALUES ('userA','PASS');
ERREUR:  droit refusé pour la séquence lgputils_numuser_seq

je ne comprend pas, un petit coup de main serait bienvenu.

Dernière modification par Morby (07/01/2012 18:45:07)

dverite

Membre

Re : Prb d'insertion sur une table avec un SERIAL PRIMARY KEY

par contre ma règle usernew_insert2 refuse de fonctionner. pourtant la règle a bien été crée sans erreur.
ex : INSERT INTO LGPUSERNEW (password) VALUES ('pass');
>> ERREUR:  une valeur NULL viole la contrainte NOT NULL de la colonne « nom »

Le premier problème est qu'il y a 2 règles AS ON INSERT TO LGPUSERNEW DO INSTEAD
et il n'en faudrait qu'une seule.
En l'état vraisemblablement PG exécute la 1ere règle d'abord et s'arrête net sur nom à NULL sans même exécuter la 2eme règle.

Le deuxième problème est que l'INSERT de la seconde règle est de toute façon invalide. Il faudrait plutôt qu'il ressemble à

INSERT INTO LGPUTILS VALUES
    (default,
     current_user,
    NEW.PASSWORD);

La syntaxe colonne=valeur, c'est pour les UPDATEs.

Sinon sur le principe, utiliser des règles pour faire ça est excessivement compliqué à mettre au point et sans intérêt réel par rapport à une simple fonction procédurale.

---

@DanielVerite
http://blog-postgresql.verite.pro/

Morby

Membre

Re : Prb d'insertion sur une table avec un SERIAL PRIMARY KEY

Pour mon projet je suis sensé passer à 100% par des vues pour toutes actions insert/update sur mes tables, ça fait parti du cahier des charges

j'ai suivi tes conseils et supprimé le doublon de règle, maintenant j'ai uniquement :

DROP RULE usernew_insert on LGPUSERNEW CASCADE;

CREATE RULE usernew_insert AS ON INSERT TO LGPUSERNEW DO INSTEAD
    INSERT INTO LGPUTILS VALUES
    (default,
    current_user,
    NEW.PASSWORD);

- Avec compte admin créateur des tables -
INSERT INTO LGPUSERNEW (password) VALUES ('testSansNom');
insert à travers la vue LGPUSERNEW sans préciser ni le n° d'utilisateur (incrémentation auto avec SERIAL) ni le nom d'utilisateur
ça marche, merci, un premier prb de réglé

- Avec compte userA -
INSERT INTO LGPUSERNEW (password) VALUES ('testSansNom');
ERREUR:  droit refusé pour la séquence lgputils_numuser_seq
ça refuse de marcher, il semble que l'incrémentation automatique de numuser pose problème.... je ne comprend tjrs pas pourquoi

- Avec compte userA -
INSERT INTO LGPUSERNEW (nom,password) VALUES ('userA','PASS');
ERREUR:  droit refusé pour la séquence lgputils_numuser_seq
même en précisant le nom d'utilisateur dans la requete, pas moyen de faire un insert

- Avec compte userA -
INSERT INTO LGPUSERNEW VALUES (1,'userA','PASS');
ERREUR:  droit refusé pour la séquence lgputils_numuser_seq
même en précisant le numuser, pas moyen que ça marche

dverite

Membre

Re : Prb d'insertion sur une table avec un SERIAL PRIMARY KEY

Cette séquence lgputils_numuser_seq est automatiquement créée pour la colonne SERIAL, mais sa gestion ultérieure est laissée à la charge de l'utilisateur.
Donc en parallèle au grant insert on LGPUTILS to public, il faudrait donner le droit d'utilisation à la séquence utilisée pour le  SERIAL.
avec une commande du type: GRANT USAGE on lgputils_numuser_seq to public;

Sur le fait de préciser le numuser:

INSERT INTO LGPUSERNEW VALUES (1,'userA','PASS');
ERREUR:  droit refusé pour la séquence lgputils_numuser_seq
même en précisant le numuser, pas moyen que ça marche

Il est normal que ça ne change rien, puisque la règle en place court-circuite justement ce qui est passé dans numuser ainsi que pour l'autre colonne, seul le mot de passe atterrit dans la table.

---

@DanielVerite
http://blog-postgresql.verite.pro/

Morby

Membre

Re : Prb d'insertion sur une table avec un SERIAL PRIMARY KEY

yesss, super, ça fonctionne, problème 2 résolu
un grand merci à toi