Forums PostgreSQL.fr

AudreyJG

Membre

Gestion des droits sur les données

Bonjour,
J'implémente une base de données avec interface web en RoR et j'essai de mettre en place une gestion de droits sur ces données. Je m'explique :
Un scientifique a fait des manips et stocke ses données dans la base. Seuls lui et son labo ont le droit de voir ces données. Mais maintenant il veut pouvoir partager ses données avec un autre labo.
En clair, je voudrais gérer les droits de SELECT sur les tuples d'une table et non sur une table entière (ou une colonne comme le permettra très bientôt postgreSQL 8.4). J'ai opté pour une vue qui est générée pour chaque labo par une requête SQL que je stocke dans une table.

Oracle propose la gestion de droits sur les données, et je voudrais savoir si quelqu'un a déjà essayé (et réussi même) à faire cela avec Postgres?

Merci d'avance pour votre aide.

gleu

Administrateur

Re : Gestion des droits sur les données

Vu que PostgreSQL-SE a été refusé pour la 8.4, le seul moyen a ma connaissance est une vue en ce qui concerne le droit sur les lignes. La vue ne doit renvoyer que les lignes de current_user. S'il vous faut plus d'infos, je peux détailler.

---

Guillaume.

AudreyJG

Membre

Re : Gestion des droits sur les données

Je veux bien plus de détails.
J'ai déjà essayé l'option de générer une vue pour l'utilisateur (enfin, le labo de mon côté) courant. Je le fais pour chaque table avec des données nécessitant de la gestion de droit. J'ai en tout 148 vues. C'est beaucoup.

gleu

Administrateur

Re : Gestion des droits sur les données

Voici comment je fais pour une table.

guillaume@laptop$ createdb audreyjg
guillaume@laptop$ psql audreyjg
Bienvenue dans psql 8.3.7, l'interface interactive de PostgreSQL.

Saisissez:
    \copyright pour les termes de distribution
    \h pour l'aide-mémoire des commandes SQL
    \? pour l'aide-mémoire des commandes psql
    \g ou point-virgule en fin d'instruction pour exécuter la requête
    \q pour quitter

audreyjg=# create user u1;
CREATE ROLE
audreyjg=# create user u2;
CREATE ROLE
audreyjg=# create table t1 (id serial, contenusecret text, user_autorise text);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
CREATE TABLE
audreyjg=# insert into t1 (contenusecret, user_autorise) values ('secret de u1', 'u1');  
INSERT 0 1                                                                               
audreyjg=# insert into t1 (contenusecret, user_autorise) values ('secret de u2', 'u2'); 
INSERT 0 1                                                                              
audreyjg=# insert into t1 (contenusecret, user_autorise) values ('deuxième secret de u1', 'u1'); 
INSERT 0 1

J'ai donc deux utilisateurs. Chacun ne doit voir que les lignes correspondants à ses droits dans une table. Le droit est fait en indiquant le code de l'utilisateur dans une colonne de cette table.

audreyjg=# select current_user;
 current_user                  
--------------                 
 guillaume                     
(1 ligne)                      

audreyjg=# revoke select on t1 from public;                 
REVOKE                                                      
audreyjg=# grant select on t1 to guillaume;                 
GRANT                                                       
audreyjg=# select current_user;                             
 current_user
--------------
 guillaume
(1 ligne)

audreyjg=# select * from t1;
 id |     contenusecret     | user_autorise
----+-----------------------+---------------
  1 | secret de u1          | u1
  2 | secret de u2          | u2
  3 | deuxième secret de u1 | u1
(3 lignes)

L'utilisateur guillaume est mon super utilisateur, il a droit de tout voir, et il est nécessaire d'en avoir un. Je vais créer deux fonctions qui me permettront d'accéder à la table pour les deux autres utilisateurs.

audreyjg=# create language plpgsql;                                                                     
CREATE LANGUAGE                                                                                         
audreyjg=# create or replace function protection_t1(i_user text) returns setof t1 security definer language plpgsql as $$
begin                                                                                                                    
return query (select * from t1 where user_autorise=i_user);                                                              
end$$;                                                                                                                   
CREATE FUNCTION                                                                                                          
audreyjg=# create or replace function protection_t1() returns setof t1 language plpgsql as $$
begin                                                                                                         
return query (select * from protection_t1(current_user));                                                     
end$$;                                                                                                        
CREATE FUNCTION

La première fonction sert à récupérer les lignes de l'utilisateur indiqué en argument. Elle est security definer pour que le SELECT sur la table soit accepté. La deuxième appelle la première en lui fournissant le nom de l'utilisateur à rechercher. On utiliser pour cela la variable current_user initialisé par PostgreSQL à la connexion. On est obligé de passer par deux fonctions car si on utilisait current_user dans la première, le current_user serait réinitialisé automatiquement à guillaume à cause de l'option security definer.

audreyjg=# select * from protection_t1('');
 id | contenusecret | user_autorise        
----+---------------+---------------       
(0 lignes)                                 

audreyjg=# select * from protection_t1('u1');
 id |     contenusecret     | user_autorise  
----+-----------------------+--------------- 
  1 | secret de u1          | u1             
  3 | deuxième secret de u1 | u1             
(2 lignes)                                   

audreyjg=# select * from protection_t1('u2');
 id | contenusecret | user_autorise          
----+---------------+---------------         
  2 | secret de u2  | u2                     
(1 ligne)

Ça fonctionne quand on appelle la première fonction. Connectons-nous avec les autres utilisateurs et essayons d'exécuter la deuxième fonction.

audreyjg=# \c audreyjg u1
Vous êtes maintenant connecté à la base de données « audreyjg »comme utilisateur « u1 ».
audreyjg=> select * from t1;
ERROR:  permission denied for relation t1
audreyjg=> select * from protection_t1();
 id |     contenusecret     | user_autorise
----+-----------------------+---------------
  1 | secret de u1          | u1
  3 | deuxième secret de u1 | u1
(2 lignes)

audreyjg=> \c audreyjg u2
Vous êtes maintenant connecté à la base de données « audreyjg »comme utilisateur « u2 ».
audreyjg=> select * from protection_t1();
 id | contenusecret | user_autorise
----+---------------+---------------
  2 | secret de u2  | u2
(1 ligne)

audreyjg=> select * from t1;
ERROR:  permission denied for relation t1

Autant on n'arrive pas à accéder à t1 directement, autant on y arrive en passant par la procédure stockée. Ensuite, on peut créer une vue qui appelle directement la fonction pour faciliter l'accès aux données. On peut aussi ajouter des règles sur la vue pour que les insertions/mises à jour/suppressions fonctionnent.

---

Guillaume.

gleu

Administrateur

Re : Gestion des droits sur les données

On est obligé de passer par deux fonctions car si on utilisait current_user dans la première, le current_user serait réinitialisé automatiquement à guillaume à cause de l'option security definer.

Plus exactement, on n'est pas obligé d'avoir la seconde si on la remplace par une vue.

---

Guillaume.

gleu

Administrateur

Re : Gestion des droits sur les données

Attention, cette solution vous protège sur votre applicatif. Le premier utilisateur qui se connecte directement à la base de données, et utilise la fonction protection_t1('un autre user') verra les lignes de l'utilisateur « un autre user ».

---

Guillaume.

gleu

Administrateur

Re : Gestion des droits sur les données

Je suis en train de regarder un tutoriel PostgreSQL sur la gestion des accès à pgCon2009. Le conférencier vient de dire qu'il existe un moyen plus simple de faire ça. Une vue serait préférable. Comme ceci :

create view v_t1 as select * from t1 where user_autorise=current_user;

Une vue est toujours exécutée en tant que celui qui l'a créé.

---

Guillaume.