Vous n'êtes pas identifié(e).
Bonjour.
Pour commencer, je suis autodidacte dans SQL, la programmation et évidemment PostgreSQL, ce qui risque de compliquer un peu vos réponses car il va falloir me parler dans un langage assez simpliste, désolé 
Le contexte :
PostgreSQL 10 installé sur une machine virtuelle Ubuntu Mate 18.04.1, elle même installée sur un windows 10 pro
Echanges avec la base via pgAdmin4, soit sur ubuntu, soit depuis la session windows (les 2 sont fonctionnels).
Une seule base de donnée créée dans le schéma PUBLIC, 12 tables, une vingtaines de fonctions pré-enregistrées.
Une vingtaine de personnes seront amenées à se connecter à la base, avec des fonctions différentes qui peuvent être résumées comme suit:
- Lecteur : accès en ... lecture 
+ insertion dans une table (journal des erreurs)
- contributeur : lecture + modification de certaines tables + insertion dans d'autres
- administrateur : full acces (il s'agit de l'administrateur de l'application cliente, et non celui de la base PostgreSQL)
Si besoin de plus d'infos, n'hésitez pas à demander.
Les contraintes :
Il s'agit d'une version de test/dev avec laquelle je peux faire n'importe quoi. Par contre, une fois la base déverminée, il faudra la transférer sur un serveur avec lequel j'ai des contraintes impossible à faire fléchir. Je n'ai droit qu'à un seul utilisateur avec LOGIN (qu'on va appeler LOGIN ci-dessous pour des raisons évidentes).
Le(s) problème(s) :
1 - Afin de ne pas donner non plus la totalité des droits aux futurs utilisateurs, j'ai créé plusieurs rôle (avec les privilèges NOLOGIN et INHERIT). Afin de faciliter également la gestion des droits, j'ai également créé des rôle "groupe" admin, contrib et lecteur auxquels j'ai paramétré les autorisations (GRANT) et dont les membres sont les rôles utilisateur. Cependant, la connexion au serveur se fait toujours via l'utilisateur LOGIN.
Donc pour réaliser l'identification de mes utilisateurs, j'ai pensé faire la connexion via l'utilisateur LOGIN, contrôler dans une table 'utilisateur' le login et le mot de passe de l'accédant et si tout est OK, de faire un
SET ROLEJ'ai fais le test, le current_user change bien (le session_user est toujours l'utilisateur LOGIN) et les droits liés au rôle groupe sont bien appliqués (je perds par exemple le droit DELETE sur toutes les tables) donc tout va bien !
Est-ce que cela semble cohérent pour vous ou y a t'il une meilleure méthode ?
2 - Certaines modifications ne doivent pas pouvoir être concurrentes : un utilisateur ne doit pas pouvoir modifier un enregistrement pendant qu'un autre le fait (c'est à mon humble avis la priorité sur les SGBD). Or quand j'ouvre 2 session avec LOGIN puis que je SET un rôle différent à chaque session (les current_user changent bien), et si je fais sur une session un
SELECT * FROM matable WHERE colonne1 = 'quelque chose' FOR UPDATE NOWAIT;je peux malgré tout faire, avec l'autre session
SELECT * FROM matable WHERE colonne1 = 'quelque chose d'autre';L'enregistrement est bien modifié par la deuxième session alors que j'ai normalement un verrou sur l'enregistrement.
3 - Je ne vais pas détailler, mais j'ai le même problème avec un LOCK TABLE.
Comme si le verrou était posé non pas par le current_user, mais par le session_user.
J'ai eu l'occasion de voir avec un collègue qui lui travaille sur une base Oracle, chaque session est indépendante. En se connectant avec le même user et mot de passe, Oracle se base sur la session pour les verrous.
Toute aide pour m'empêcher de m'arracher les cheveux est la bienvenue
[EDIT] A titre de test, j'ai essayé de me connecter avec 2 utilisateurs différents (avec le privilège LOGIN) pour vérifier si les verrous sont appliqués en fonction du current_user ou du session_user, mais je n'arrive pas à créer 2 sessions différents de pgadmin : à chaque fois, la modification de la connexion sur une page de pgAdmin entraine la modification de l'autre page ... au secours !
[EDIT 2] Bon j'avance un peu. A priori, il n'est pas possible de faire un SELECT FOR UPDATE en dehors d'une transaction BEGIN - COMMIT/ROLLBACK. Je vais creuser cette piste.
Dernière modification par ramirez22 (26/10/2018 09:37:04)
Hors ligne
Bonjour,
1. Il est en général préférable de gérer les autorisations dans l'applicatif et de ne se connecter qu'avec un seul utilisateur. De toutes façon, étant donné vos contraintes votre solution actuelle ne peut pas fonctionner à priori.
2. un SELECT ne modifie pas un enregistrement. Vous voulez à priori empêcher une lecture concurrente car vous allez modifier la ligne, mais il s'agit d'un problème différent. Pour cela il faudrait systématiquement mettre des clause FOR UPDATE aux select. Mais votre exemple ne marcherait pas car les clauses WHERE concernent deux lignes différentes.
3. Un LOCK TABLE verrouille la table pour tout le monde excepté la connexion courate, quelquesoit l'utilisateur connecté.
Julien.
https://rjuju.github.io/
Hors ligne
A titre de test, j'ai essayé de me connecter avec 2 utilisateurs différents (avec le privilège LOGIN) pour vérifier si les verrous sont appliqués en fonction du current_user ou du session_user
On peut vous donner tout de suite la réponse: ce lien entre utilisateur et verrouillage est une mauvaise direction.
Le verrouillage et les exclusions qui s'ensuivent s'entendent entre transactions.
Deux transactions concurrentes sont forcément dans des sessions différentes, mais qu'elles soient ouvertes par le même login ou pas n'a pas d'importance particulière par rapport à toutes les questions relatives aux verrous.
Un autre fait structurant important est que les verrous acquis dans une transaction sont libérés automatiquement à la fin de la transaction, et ne peuvent pas être libérés avant.
@DanielVerite
http://blog-postgresql.verite.pro/
Hors ligne
Bonjour à vous 2 et merci de vos réponses.
Je ne devais pas être réveillé lors de mon premier message : j'ai un peu tout mélangé. Je suis navré de cette confusion, elle vous a forcément mal orientée.
Lorsque je parlais de
SELECT * FROM matable WHERE colonne1 = 'quelque chose' FOR UPDATE NOWAIT;
et
SELECT * FROM matable WHERE colonne1 = 'quelque chose d'autre';Je voulais plutôt dire que si je fais depuis la session 1:
SELECT * FROM matable WHERE colonne1 = 'quelque chose' FOR UPDATE NOWAIT;et sur la session 2
UPDATE matable SET colonne2 = "blabla" WHERE colonne1 = 'quelque choe'La modification de la session 2 passait quand même.
Mais comme je l'ai dit dans l'EDIT1, je pense que je me suis fait "duper" par pgAdmin : la session ouverte est la même quel que soit le nombre de page de l'explorateur ouverte. Je pensais avoir ouvert plusieurs session mais non.
Du coup, j'ai fais l’essai avec une session sur pgAdmin et une via mon application : ça fonctionne bien ! Problème 2 : Résolu.
Par extension, j'imagine que le problème 3 est du même tonneau; Je n'ai cependant pas encore eu le temps de tester. A titre de question subsidiaire, qu'advient-il des verrous (via LOCK ou SELECT FOR UPDATE) en cas de coupure brutale des processus (genre coupure de jus sur la machine cliente...) ?
1. Il est en général préférable de gérer les autorisations dans l'applicatif et de ne se connecter qu'avec un seul utilisateur. De toutes façon, étant donné vos contraintes votre solution actuelle ne peut pas fonctionner à priori.
Désolé, j'ai pas tout compris
Ce mode de connexion (1 seul utilisateur avec LOGIN) est donc courant ? J'en suis surpris avec une base qui gère les droits d'accès, je pensais m'appuyer dessus. Mais bon, du coup j'ai pas le choix donc 
Mais pour la gestion des droits ? J'ai testé un SET ROLE pour "revenir" à des droits utilisateur plus restreints et ça semble fonctionner. Est-ce que je me fourvoie ?
En tout cas, je vous remercie pour vos retour. Je me sens moins seul 
Hors ligne
par définition un SELECT FOR UPDATE ne bloque pas un SELECT. Si vous voulez que vos deux ordres se bloquent mutuellement, il faut un FOR UPDATE pour chaque.
Pour pgAdmin, je ne sais pas ce que vous entendez par session.
Pour votre problème 3, comme l'a dit Daniel les verrous sont automatiquement libérés à la fin de la transaction. Du coup rollback, fermeture brutale de la connexion, crash ou redémarrage l'effet est le même : la transaction est annulée et les verrous sont relachés. À la seule exception d'une transaction préparée, qui elle survit tant qu'elle n'est pas explicitement validée ou annulée.
Concernant vos problème de droit, oui c'est très courant de n'avoir qu'un seul role postgres pour l'applicatif. Votre méthode de SET ROLE fonctionne effectivement, mais cela n'est pas recommandé pour beaucoup de raisons. Les premières qui me viennent à l'esprit :
- en cas d'installation sur une plateforme mutualisée, on n'a en général qu'un seul utilisateur à dispo
- on peut également avoir un utilisateur qui n'a pas le droit de créer d'autres utilisateurs
- il est bien plus compliqué de générer tous les GRANT et REVOKE sur tous les objets (y compris à la colonne dans certains cas), voire de générer des clause de ROW LEVEL SECURITY que de gérer cela dans l'applicatif
- pour une application fortement sollicitée, le fait d'avoir un utilisateur dédié par utilisateur final ne scale pas. Vous devrez créer des milliers d'utilisateurs ce qui ralentira globalement la base, et cela sera relativement incompatible avec un pooler de connexion
Julien.
https://rjuju.github.io/
Hors ligne
Et on a même des réponses le dimanche ... alors là, respect (et merci !).
OK je commence à comprendre l'utilité de n'avoir qu'un seul utilisateur en Login. Je trouve que c'est dommage de ne pas profiter de la gestion des droits de la base, mais le boulot peut être fait sans trop de problème dans l'application (sous réserve de s'y atteler ) et si c'est pour soulager la base... Je vire donc le SET ROLE
Merci encore de votre aide, je devrais maintenant m'en sortir
Bonne fin de weekend !
Hors ligne