Table des matières Conceptualisation.......................................................................................................................................................................2 Outils graphique....................................................................................................................................................................2 Jmerise....................................................................................................................................................................................2 Opensphere........................................................................................................................................................................3 Le sql.....................................................................................................................................................................................................4 Structure sql...................................................................................................................................................................................4 Typages des données......................................................................................................................................................4 Données numérique.....................................................................................................................................................4 Type alphanumérique................................................................................................................................................4 Nombre...................................................................................................................................................................................4 SQL dispose de commande standard..........................................................................................................4 Memo Requêtes SQL....................................................................................................................................................4 Jointure...................................................................................................................................................................................5 Scripts SQL.................................................................................................................................................................................6 Postgresql................................................................................................................................................................................................8 Installation...................................................................................................................................................................................8 Configuration............................................................................................................................................................................9 Connexion....................................................................................................................................................................................9 Description..........................................................................................................................................................................10 Mot de passe postgres UNIX......................................................................................................................................11 Mot de passe postgres postgres...........................................................................................................................11 Connexion host avec psql.............................................................................................................................................11 PSQL.......................................................................................................................................................................................................11 Créer un cluster de base de données...................................................................................................................15 Nota benne...............................................................................................................................................................................15 Creer une base....................................................................................................................................................................15 Importation...............................................................................................................................................................................15 Créer un rôle..........................................................................................................................................................................16 Droit du rôle.............................................................................................................................................................................16 Sur tpsql................................................................................................................................................................................16 Ajout d'un groupe..........................................................................................................................................................17 Groupe et heritage......................................................................................................................................................17 Privilèges du groupe..................................................................................................................................................17 Altérer un profil....................................................................................................................................................................17 Menu aide generale....................................................................................................................................................18 Général........................................................................................................................................................................................18 Tampon de requête...........................................................................................................................................................18 Connection à la base :.....................................................................................................................................................19 Connexion en réseau.......................................................................................................................................................19 Connexion à distance psql.........................................................................................................................................22 Mise à jour...............................................................................................................................................................................23 Restauration.....................................................................................................................................................................23 Résultat.......................................................................................................................................................................................24 Méthode d'authentification........................................................................................................................................25 SSL...................................................................................................................................................................................................25 Outils graphique.......................................................................................................................................................................27 Bureautique.............................................................................................................................................................................27 Ucanaccess.......................................................................................................................................................................27 Requêtes sql...........................................................................................................................................................................27 LibreOffice........................................................................................................................................................................29 Attention....................................................................................................................................................................................30
Principe et fonction L’informatique étant le traitement automatique d’information, en toute logique nous allons nourrir nos systèmes et services que nous administrons avec de la donnée. L’information c’est de la donnée. Il y a différent/es motivation, besoin/s, objectif/s sur la/les donnée/s. Différentes manière de la « manager », manipuler. La première a bas niveau, suivant les choix fait, cela apporte souplesse ou rapidité, et/ou sécurité. physiquement : block, fs, lvm, raid
La suivante au niveau soft (système) de l'organiser, la structurer (textuellement, facilement manipulable et intelligible humainement).
On ne collecte pas, ni n’organisons pas de la donnée, et ne requêtons pas sans raison. Cela découle d’une demande, ayant officié régulièrement en tant que Barman. Je pense que je prendrais la gestion des produits d'un bar, et de sa carte comme exemple.
Il y a donc une partie analyse et conceptualisation, pour une application orientée métier, sur mesure. Nous allons simplement analyser l’état d’origine, la demande, le besoin, le processus, et le résultat. De manière totalement détachée par rapport au problème posé, il faut recenser et décrire. Il existe la méthode merise, ainsi que des outils graphique afin de « fixer » les idées. Organiser et structurer, pour l’exporter et véritablement exploiter la base en la nourrissant et la requêtant. Cette base sera requêté également par une application
Conceptualisation Le MCD , ou Model Conceptuel de données. Faire un constat : état de début besoin recenser processus Résultat Dans l'exemple de comment faire un café sur youtube, nous voyions le mécanisme pour un process. Pour un bar c'est pareil, chaque production correspond à une recette ou un produit.
Outils graphique Jmerise
Jmerise permet d'appliquer la méthode Merise du début à la fin. Conceptualiser, créer les dictionnaires, les clé, et les relations. D'exporter en SQL. Je ne suis pas persuadé d'avoir bien saisi à quel moment je dois passer de la conceptualisation À la création de la base, l'exportation. Pour l'instant le projet Jarvis est un peu en stand bye.
Opensphere. Open-sphere permet de faire la même chose que Jmerise. Ici un TP sur un video club,
En fonction du besoin, du domaine et champs d’application elle peut être de très grande dimension, fournis et complexe.. Différents types de schémas existent, comme l’arborescence de notre système, ou les noms de domaine.
Dans le cas présent nous allons nous orienter sur un système de gestion organisé autour de tableau
avec un en-tete et constitué de qui sont des enregistrements ; et ces enregistrements sont eux même constituées d' . . Ces sont les dites données
Nos données qui alimentent les tableaux ont des caractéristiques intrinsèques comme : int, char, varchar : Un type de « nombre » à une définition et un comportement précis. Comme les caractères en ont d'autres. Chose déjà vu avec python, ce qui nous renvoie à nos variables, mais en plus fournis…
Jointure Jointures de 2 tables SELECT * FROM table INNER JOIN table2 ON table.id = table2.id
Retourner l'ID du dernier INSERT SELECT LAST_INSERT_ID() as new
Retourner le nombre de lignes SELECT COUNT(*) FROM table
Insérer un enregistrement INSERT INTO table VALUES ('valeur 1', 'valeur 2', …) Modifier un enregistrement UPDATE table SET nom_colonne_1 = 'nouvelle valeur' WHERE condition
Scripts SQL SQL permet de charger des scripts crée à l'avance, construit sur mesure. Comme c'est le cas avec le fichier d'install fourni, et les commandes s’enchaînent, j'ai mis un extrait plus bas, il est d’ailleurs commenté, ce qui est appréciable. \c postgres DROP DATABASE centralivres CREATE DATABASE centralivres encoding='UTF-8 ' \c centralivres Tout de suite , on remarque que l'on DROP la base, cela évite les conflits avec des résidus d'installation, on part sur une base propre. Je fais cela aussi pour les Dockers. Et cela se pratique pour d'autres installations.
Voici le fichier d’installation fourni pour le TP, 2 autres seront fournis afin de peupler notre base. -- Ce fichier contient le script destiné à créer la base de données le schéma "adherents", et les utilisateurs. -- Ce script doit être exécuté en tant qu'utilisateur "postgres".
Connection
|
\echo [INFO] Début du script \c postgres
| | |
\echo [INFO] Suppression de la base de données DROP DATABASE centralivres; Destruction de l'ancienne base de données si existante avec DROP
\echo [INFO] Création de la base de données CREATE DATABASE centralivres ENCODING 'UTF8'; Création de la base de donnée : centralivres avec CREATE DATABASE \echo [INFO] Connexion à la nouvelle base de données \c centralivres Une fois connecté à la base \echo [INFO] Création du schéma adherents CREATE SCHEMA adherents; \echo [INFO] Création des droits de lecture sur le schéma adherents GRANT USAGE ON SCHEMA adherents TO PUBLIC;
\echo [INFO] Création de la table adherents.editions CREATE TABLE adherents.editions( code_editions integer CONSTRAINT editions_CP PRIMARY KEY CONSTRAINT editions_code_editions_CHECK CHECK(code_editions > 0 AND code_editions < 10000000), nom varchar(40) UNIQUE NOT NULL, adresse varchar(50), code_postal integer CONSTRAINT editions_code_postal_CHECK CHECK(code_postal BETWEEN 1000 AND 99000), ville varchar(25), nombre_employes smallint CONSTRAINT editions_nombre_employes_CHECK CHECK(nombre_employes > 0), email_contact varchar(30), date_creation date CONSTRAINT editions_date_CHECK CHECK(date_part('year', date_creation) > 1500) ); \echo [INFO] Création des droits de lecture sur la table editions du schéma adherents GRANT SELECT ON TABLE adherents.editions TO PUBLIC; -- Pour que l'insertion des dates se fasse sans problème, elles doivent avoir le format jour-moisannée: SET DATESTYLE to DMY; INSERT INTO adherents.editions (code_editions,nom,adresse,code_postal,ville,nombre_employes,email_contact,date_creation) VALUES (7460,'ENI Editions','Rue Franklin',44800,'SaintHerblain',150,'[email protected]','01-01-1981');
Postgresql Postegres est le server qui va faire « tourner » la base de données. C'est un SGBDD Système de Gestion de Base de Données sans lien avec une entreprise. Il existe bien sur d'autres base de données, comme MySQL qui est installé par défaut dans ma Débian. Je dois vérifier dans Ubuntu. Mais également Oracle. RedHat, qui fourni ses services aux places Boursières de Londres, New York, ou Tokyo, ainsi que le CERN.
Récupération de la clé C’est une clé permettant d'identifier le serveur de manière fiable. Elle est récupérable facilement et doit correspondre avec la clé privée du serveur lors de l'échange. sudo wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
Ajout des dépots : La liste des dépots se trouve ici, il est simple de vérifier si notre système y est. Et il y en a beaucoup.
http://apt.postrgresql.org/pub/repos/apt/ Sous debian jessie modifier le fichier source.list #vi /etc/apt/source.list deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main 9.4 deb-src http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main 9.4
Installation # apt-get install postgresql-9.4 postgresql-contrib-9.4 postgresql-plperl-9.4 Removing obsolete dictionary files: Paramétrage de libxslt1.1:amd64 (1.1.28-2+b2) ... Paramétrage de libperl5.20 (5.20.2-3+deb8u3) ... Paramétrage de postgresql-9.4 (9.4.5-2.pgdg80+1) ... update-alternatives: mise à jour de l'alternative /usr/share/postgresql/9.4/man/man1/postmaster.1.gz car le groupe de liens postmaster.1.gz contient des liens esclaves modifiés Paramétrage de postgresql-contrib-9.4 (9.4.5-2.pgdg80+1) ... Paramétrage de postgresql-plperl-9.4 (9.4.5-2.pgdg80+1) ... Traitement des actions différées (« triggers ») pour libc-bin (2.19-18+deb8u2) …
Il faut également ajouter l'adminpack, il me sera réclamé pour [email protected]
Configuration Les fichiers se situent dans 2 répertoires différents. /etc/postgresql/9.4/main/ root@tpsql:/home/username# ls /etc/postgresql/9.4/main/ environment pg_hba.conf postgresql.conf pg_ctl.conf pg_ident.conf start.conf
/var/lib/postgresql/9.4/main/ root@tpsql:/home/username# ls /var/lib/postgresql/9.4/main/ base pg_commit_ts pg_multixact pg_serial pg_stat_tmp pg_twophase postgresql.auto.conf global pg_dynshmem pg_notify pg_snapshots pg_subtrans PG_VERSION postmaster.opts pg_clog pg_logical pg_replslot pg_stat pg_tblspc pg_xlog postmaster.pid
Connexion C'est le fichier pg_hba.conf qui gère les autorisations de connexion. Sa syntaxe se présente de cette manière. # PostgreSQL Client Authentication Configuration File # =================================================== # # Refer to the "Client Authentication" section in the PostgreSQL # documentation for a complete description of this file. A short # synopsis follows. # # This file controls: which hosts are allowed to connect, how clients # are authenticated, which PostgreSQL user names they can use, which # databases they can access. Records take one of these forms: local database user auth-method [auth-options] host database user address auth-method [auth-options] hostssl database user address auth-method [auth-options] hostnossl database user address auth-method [auth-options] host database user IP-address IP-mask auth-method [auth-options] hostssl database user IP-address IP-mask auth-method [auth-options] hostnossl database user IP-address IP-mask auth-method [auth-options]
Par defaut seul postgres peut s'y connecter en local, nous connectant dans un premier temps en ssh, la Postgrés nous voit comme un utilisateur local. Voilà pourquoi nous ne rencontrons pas de problèmes de connexion, de cette manière.
Description Cette colonne indique si nous sommes en local (local), à distance (host) avec ou sans ssl.
Database pour le nom de la database. L'utilisateur de la base. En local il y a la méthode. Si il y a connection alors indiquer l'adresse IP, voir le netmask. Il y a les méthode d'authentification, ainsi que les options. Md5 ; Password, ou SSL. Voici un exemple de configuration pour les hotes distant.
#Connexion distante en IPv4 #TYPE DATABASE USER host all userdbname host all userdbname host all postgres
Par contre autoriser postgres a se connecter depuis une IP sans mot de passe, je ne suis pas sur que se soit une bonne idée, un administrateur ne doit pas avoir de problème de gestion des profils et mot de passe. Je ne mettrais pas cette ligne. host tsgeri centralivres 172.25.5.2/32 trust
Utilisateur postgres. Nous devons prendre les droits de l’utilisateur postgres en utilisant su pour substitute user :su. su – postgres postgres@gally:~$
Je constate que l'utilisateur Postgres n'a pas de MdP, donc attention, si un admin, ne protège pas sa session, un tiers peu accéder à la base. D'un autre côté sans mot de passe on ne peu pas se loger avec son profil.
Mot de passe postgres UNIX root@local-machine : ~# passwd postgres Saisissez le nouveau mot de passe unix : Saisissez-le à nouveau :
Mot de passe postgres postgres Postgres à un mot de passe en tant qu'utisateur UNIX, mais pas en tant que postgres le root de la base. De la même manière je préfère protéger ce compte.Ignorant les possibilités de connection pour le moment, je suis prudent. su - postgres Mot de passe : postgres@gally:~$ psql psql (9.4.5) Saisissez « help » pour l'aide. postgres=# \password postgres Saisissez le nouveau mot de passe : Saisissez-le à nouveau : postgres=#
Connexion host avec psql Postgres peut se connecter sur serveur distant, comme ceci. On invoque psql l'utilitaire. L'option -h (host) 172.25.3.70 L'option -p (port) 5432 L'option -U (utilisateur) postgrés L'option -d (database) centralivres postgres@gally:~$ psql -h 172.25.3.70 -p 5432 -U postgres -d centralivres Mot de passe pour l'utilisateur postgres : psql (9.4.5, serveur 9.4.6) Connexion SSL (protocole : TLSv1.2, chiffrement : ECDHE-RSA-AES256-GCM-SHA384, bits : 256, compression : désactivé) Saisissez « help » pour l'aide. centralivres=#
PSQL Psql c'est « LE » terminal interactif de postgreSQL.
h nomhôte, --host nomhôte : • indique le nom d’hôte ou l’adresse IP du serveur. Cette option permet donc de se connecter à un serveur distant, c’est-à-dire différent de l’hôte sur lequel est exécuté psql. p port, --port port : • indique le port TCP sur lequel ouvrir la connexion. Ce port est donc le port utilisé par le serveur. Le port par défaut est 5432, mais une autre valeur peut être utilisée, selon la configuration du serveur. U nomutilisateur, --username • nomutilisateur : indique le nom d’utilisateur utilisé pour ouvrir la connexion. W, --password : • indique à psql de demander, de façon interactive, un mot de passe à l’utilisateur. Il n’est pas possible, avec psql, de donner un mot de passe directement dans la liste des options : un mot de passe est soit saisi par l’utilisateur de façon interactive, soit lu depuis un fichier spécifique. d : • indique la base de données à laquelle se connecter. l, --list : • liste les bases de données existantes dans le serveur. e : • affiche toutes les requêtes SQL envoyées au serveur. f nomfichier, --file nomfichier : • interprète les instructions du fichier nomfichier puis quitte le programme. c : • exécute une requête SQL. L : • écrit le résultat des requêtes dans un fichier, en plus de la sortie standard. o : • redirige le résultat des requêtes dans un fichier, comme la commande interne \o. s : • exécute les requêtes une par une, en demandant à l’utilisateur de confirmer l’exécution de chaque requête, lors de l’utilisation en mode non interactif. A : • désactive l’alignement des données en sortie. Équivaut à la commande interne \a. t : • désactive la production des en-têtes dans le résultat des données. Équivaut à la commande \t. X, --no-psqlrc : • ne lit pas le fichier .psqlrc.
Il existe des templates de base de données. Et pour se connecter il faut utiliser psql l'interface de commande postgresql. psql templates1 permet de se connecter à la base template1, qui fourni les premières commandes. postgres@tpsql:~$ psql template1 psql (9.4.5) Saisissez « help » pour l'aide. template1=# help Vous utilisez psql, l'interface en ligne de commande de PostgreSQL. Saisissez: \copyright pour les termes de distribution \h pour l'aide-mémoire des commandes SQL \? pour l'aide-mémoire des commandes psql \g ou point-virgule en fin d'instruction pour exécuter la requête \q pour quitter template1=#
D'ailleurs il en existe 2 et ont un rôle important, par sécurité l'un sert de modèle à l'autre. En cas de fausse manipulation, pensant que vous allez DROP votre DATABASE, vous dropez l'ensemble. [email protected]
14/44
Ludovic MARCHAL
Formation : TSGERI
Session 2015/2016
De ce fait ce n'est plus possible de créer une DATABASE, puisque vous avez balancé l'ossature. Il s'agit d'une erreur qui peut arriver. Il existe donc un modèle au modèle.Je le met en Extra. Pour quitter psql \q
Créer un cluster de base de données Nota benne Il peut être nécessaire de créer un cluster de base de données. Avec certaines distributions (CentOS, Debian, Windows), un cluster est créé par défaut à l’installation des paquets. Si vous êtes dans un autre cas de figure, il vous faudra donc en créer un. Pour cela, utilisez la commande : initdb
Creer une base Ici création d’une base en local sur ma machine avec un alias UNIX username@gally:~$ su – postgresql postgres@gally:~$ createdb jarvis
C'est une commande SQL qui est en fait lancée : SQL « CREATE DATABASE » Création d'une base test2 sur un serveur distant 'tpsql'. postgres=# CREATE DATABASE test2 ENCODING 'UTF8'; CREATE DATABASE postgres=# \c test2 psql (9.5.1, serveur 9.4.6) Vous êtes maintenant connecté à la base de données « test2 » en tant qu'utilisateur « postgres ». test2=#
Importation Il est possible d'importer une base de données avec un script d'installation. Pour cela on l'envoie avec SSH scp /home/user/downloads/InstallationBD_02.sql [email protected]:/home/myuser/InstallationBD_02.sql [email protected]'s password: InstallationBD_02.sql 100% 6885
6.7KB/s
00:00
Puis on importe. [email protected]:~$ su - postgres [email protected]:~$ psql -U postgres -d postgres postgres=> \i /home/postgres/InstallationBD_02.sql
Créer un rôle Avec postgresql les utilisateurs et les groupes sont gérés sous formes de rôles. Lors de la création l'argument LOGIN définit que le rôle peut se connecter, le login définit la session root@gally:/home/mon_user# su - postgres postgres@gally:~$ psql psql (9.4.5) Saisissez « help » pour l'aide. postgres=# CREATE ROLE username LOGIN password 'motdepasse'; CREATE ROLE
Droit du rôle Propriétaire postgres=# ALTER DATABASE nomdb OWNER TO username; ALTER DATABASE
Créateur postgres=# ALTER ROLE username CREATEDB; ALTER ROLE
Relancer postgres Puis se connecter \O/ [username@gally]-[~]-[ 0:49]--> >~ psql -U rolename dbname psql (9.4.5) Saisissez « help » pour l'aide. jarvis=>
Sur tpsql Je liste les ROLES avec \du postgres=# \du Liste des rôles Nom du rôle | Attributs | Membre de -------------+--------------------------------------------------------------------------+----------postgres | Superutilisateur, Créer un rôle, Créer une base, Réplication, Bypass RLS | {} tsgeri | 5 connexions | {} mon_user | 5 connexions
Je trouve un utilisateur tsgeri, je lui fais la chasse, tout le monde utilise le même login, je vais donc m'en occuper. Je veux éviter que l'on puisse se connecter à mon insu dans la base. J'ai eu la surprise avec MySQL donc je reste prudent.
Ajout d'un groupe Je crée le rôle « admingroup », et y ajoute mon utilisateur. postgres=# CREATE ROLE admingroup ROLE mon_user; CREATE ROLE postgres=# \du Liste des rôles Nom du rôle | Attributs | Membre de -------------+--------------------------------------------------------------------------+-------------admingroup | Ne peut pas se connecter | {} postgres | Superutilisateur, Créer un rôle, Créer une base, Réplication, Bypass RLS | {} tsgeri | 5 connexions | {} mon_user | 5 connexions | {admingroup} postgres=#
Groupe et heritage Création d'un compte pour un administrateur, avec héritage des droits postgres=# CREATE ROLE stef LOGIN PASSWORD 'XXXX' INHERIT IN ROLE admingroup;
Privilèges du groupe postgres=# CREATE ROLE sandrine LOGIN PASSWORD 'XXXX' IN ROLE admingroup;
Altérer un profil J'interdis à tsgeri de se connecter postgres=# ALTER ROLE tsgeri NOLOGIN; ALTER ROLE postgres=#
Résultat Pour l'instant je cherche à connaître les droits du groupe admingroup. postgres=# \du Liste des rôles Nom du rôle | Attributs | Membre de -------------+--------------------------------------------------------------------------+-------------admingroup | Ne peut pas se connecter | {} postgres | Superutilisateur, Créer un rôle, Créer une base, Réplication, Bypass RLS | {} sandrine | | {admingroup} stef | | {admingroup} tsgeri | Ne peut pas se connecter +| {} | 5 connexions | mon_user | Superutilisateur +| {admingroup} | 5 connexions |
Menu aide generale Général \copyright \g [FICHIER] ou ; \gset [PRÉFIXE] \h [NOM]
affiche les conditions d'utilisation et de distribution de PostgreSQL envoie le tampon de requêtes au serveur (et les résultats au fichier ou |tube) exécute la requête et stocke les résultats dans des variables psql aide-mémoire pour les commandes SQL, * pour toutes les commandes quitte psql exécute la requête toutes les SEC secondes
\q \watch [SEC] Tampon de requête \e [FICHIER] [LIGNE] édite le tampon de requête ou le fichier avec un éditeur externe \ef [FONCTION [LIGNE]] édite la définition de fonction avec un éditeur externe \p affiche le contenu du tampon de requête \r efface le tampon de requêtes \s [FICHIER] affiche l'historique ou le sauvegarde dans un fichier \w [FICHIER] écrit le contenu du tampon de requêtes dans un Fichier Entrée/Sortie \copy ... exécute SQL COPY avec le flux de données dirigé vers l'hôte client \echo [TEXTE] écrit un texte sur la sortie standard \i FICHIER exécute les commandes du fichier \ir FICHIER identique à \i, mais relatif à l'emplacement du script ou un |tubeCel
Connection à la base : Ici nous nous connectons avec host -h 127.0.0.1 user -U myser database -d centralivres root@tpsql:/home/myuser# psql -h 127.0.0.1 -U myuser -d centralivres Mot de passe pour l'utilisateur myuser : psql (9.4.5) Connexion SSL (protocole : TLSv1.2, chiffrement : ECDHE-RSA-AES256-GCM-SHA384, bits : 256, compression : désactivé) Saisissez « help » pour l'aide. centralivres=>
Du coup help pour revoir le menu et \h pour les COMMANDES SQL : Aide-mémoire disponible : ABORT ALTER AGGREGATE ALTER COLLATION ALTER CONVERSION
CLUSTER COMMENT COMMIT COMMIT PREPARED
DECLARE DELETE DISCARD DO INSERT
EXPLAIN FETCH GRANT
Listons les bases de données La commande \l nous permet de lister les bases. centralivres=> \l Liste des bases de données Nom|Propriétaire|Encodage|Collationnement|Type caract.|Droits d'accès ----+------+---+---------------------------+----------------------centralivres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =CTc/postgres + | | | | | postgres=CTc/postgres postgres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | template0 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 |
Mon utilisateur est parfaitement enregistré dans postgresql, et opérationnel.
Connexion en réseau Les tableaux qui apparaissent en pointillé, c’est un petit module perl qui est téléchargé à l’installation, c’est une de ses fonctions. Mais utiliser un outil graphique pour se connecter c’est mieux. Le port d’écoute par défaut est le 5432, toutefois les connexions réseaux ne sont pas autorisées par défaut.
À l’installation, PostgreSQL est configuré pour n’accepter que les [email protected]
20/44
Ludovic MARCHAL
Formation : TSGERI
Session 2015/2016
connexions locales (c’est le paramètre listen_addresses). Si vous souhaitez pouvoir vous connecter à partir du réseau, il faut définir le paramètre listen_addresses avec « ALTER SYSTEM », et préciser sur quelle(s) adresse(s) postgres accepte les connexions. postgres=# ALTER SYSTEM SET listen_addresses='192.168.8.102, localhost'; ALTER SYSTEM
Après redémarrage de postgres postgres=# SHOW listen_addresses ; listen_addresses -------------------------192.168.8.102, localhost (1 ligne) postgres=#
Il faut ensuite modifier le fichier d’authentification : # vi /etc/postgresql/9.4/main/pg_hba.conf
Le fichier est assez volumineux. La première ligne : compte d’administration autorisé localement. # Database administrative login by Unix domain socket local all postgres
peer
La suivante autorise la connexion en local pour tout les utilisateurs sur toutes les bases.
# "local" is for Unix domain socket connections only local all all
peer
J'ai ajouté ceci ; afin d’autoriser la connexion à toutes les bases avec l’utilisateur indiqué et depuis les 2 adresses. #Connexion distante en IPv4 #TYPE DATABASE USER host all user_dbname host all user_dbname
Et voilà Dbeaver connecter à la base centralivres avec l’utilisateur que j’ai indiqué.
centralivres=> \d Liste des relations Schéma | Nom | Type | Propriétaire --------+-------------------------------+----------+-------------public | auteurs | table | xtbushido public | collections | table | xtbushido public | distinctions | table | xtbushido public | ecrit_par | table | xtbushido public | editions | table | xtbushido public | entrepose | table | xtbushido public | entrepots | table | xtbushido public | imprime | table | xtbushido public | imprimeurs | table | xtbushido public | imprimeurs_code_imprimeur_seq | séquence | xtbushido public | livres | table | xtbushido public | recompense_par | table | xtbushido (12 lignes)
Connexion à distance psql Il est tout à fait possible de se connecter à distance, sans pour autant utiliser SSH, directement avec psql : psql -h -U -d \O/ [mon_user@ma_machine]-[~]-[ 9:19]--> >~ psql -h 172.25.3.70 -U user_dbname -d database Mot de passe pour l'utilisateur mon_dbname : psql (9.4.5) Connexion SSL (protocole : TLSv1.2, chiffrement : ECDHE-RSA-AES256-GCM-SHA384, bits : 256, compression : désactivé) Saisissez « help » pour l'aide. centralivres=>
Mise à jour Sauvegarde et migration. Lors de l'installation de phppgadmin, le serveur a opéré une mise à jour de postgresql du à un une erreur de frappe. C'est l'occasion de faire une sauvegarde.On change simplement d'utilisateur et on liste les tables. user_local@tpsql:~$ su - postgres Mot de passe : postgres@tpsql:~$ psql -l Liste des bases de données Nom | Propriétaire | Encodage | Collationnement | Type caract. | Droits d'accès --------------+--------------+----------+-----------------+--------------+----------------------centralivres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =CTc/postgres + | | | | | postgres=CTc/postgres postgres | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | template0 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test2 | xtbushido | UTF8 | fr_FR.UTF-8 | fr_FR.UTF-8 | (5 lignes)
Et on ne va pas faire les choses à moitié je vais ici dumper l’ensemble des bases avec pg_dumpall postgres@tpsql:~$ pg_dumpall > pg_global.sql
Le dump sera enregistrée dans le répertoire par défault : /var/lib/postgres, à côté de 9.4 et 9.5. De plus par défault, nous sommes connecté au serveur postgresql version 9.4.
Restauration Puisque nous souhaitons restaurer nos bases dans la version du serveur postgresql 9.5 ; il faut se connecter sur un autre socket UNIX, le port 5433, postgressql change simplement de port. postgres@tpsql:~$ psql -p 5433 psql (9.5.1) Saisissez « help » pour l'aide. postgres=# \i pg_global.sql
Aprés une restauration, il est préconisé d’analyser les bases. postgres@tpsql:~$ vacuumdb -a -z vacuumdb : exécution de VACUUM sur vacuumdb : exécution de VACUUM sur vacuumdb : exécution de VACUUM sur vacuumdb : exécution de VACUUM sur
SSL Après observations des requêtes avec wireshark, j’ai constaté que seul le mot de passe est chiffré.(md5). C'est une méthode qui chiffre le mot de passe Vérifier la présence de openSSL dans votre système : whereis : openssl
Vérifier les types de chiffrements : openssl ciphers -v 'HIGH:MEDIUM:+3DES:!aNULL'
Il y a là plus de 50 méthodes de chiffrement, c'est l’occasion de leur faire prendre l'air. Nous allons donc aborder un aspect de l'informatique, les protocole de chiffrement Ce qui représente une large surface d'attaque, aussi le chiffrement est un point clé.
Outils graphique Bureautique Il existe un outil propriétaire éditer par M$ qui se nomme access, celui-ci permet une saisi aisé des données. Utilisant libre-office : les fichiers .accdb peuvent être manipulé par celuici à l'aide d'un « pilote ».
Ucanaccess Il existe un projet : « ucanaccess » qui bien évidement ne peut pas correspondre à l'usage de Bernadette la secrétaire. Ucanaccess est un outil console similaire à psql. ./console.sh /home/xtbushido/downloads/UCanAccess-3.0.3.1-binPlease, enter the full path to the access file (.mdb or accdb): /home/xtbushido/tsgeri/SGBDD/tp/centralivres.accdb Loaded Tables: collections, editions, livres Loaded Queries: Loaded Indexes: Primary Key on collections Columns: (code_collection) , Primary Key on editions Columns: (code_edition) , Primary Key on livres Columns: (id) , Index on collections Columns: (code_editions) , Index on editions Columns: (code_postal) , Index on livres Columns: (code_collection) UCanAccess> Copyright (c) 2012 Marco Amadei UCanAccess version 3.0.3.1 You are connected!! Type quit to exit Commands end with ; use: export ; for exporting into a .csv file the result set from the last executed query
Après avoir tenté en vain d’utiliser base de libre_office je me suis orienté vers dbeaver. Et le plus merveilleux, dbeaver manipule parfaitement les db access.
Illustration 1: chemin de la base
Illustration 2: Télécharger les drivers de base
Dbeaver vous accompagnera pour la récupération des driver manquant. Un test est possible afin de s'assurer que notre configuration est bonne.
Illustration 4: Test
Illustration 3: Chemin du driver
On constate que je suis bien connecté à la base access.
On constate également que je suis connecté à la base postgres : centralivres, hébergé sur le serveur ; ainsi que sur jarvis crée en local, et sur centralivres.accdb.
LibreOffice Il y a un petit peu de travail avec libreOffice mais rien de bien compliqué avec les bonnes informations. C'est parti !! Ouvrons simplement libreOffice writer et dans sélectionner .
Puis toujours le panneau options > avancé > paramètres.
Indiquer le chemin du dossier contenant les archives.
Lancer ensuite BASE de la suite LibreOffice, un assistant de création de base vous accueil. L’objectif étant d'accéder à une base ACCESS ; on se connecte à une base existante, en choisissant JDBC.
Concernant cette base inutile d’indiquer un login/MdP, faire simplement suivant. Cocher « créer des tables à l’aide de l’assistant Table », n’est pas obligatoire.
Et nous voilà dans la base centralivre qui sera renommer en centralivres.odb.
Pgadmin3 Pgadmin3 est dans les dépots des système Gnu/linux l'installation ne posera donc pas de problème. Et l’ajout d’un serveur ainsi qu’une base sont d’une facilité déconcertante. Ici la base jarvis installée en local.
Compléments Une grosse erreur à causé une grosse frayeur à un admin, celui ci a DROPE template1, qui est le template de la database elle même. Le mode de fonctionnement de votre équipe. chocolate-mousse:> psql -U postgres template1 Welcome to psql, the PostgreSQL interactive terminal. Type:
\copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit
template1=# DROP DATABASE staffos; DROP template1=# CREATE DATABASE staffos; CREATE template1=# \i staffos_revised.pgdump CREATE CREATE CREATE CREATE etc ...
Il semble que faire un INITDB qui prendrait trop de temps. Il existe Une réplique de votre template, nommé Template0. Surlaquelle on ne peut se connecter. template1=# \c template0 FATAL 1: Database "template0" is not currently accepting connections Previous connection kept
En requetant la base pg_database, nousavons un état des lieux. Tout n'est pas fini. template1=# select * from pg_database; datname | datdba | encoding | datistemplate | datallowconn | -------------+--------+----------+---------------+--------------+ backup_test | 27 | 0 | f | t | britlist | 27 | 0 | f | t | dcl | 1 | 0 | f | t | template0 | 1 | 0 | t | f | kevinprob | 28 | 0 | f | t | kitchen | 1 | 0 | f | t | oooconlist | 103 | 0 | f | t | regression | 1 | 0 | f | t | staffos | 27 | 0 | f | t | template1 | 1 | 0 | t | t |
Il faut changer le drapeau de template0 dans datallowconn. template1=# UPDATE pg_database SET datallowconn = TRUE template1-# WHERE datname = 'template0'; UPDATE 1
Maintenant il est possible de se connecter. template1=# \c template0 CONNECT template0=# drop database template1; ERROR: DROP DATABASE: database is marked as a template
Encore une modification. template0=# UPDATE pg_database SET datistemplate = FALSE template0-# WHERE datname = 'template1'; UPDATE 1
C'est parti. template0=# drop database template1; DROP template0=# create database template1 with template = template0; CREATE
On remet les droits correctement. template0=# template0-# UPDATE 1 template0=# CONNECT template1=# template1-#
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1'; \c template1 UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template0';
Et a priori pas de Vacuum template1-# VACUUM FULL FREEZE; VACUUM
