Comment créez-vous un utilisateur en lecture seule dans PostgreSQL?
Je voudrais créer un utilisateur dans PostgreSQL qui ne peut faire que des sélections à partir d'une base de données particulière. Dans MySQL, la commande serait:
GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';
Quelle est la commande ou la série de commandes équivalente dans PostgreSQL?
J'ai essayé...
postgres=# CREATE ROLE xxx LOGIN PASSWORD 'yyy';
postgres=# GRANT SELECT ON DATABASE mydb TO xxx;
Mais il semble que les seules choses que vous pouvez accorder sur une base de données sont CREATE, CONNECT, TEMPORARY et TEMP.
9 réponses
Accorder l'utilisation / sélectionner à une seule table
Si vous n'accordez que la connexion à une base de données, l'utilisateur peut se connecter mais n'a pas d'autres privilèges. Vous devez accorder L'utilisation sur les espaces de noms (schémas) et sélectionner sur les tables et les vues individuellement comme ceci:
GRANT CONNECT ON DATABASE mydb TO xxx;
-- This assumes you're actually connected to mydb..
GRANT USAGE ON SCHEMA public TO xxx;
GRANT SELECT ON mytable TO xxx;
Plusieurs tables / vues (PostgreSQL 9.0+)
Dans les dernières versions de PostgreSQL, vous pouvez accorder des autorisations sur toutes les tables / vues / etc du schéma en utilisant une seule commande plutôt que de devoir les taper une par un:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;
Cela n'affecte que les tables qui ont déjà été créées. Plus puissamment, vous pouvez automatiquement affecter des rôles par défaut aux nouveaux objets à l'avenir:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO xxx;
Notez que par défaut, cela n'affectera que les objets (tables) créés par l'utilisateur qui a émis cette commande: bien qu'elle puisse également être définie sur n'importe quel rôle dont l'utilisateur émetteur est membre. Cependant, vous ne récupérez pas les privilèges par défaut pour tous les rôles dont vous êtes membre lors de la création de nouveaux objets... si il y a encore quelques faffing autour. Si vous adoptez l'approche selon laquelle une base de données a un rôle propriétaire et que les modifications de schéma sont effectuées en tant que rôle propriétaire, vous devez attribuer des privilèges par défaut à ce rôle propriétaire. IMHO tout cela est un peu déroutant et vous devrez peut-être expérimenter pour trouver un flux de travail fonctionnel.
Plusieurs tables / vues (versions PostgreSQL avant 9.0)
Pour éviter les erreurs dans les modifications longues et multi-tables, il est recommandé d'utiliser le "automatique" suivant processus pour générer le GRANT SELECT requis pour chaque table / vue:
SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');
Cela devrait afficher les commandes GRANT pertinentes pour accorder SELECT sur toutes les tables, vues et séquences en public, pour copier-n-coller l'amour. Naturellement, cela ne sera appliqué qu'aux tables qui ont déjà été créées.
Notez que PostgreSQL 9.0 (aujourd'hui en version bêta) aura un moyen simple de le faire:
test=> GRANT SELECT ON ALL TABLES IN SCHEMA public TO joeuser;
GRANT
Voici la meilleure façon que j'ai trouvée pour ajouter des utilisateurs en lecture seule (en utilisant PostgreSQL 9.0 ou plus récent):
$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE';
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
, Puis connectez-vous à toutes les machines (master + lire-esclave(s)/hot-standby(s), etc..) et exécuter:
$ echo "hostssl <PUT_DBNAME_HERE> <PUT_READONLY_USERNAME_HERE> 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/9.2/main/pg_hba.conf
$ sudo service postgresql reload
Référence prise à partir de ce blog:
Script pour créer un utilisateur en lecture seule:
CREATE ROLE Read_Only_User WITH LOGIN PASSWORD 'Test1234'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';
Attribuez l'autorisation à cet utilisateur en lecture seule:
GRANT CONNECT ON DATABASE YourDatabaseName TO Read_Only_User;
GRANT USAGE ON SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO Read_Only_User;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;
Par défaut, les nouveaux utilisateurs auront l'autorisation de créer des tableaux. Si vous envisagez de créer un utilisateur en lecture seule, ce n'est probablement pas ce que vous voulez.
Pour créer un véritable utilisateur en lecture seule avec PostgreSQL 9.0+, exécutez les étapes suivantes:
# This will prevent default users from creating tables
REVOKE CREATE ON SCHEMA public FROM public;
# If you want to grant a write user permission to create tables
# note that superusers will always be able to create tables anyway
GRANT CREATE ON SCHEMA public to writeuser;
# Now create the read-only user
CREATE ROLE readonlyuser WITH LOGIN ENCRYPTED PASSWORD 'strongpassword';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
Si votre Utilisateur en lecture seule n'a pas l'autorisation de lister les tables (c'est-à-dire que \d ne renvoie aucun résultat), c'est probablement parce que vous n'avez pas les autorisations USAGE pour le schéma. USAGE est une autorisation qui permet aux utilisateurs d'utiliser effectivement les autorisations ils ont été affectés. Quel est le point de tout cela? Je ne suis pas sûr. Pour corriger:
# You can either grant USAGE to everyone
GRANT USAGE ON SCHEMA public TO public;
# Or grant it just to your read only user
GRANT USAGE ON SCHEMA public TO readonlyuser;
J'ai créé un script pratique pour cela; pg_grant_read_to_db.sh . ce script accorde des privilèges en lecture seule à un rôle spécifié sur toutes les tables, vues et séquences d'un schéma de base de données et les définit par défaut.
Si votre base de données est dans le schéma public, c'est facile (cela suppose que vous avez déjà créé le readonlyuser)
db=> GRANT SELECT ON ALL TABLES IN SCHEMA public to readonlyuser;
GRANT
db=> GRANT CONNECT ON DATABASE mydatabase to readonlyuser;
GRANT
db=> GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to readonlyuser;
GRANT
Si votre base de données utilise customschema, exécutez ce qui précède mais ajoutez une commande supplémentaire:
db=> ALTER USER readonlyuser SET search_path=customschema, public;
ALTER ROLE
La façon non simple de le faire serait d'accorder select sur chaque table de la base de données:
postgres=# grant select on db_name.table_name to read_only_user;
Vous pouvez automatiser cela en générant vos instructions grant à partir des métadonnées de la base de données.
Extrait d'un lien posté en réponse au lien despesz'.
Postgres 9.x semble avoir la capacité de faire ce qui est demandé. Voir le paragraphe Grant On Database Objects de:
Http://www.postgresql.org/docs/current/interactive/sql-grant.html
Où il est dit: "il y a aussi une option pour accorder des privilèges sur tous les objets du même type dans un ou plusieurs schémas. Cette fonctionnalité est actuellement prise en charge uniquement pour les tables, fonctions (mais notez que toutes les TABLES sont considérées comme incluant des vues et des tables étrangères)."
Cette page traite également de L'utilisation des rôles et D'un privilège appelé "tous les privilèges".
Vous trouverez également des informations sur la façon dont les fonctionnalités de GRANT se comparent aux normes SQL.