PostgreSQL ou Postgres est un système de gestion de base de données relationnelle objet à usage général open source avec de nombreuses fonctionnalités avancées qui vous permet de créer des environnements tolérants aux pannes ou des applications complexes.
Dans ce guide, nous expliquerons comment installer le serveur de base de données PostgreSQL sur Ubuntu 20.04 et explorerons les bases de l'administration de la base de données PostgreSQL.
Prérequis #
Pour pouvoir installer des packages, vous devez être connecté en tant qu'utilisateur root ou utilisateur avec les privilèges sudo.
Installer PostgreSQL sur Ubuntu #
Au moment de la rédaction de cet article, la dernière version de PostgreSQL disponible dans les dépôts officiels d'Ubuntu est PostgreSQL version 10.4.
Exécutez les commandes suivantes pour installer le serveur PostgreSQL sur Ubuntu :
sudo apt updatesudo apt install postgresql postgresql-contrib
Nous installons également le package contrib PostgreSQL qui fournit plusieurs fonctionnalités supplémentaires pour le système de base de données PostgreSQL.
Une fois l'installation terminée, le service PostgreSQL démarrera automatiquement. Utilisez le psql outil pour vérifier l'installation en se connectant au serveur de base de données PostgreSQL et en imprimant sa version :
sudo -u postgres psql -c "SELECT version();"PostgreSQL 12.2 (Ubuntu 12.2-4) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-8ubuntu1) 9.3.0, 64-bit
C'est ça. PostgreSQL a été installé et vous pouvez commencer à l'utiliser.
Rôles PostgreSQL et méthodes d'authentification #
Les autorisations d'accès à la base de données dans PostgreSQL sont gérées avec le concept de rôles. Un rôle peut représenter un utilisateur de base de données ou un groupe d'utilisateurs de base de données.
PostgreSQL prend en charge plusieurs méthodes d'authentification. Les méthodes les plus couramment utilisées sont :
- Confiance - Un rôle peut se connecter sans mot de passe, tant que les conditions définies dans le
pg_hba.confsont remplies. - Mot de passe :un rôle peut se connecter en fournissant un mot de passe. Les mots de passe peuvent être stockés sous la forme
scram-sha-256,md5, etpassword(texte clair). - Ident :uniquement pris en charge sur les connexions TCP/IP. Il fonctionne en obtenant le nom d'utilisateur du système d'exploitation du client, avec un mappage de nom d'utilisateur facultatif.
- Peer :identique à Ident, mais il n'est pris en charge que sur les connexions locales.
L'authentification du client PostgreSQL est définie dans le fichier de configuration nommé pg_hba.conf . Par défaut, PostgreSQL utilise la méthode d'authentification par les pairs pour les connexions locales.
Le postgres user est automatiquement créé lorsque vous installez PostgreSQL. Cet utilisateur est le superutilisateur de l'instance PostgreSQL, et il équivaut à l'utilisateur racine MySQL.
Pour vous connecter au serveur PostgreSQL en tant que postgres utilisateur, passez d'abord à l'utilisateur, puis accédez à l'invite PostgreSQL à l'aide de psql utilitaire :
sudo su - postgrespsql
À partir de là, vous pouvez interagir avec votre instance PostgreSQL. Pour quitter le shell PostgreSQL, tapez :
\q
Une autre façon d'accéder à l'invite PostgreSQL sans changer d'utilisateur consiste à utiliser le sudo commande :
sudo -u postgres psql
Généralement, vous devez vous connecter au serveur de base de données en tant que postgres uniquement à partir de l'hôte local.
Création du rôle et de la base de données PostgreSQL #
Seuls les super-utilisateurs et les rôles avec CREATEROLE privilège peut créer de nouveaux rôles.
L'exemple suivant montre comment créer un nouveau rôle nommé john une base de données nommée johndb et accorder des privilèges sur la base de données :
-
Créez un nouveau rôle PostgreSQL :
sudo su - postgres -c "createuser john" -
Créez une nouvelle base de données PostgreSQL :
sudo su - postgres -c "createdb johndb"
Pour accorder des autorisations à l'utilisateur sur la base de données, connectez-vous au shell PostgreSQL :
sudo -u postgres psqlet exécutez la requête suivante :
GRANT ALL PRIVILEGES ON DATABASE johndb TO john;Activer l'accès à distance au serveur PostgreSQL #
Par défaut, le serveur PostgreSQL écoute uniquement sur l'interface locale (127.0.0.1 ).
Pour activer l'accès à distance à votre serveur PostgreSQL, ouvrez le fichier de configuration postgresql.conf et ajoutez listen_addresses = '*' dans le CONNECTIONS AND AUTHENTICATION rubrique.
sudo nano /etc/postgresql/12/main/postgresql.conf#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;Enregistrez le fichier et redémarrez le service PostgreSQL :
sudo service postgresql restart
Vérifiez les modifications avec le ss utilitaire :
ss -nlt | grep 5432
La sortie montre que le serveur PostgreSQL écoute toutes les interfaces (0.0.0.0 ):
LISTEN 0 244 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 244 [::]:5432 [::]:*
L'étape suivante consiste à configurer le serveur pour qu'il accepte les connexions à distance en modifiant le pg_hba.conf fichier.
Voici quelques exemples montrant différents cas d'utilisation :
/etc/postgresql/12/main/pg_hba.conf# TYPE DATABASE USER ADDRESS METHOD
# The user jane can access all databases from all locations using md5 password
host all jane 0.0.0.0/0 md5
# The user jane can access only the janedb from all locations using md5 password
host janedb jane 0.0.0.0/0 md5
# The user jane can access all databases from a trusted location (192.168.1.134) without a password
host all jane 192.168.1.134 trust
La dernière étape consiste à ouvrir le port 5432 dans votre pare-feu.
En supposant que vous utilisez UFW pour gérer votre pare-feu, et vous souhaitez autoriser l'accès depuis le 192.168.1.0/24 sous-réseau, vous exécuterez la commande suivante :
sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432Assurez-vous que votre pare-feu est configuré pour accepter uniquement les connexions provenant de plages d'adresses IP approuvées.