README.md 17 KB
Newer Older
THION ROMUALD's avatar
THION ROMUALD committed
1
Documentation des serveurs pédagogiques de bases de données `bd-pedago`
THION ROMUALD's avatar
THION ROMUALD committed
2
=========================================================
THION ROMUALD's avatar
THION ROMUALD committed
3

THION ROMUALD's avatar
THION ROMUALD committed
4
<!-- markdownlint-disable MD004-->
THION ROMUALD's avatar
THION ROMUALD committed
5
- [Documentation des serveurs pédagogiques de bases de données `bd-pedago`](#documentation-des-serveurs-pédagogiques-de-bases-de-données-bd-pedago)
THION ROMUALD's avatar
THION ROMUALD committed
6
  - [Documentation du serveur PostgreSQL](#documentation-du-serveur-postgresql)
THION ROMUALD's avatar
THION ROMUALD committed
7
    - [Connection au serveur](#connection-au-serveur)
THION ROMUALD's avatar
THION ROMUALD committed
8
    - [Les interfaces clients PostgreSQL](#les-interfaces-clients-postgresql)
THION ROMUALD's avatar
THION ROMUALD committed
9
      - [`psql`](#psql)
THION ROMUALD's avatar
THION ROMUALD committed
10
      - [DBeaver](#dbeaver)
THION ROMUALD's avatar
THION ROMUALD committed
11
      - [CloudBeaver](#cloudbeaver)
THION ROMUALD's avatar
THION ROMUALD committed
12
13
14
15
16
    - [Gestion des schémas en PostgreSQL](#gestion-des-schémas-en-postgresql)
      - [Le `search_path`](#le-search_path)
      - [Le schéma public](#le-schéma-public)
      - [Modifier le  `search_path`](#modifier-le-search_path)
    - [Accéder à d'autres bases de données depuis la sienne](#accéder-à-dautres-bases-de-données-depuis-la-sienne)
THION ROMUALD's avatar
THION ROMUALD committed
17
  - [Documentation du serveur MongoDB](#documentation-du-serveur-mongodb)
THION ROMUALD's avatar
THION ROMUALD committed
18
19
20
21
    - [Connection au serveur](#connection-au-serveur-1)
      - [Avec le tunnel SSH](#avec-le-tunnel-ssh)
      - [Connection directe depuis Eduroam ou le VPN](#connection-directe-depuis-eduroam-ou-le-vpn)
    - [Installation locale](#installation-locale)
THION ROMUALD's avatar
THION ROMUALD committed
22
  - [Annexes](#annexes)
THION ROMUALD's avatar
THION ROMUALD committed
23
    - [PostgreSQL : exemple complet avec `search_path`](#postgresql--exemple-complet-avec-search_path)
THION ROMUALD's avatar
THION ROMUALD committed
24
    - [PostgreSQL : exemple complet FDW](#postgresql--exemple-complet-fdw)
THION ROMUALD's avatar
THION ROMUALD committed
25
26
27
28
    - [PostgreSQL : configuration du tunnelling ssh (obsolete depuis le routage public internet)](#postgresql--configuration-du-tunnelling-ssh-obsolete-depuis-le-routage-public-internet)
      - [Configuration de la redirection en ligne de commande sous Linux](#configuration-de-la-redirection-en-ligne-de-commande-sous-linux)
      - [Configuration de la redirection via putty sous windows](#configuration-de-la-redirection-via-putty-sous-windows)
      - [Configuration de la redirection directement dans DBeaver](#configuration-de-la-redirection-directement-dans-dbeaver)
THION ROMUALD's avatar
THION ROMUALD committed
29
<!-- markdownlint-enable MD004-->
THION ROMUALD's avatar
THION ROMUALD committed
30

THION ROMUALD's avatar
THION ROMUALD committed
31
Le point d'entrée aux serveurs est `bd-pedago.univ-lyon1.fr`.
THION ROMUALD's avatar
THION ROMUALD committed
32
33
34
La machine est **publiquement routée** sur internet et EDUROAM mais **seul** le port PostgreSQL/5432 est visible. **Attention**  le service n'est **pas** accessible depuis les réseaux wifi UCBL-Portail et eduscol

Si un service n'est accessible **que** depuis le campus (autres ports) :
THION ROMUALD's avatar
THION ROMUALD committed
35
36
37

- utiliser le réseau EDUROAM en wifi cf. <https://etu.univ-lyon1.fr/guide-wifi-eduroam-694268.kjsp> ou <https://cat.eduroam.org/>
- utiliser un tunnel SSH ou le VPN depuis l'exterieur, cf. <https://documentation.univ-lyon1.fr/>
THION ROMUALD's avatar
THION ROMUALD committed
38

THION ROMUALD's avatar
THION ROMUALD committed
39
40
Documentation du serveur PostgreSQL
-----------------------------------
THION ROMUALD's avatar
THION ROMUALD committed
41

THION ROMUALD's avatar
THION ROMUALD committed
42
**Documentation de référence : <https://www.postgresql.org/docs/current/index.html>**
THION ROMUALD's avatar
THION ROMUALD committed
43

THION ROMUALD's avatar
THION ROMUALD committed
44
### Connection au serveur
THION ROMUALD's avatar
THION ROMUALD committed
45

THION ROMUALD's avatar
THION ROMUALD committed
46
47
48
49
Pour se connecter `bd-pedago.univ-lyon1.fr`, avec `Login` votre identifiant Lyon 1 de la forme `pAB12345` (avec `AB` votre année de première inscription) et le mot de passe `MotDePasse` à 12 caractères donné dans <https://tomuss.univ-lyon1.fr/> dans une colonne `Password_PostgreSQL`.

### Les interfaces clients PostgreSQL

THION ROMUALD's avatar
THION ROMUALD committed
50
Nous recommandons d'utiliser la ligne de commande interactive `psql`, le client local DBeaver (à installer sur votre poste, déjà présent sous Linux sur les postes de l'université) ou le serveur CloudBeaver (interface web, pas d'installation).
THION ROMUALD's avatar
THION ROMUALD committed
51

THION ROMUALD's avatar
THION ROMUALD committed
52
#### `psql`
THION ROMUALD's avatar
THION ROMUALD committed
53

THION ROMUALD's avatar
THION ROMUALD committed
54
Utiliser l'une des deux méthodes suivantes pour vous connecter :
THION ROMUALD's avatar
THION ROMUALD committed
55

THION ROMUALD's avatar
THION ROMUALD committed
56
57
58
59
```bash
# en tapant le mot de passe à l'inve intéractive
psql -h bd-pedago.univ-lyon1.fr -U Login -d BaseDeDonnees

THION ROMUALD's avatar
THION ROMUALD committed
60
# en mettant le mot de passe dans une variable d'environnement
THION ROMUALD's avatar
THION ROMUALD committed
61
PGPASSWORD=MotDePasse psql -h bd-pedago.univ-lyon1.fr -U Login -d BaseDeDonnees
THION ROMUALD's avatar
THION ROMUALD committed
62
63
```

THION ROMUALD's avatar
THION ROMUALD committed
64
65
Si vous ne précisez pas `-d BaseDeDonnees`, vous essaierez de vous connecter par défaut dans la base qui a le même nom que le login (ici `Login`).

THION ROMUALD's avatar
THION ROMUALD committed
66
Pour _éviter de saisir à chaque fois votre mot de passe_ : créer un script `.sh` avec la commande précédente ou utiliser le fichier `.pgpass` qui permet de saisir vos information de connection, par exemple (voir <https://www.postgresql.org/docs/current/libpq-pgpass.html>)
THION ROMUALD's avatar
THION ROMUALD committed
67

THION ROMUALD's avatar
THION ROMUALD committed
68
```pgpass
THION ROMUALD's avatar
THION ROMUALD committed
69
70
bd-pedago.univ-lyon1.fr:5432:pgrthion:pgrthion:MotDePasse
bd-pedago.univ-lyon1.fr:5432:pedago:pgrthion:MotDePasse
THION ROMUALD's avatar
THION ROMUALD committed
71
72
```

THION ROMUALD's avatar
THION ROMUALD committed
73
74
75
76
Configurer **en particulier** la pagination et l'éditeur utilisé accessible via `\e` par `psql` :

- soit avec `select-editor` sous Ubuntu
- soit via la variable d'environnement `PSQL_EDITOR`
THION ROMUALD's avatar
THION ROMUALD committed
77

THION ROMUALD's avatar
THION ROMUALD committed
78
Un exemple de  configuration du fichier `$HOME/.psqlrc` est [accessible dans ce dépôt](./.psqlrc). Il est **très recommandé** de l'utiliser. Pour aller plus loin, vous pouvez [voir la configuration détaillée et expliquée de DImitri Fontaine](https://tapoueh.org/blog/2017/12/setting-up-psql-the-postgresql-cli/), un contributeur de PostgreSQL.
THION ROMUALD's avatar
THION ROMUALD committed
79
Voir [la doc officielle](https://www.postgresql.org/docs/current/app-psql.html).
THION ROMUALD's avatar
THION ROMUALD committed
80

THION ROMUALD's avatar
THION ROMUALD committed
81
82
#### DBeaver

THION ROMUALD's avatar
THION ROMUALD committed
83
84
85
DBeaver Community Edition <https://dbeaver.io/> est installé sur les postes Linux de l'établissement. La connexion se configure avec les mêmes informations, voir l'exemple ci-dessous avec le compte `rthion`. **Pensez bien à configurer le nom de la base de donnéess**, qui est le même que votre login.

![Configuration DBeaver](./img/DBeaver_connection_base.png)
THION ROMUALD's avatar
THION ROMUALD committed
86

THION ROMUALD's avatar
THION ROMUALD committed
87
#### CloudBeaver
THION ROMUALD's avatar
THION ROMUALD committed
88

THION ROMUALD's avatar
THION ROMUALD committed
89
Un équivalent allégé de DBeaver, nommé CloudBeaver, est installé en mode serveur. Il est accessible à l'adresse <https://cloudbeaver.univ-lyon1.fr/>. Une documentation pour configurer votre connection est accessible <https://documentation.univ-lyon1.fr/sgbd/cloudbeaver>.
THION ROMUALD's avatar
THION ROMUALD committed
90

THION ROMUALD's avatar
THION ROMUALD committed
91
### Gestion des schémas en PostgreSQL
THION ROMUALD's avatar
THION ROMUALD committed
92

THION ROMUALD's avatar
THION ROMUALD committed
93
**Attention** dans PostgreSQL, l'organisation est **Base de données > Schémas > Tables**.
94
95
Il est recommandé de créer un nouveau schéma pour chaque application, c'est-à-dire en pratique pour chaque nouveau sujet de TP/Projet.

THION ROMUALD's avatar
THION ROMUALD committed
96
97
Un exemple complet d'utilisation du search_path est donné [dans l'annexe](#Exemple-complet-avec-search_path)

THION ROMUALD's avatar
THION ROMUALD committed
98
#### Le `search_path`
THION ROMUALD's avatar
THION ROMUALD committed
99

100
101
Postgres va chercher les noms dans les schémas avec un mecanismes `search_path` similaire à celui des systèmes de gestion de fichiers.
Voir <https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH>
THION ROMUALD's avatar
THION ROMUALD committed
102

THION ROMUALD's avatar
THION ROMUALD committed
103
104
```sql
show search_path;
THION ROMUALD's avatar
THION ROMUALD committed
105

THION ROMUALD's avatar
THION ROMUALD committed
106
107
108
--      search_path
-- ------------------------
-- "$user", public
THION ROMUALD's avatar
THION ROMUALD committed
109
110
```

111
Par défaut, postgres va donc chercher une table d'abord dans le schéma éponyme de l'utilisateur `$user` puis dans le schéma `public`.
THION ROMUALD's avatar
THION ROMUALD committed
112

THION ROMUALD's avatar
THION ROMUALD committed
113
#### Le schéma public
THION ROMUALD's avatar
THION ROMUALD committed
114

115
116
117
Par défaut, si vous créez une table sans préciser de schéma, postgres va essayer de créer dans la table dans l'ordre du `search_path`.
Le schéma `public` étant toujours créé, si vous n'avez pas crée de schéma éponyme à votre nom d'utilisateur, c'est dans `public` que la table sera créee.

THION ROMUALD's avatar
THION ROMUALD committed
118
**Tous les utilisateurs ont les droits de création par défaut dans tous les schémas publics de tous les utilisateurs**. Ce comportement sera modifié dans le futur sur l'instance.
119

THION ROMUALD's avatar
THION ROMUALD committed
120
#### Modifier le  `search_path`
121
122
123

Vous pouvez modifier le  `search_path` avec la commande, par exemple pour y ajouter un schéma nouvellement crée. Cette modification sera valable **pour la session en cours**

THION ROMUALD's avatar
THION ROMUALD committed
124
```sql
125
126
127
SET search_path TO myschema,public;
```

THION ROMUALD's avatar
THION ROMUALD committed
128
Si vous voulez altérer  `search_path` pour **toutes les futures sessions d'une base ou d'un utilisateur** utilisez une des commandes suivantes (voir <https://stackoverflow.com/questions/2875610/permanently-set-postgresql-schema-path> ).
129

THION ROMUALD's avatar
THION ROMUALD committed
130
```sql
THION ROMUALD's avatar
THION ROMUALD committed
131
-- /!\ privilégiez cette solution /!\
THION ROMUALD's avatar
THION ROMUALD committed
132
133
134
135
-- au niveau utilisateur, pour toutes les sessions de cet utilisateur
-- quelle que soit la base
ALTER ROLE <role_name> SET search_path TO schema1,schema2;

136
137
138
-- au niveau base de données
-- tous les utilsateurs qui s'y connectent auront ce search_path
ALTER DATABASE <database> SET search_path TO schema1,schema2;
THION ROMUALD's avatar
THION ROMUALD committed
139
```
140

THION ROMUALD's avatar
THION ROMUALD committed
141
### Accéder à d'autres bases de données depuis la sienne
THION ROMUALD's avatar
THION ROMUALD committed
142

THION ROMUALD's avatar
THION ROMUALD committed
143
Tous les utilisateurs ont droit **d'usage** dans tous les schémas de la base nommée `pedago` via le rôle `pedago-reader`. Pour lire, il suffit donc de se connecter à cette base :
THION ROMUALD's avatar
THION ROMUALD committed
144

THION ROMUALD's avatar
THION ROMUALD committed
145
146
- en psql depuis une autre base, la commande est  `\c pedago`
- à la connection directement `psql -h bd-pedago.univ-lyon1.fr -U LoginPostgres -d pedago`
THION ROMUALD's avatar
THION ROMUALD committed
147

THION ROMUALD's avatar
THION ROMUALD committed
148
149
Pour croiser des données entre plusieurs bases de données, il faut utiliser l'extension *Foreign Data Wrapper* (FDW) qui permet d'accéder à des bases distances (potentiellement, sur un autre serveur).
L'extension a été activée dans chaque base utilisateur. Pour accéder à des tables se fait en trois étapes :
THION ROMUALD's avatar
THION ROMUALD committed
150

THION ROMUALD's avatar
THION ROMUALD committed
151
152
153
 1. créer le serveur
 2. créer la correspondance utilisateur
 3. importer les tables
THION ROMUALD's avatar
THION ROMUALD committed
154

THION ROMUALD's avatar
THION ROMUALD committed
155
 Voir le script en annexe
THION ROMUALD's avatar
THION ROMUALD committed
156

THION ROMUALD's avatar
THION ROMUALD committed
157
Documentation
THION ROMUALD's avatar
THION ROMUALD committed
158

THION ROMUALD's avatar
THION ROMUALD committed
159
160
161
162
163
- <https://www.postgresql.org/docs/current/postgres-fdw.html>
- <https://www.postgresql.org/docs/current/sql-createserver.html>
- <https://www.postgresql.org/docs/current/sql-createusermapping.html>
- <https://www.postgresql.org/docs/current/sql-importforeignschema.html>
- <https://www.postgresql.org/docs/current/sql-createforeigntable.html>
THION ROMUALD's avatar
THION ROMUALD committed
164

THION ROMUALD's avatar
THION ROMUALD committed
165
166
167
Documentation du serveur MongoDB
--------------------------------

THION ROMUALD's avatar
THION ROMUALD committed
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
Les bases de données accessibles sur le serveur sont les suivantes :

- `grades` : une base de notes académiques
- `zips` : les données du recensement des États-Unis par codes postaux
- `restaurants` : des restaurants à Manhattan
- `neighborhoods` : les quartiers de Manhattan et leur définition géométrique

### Connection au serveur

**Attention, le port 27017 de MongoDB n'est pas ouvert sur internet**.
Il faut donc **obligatoirement** utiliser Eduroam, le VPN, ou un tunnel SSH.

#### Avec le tunnel SSH

La commande pour créer le tunnel est la suivante
THION ROMUALD's avatar
THION ROMUALD committed
183
184

```bash
THION ROMUALD's avatar
THION ROMUALD committed
185
186
187
188
189
190
# avec loginUCBL votre identifiant p1234567

# -f met ssh en tâche de fond
# -N n'exécute pas de commande une fois connecté
# -L bind_address:port:host:hostport redirige ici bd-pedago.univ-lyon1.fr:27017 sur localhost:27017
ssh -f -N -L localhost:27017:bd-pedago.univ-lyon1.fr:27017 loginUCBL@linuxetu.univ-lyon1.fr
THION ROMUALD's avatar
THION ROMUALD committed
191
192
```

THION ROMUALD's avatar
THION ROMUALD committed
193
Vous pouvez maintenant vous connecter sur la base  `base` avec l'utilisateur `login` et son mot de passe `password` définis dans la base `auth-db` (la base d'authentification n'est pas nécessairement celle ou on se connecte), la commande est la suivante :
THION ROMUALD's avatar
THION ROMUALD committed
194

THION ROMUALD's avatar
THION ROMUALD committed
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
```bash
# notez le localhost à cause du tunnel SSH
mongo -u "login" -p "password"  --authenticationDatabase "auth-db" "mongodb://localhost:27017/base"
```

Les informations `base`, `login`, `password` et `auth-db` sont données en TP.

#### Connection directe depuis Eduroam ou le VPN

Si vous n'avez pas besoin de SSH, la commande est la suivante

```bash
mongo -u "login" -p "password"  --authenticationDatabase "auth-db" "mongodb://bd-pedago.univ-lyon1.fr:27017/base"
```

### Installation locale
THION ROMUALD's avatar
THION ROMUALD committed
211

THION ROMUALD's avatar
THION ROMUALD committed
212
213
214
Si vous souhaitez reproduire l'environnement fourni, vous pouvez suivre les guides d'installation officiels <https://docs.mongodb.com/manual/installation/>  et installer MongoDB sur votre propre machine.
Il n'y a quasiement aucun `tuning` ou spécificité sur l'instance que nous exploitons, vous pourrez donc facilement reproduire les TPs sur votre propre serveur.
Vous aurez simplement besoin des jeux de données, voici comment les télécharger :
THION ROMUALD's avatar
THION ROMUALD committed
215
216
217
218
219
220
221
222

```bash
wget https://raw.githubusercontent.com/ozlerhakan/mongodb-json-files/master/datasets/grades.json
wget http://media.mongodb.org/zips.json
wget https://raw.githubusercontent.com/mongodb/docs-assets/geospatial/restaurants.json
wget https://raw.githubusercontent.com/mongodb/docs-assets/geospatial/neighborhoods.json
```

THION ROMUALD's avatar
THION ROMUALD committed
223
Ensuite, il suffit de les importer dans votre serveur, ici dans une base `mif04` sans authentification (ce qui est le cas d'une installation par défaut : pas de mot de passe mais accès local uniquement) :
THION ROMUALD's avatar
THION ROMUALD committed
224

THION ROMUALD's avatar
THION ROMUALD committed
225
226
227
228
229
230
231
```bash
mongoimport --host localhost --db mif04 --collection grades --drop --file grades.json
mongoimport --host localhost --db mif04 --collection zips --drop --file zips.json
mongoimport --host localhost --db mif04 --collection restaurants --drop --file restaurants.json
mongoimport --host localhost --db mif04 --collection neighborhoods --drop --file neighborhoods.json
```

THION ROMUALD's avatar
THION ROMUALD committed
232
233
234
Annexes
-------

THION ROMUALD's avatar
THION ROMUALD committed
235
### PostgreSQL : exemple complet avec `search_path`
236

THION ROMUALD's avatar
THION ROMUALD committed
237
On se connecte ici avec `psql -U pgrthion -h bd-pedago.univ-lyon1.fr` :
238

THION ROMUALD's avatar
THION ROMUALD committed
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
```sql
show search_path ;
--   search_path
-- -----------------
-- "$user", public
-- (1 row)

\dn
--   List of schemas
--   Name  |  Owner
-- --------+----------
--  public | postgres

\dt
-- Did not find any relations.

CREATE TABLE test(id int);
-- CREATE TABLE
\dt
--         List of relations
--  Schema | Name | Type  |  Owner
-- --------+------+-------+----------
--  public | test | table | pgrthion
-- (1 row)
263
264
265

-- création dans public, car pas de schéma pgrthion de crée

THION ROMUALD's avatar
THION ROMUALD committed
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
CREATE SCHEMA pgrthion;
-- CREATE SCHEMA

\dt
--         List of relations
--  Schema | Name | Type  |  Owner
-- --------+------+-------+----------
--  public | test | table | pgrthion
-- (1 row)

CREATE TABLE test2(id int);
-- CREATE TABLE

\dt
--           List of relations
--   Schema  | Name  | Type  |  Owner
-- ----------+-------+-------+----------
--  pgrthion | test2 | table | pgrthion
--  public   | test  | table | pgrthion
-- (2 rows)

CREATE SCHEMA test;
-- CREATE SCHEMA

\dn
--    List of schemas
--    Name   |  Owner
-- ----------+----------
--  pgrthion | pgrthion
--  public   | postgres
--  test     | pgrthion
-- (3 rows)
298
299
300

-- /!\ attention test est crée mais n'est pas dans le path !

THION ROMUALD's avatar
THION ROMUALD committed
301
302
303
304
305
306
307
308
309
310
CREATE TABLE test.test3(id int);
-- CREATE TABLE

\dt
--           List of relations
--   Schema  | Name  | Type  |  Owner
-- ----------+-------+-------+----------
--  pgrthion | test2 | table | pgrthion
--  public   | test  | table | pgrthion
-- (2 rows)
311
312
313

-- on ne voit pas test3 !

THION ROMUALD's avatar
THION ROMUALD committed
314
315
ALTER ROLE pgrthion SET search_path TO "$user",test;
-- ALTER ROLE
316

THION ROMUALD's avatar
THION ROMUALD committed
317
318
319
320
321
322
323
\dt
--           List of relations
--   Schema  | Name  | Type  |  Owner
-- ----------+-------+-------+----------
--  pgrthion | test2 | table | pgrthion
--  public   | test  | table | pgrthion
-- (2 rows)
324
325
326
327
328

-- /!\ SERA VISIBLE à la prochaine connexion
\q
```

THION ROMUALD's avatar
THION ROMUALD committed
329
On se connecte à nouveau avec `psql -U pgrthion -h bd-pedago.univ-lyon1.fr`
330

THION ROMUALD's avatar
THION ROMUALD committed
331
332
333
334
335
336
337
338
```sql
\dt
--           List of relations
--   Schema  | Name  | Type  |  Owner
-- ----------+-------+-------+----------
--  pgrthion | test2 | table | pgrthion
--  test     | test3 | table | pgrthion
-- (2 rows)
339
340
341
342

-- /!\ on voit la table test3, mais plus test qui est dans public car public n'est plus dans le path
```

THION ROMUALD's avatar
THION ROMUALD committed
343
### PostgreSQL : exemple complet FDW
THION ROMUALD's avatar
THION ROMUALD committed
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365

Le script ci-dessous vous donne toutes les commandes pour importer des tables d'une autre base de données

```sql
-- on crée le serveur "distant", ici vers la base 'pedago'
-- on lève ici les droits d'écritures (que les utilisateurs n'ont pas de toute façon)
CREATE SERVER IF NOT EXISTS ro_local_bd_pedago
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'bd-pedago.univ-lyon1.fr', port '5432', dbname 'pedago',  updatable 'false');

-- pour annuler
-- DROP SERVER IF EXISTS ro_local_bd_pedago;

--  on dit que l'utilisateur local LoginPostgres accède au distant via le même compte LoginPostgres : il faut préciser le mot de passe
CREATE USER MAPPING IF NOT EXISTS FOR LoginPostgres SERVER ro_local_bd_pedago OPTIONS (user 'LoginPostgres', password 'MotDePasse');

-- pour annuler
-- DROP USER MAPPING IF EXISTS FOR LoginPostgres SERVER ro_local_bd_pedago;

-- enfin pour ajouter, ici DEUX TABLES DU SCHEMA mif04_xml
IMPORT FOREIGN SCHEMA mif04_xml
  LIMIT TO (arbres_foret_2011, documentation_2011)
THION ROMUALD's avatar
THION ROMUALD committed
366
  FROM SERVER ro_local_bd_pedago INTO public;
THION ROMUALD's avatar
THION ROMUALD committed
367

THION ROMUALD's avatar
THION ROMUALD committed
368
-- on teste
THION ROMUALD's avatar
THION ROMUALD committed
369
370
371
372
373
374
SELECT COUNT(*)
FROM arbres_foret_2011;

-- PAS LA PEINE DE RECRER ro_local_bd_pedago et son mapping par la suite si on veut ajouter d'autres tables
```

THION ROMUALD's avatar
THION ROMUALD committed
375
376
377
Pour lister les tables importées:

```sql
THION ROMUALD's avatar
THION ROMUALD committed
378
\det
THION ROMUALD's avatar
THION ROMUALD committed
379
```
THION ROMUALD's avatar
THION ROMUALD committed
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403

### PostgreSQL : configuration du tunnelling ssh (obsolete depuis le routage public internet)

#### Configuration de la redirection en ligne de commande sous Linux

Pour créer un tunnel qui redirige (option `-L`) le port 5432 de `bd-pedago.univ-lyon1.fr` sur le port 5432 de votre `localhost` en tâche de fond (option `-f`) et qui n'exécute aucune commmande (option `-N`), la commande est la suivante :

```bash
ssh -f -N -L localhost:5432:bd-pedago.univ-lyon1.fr:5432 LoginUCBL@linuxetu.univ-lyon1.fr
# le prompt va vous demander votre pass UCBL, pas votre pass PotsgreSQL
```

Ensuite, vous pouvez vous connecter au port local avec la commande suivante et saisir votre mot de passe PostgreSQL

```bash
psql -p 5432 -U LoginPostgres -h localhost
```

**Si vous avez déjà PostgreSQL d'installé en local, alors changez de port local**.

#### Configuration de la redirection via putty sous windows

En utilisant <https://wwww.putty.org>, il faut configurer la redirection dans les options SSH/Tunnels, voir les captures ci-dessous. Pensez à sauvegarder votre configuration.

THION ROMUALD's avatar
THION ROMUALD committed
404
![Configuration tunnel Putty 1/2](./img/Putty_host.png)
THION ROMUALD's avatar
THION ROMUALD committed
405

THION ROMUALD's avatar
THION ROMUALD committed
406
![Configuration tunnel Putty 2/2](./img/Putty_tunnel.png)
THION ROMUALD's avatar
THION ROMUALD committed
407
408
409
410
411

#### Configuration de la redirection directement dans DBeaver

Le client recommandé <https://dbeaver.io/> permet de configurer directement un tunnel SSH sans passer par Putty, dans l'onglet SSH lors de la création de la connection.

THION ROMUALD's avatar
THION ROMUALD committed
412
![Configuration tunnel DBeaver 1/2](./img/DBeaver_connection.png)
THION ROMUALD's avatar
THION ROMUALD committed
413

THION ROMUALD's avatar
THION ROMUALD committed
414
![Configuration tunnel DBeaver 2/2](./img/DBeaver_tunnel.png)