Droits sur tous les schémas d'une base de données

EPTBSGL · 12/10/2018 09:42:47

Bonjour,

1 - Je souhaite affecter le même droit à un groupe sur tous les schémas (par exemple Usage) d'une base de données. Y a-t-il un moyen de faire cela ? J'ai vu que l'on pouvait le faire sur toutes les tables d'un schéma (par exemple avec le droit read) mais je n'ai pas trouvé pour les schémas.
2 - Y a-t-il moyen d'affecter un ou plusieurs droit(s) à toutes les tables de tous les schémas ?

En vous remerciant d'avance pour vos réponse,

Philippe NICOLAS

dverite · 12/10/2018 14:01:55

Pour 1), si c'est pour de futures créations, à partir de la version 10 on peut faire:

ALTER DEFAULT PRIVILEGES GRANT usage ON schemas TO nom_du_role;

Pour 2) on peut faire, sur toutes versions il me semble:

ALTER DEFAULT PRIVILEGES GRANT ... ON tables TO nom_du_role;

Si c'est pour des tables pré-existantes je pense qu'il faut faire pour chaque schéma:

GRANT ... ON ALL TABLES IN SCHEMA nom_schema TO nom_du_role;

EPTBSGL · 12/10/2018 16:33:25

Merci pour votre réponse rapides. J'avais effectivement déjà vu les privilèges par défaut mais dans ce cas précis, je cherche à affecter le même droit à tous les schémas déjà créés. Idem pour les tables. Pour avancer, j'ai mis les droits un à un sur toutes les tables de chaque schéma. ça va bien parce que je n'ai que 14 schémas (+ public) mais qu'en serait-il si j'avais un plus grand nombre de schémas ?
Peut-être faut-il écrire un script qui parcours systématiquement tous les schémas, créer une variable indexée (dans le code sql) et attribuer les droits ?

dverite · 12/10/2018 16:58:23

Il est assez facile généralement de scripter ces ordres GRANT. Les schémas existants peuvent être listés via la table système pg_namespace ou la vue information_schema.schemata qui est plus sélective.

Avec un psql récent (9.6) on peut écrire quelque chose du style:

 SELECT format('GRANT USAGE ON SCHEMA %I TO nom_du_role', schema_name)
    FROM information_schema.schemata \gexec

Le SELECT va générer les ordres de GRANT, et \gexec va directement exécuter ces ordres sans qu'il y ait besoin de plus de code.
On peut remplacer \gexec par \g pour voir les requêtes sans les exécuter dans la phase de mise au point.

Le faire en plpgsql est un peu plus compliqué mais pas bien méchant non plus: dans un bloc DO, un EXECUTE pour chaque GRANT généré à l'intérieur d'un LOOP sur la requête de sélection de schémas.

EPTBSGL · 17/10/2018 16:01:20

Encore merci pour votre réponse.
J'ai une version 9.5.6 dans laquelle les méta-commandes \gexec et \g n'existent pas. Si j'exécute la commande, j'ai bien une ligne par schéma qui est généré avec tout ce qu'il faut dedans. Par contre, je ne vois pas bien comment je peux écrire ça. Je pensais à (j'ai 17 schémas) :
DO
FOR i IN 1..17 LOOP
EXECUTE SELECT format('GRANT USAGE ON SCHEMA %I TO sig_bassee', schema_name) FROM information_schema.schemata
END LOOP;
A l'exécution j'ai une erreur de syntaxe sur FOR. Je ne comprends pas bien les instruction DO et EXECUTE (j'ai regardé dans la doc mais ce n'est pas très clair pour moi...).

J'ai une autre question :
Le schéma "monschema" est utilisé par user1, user2, ..., ces utilisateurs faisant partie du groupe "user_read" (on peut imaginer de mettre en place d'autres groupes").
Je voudrais que l'utilisateur créateur d'une table ait tous les droits sur celle-ci et uniquement les droits de lecture sur les tables créées par les autres utilisateurs. J'ai essayé de jouer avec les privilège par défaut. Le problème, c'est que celui qui transmet les droits doit être le propriétaire de la table. Hors, comme celui qui doit transmettre les droits doit être le propriétaire de la table, ça ne peut pas être le groupe (puisque le groupe ne peut pas se connecter). Je pensais déclencher une requête lors de la création de table (grant select on all tables in schema monschema to user_read) mais les triggers ne se déclenche que sur des drop, insert, ... mais pas create.
Auriez-vous une piste à me donner ?

En vous remerciant pour toute votre aide,
Philippe NICOLAS

Dernière modification par EPTBSGL (17/10/2018 16:19:43)

dverite · 18/10/2018 13:16:09

Il faut boucler sur le résultat d'une requête listant les schémas concernés, et il faut déclarer explicitement une variable pour ça.

DO
DECLARE
  s text;
BEGIN
 FOR s IN SELECT schema_name FROM information_schema.schemata
 LOOP
   EXECUTE format('GRANT USAGE ON SCHEMA %I TO sig_bassee', s);
 END LOOP;
END;

dverite · 18/10/2018 13:39:09

Je voudrais que l'utilisateur créateur d'une table ait tous les droits sur celle-ci

C'est le comportement de base, rien de spécial à faire.

uniquement les droits de lecture sur les tables créées par les autres utilisateurs

Le plus simple est de les donner explicitement: après avoir créé une table, un utilisateur doit faire

GRANT SELECT ON nouvelletable TO user_read;

Ou bien il le fait une fois pour toutes, si ça concerne toutes ses créations, avec

ALTER DEFAULT PRIVILEGES in SCHEMA nomduschema GRANT SELECT ON TABLES TO read_only;

Il est aussi possible de passer provisoirement à un autre utilisateur (si on a le droit) en faisant

SET ROLE username;

et

RESET ROLE;

pour revenir à celui du départ, ce qui permet notamment à un superutilisateur de scripter des commandes en lieu et place de l'utilisateur en question.

EPTBSGL · 19/10/2018 11:01:44

Bonjour et encore merci.
1 - Je me suis sûrement mal exprimé. En fait, je voudrais que les droits soient affectés automatiquement à la table lors de sa création (je suis d'accord que le créateur de la table a, de base, tous les droits dessus). Avec les privilèges par défaut, ça fonctionne si j'ai 2 utilisateurs : je fais un alter default privileges in schema bassee for role u1 grant select on tables to u2 puis alter default privileges in schema bassee for role u2 grant select on tables to u1. Pour la 1ère ligne le "cédant" (grantor) est u1 et le bénéficiaire du droit select est u2. Donc quand u1 crée une table, il cède le droit select à u2. Pour la 2ème ligne, c'est u2 qui cède le droit select à u1 pour les tables qu'il crée. Le problème, c'est qu'on ne peut pas mettre 2 fois le même bénéficiaire (grantee) ou 2 fois le même "cédant" (grantor) ; donc, à partir de 3 utilisateurs, ça coince. D'autre part si c'est le groupe qui cède les droits, ça ne peut pas fonctionner puisque c'est l'utilisateur qui est propriétaire de sa table et non le groupe.
votre proposition "ALTER DEFAULT PRIVILEGES in SCHEMA nomduschema GRANT SELECT ON TABLES TO read_only" fonctionne pour l'utilisateur courant qui cède le droit (grantor). Du coup quand c'est l'autre utilisateur du schéma qui crée la table le droit select n'est plus affecté;
Je me casse la tête avec ça depuis plusieurs jours et je crois que je vais finir par faire un schéma dédié à chaque utilisateur... à moins qu'il y ait une solution.

2 - Question idiote : pour ce qui est du code donnant des droits sur tous les schémas, j'ai le message "erreur de syntaxe sur ou près de « DECLARE »". En fait j'utilise la fenêtre SQL de pgadmin 4. Sauf que le sql, ce n'est pas du pl/pgsql. Du coup, je cherche à savoir si pl/pgsql est bien implémenté avec ma version de postgresql (9.5) sur centOS 7 mais je n'y arrive pas. Pour utiliser pl/pgsql, il suffit de taper le code dans la fenêtre d'édition SQSL de pgAdmin ? Faut-il écrire un fichier ? Pour info, j'ai accès au serveur et je peux aussi me connecter en ligne de commande.
En tous cas, toutes vos indications me permettent de progresser et je vous en remercie

Philippe NICOLAS

Dernière modification par EPTBSGL (19/10/2018 13:55:11)

EPTBSGL · 19/10/2018 12:16:30

Pour ma question idiote, j'ai la réponse : j'ai fait un create "create language plpgsql" sur ma base et il me répond "ERROR: ERREUR: le langage « plpgsql » existe déjà". J'en déduis donc qu'il faut saisir le code dans la fenêtre SQL de pgAdmin 4. Du coup, pourquoi ai-je une erreur de syntaxe sur "DECLARE" ?

dverite · 19/10/2018 13:30:12

Pour l'erreur de syntaxe, le corps du bloc DO doit être dans une chaîne de caractères. La syntaxe complète copier-collable dans une fenêtre SQL doit être

DO $$
DECLARE
  s text;
BEGIN
 FOR s IN SELECT schema_name FROM information_schema.schemata
 LOOP
   EXECUTE format('GRANT USAGE ON SCHEMA %I TO sig_bassee', s);
 END LOOP;
END;
$$ language plpgsql;

sachant que $$ peut être remplacé par $body$ ou $âutrechose$ si le corps du code utilise déjà ce séparateur.

EPTBSGL · 19/10/2018 13:53:29

merci pour votre patience. Cette fois, ça fonctionne.
Philippe NICOLAS

dverite · 19/10/2018 13:58:18

votre proposition "ALTER DEFAULT PRIVILEGES in SCHEMA nomduschema GRANT SELECT ON TABLES TO read_only" fonctionne pour l'utilisateur courant qui cède le droit (grantor). Du coup quand c'est l'autre utilisateur du schéma qui crée la table le droit select n'est plus affecté;

Oui mais l'autre utilisateur est courant dans sa session au moment où il créé la table donc je ne comprends pas pourquoi cette opposition entre l'utilisateur courant et l'autre utilisateur poserait problème ici, c'est juste une inversion de point de vue.

Voici un script d'exemple exécutable dans une base initialement vide qui illustre ma réponse plus haut. Il y a 3 utilisateurs, un schéma, un rôle read only. Les utilisateurs créent des tables, les autres les lisent mais ne peuvent pas écrire dedans.

SET ROLE postgres;

CREATE USER u1;
CREATE USER u2;
CREATE USER u3;
CREATE ROLE read_only;

GRANT read_only to u1,u2,u3;

CREATE SCHEMA s;
GRANT USAGE,CREATE ON schema s to u1,u2,u3;

SET ROLE u1;
ALTER DEFAULT PRIVILEGES in SCHEMA s GRANT SELECT ON TABLES
TO read_only;

SET ROLE u2;
ALTER DEFAULT PRIVILEGES in SCHEMA s GRANT SELECT ON TABLES
TO read_only;

SET ROLE u3;
ALTER DEFAULT PRIVILEGES in SCHEMA s GRANT SELECT ON TABLES
TO read_only;


SET ROLE u1;
CREATE TABLE s.test1(txt text);
INSERT INTO s.test1 values('je suis u1');

SET ROLE u2;
SELECT * FROM s.test1;
INSERT INTO s.test1 values('je suis u2 et je ne peux pas écrire dans test1');

SET ROLE u3;
CREATE TABLE s.test3(txt text);
INSERT INTO s.test3 values('je suis u3 avec ma table s.test3');

SET ROLE u1;
SELECT * FROM s.test1;
SELECT * FROM s.test3;

EPTBSGL · 19/10/2018 17:52:59

Si je fais ça, ça ne marche pas car, comme je l'ai constaté, il ne peut pas y avoir plusieurs "cédant" (grantor) pour un même bénéficiaire (grantee - ici le groupe read_only). J'ai donc une erreur "ERROR: ERREUR: droit refusé pour la relation test1 " sur la commande insert. En fait c'est le dernier "Alter default privilege" qui modifie le précédent s'il y a entre les deux 2 grantor ou grantee identiques (on voit ça très bien sur l'interface de pgadmin).
J'ai exactement déroulé votre script. Je pense donc que, si vous essayez, vous devriez avoir le même comportement.

En vous remerciant pour toute l'aide apportée,

Philippe NICOLAS

Dernière modification par EPTBSGL (19/10/2018 17:54:04)

dverite · 19/10/2018 19:41:45

L'exécution du script sous psql donne ça, pour la partie qui créé puis utilise des tables:

tst=> SET ROLE u1;
SET
tst=> CREATE TABLE s.test1(txt text);
CREATE TABLE
tst=> INSERT INTO s.test1 values('je suis u1');
INSERT 0 1
tst=> 
tst=> SET ROLE u2;
SET
tst=> SELECT * FROM s.test1;
    txt     
------------
 je suis u1
(1 row)

tst=> INSERT INTO s.test1 values('je suis u2 et je ne peux pas écrire dans test1');
ERROR:  permission denied for relation test1
tst=> 
tst=> SET ROLE u3;
SET
tst=> CREATE TABLE s.test3(txt text);
CREATE TABLE
tst=> INSERT INTO s.test3 values('je suis u3 avec ma table s.test3');
INSERT 0 1
tst=> 
tst=> SET ROLE u1;
SET
tst=> SELECT * FROM s.test1;
    txt     
------------
 je suis u1
(1 row)

tst=> SELECT * FROM s.test3;
               txt                
----------------------------------
 je suis u3 avec ma table s.test3
(1 row)

L'INSERT qui est rejeté pour cause de pas de permission montre justement que l'utilisateur u2 ne peut
pas écrire dans la table qui appartient à u1 (en revanche il peut la lire). C'est celui-là:

tst=> INSERT INTO s.test1 values('je suis u2 et je ne peux pas écrire dans test1');
ERROR:  permission denied for relation test1

Or c'est le comportement voulu tel que décrit plus haut dans la discussion:

Je voudrais que l'utilisateur créateur d'une table ait tous les droits sur celle-ci et uniquement les droits de lecture sur les tables créées par les autres utilisateurs

"Uniquement les droits de lecture", ça veut dire qu'une tentative d'écriture doit être rejetée, et c'est bien ce qui se passe.

EPTBSGL · 22/10/2018 12:06:50

Bonjour et un grand merci pour votre réponse. J'ai repris tous les tests ce matin et cette fois, ça fonctionne conformément à vos différents messages. Vendredi, j'avais quelque chose de bizarre : l'utilisateur créateur de la table ne pouvait pas accéder à celle-ci en lecture/écriture alors qu'il pouvait modifier sa structure...
Bref, j'ai ajouté des "encrypted password" pour assigner un mot de passe à la suite de chaque création de rôle et remplacé la ligne "GRANT USAGE,CREATE ON schema s to u1,u2,u3;" par "GRANT USAGE,CREATE ON schema s to read_only;" et ça fonctionne toujours.
Je pense donc que vendredi je me suis embrouillé dans mes tests.
Encore merci pour toutes vos explications et votre patience.
Philippe NICOLAS

#1 12/10/2018 09:42:47

Droits sur tous les schémas d'une base de données

#2 12/10/2018 14:01:55

Re : Droits sur tous les schémas d'une base de données

#3 12/10/2018 16:33:25

Re : Droits sur tous les schémas d'une base de données

#4 12/10/2018 16:58:23

Re : Droits sur tous les schémas d'une base de données

#5 17/10/2018 16:01:20

Re : Droits sur tous les schémas d'une base de données

#6 18/10/2018 13:16:09

Re : Droits sur tous les schémas d'une base de données

#7 18/10/2018 13:39:09

Re : Droits sur tous les schémas d'une base de données

#8 19/10/2018 11:01:44

Re : Droits sur tous les schémas d'une base de données

#9 19/10/2018 12:16:30

Re : Droits sur tous les schémas d'une base de données

#10 19/10/2018 13:30:12

Re : Droits sur tous les schémas d'une base de données

#11 19/10/2018 13:53:29

Re : Droits sur tous les schémas d'une base de données

#12 19/10/2018 13:58:18

Re : Droits sur tous les schémas d'une base de données

#13 19/10/2018 17:52:59

Re : Droits sur tous les schémas d'une base de données

#14 19/10/2018 19:41:45

Re : Droits sur tous les schémas d'une base de données

#15 22/10/2018 12:06:50

Re : Droits sur tous les schémas d'une base de données

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux