Octroi de privilèges pour une base de données particulière dans PostgreSQL

concept de base dans Postgres

Les rôles

sont des objets globaux qui peuvent accéder à toutes les bases de données dans un cluster de bases de données - étant donné les privilèges requis.

a cluster contient de nombreuses bases de données , qui contiennent de nombreux schémas . Les schémas (même avec le même nom) dans les différentes bases de données ne sont pas liés. L'octroi de privilèges pour un schéma ne s'applique qu'à ce schéma particulier dans les DB (DB au moment de l'octroi).

chaque base de données commence par un schéma public par défaut. C'est une convention, et de nombreux paramètres de départ. Autre que cela, le schéma public est juste un schéma comme les autres.

venant de MySQL, vous pouvez vouloir commencer avec un seul schéma public , ignorant effectivement la couche de schéma complètement. J'utilise régulièrement des douzaines de schémas par base de données.

Les schémas sont un peu (mais pas complètement) comme des répertoires dans le système de fichiers.

une fois que vous utilisez plusieurs schémas, assurez-vous de comprendre search_path paramètre:

• comment search_path influence la résolution de l'identificateur et le "schéma actuel "

"1519250920 par Défaut" privilèges

par documentation sur GRANT :

PostgreSQL accorde des privilèges par défaut sur certains types d'objets pour PUBLIC . Aucun privilège n'est accordé à PUBLIC par défaut sur les tables, colonnes, schémas ou tablespaces. Pour les autres types, la valeur par défaut les privilèges accordés à PUBLIC sont les suivants: CONNECT et CREATE TEMP TABLE pour les bases de données; EXECUTE privilège pour les fonctions; et USAGE privilège pour les langues.

toutes ces valeurs par défaut peuvent être changées avec ALTER DEFAULT PRIVILEGES :

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

rôle de groupe

comme @Craig a commenté , il est préférable de GRANT privilèges à un rôle de groupe et ensuite faire un membre utilisateur spécifique de ce rôle ( GRANT le rôle du groupe du rôle de l'utilisateur). de cette façon, il est plus simple de distribuer et de révoquer des paquets de privilèges nécessaires pour certaines tâches.

un rôle de groupe est juste un autre rôle sans connexion. Ajouter une connexion d'accès à la transformer en un rôle d'utilisateur. Plus:

• pourquoi PostgreSQL fusionne-t-il les utilisateurs et les groupes en rôles?

Recette

dites, nous avons une nouvelle base de données mydb , un groupe mygrp , et un utilisateur myusr ...

Lorsqu'il est connecté à la base de données en question en tant que superutilisateur ( postgres par exemple):

REVOKE ALL ON DATABASE mydb FROM public;  -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp;  -- since we revoked from public

GRANT USAGE ON SCHEMA public TO mygrp;

pour assigner a user all privileges to all tables comme vous avez écrit (je pourrais être plus restrictif):

GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; -- don't forget those

pour définir les privilèges par défaut pour les objets futurs, exécutez pour chaque rôle qui crée des objets dans ce schéma:

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON TABLES TO mygrp;

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON SEQUENCES TO mygrp;

-- more roles?

maintenant, accordez le groupe à l'utilisateur:

GRANT mygrp TO myusr;

réponse connexe:

• PostgreSQL - DB utilisateur ne devrait être autorisé à appeler des fonctions

Alternatifs (non-standard)

venant de MySQL, et puisque vous voulez garder les privilèges sur les bases de données séparées, vous pourriez aimer ce paramètre non standard db_user_namespace . par documentation:

ce paramètre permet les noms d'utilisateurs par base de données. Il est désactivé par défaut.

lire attentivement le manuel. Je n'utilise pas ce paramètre. Il n'annule pas ce qui précède.