Différences

Ci-dessous, les différences entre deux révisions de la page.

--- serveurs:installation:db-srv:postgresql-ssh-tunnel [2021/07/30 10:03] – [Configuration de l'accès avec DBeaver] jpmilcent
+++ serveurs:installation:db-srv:postgresql-ssh-tunnel [2026/09/16 16:28] (Version actuelle) – [Création d'un utilisateur "dbreader" sans "home"] jpmilcent
@@ Ligne 8: / Ligne 8: @@
 ===== Création d'un utilisateur "dbreader" sans "home" =====
 L'utilisateur système permettant de créer le tunnel SSH sera nommé "//dbreader//". Il n'aura pas de dossier //home// et aucun shell actif. Pour cela suivre les étapes suivantes :
-  * Création de l'utilisateur //dbreader// sur l'instance "//db-srv//" : ''<nowiki> useradd --no-create-home -s /usr/sbin/nologin dbreader </nowiki>''
+  * Création de l'utilisateur //dbreader// sur l'instance "//db-srv//" : ''%% useradd --no-create-home -s /usr/sbin/nologin dbreader %%''
-    * Créer un utilisateur sans possibilité de se loguer à un shell '' -s /usr/sbin/nologin '' n'empêche pas la connexion à la base Postgresql
+    * Créer un utilisateur sans possibilité de se loguer à un shell ''%% -s /usr/sbin/nologin %%'' n'empêche pas la connexion à la base Postgresql
-  * Ajouter un mot de passe à l'utilisateur : '' passwd dbreader ''
+  * Ajouter un mot de passe à l'utilisateur : ''%% passwd dbreader %%''
-  * Modifier le fichier de config du serveur SSH pour permettre un accès par mot de passe uniquement pour cet utilisateur : '' vi /etc/ssh/sshd_config ''
+  * Modifier le fichier de config du serveur SSH pour permettre un accès par mot de passe uniquement pour cet utilisateur : ''%% vi /etc/ssh/sshd_config %%''
     * Ajouter à la fin du fichier les lignes suivantes (**il est important que ces lignes soient bien complètement à la fin du fichier**) : <code properties>
 Match User dbreader
@@ Ligne 20: / Ligne 20: @@
     AuthorizedKeysFile  /etc/ssh/authorized_keys_%u
 </code>
-      * Vous pouvez copier/collet le fichier //authorized_keys// de l'utilisateur //admin// comme base de départ (et le modifier au besoin) : '' cp /home/admin/.ssh/authorized_keys /etc/ssh/authorized_keys_dbreader ''
+      * Vous pouvez copier/collet le fichier //authorized_keys// de l'utilisateur //admin// comme base de départ (et le modifier au besoin) : ''%% cp /home/admin/.ssh/authorized_keys /etc/ssh/authorized_keys_dbreader %%''
-      * Il est nécessaire de définir les droits sur le fichier ///etc/ssh/authorized_keys_dbreader// ainsi : '' chmod 600 /etc/ssh/authorized_keys_dbreader ; chown dbreader: /etc/ssh/authorized_keys_dbreader ''
+      * Il est nécessaire de définir les droits sur le fichier ///etc/ssh/authorized_keys_dbreader// ainsi : ''%% chmod 600 /etc/ssh/authorized_keys_dbreader ; chown dbreader: /etc/ssh/authorized_keys_dbreader %%''
     * Notez qu'il est aussi possible de combiner les deux possibilités (par mot de passe et clé SSH) ainsi : <code properties>
 Match User dbreader
@@ Ligne 28: / Ligne 28: @@
     Banner none
 </code>
-      * Pour éviter l'affichage de la bannière de connexion au serveur : '' Banner none ''
+      * Pour éviter l'affichage de la bannière de connexion au serveur : ''%% Banner none %%''
-    * Redémarrer le serveur //Sshd// : '' systemctl restart sshd ''
+    * Redémarrer le serveur //Sshd// : ''%% systemctl restart sshd %%''
-    * Tester la connexion au serveur : '' ssh dbreader@db-<region>-sinp ''
+    * Tester la connexion au serveur : ''%% ssh dbreader@db-<region>-sinp %%''
       * Cela devrait afficher : <code>
 Could not chdir to home directory /home/dbreader: No such file or directory
@@ Ligne 36: / Ligne 36: @@
 Connection to db-paca-sinp closed.
 </code>
+  * Pour supprimer l'affichage de LinuxLogo lors de la connexion, éditer le fichier : ''vi /etc/pam.d/sshd''
+    * Descendez jusqu'à trouver les lignes contenant ''%% pam_motd.so %%''. Il y en a généralement deux.
+    * Ajoutez la ligne : ''%%session [success=2 default=ignore] pam_succeed_if.so user = dbreader %%'' juste au-dessus.
+    * Le bloc devrait ressembler exactement à ça :<code>
+session [success=2 default=ignore] pam_succeed_if.so user = dbreader
+session    optional     pam_motd.so  motd=/run/motd.dynamic
+session    optional     pam_motd.so  noupdate
+</code>
+    * Si vous n'avez qu'une seule ligne ''%% pam_motd.so %%'' dans votre fichier, remplacez ''%% success=2 %%'' par ''%% success=1 %%''
+    * Après l'enregistrement de la modification, la bannière LinuxLogo disparaîtra pour cet utilisateur.
 ===== Création d'un utilisateur en lecture seule pour Postgresql =====
 ==== Création de l'utilisateur et définition des droits ====
   * Se connecter à la base avec un compte superadmin : '' psql -h "localhost" -U "admin" -d "geonature2db" ''
-  * Exécuter les requêtes suivantes :<code sql>
+  * Pour attribuer les droits de lecture seule à un utilisateur, si les droits ont été révoqué sur le schéma ''public'' des bases de données, il suffit depuis Postgresql 14 d’attribuer l'utilisateur lecteur au rôle par défaut ''pg_read_all_data'' et d'autoriser sa connexion à la base. Exécuter les requêtes suivantes :<code sql>
 -- Créer l'utilisateur "gnreader"
 CREATE USER gnreader WITH ENCRYPTED PASSWORD '<mot-de-passe>' ;
@@ Ligne 49: / Ligne 60: @@
 GRANT CONNECT ON DATABASE gnatlas TO gnreader ;
+-- Associer l'utilisateur gnreader au rôle par défaut pg_read_all_data
+GRANT pg_read_all_data TO gnreader;
+</code>
+    * Pour révoquer les droits d'accès en lecture seule à une base de données : <code sql>REVOKE CONNECT ON DATABASE <nom-base> FROM <nom-utilisateur-lecteur>;</code>
+=== 🗑️ Base "geonature2db" (avant Postgresql 14) ===
+  * Se connecter à la base "//geonature2db//" avec un compte superadmin : '' psql -h "localhost" -U "admin" -d "geonature2db" ''
+  * Exécuter les requêtes suivantes :<code sql>
 -- Autoriser l'utilisation de tous les schémas de la base :
 -- 1. Générer la requête à exécuter
@@ Ligne 75: / Ligne 94: @@
 -- Ajouter l'accès en lecture sur les futures tables :
 -- Réutiliser la requête précédente et remplacer la première et la dernière ligne :
-ALTER DEFAULT PRIVILEGES IN SCHEMA
+ALTER DEFAULT PRIVILEGES FOR USER gnreader IN SCHEMA
 pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, public, information_schema, gn_commons, gn_exports, gn_imports, gn_meta, gn_monitoring, gn_permissions, gn_sensitivity, gn_synthese, ref_geo, ref_habitats, ref_nomenclatures, taxonomie, utilisateurs
     GRANT SELECT ON TABLES TO gnreader ;
+-- Ajouter l'accès en lecture sur les futures sequences :
+-- Réutiliser la requête précédente et remplacer la dernière ligne :
+ALTER DEFAULT PRIVILEGES FOR USER gnreader IN SCHEMA
+pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, public, information_schema, gn_commons, gn_exports, gn_imports, gn_meta, gn_monitoring, gn_permissions, gn_sensitivity, gn_synthese, ref_geo, ref_habitats, ref_nomenclatures, taxonomie, utilisateurs
+    GRANT SELECT ON SEQUENCES TO gnreader ;
 </code>
+=== 🗑️ Base "gnatlas" (avant Postgresql 14) ===
+  * Se connecter à la base avec un compte superadmin : '' psql -h "localhost" -U "admin" -d "gnatlas" ''
+  * Exécuter les requêtes suivantes :<code sql>
+-- Autoriser l'utilisation de tous les schémas de la base :
+-- 1. Générer la requête à exécuter
+SELECT 'GRANT USAGE ON SCHEMA ' || string_agg(nspname, ', ') || ' TO gnreader ;' FROM pg_namespace ;
+-- 2. Exécuter la requête obtenue précédemment
+GRANT USAGE ON SCHEMA
+pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, public, information_schema, taxonomie, synthese, ref_geo, atlas, pg_temp_22, pg_toast_temp_22, pg_temp_24, pg_toast_temp_24, pg_temp_45, pg_toast_temp_45, pg_temp_37, pg_toast_temp_37
+    TO gnreader ;
+-- Autoriser l'utilisateur à faire des sélection sur toutes les tables de tous les schémas (même principe que ci-dessus)
+-- 1. Générer la requête à exécuter
+SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || string_agg(nspname, ', ') || ' TO gnreader ;' FROM pg_namespace ;
+-- 2. Exécuter la requête obtenue précédemment
+GRANT SELECT ON ALL TABLES IN SCHEMA
+pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, public, information_schema, taxonomie, synthese, ref_geo, atlas, pg_temp_22, pg_toast_temp_22, pg_temp_24, pg_toast_temp_24, pg_temp_45, pg_toast_temp_45, pg_temp_37, pg_toast_temp_37
+    TO gnreader ;
+-- Autoriser l'utilisateur à faire des sélection sur toutes les sequences (id)
+-- Réutiliser la requête précédente et remplacer "TABLES" par "SEQUENCES" :
+GRANT SELECT ON ALL SEQUENCES IN SCHEMA
+pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, public, information_schema, taxonomie, synthese, ref_geo, atlas, pg_temp_22, pg_toast_temp_22, pg_temp_24, pg_toast_temp_24, pg_temp_45, pg_toast_temp_45, pg_temp_37, pg_toast_temp_37
+    TO gnreader ;
+-- Ajouter l'accès en lecture sur les futures tables :
+-- Réutiliser la requête précédente et remplacer la première et la dernière ligne :
+ALTER DEFAULT PRIVILEGES FOR USER gnreader IN SCHEMA
+pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, public, information_schema, taxonomie, synthese, ref_geo, atlas, pg_temp_22, pg_toast_temp_22, pg_temp_24, pg_toast_temp_24, pg_temp_45, pg_toast_temp_45, pg_temp_37, pg_toast_temp_37
+    GRANT SELECT ON TABLES TO gnreader ;
+-- Ajouter l'accès en lecture sur les futures sequences :
+-- Réutiliser la requête précédente et remplacer la dernière ligne :
+ALTER DEFAULT PRIVILEGES FOR USER gnreader IN SCHEMA
+pg_toast, pg_temp_1, pg_toast_temp_1, pg_catalog, public, information_schema, taxonomie, synthese, ref_geo, atlas, pg_temp_22, pg_toast_temp_22, pg_temp_24, pg_toast_temp_24, pg_temp_45, pg_toast_temp_45, pg_temp_37, pg_toast_temp_37
+    GRANT SELECT ON SEQUENCES TO gnreader ;
+-- Ajouter l'accès aux "foreign data tables" :
+GRANT USAGE ON FOREIGN SERVER geonaturedbserver TO gnreader ;
+GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO gnreader ;
+CREATE USER MAPPING FOR gnreader SERVER geonaturedbserver OPTIONS (user 'gnreader', password '<mot-de-passe-de-gnreader>');
+</code>
+=== Ajout d'un espace permetant la création de table/VM pour gnreader ===
+//gnreader// est un utilisateur en lecture seule sur les schémas de GeoNature mais nous lui créons un schéma où il aura un accès en écriture.
+Cela permettra la création de table ou VM pour des requêtes d'extraction sur les tables de GeoNature.
+<code sql>
+-- Création du schéma "playground"
+CREATE SCHEMA playground AUTHORIZATION geonatadmin;
+-- Ajout des droits d'écriture sur schéma à l'utilisateur gnreader
+GRANT USAGE, CREATE ON SCHEMA playground TO gnreader;
+</code>
 ==== Modification des autorisations d'accès au serveur Postgresql ====
@@ Ligne 85: / Ligne 171: @@
     * Ajouter le contenu suivant : <code properties>
 # GeoNature : access by gnreader (read only)
-host    geonature2db    gnreader        10.0.1.20/32            md5
+host    geonature2db    gnreader        10.0.1.20/32            scram-sha-256
-host    gnatlas         gnreader        10.0.1.20/32            md5
+host    gnatlas         gnreader        10.0.1.20/32            scram-sha-256
 </code>
   * Recharger la configuration //Postgresql// : '' systemctl reload postgresql ''
 ==== Configuration de l'accès avec DBeaver ====
-  * Tester la connexion en lecture seule depuis Dbeaver en créant une nouvelle connexion avec ces paramètres :
+Vous pouvez configurer votre accès avec [[|DBeaver]] comme indiqué sur [[serveurs:installation:dbeaver|la page spécifique à cet outil]].
-    * Onglet Général :
-      * Host : localhost (tester aussi 10.0.1.20 en cas de problème)
-      * Port : 5432
-      * Database : geonature2db
-      * Authentification : Database Native
-      * Nom d'utilisateur : gnreader
-      * Mot de passe : <gnreader-password>
-      * Cocher "Save password locally"
-      * Driver name : PostgreSQL
-    * Onglet Postgresql :
-      * Cocher "Show all databases"
-      * Laisser les autres champs avec les valeurs par défaut.
-    * Onglet SSH :
-      * Cocher "Utiliser le tunnel SSH"
-      * Hôte/IP : <ip-db-srv>
-      * Port : <port-ssh-db-srv>
-      * Nom d'utilisateur : dbreader
-      * Pour une connexion via le mot de passe :
-        * Méthode d'authentification : "Mot de passe"
-        * Mot de passe : <dbeader-unix-password>
-        * Cocher "Enregistrer le mot de passe"
-      * Pour une connexion via une clé SSH :
-        * Méthode d'authentification : "Clef publique"
-        * Clé privée : indiquer le chemin vers votre clé SSH privée (Ex. : ''/home/${USER}/.ssh/id_rsa'')
-        * Phrase passe : indiquer votre "passe phrase" associé à votre clé SSH ou laisser vide si vous n'en avez pas associé.
-        * Cocher "Enregistrer le mot de passe"
-  * Cliquer en bas à gauche sur "Test de la connexion..."