Comment voulez-vous créer un utilisateur en lecture seule dans PostgreSQL

Question

J'aimerais créer un utilisateur de PostgreSQL qui ne peut ne Sélectionne à partir d'une base de données particulière. Dans MySQL, la commande serait...

GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';

Quel est l'équivalent de la commande ou d'une série de commandes 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 DB sont CRÉER, CONNECTER, TEMPORAIRE, et TEMP.

Answer 1

L'utilisation de la subvention/sélectionner une seule table

Si vous n'accordent de se CONNECTER à une base de données, l'utilisateur peut se connecter mais n'a pas d'autres privilèges. Vous devez accorder de l'UTILISATION des espaces de noms (schémas) et SÉLECTIONNEZ-la sur des tables et des vues individuellement comme suit:

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 dans le schéma à l'aide d'une commande unique, plutôt que d'avoir à les taper un par un:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;

Cela affecte uniquement les tableaux qui ont déjà été créés. Plus puissant, vous pouvez automatiquement par défaut rôles attribués à de nouveaux objets à l'avenir:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO xxx;

Notez que par défaut, cela ne concerne que les objets (tables), créé par l'utilisateur qui a émis cette commande: mais il peut aussi être fixé sur n'importe quel rôle que la délivrance de l'utilisateur est un membre de. Cependant, vous n'avez pas ramasser les droits par défaut pour tous les rôles que vous êtes un membre d'lors de la création de nouveaux objets... donc il y a encore certaines tourner en rond. Si vous adoptez l'approche d'une base de données dispose d'un propriétaire de rôle, et le schéma des modifications sont effectuées, que la propriété de rôle, vous devez attribuer des droits par défaut pour que la propriété de rôle. À mon humble avis c'est un peu une source de confusion et vous pouvez avoir besoin d'expérimenter pour trouver une fonctionnelle de flux de travail.

Plusieurs tables/vues (les versions de PostgreSQL avant 9.0)

Pour éviter les erreurs de long, multi-change de table, il est recommandé d'utiliser la suite de "automatique" pour générer de l'requis GRANT SELECT 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 sortie du financement des commandes GRANT SELECT sur toutes les tables, vues et séquences en public, par copier-n-coller de l'amour. Naturellement, ce ne sera appliquée que dans les tableaux qui ont déjà été créés.

Answer 2

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

Answer 3

Ici est le meilleur moyen que j'ai trouvé pour ajouter de la lecture, seuls les utilisateurs (utilisation de postgresql 9.0 ou plus récent):

$ sudo -upostgres psql postgres
postgres=# CREATE ROLE readonly;
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
postgres=# BEGIN;
postgres=# CREATE ROLE "<PUT_READONLY_USERNAME_HERE>" WITH LOGIN ENCRYPTED PASSWORD '<USE_A_NICE_STRONG_PASSWORD_PLEASE' IN ROLE readonly;
postgres=# COMMIT;

puis connectez-vous à toutes les machines (master + lire-esclave(s)/hot-standby(s), etc..) et de l'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

Answer 4

J'ai créé une pratique de script pour qu'; pg_grant_read_to_db.sh. Ce script accorde des droits en lecture seule à un rôle déterminé sur toutes les tables, vues et séquences dans un schéma de base de données et définit par défaut.

Answer 5

Le pas de moyen simple de le faire serait l'octroi de sélectionner sur chaque table de la base de données:

postgres=# grant select on db_name.table_name to read_only_user;

Vous pouvez automatiser que par la génération de votre subvention consolidés à partir de la base de données de métadonnées.