Accorder tout sur un schéma spécifique dans le db à un rôle de groupe dans PostgreSQL

en utilisant PostgreSQL 9.0, j'ai un rôle de groupe appelé "staff" et je voudrais accorder tous (ou certains) privilèges à ce rôle sur les tables d'un schéma particulier. Aucun des travaux suivants 

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

les membres du" staff "sont toujours incapables de sélectionner ou mettre à jour sur les tables individuelles du schéma" foo "ou (dans le cas de la seconde commande) à n'importe quelle table dans la base de données à moins que j'accorde tous sur cette table spécifique.

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

mise à jour: trouvé à l'aide de une question similaire sur serverfault.com . 

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO staff;
62
demandé sur Community 2012-04-27 18:39:03

2 réponses

vous avez trouvé le raccourci pour définir des privilèges pour toutes les tables existantes dans le schéma donné. le manuel précise :

(mais notez que ALL TABLES est considéré inclure vues et tables étrangères ).

le Gras c'est moi qui souligne. Les colonnes serial sont mises en œuvre avec nextval() sur une séquence comme colonne par défaut et citant le manuel :

pour les séquences, ce privilège permet l'utilisation des fonctions currval et nextval .

donc s'il y a des colonnes serial , vous voudrez aussi accorder USAGE (ou ALL PRIVILEGES ) sur séquences 

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Remarque: colonnes d'identité dans Postgres 10 ou version ultérieure utilisation implicite des séquences qui ne nécessitent pas de privilèges supplémentaires. (Envisager la mise à niveau des colonnes serial .)

Qu'en est-il nouveaux 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 ...;

définit les privilèges pour les objets créé dans le futur automatiquement - mais pas pour les objets préexistants.

les privilèges par défaut sont seulement appliqués aux objets créés par l'utilisateur ciblé ( FOR ROLE my_creating_role ). Si cette clause est omise, elle est par défaut à l'utilisateur courant exécutant 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 ...;

noter également que toutes les versions de pgAdmin III ont un bug subtil et afficher privilèges par défaut dans le SQL volet, même si elles ne s'appliquent pas au courant de rôle. Assurez-vous d'ajuster manuellement la clause FOR ROLE lors de la copie du script SQL.

80
répondu Erwin Brandstetter 2017-09-25 23:25:07

ma réponse est similaire à celle-ci ServerFault.com .

Conservateur

si vous voulez être plus conservateur que d'accorder" tous les privilèges", vous pourriez essayer quelque chose de plus semblable. 

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 se réfère 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, pour toute action, l'utilisateur doit se voir accorder des droits" d'usage". Avant qu'un utilisateur puisse sélectionner, Insérer, mettre à jour, ou supprimer, un utilisateur doit d'abord être accordé "usage" à un schéma.

vous ne remarquerez pas cette exigence lors de la première utilisation de Postgres. Par défaut, chaque base de données a un premier schéma nommé public . Et chaque utilisateur par défaut a été automatiquement accordé des droits "d'usage" à ce schéma. Lorsque vous ajoutez un schéma supplémentaire, vous devez explicitement accorder des droits d'utilisation. 

GRANT USAGE ON SCHEMA some_schema_ TO some_user_ ;

extrait du Postgres doc :

pour les schémas, permet l'accès aux objets contenus dans le schéma spécifié (en supposant que les exigences de privilèges propres aux objets sont également respectées). Essentiellement, cela permet au titulaire de "chercher" des objets dans le schéma. Sans cette autorisation, il est encore possible de voir les noms des objets, par exemple en interrogeant les tables système. Aussi, après avoir révoqué cette permission, Les sauvegardes existantes pourraient avoir des déclarations qui ont déjà effectué cette recherche, donc ce n'est pas une façon complètement sécurisée d'empêcher l'accès à l'objet.

pour plus de détails, voir la Question, quelle utilisation de la subvention sur SCHEMA fait exactement? . Porter une attention particulière à la réponse par L'expert de Postgres Craig Sonnerie .

Objets Existants Et Futurs

ces commandes n'affectent que les objets existants. Les Tables et telles que vous créez dans le futur obtiennent des privilèges par défaut jusqu'à ce que vous ré-exécutiez ces lignes ci-dessus. Voir la autre réponse D'Erwin Brandstetter pour changer les valeurs par défaut affectant ainsi les objets futurs.

31
répondu Basil Bourque 2017-05-23 12:18:18