Pour se connecter à PostgreSQL, il faut d’abord se connecter à l’utilisateur postgres :
su - postgres
On peut ensuite exécuter la commande psql. On peut également spécifier un user / mot de passe lor de la connexion :
psql -U djeeks -W
PostgreSQL ne fonctionne pas du tout comme MySQL. Nous allons voir les principales différences et présenter les commandes utiles poiur l’administration de base du système.
La première chose qui vous sera certainement utile après avoir installé PostgreSQL est de pouvoir créer une base de données. Pour cela, il faut utiliser la commande
createdb ma_base
Attention : cette commande s’utilise directement depuis le shell Bash et non dans le shell PostgreSQL.
Pour autoriser un utilisateur du système linux à créer des bases de données, il faut créer l’user sous PostgreSQL :
CREATE USER djeeks;
Ensuite, il faut lui donner les droits nécessaires :
ALTER USER djeeks CREATEDB;
Enfin, on peut verifier que l’utilisateur dispose bien des bons droits via la commande \du.
La commande \? permet d’afficher l’aide PostgreSQL.
Pour gérer les utilisateurs sous PostgreSQL, on peut soit créer un user, soit un rôle. La différence est que lorsqu’on crée un rôle via la commande CREATE ROLE djeeks;, il faut explicitement lui donner les droits LOGIN pour qu’il puisse se conecter.
Pour restreindre les accès par IP, il faut renseigner le fichier /etc/postgresql/10/main/pg_hba.conf :
#TYPE DATABASE USER CIDR-ADDRESS METHOD
local all postgres ident sameuser
host ma_base djeeks 10.0.1.5 md5
La première ligne par exemple donne le droit à l’utilisateur postgres de se connecter à toutes les bases (all) via un socket unix (local). La seconde ligne, elle, permet à l’utilisateur djeeks de se connecter à la base ma_base depuis l’IP 10.0.1.5.
La métdode ident sameuser signifie que l’utilisateur Unix et PostgreSQL doivent être les mêmes. Cette métdode ne doit être utilisée qu’en local et est très risquée sur internet. La métdode md5 identifie l’utilisateur à partir du hash md5 de son password.
Après avoir modifié le fichier pg_hba.conf, il est nécessaire de recharger la configuration de PostgreSQL via systemctl reload postgresql. Il est également possible de recharger le fichier contenu du fichier pg_hba.conf via la commande suivante :
SELECT pg_reload_conf();
Pour lister les bases de données disponibles, il faut utiliser la commande :
\list
Cette commande peut être abrégée en \l.
On peut, ensuite, sélectionner la base sur laquelle on souhaite travailler avec la commande
\connect ma_base
Il sera également possible de l’abréger en \c ma_base.
Si on souhaite préciser un user pour la connexion, on peut utiliser la commande suivante :
\c 'user=djeeks dbname=ma_base'
Il est également possible de sélectionner directement sa base (si on connait son nom) lors de la connection à postgresql :
psql ma_base
Nous pouvons alors créer des tables au sein de la base à laquelle nous venons de nous connecter, via les commandes SQL traditionnelles. Ensuite, il est possible de donner les droits sur les bases et les tables. Attention, si on souhaite donner les droits sur la base complète, il faut le préciser explicitement
GRANT ALL PRIVILEGES ON DATABASE ma_base TO djeeks;
Pour lister les différentes relations présentes dans une base de données, on peut utiliser la commande : \d.Cette commande permet notammment d’afficher les tables présentes dans une base de données. La commande \dp permet, elle, d’afficher les droits par table.
Enfin, pour quitter postgresql, il faut saisir la commande \q.
Pour lister les requêtes actuellement en cours d’exécution sur PostgreSQL, il faut tapper la commande :
SELECT * FROM pg_stat_activity;