Accorder tous les droits sur un schéma spécifique de la base de données à un rôle de groupe dans PostgreSQL

Question

En utilisant PostgreSQL 9.0, j'ai un rôle de groupe appelé "staff" et j'aimerais accorder tous les privilèges (ou certains) à ce rôle sur les tables d'un schéma particulier. Aucune des méthodes suivantes ne fonctionne

GRANT ALL ON SCHEMA foo TO staff;
GRANT ALL ON DATABASE mydb TO staff;

Les membres du "staff" ne peuvent toujours pas effectuer de SELECT ou UPDATE sur les tables individuelles du schéma "foo" ou (dans le cas de la deuxième commande) sur n'importe quelle table de la base de données. sauf si J'accorde tout sur cette table spécifique.

Que puis-je faire pour faciliter ma vie et celle de mes utilisateurs ?

Mise à jour : J'ai trouvé la solution avec l'aide de une question similaire sur serverfault.com .

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO staff;

Answer 1

Vous avez trouvé l'abréviation pour définir les privilèges de tous les utilisateurs. existant dans le schéma donné. Le manuel précise :

(mais notez que ALL TABLES est considéré comme comprenant points de vue y tables étrangères ).

C'est moi qui souligne en gras. serial Les colonnes sont mises en œuvre à l'aide de nextval() sur une séquence en tant que colonne par défaut et, citer le manuel :

Pour les séquences, ce privilège permet d'utiliser la fonction currval y nextval fonctions.

Ainsi, s'il y a serial vous voudrez également accorder des droits d'accès aux colonnes USAGE (ou ALL PRIVILEGES ) sur séquences

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Nota: IDENTITY colonnes dans Postgres 10 ou plus utilisent des séquences implicites qui ne nécessitent pas de privilèges supplémentaires. (Envisager la mise à niveau de serial colonnes.)

Qu'en est-il de nouveau objets ?

Vous serez également intéressé par DEFAULT PRIVILEGES pour les utilisateurs ou les schémas :

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT USAGE          ON SEQUENCES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

Cela permet de définir automatiquement les privilèges pour les objets créés à l'avenir, mais pas pour les objets préexistants.

Les privilèges par défaut sont les suivants seulement appliquée aux objets créés par l'utilisateur ciblé ( FOR ROLE my_creating_role ). Si cette clause est omise, la valeur par défaut est celle de l'utilisateur en cours d'exécution. ALTER DEFAULT PRIVILEGES . Pour être explicite :

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

Notez également que toutes les versions de pgAdmin III ont un bogue subtil et affichage les privilèges par défaut dans le volet SQL, même s'ils ne s'appliquent pas au rôle actuel. Veillez à ajuster les FOR ROLE manuellement lors de la copie du script.

Answer 2

Ma réponse est similaire à celui-ci sur ServerFault.com .

Être conservateur

Si vous voulez être plus conservateur que d'accorder "tous les privilèges", vous pourriez essayer quelque chose de plus proche de ce qui suit.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO some_user_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO some_user_;

L'utilisation de public fait référence au nom du schéma par défaut créé pour chaque nouvelle base de données/catalogue. Remplacez par votre propre nom si vous avez créé un schéma.

Accès au schéma

Pour accéder à un schéma, quelle que soit l'action, l'utilisateur doit se voir accorder des droits d'utilisation. Avant qu'un utilisateur puisse sélectionner, insérer, mettre à jour ou supprimer un schéma, il doit d'abord se voir accorder des droits d'utilisation.

Vous ne remarquerez pas cette exigence lorsque vous utiliserez Postgres pour la première fois. Par défaut, chaque base de données possède un premier schéma nommé public . Par défaut, chaque utilisateur se voit automatiquement accorder des droits d'utilisation pour ce schéma particulier. Lorsque vous ajoutez des schémas supplémentaires, vous devez explicitement accorder des droits d'utilisation.

GRANT USAGE ON SCHEMA some_schema_ TO some_user_ ;

Extrait de la Postgres doc :

Pour les schémas, autorise l'accès aux objets contenus dans le schéma spécifié (en supposant que les exigences de privilèges propres aux objets soient également respectées). Cela permet essentiellement au bénéficiaire de la subvention de "rechercher" des objets dans le schéma. Sans cette autorisation, il est toujours possible de voir les noms des objets, par exemple en interrogeant les tables du système. De plus, après avoir révoqué cette permission, les backends existants peuvent avoir des déclarations qui ont précédemment effectué cette recherche, ce qui fait que ce n'est pas un moyen totalement sûr d'empêcher l'accès aux objets.

Pour plus de détails, voir la question, Que fait exactement GRANT USAGE ON SCHEMA ? . Accordez une attention particulière aux éléments suivants La réponse par l'expert Postgres Craig Ringer .

Objets existants et futurs

Ces commandes n'affectent que les objets existants. Les tables et autres objets que vous créerez à l'avenir bénéficient de privilèges par défaut jusqu'à ce que vous exécutiez à nouveau les lignes ci-dessus. Voir l'article autre réponse de Erwin Brandstetter pour modifier les valeurs par défaut et affecter ainsi les objets futurs.