Utiliser votre base de données PostgreSQL

Extensions PostgreSQL

Toutes les bases de données PostgreSQL que nous mettons à votre disposition ont les extensions suivantes :

• PostGIS 3

• postgis_raster

• pg_trgm

• unaccent, fuzzystrmatch, tablefunc, hstore, uuid-ossp

D’autres extensions sont possibles en fonction de l’offre, en particulier sur les offres de type « serveur dédié ». Par exemple, nous pouvons vous installer pgrouting, ogr-fdw, pgagent… Contactez-nous pour personnaliser votre installation.

Les droits et rôles

Pour accéder à votre base postgresql, il vous a été donné lors de la souscription à votre abonnement, un nom de serveur, un nom de base de donnée, un identifiant et un mot de passe.

Le nom de la base de donnée est construit selon ce motif : lizmap_<groupinstances>_<nominstance> (exemple lizmap_mongroupe_moninstance).

Cet identifiant est dans un groupe nommé __group__@<groupinstances>.<nominstance> (exemple __group__@mongroupe.moninstance), qui a un certain nombre de droits permettant de gérer vos schemas, tables et autres objets PostgreSQL.

Cet identifiant et tout ceux qui sont dans le groupe __group__@.. vous permettent de :

• accéder à la base

• créer des tables, des schémas, des vues etc.

• lire et modifier le contenu des tables

• gérer les droits sur vos schemas, tables etc.

Ils ne vous permettent pas de :

• gérer des rôles PostgreSQL

• installer des extensions PostgreSQL

• faire tout autre opérations administratives sur le serveur

Pour obtenir d’autres identifiants, veuillez contacter 3Liz.

Un autre groupe est prédéfini : __group__restricted__@<groupinstances>.<nominstance> (exemple __group__restricted__@mongroupe.moninstance). Il accède par défaut en lecture seule au schema « public » et aux tables qui s’y trouvent (seul des SELECT sont autorisés). Vous pouvez demander des identifiants à mettre dans ce groupe.

Il existe enfin un autre groupe prédéfini, __group__internal__@<groupinstances>.<nominstance>, qui est utilisé par les instances lizmap, et qui permet de gérer les schemas dédiés à chaque instance lizmap (qui ont un nom commençant par lizmap_). Lizmap y stocke ses propres tables.

Modifier les droits

Quand vous créez un schema ou une table, par défaut il/elle vous appartient, et les autres utilisateurs n’y ont pas accès.

Il faut donc leur donner des droits avec la requête SQL GRANT. Exemple :

GRANT USAGE ON SCHEMA monschema TO "nom_du_group_ou_de_l_utilisateur";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA monschema TO "nom_du_group_ou_de_l_utilisateur";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA monschema TO "nom_du_group_ou_de_l_utilisateur";
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA monschema TO "nom_du_group_ou_de_l_utilisateur";

GRANT SELECT ON ALL TABLES IN SCHEMA monschema TO "nom_du_group_restricted_ou_de_l_utilisateur";
GRANT EXECUTE ON ALL SEQUENCES IN SCHEMA monschema TO "nom_du_group_restricted_ou_de_l_utilisateur";
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA monschema TO "nom_du_group_restricted_ou_de_l_utilisateur";

GRANT SELECT ON TABLE monschema.lizmap_search TO "nom_du_group_restricted_ou_de_l_utilisateur";

Pour en savoir plus sur la commande GRANT, voir la documentation de PostgreSQL sur GRANT.

Similairement, vous devrez utiliser la commande SQL REVOKE pour interdire l’accès à un utilisateur. Ainsi vous pouvez par exemple autoriser tous les utilisateurs d’un groupe sauf certains utilisateurs, en faisant un GRANT pour ce groupe et un REVOKE pour ces utilisateurs.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA monschema TO "nom_du_group";
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA monschema FROM "nom_de_l_utilisateur";

Pour mettre des droits automatiquement sur chaque nouvelle table d’un schéma, vous pouvez utiliser la commande ALTER DEFAULT PRIVILEGES. Exemples :

ALTER DEFAULT PRIVILEGES IN SCHEMA monschema GRANT ALL PRIVILEGES ON TABLES TO "__group__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA monschema GRANT ALL PRIVILEGES ON SEQUENCES TO "__group__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA monschema GRANT ALL PRIVILEGES ON FUNCTIONS TO "__group__@..";

ALTER DEFAULT PRIVILEGES IN SCHEMA monschema REVOKE ALL PRIVILEGES ON TABLES FROM "un_utilisateur";

ALTER DEFAULT PRIVILEGES IN SCHEMA monschema GRANT SELECT ON TABLES TO "__group__restricted__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA monschema GRANT SELECT ON SEQUENCES TO "__group__restricted__@..";
ALTER DEFAULT PRIVILEGES IN SCHEMA monschema GRANT EXECUTE ON FUNCTIONS TO "__group__restricted__@..";

Attention, la commande ALTER DEFAULT PRIVILEGES ne change pas les droits des tables existantes.

Voir la documentation de cette commande.

À propos de lizmap_search

Si vous créez une table ou une vue lizmap_search pour Lizmap dans un de vos schemas, il faudra donner les droits d’accès sur lizmap_search à ce groupe __group__internal__@...

Les limites de connexions

Chaque identifiant a droit à un nombre limité de connexion (50). Il est donc conseillé de ne pas utiliser le même identifiant pour toutes vos cartes.

Restrictions d’accès depuis l’extérieur

Par défaut, votre base est accessible depuis n’importe quel machine sur internet. Cependant, vous pouvez limiter cet accès depuis certaines adresses IP.

En général, il peut s’agir de l’IP d’un proxy de votre réseau d’entreprise. Contactez votre service informatique pour savoir quelle(s) adresse(s) IP sont possibles.

Vous pouvez ensuite nous les indiquer, et nous configurerons votre base de données pour qu’elle ne puisse être accessible que depuis les IP que vous nous donnerez.

Configurer l’accès dans QGIS

Dans QGIS, il faut créer une nouvelle connexion à PostgreSQL avec ces informations:

• laisser le champ « Service » vide

• cocher les cases « Enregistrer » à côté de l’utilisateur et du mot de passe (après avoir cliqué sur le bouton « Tester la connexion »)

• cocher la dernière case tout en bas « Utiliser la table de métadonnées estimées »

• Valider et accepter le message d’avertissement (sur l’enregistrement du mot de passe)

Il est aussi intéressant pour les performances d’accès aux données PostgreSQL de modifier une option dans les options de QGIS, onglet « Rendu »: il faut cocher la case « Réaliser la simplification par le fournisseur de données lorsque c’est possible ». Cela permet de télécharger des versions allégées des données aux petites échelles.

Pour les couches PostGIS qui auraient déjà été ajoutées avant d’avoir activé cette option, vous pouvez manuellement changer dans vos projets via l’onglet « Rendu » de la boîte de dialogue des propriétés de chaque couche PostGIS.

Restaurer votre base de données

Si vous avez fait une mauvaise manipulation, qui a détruit des données, il est possible de les récupérer.

À votre demande, nous pouvons restaurer l’intégralité d’une base de données, gratuitement, à partir d’une des sauvegardes quotidiennes effectuées durant les 6 jours précédents.

Cependant, si vous voulez restaurer seulement une partie de la base de données, par exemple pour les données d’une table spécifique, la restauration peut être compliquée, en fonction de la complexité de la structure de votre base de données. S’offre à vous deux choix :

• soit vous nous demandez un devis pour la restauration : nous étudions la structure de votre base de données et nous vous proposerons une prestation

• soit nous mettons à votre disposition une sauvegarde (un « dump » réalisé avec pg_dump) et vous pourrez l’importer dans une base temporaire d’un serveur postgresql, extraire les données qui vous intéressent, et les réinjecter dans votre base de données PostGIS.

Vue v_lizmap_users et tables de log

Lizmap stocke en base de données ses comptes utilisateur et groupes, ainsi que les logs détaillés et compteurs associés.

Dans le schema par défaut correspondant à votre instance lizmap_<nominstance> la vue v_lizmap_users vous permet de consulter les utilisateur lizmap existants, ainsi que les groupes auxquels ils sont rattachés.

Dans ce même schema, les deux tables log_counter et log_detail permettent de visualiser les logs détaillés et compteur de lizmap.

Utilisation de pgAgent

PgAgent permet de lancer des requêtes SQL à des moments prédéterminés, par exemple tous les jours à tel heure, ou tel jour de la semaine etc. En théorie il permet aussi d’exécuter des scripts batch mais cette fonction a été désactivée sur notre infrastructure, pour des raisons de sécurité.

PgAgent est un service payant qui est installé seulement sur les offres serveurs dédiés.

Une fois que vous avez souscrit à ce service, une base de donnée est crée et est dédié au service pgagent. Des droits sont donnés à votre identifiant postgresql pour y accéder.

Pour pouvoir utiliser le service vous devez installer le logiciel pgAdmin. Celui-ci dispose d’une interface pour gérer les « jobs ». Elle n’apparait que si vous vous connectez directement sur la base de donnée de PgAgent.

Dans pgAdmin, il faut donc créer une nouvelle connection à votre serveur de donnée, en indiquant la base de donnée de pgAgent dont le nom vous a été donnée lors de la souscription à votre abonnement.

Une fois connecté, vous verrez alors un item « pgAgent jobs » dans la liste des objets de la base de données.

Un clic-droit sur cet item permet de créer un nouveau « job » (tâche).

Dans la boîte qui apparait, vous indiquez un nom, puis dans l’onglet « steps » (étapes), vous ajoutez des étapes pour la tâche. Pour chaque étape, il faut indiquer :

1. un nom

2. puis en cliquant sur le petit crayon à gauche du nom, il faut indiquer - soit le nom de la base de données dans laquelle votre script SQL sera executé, et à laquelle votre identifiant permet d’accéder, - soit une chaîne de connexion pour accéder à une base externe

3. Pour le type de script, seul « SQL » est autorisé. Si vous choisissez « Batch », vous ne pourrez pas sauvegarder car nous avons réglé des droits pour l’interdire, pour des raisons de sécurité.

4. En cliquant sur l’onglet « code », vous pourrez taper le code SQL à exécuter. Cela peut être des simples requêtes comme des scripts plus complexes avec des procédures stockées etc.

Enfin, dans l’onglet « Schedules », vous indiquez les dates et/où heures à laquelle la tâche sera lancée. Veillez à lancer vos tâches à des heures qui ne risquent pas de gêner vos utilisateurs, en particulier si ce sont des traitements lourds.

Veillez également à ce que vos tâches ne prennent pas des heures à s’exécuter, les performances générales de vos applications lizmap pourraient en souffrir.

Pour savoir si les tâches se sont bien passés, il faut cliquer sur une tâche dans le panneau de droite, puis afficher l’onglet « statistics ». Si vous ne le voyez pas, cliquer sur le bouton qui a trois petits points verticaux, tout à droite de la barre d’onglet, et choisir « Open > Statistics ». Vous verrez alors un tableau qui liste tous les lancements des tâches, avec une colonne « Status », qui indique le résultat de l’exécution :

• « r » : en cours d’exécution

• « s » : succès

• « f » : échec

• « i » : échec interne

• « d » : annulé

Pour plus de détails sur l’utilisation de PgAgent, lire la documentation officielle.