Mettre à jour son mot de passe de MD5 vers SCRAM-SHA-256 :
Avec la nouvelle version de PostgreSQL, l’encryption des mots de passe par défaut est à présent le SCRAM-SHA-256.
Il faut donc que vos utilisateurs changent leur mot de passe, ou que l’encodage de ceux-ci soit modifié.
Pour faire cela, voici une procédure d’évolution de l’encryptage. Nous partons d’une base Postgresql 13 que nous allons faire évoluer vers une base 14. Mais avant de procéder à cette évolution, il faut que nos mots de passes utilisateurs soient prêts à changer d’encryptage.
Commençons par installer notre instance Postgresql 13 :
root@sarah:~# sudo apt-get -y install postgresql-13 Reading package lists... Done Building dependency tree Reading state information... Done The following package was automatically installed and is no longer required: libfreetype6 Use 'sudo apt autoremove' to remove it. The following additional packages will be installed: postgresql-client-13 Suggested packages: postgresql-doc-13 The following NEW packages will be installed: postgresql-13 postgresql-client-13 0 upgraded, 2 newly installed, 0 to remove and 6 not upgraded. Need to get 16.4 MB of archives. After this operation, 54.1 MB of additional disk space will be used. Get:1 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-client-13 amd64 13.6-1.pgdg18.04+1 [1514 kB] Get:2 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-13 amd64 13.6-1.pgdg18.04+1 [14.9 MB] Fetched 16.4 MB in 2s (6613 kB/s) Preconfiguring packages ... Selecting previously unselected package postgresql-client-13. (Reading database ... 36725 files and directories currently installed.) Preparing to unpack .../postgresql-client-13_13.6-1.pgdg18.04+1_amd64.deb ... Unpacking postgresql-client-13 (13.6-1.pgdg18.04+1) ... Selecting previously unselected package postgresql-13. Preparing to unpack .../postgresql-13_13.6-1.pgdg18.04+1_amd64.deb ... Unpacking postgresql-13 (13.6-1.pgdg18.04+1) ... Setting up postgresql-client-13 (13.6-1.pgdg18.04+1) ... update-alternatives: using /usr/share/postgresql/13/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-13 (13.6-1.pgdg18.04+1) ... Creating new PostgreSQL cluster 13/main ... /usr/lib/postgresql/13/bin/initdb -D /var/lib/postgresql/13/main --auth-local peer --auth-host md5 The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "C.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/13/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Etc/UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Success. You can now start the database server using: /usr/lib/postgresql/13/bin/pg_ctl -D /var/lib/postgresql/13/main -l logfile start Ver Cluster Port Status Owner Data directory Log file 13 main 5433 down postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log update-alternatives: using /usr/share/postgresql/13/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Processing triggers for postgresql-common (190ubuntu0.1) ... Building PostgreSQL dictionaries from installed myspell/hunspell packages... Removing obsolete dictionary files:
Une fois notre instance installée, nous allons nous y connecter et créer une base de données et y créer des comptes utilisateurs avec leurs mots de passes uniques pour chacun :
root@sarah:~# su - postgres postgres@sarah:~$ psql psql (13.6 (Ubuntu 13.6-1.pgdg18.04+1)) Type "help" for help. postgres=# create database exemple; CREATE DATABASE postgres=# create user test1 with password 'test1'; CREATE ROLE postgres=# create user test2 with password 'password'; CREATE ROLE postgres=# create user test3 with password 'jw8s0F4'; CREATE ROLE
Pour réaliser notre première étape de notre configuration en SCRAM, il faut se rendre dans le postgresql.conf. A l’intérieur du fichier de configuration, il existe une ligne qui permet de définir l’encryption des mots de passe utilisateur.
password_encryption = scram-sha-256 # md5 or scram-sha-256
Une fois ce paramètre positionné, il faut commencer par identifier quels utilisateurs ont besoin d’une mise à niveau de mot de passe. Pour se faire, vous pouvez utiliser la requête suivante :
SELECT
rolname,~'^SCRAM-SHA-256\$'AS has_upgraded
FROM
WHERE rolcanlogin;
Dans notre cas, cela donne pour résultat (vous pouvez cliquer pour agrandir) :
Cette requête permet pour chaque utilisateur de vérifier si il dispose d’un mot de passe compatible avec la nouvelle encryption définie. Quand le has_upgraded retourne faux, cela veut dire qu’une mise à jour du mot de passe est necessaire.
Pour cela, deux méthodes s’offrent à nous.
La première est de demander à tout les utilisateurs de changer leur mot de passe. Il suffit alors de le forcer en utilisant (avec un compte super user) la commande suivante :
\password username
Quand l’utilisateur se connectera la prochaine fois, son mot de passe devra être changé. Quand il en aura saisit un nouveau, celui-ci sera encrypté avec la bonne clé.
La deuxième méthode est celle du script de Jonathan S. Katz disponible ici
Il s’agit d’un script Python qui permet d’encrypter vous même vos mots de passes avec la méthode SCRAM-SHA-256.
Pour faire fonctionner ce script, qui est une classe python, j’ai créé un autre petit script que voici :
from encrypt_password import EncryptPassword
login = input("Entrez votre login : ")
mdp = input("Entrez votre mot de passe : ")
pw = EncryptPassword(
user=login,
password=mdp,
algorithm="scram-sha-256",
)
print( pw.encrypt())
Pour le faire fonctionner, il faut que l’utilisateur entre son login de connexion et son mot de passe, et en réponse, le script renvoie une clé SCRAM-SHA-256
postgres@sarah:~$ python3 main.py Entrez votre login :Test Entrez votre mot de passe :Test1 b'SCRAM-SHA-256$4096:ovbMOR5cIHTXlqDgRqM6GQ==$eG7O1ysNZPMiVrixAotiuXXo+EjuRifDtrPrBWt9mLE=:DXRFSXIGKrvT+bygrWToV9k/Nt9Mqlg9RLVJjoUxJzs='
Après cela, il n’y a plus qu’à remplacer le mot de passe actuel par celui généré par le script.
Alter role Test password 'SCRAM-SHA-256$4096:ovbMOR5cIHTXlqDgRqM6GQ==$eG7O1ysNZPMiVrixAotiuXXo+EjuRifDtrPrBWt9mLE=:DXRFSXIGKrvT+bygrWToV9k/Nt9Mqlg9RLVJjoUxJzs=';
Réalisons l’action pour nos trois comptes de test :
postgres@sarah:~$ python3 main.py Entrez votre login : test1 Entrez votre mot de passe : test1 b'SCRAM-SHA-256$4096:g0+esG9BTykOOzM/rx4QAw==$JJqMWuPABqT2vLngzaM08/FtQXqlkbkxtlon5FWI+rM=:+aFW1HoU5X2xB0cEVV+dZZwfOPgUuPmj34mZNSu/RIs=' postgres@sarah:~$ python3 main.py Entrez votre login : test2 Entrez votre mot de passe : password b'SCRAM-SHA-256$4096:/cbTyOf95U5SF3ECFewc0Q==$13iERuaUDh63ygjMU69/z0Cvr7iyrykhhZgAXCe74lU=:qHW48AfSI2bs9AdsJDZ0NKUOTdbcod6w12x7jGK7sWM=' postgres@sarah:~$ python3 main.py Entrez votre login : test3 Entrez votre mot de passe : jw8s0F4 b'SCRAM-SHA-256$4096:2ZAA+2SlIji3nriUl/Qnug==$yLcdUr2nYCAijySpl3YqE2iEaqAhsP39d3nPMHUHYBo=:fibdrIi0j43xsonNIQNXKTTHWvwwnUjrg9XIpmK7p0U=' postgres@sarah:~$ psql psql (13.6 (Ubuntu 13.6-1.pgdg18.04+1)) Type "help" for help. postgres=# alter role test1 password 'SCRAM-SHA-256$4096:g0+esG9BTykOOzM/rx4QAw==$JJqMWuPABqT2vLngzaM08/FtQXqlkbkxtlon5FWI+rM=:+aFW1HoU5X2xB0cEVV+dZZwfOPgUuPmj34mZNSu/RIs='; ALTER ROLE postgres=# alter role test2 password 'SCRAM-SHA-256$4096:/cbTyOf95U5SF3ECFewc0Q==$13iERuaUDh63ygjMU69/z0Cvr7iyrykhhZgAXCe74lU=:qHW48AfSI2bs9AdsJDZ0NKUOTdbcod6w12x7jGK7sWM='; ALTER ROLE postgres=# alter role test3 password 'SCRAM-SHA-256$4096:2ZAA+2SlIji3nriUl/Qnug==$yLcdUr2nYCAijySpl3YqE2iEaqAhsP39d3nPMHUHYBo=:fibdrIi0j43xsonNIQNXKTTHWvwwnUjrg9XIpmK7p0U=' ; ALTER ROLE
Une fois que les mots de passe ont bien tous été modifiés, on peut repasser la requête du début afin de voir si les modifications ont bien été prises en compte.
SELECT rolname,~'^SCRAM-SHA-256\$'AS has_upgraded FROM WHERE rolcanlogin;
Ce qui nous donne ce résultat : (vous pouvez cliquer pour agrandir)
On voit donc bien que tout nos comptes sont prêts à passer à une authentification SCRAM-SHA-256. Ne nous reste plus qu’à modifier le pg_hba.conf pour spécifier qu’on acceptera plus que les connexions avec cette méthode : (vous pouvez cliquer pour agrandir)
Après cela, il n’y a plus qu’à faire un petit rechargement de la configuration, et ce sera ok.
postgres@sarah:/etc/postgresql/13/main/data$ /usr/lib/postgresql/13/bin/pg_ctl reload -D /etc/postgresql/13/main
Pour vérifier que tout a bien fonctionné, il ne nous reste plus qu’à nous connecter avec l’un des comptes de test pour voir si on arrive bien à se connecter avec notre ancien mot de passe qui a été modifié grâce au script qui l’a encrypté.
postgres@sarah:/etc/postgresql/13/main$ psql -d exemple -U test1 Password for user test1: psql (13.6 (Ubuntu 13.6-1.pgdg18.04+1)) Type "help" for help. exemple=>
Pour faire le test, nous avons choisi de faire un pg_upgrade sur la base de donnée vers 13 pour la passer en version 14. Afin de voir si nos utilisateurs pouvaient continuer à se connecter même après que l’encryption SCRAM-SHA soit devenue obligatoire.
Nous avons donc installé une version 14 de postgres :
</pre> root@sarah:~# sudo apt-get -y install postgresql14 Reading package lists... Done Building dependency tree Reading state information... Done E: Unable to locate package postgresql14 root@sarah:~# sudo apt-get -y install postgresql-14 Reading package lists... Done Building dependency tree Reading state information... Done The following package was automatically installed and is no longer required: libfreetype6 Use 'sudo apt autoremove' to remove it. The following additional packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libtypes-serialiser-perl pgdg-keyring postgresql-client-14 postgresql-client-common postgresql-common Suggested packages: postgresql-doc-14 The following NEW packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libtypes-serialiser-perl pgdg-keyring postgresql-14 postgresql-client-14 The following packages will be upgraded: postgresql-client-common postgresql-common 2 upgraded, 7 newly installed, 0 to remove and 4 not upgraded. Need to get 17.7 MB of archives. After this operation, 57.6 MB of additional disk space will be used. Get:1 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB] Get:2 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-common all 238.pgdg18.04+1 [229 kB] Get:3 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-client-common all 238.pgdg18.04+1 [91.9 kB] Get:4 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-client-14 amd64 14.2-1.pgdg18.04+1 [1619 kB] Get:5 http://archive.ubuntu.com/ubuntu bionic/universe amd64 libcommon-sense-perl amd64 3.74-2build2 [20.3 kB] Get:6 http://apt.postgresql.org/pub/repos/apt bionic-pgdg/main amd64 postgresql-14 amd64 14.2-1.pgdg18.04+1 [15.5 MB] Get:7 http://archive.ubuntu.com/ubuntu bionic/universe amd64 libjson-perl all 2.97001-1 [73.3 kB] Get:8 http://archive.ubuntu.com/ubuntu bionic/universe amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB] Get:9 http://archive.ubuntu.com/ubuntu bionic/universe amd64 libjson-xs-perl amd64 3.040-1 [81.2 kB] Fetched 17.7 MB in 1s (17.1 MB/s) Preconfiguring packages ... Selecting previously unselected package libcommon-sense-perl. (Reading database ... 38588 files and directories currently installed.) Preparing to unpack .../0-libcommon-sense-perl_3.74-2build2_amd64.deb ... Unpacking libcommon-sense-perl (3.74-2build2) ... Selecting previously unselected package libjson-perl. Preparing to unpack .../1-libjson-perl_2.97001-1_all.deb ... Unpacking libjson-perl (2.97001-1) ... Selecting previously unselected package libtypes-serialiser-perl. Preparing to unpack .../2-libtypes-serialiser-perl_1.0-1_all.deb ... Unpacking libtypes-serialiser-perl (1.0-1) ... Selecting previously unselected package libjson-xs-perl. Preparing to unpack .../3-libjson-xs-perl_3.040-1_amd64.deb ... Unpacking libjson-xs-perl (3.040-1) ... Selecting previously unselected package pgdg-keyring. Preparing to unpack .../4-pgdg-keyring_2018.2_all.deb ... Unpacking pgdg-keyring (2018.2) ... Preparing to unpack .../5-postgresql-common_238.pgdg18.04+1_all.deb ... Leaving 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common' Unpacking postgresql-common (238.pgdg18.04+1) over (190ubuntu0.1) ... Preparing to unpack .../6-postgresql-client-common_238.pgdg18.04+1_all.deb ... Unpacking postgresql-client-common (238.pgdg18.04+1) over (190ubuntu0.1) ... Selecting previously unselected package postgresql-client-14. Preparing to unpack .../7-postgresql-client-14_14.2-1.pgdg18.04+1_amd64.deb ... Unpacking postgresql-client-14 (14.2-1.pgdg18.04+1) ... Selecting previously unselected package postgresql-14. Preparing to unpack .../8-postgresql-14_14.2-1.pgdg18.04+1_amd64.deb ... Unpacking postgresql-14 (14.2-1.pgdg18.04+1) ... Setting up libcommon-sense-perl (3.74-2build2) ... Setting up libtypes-serialiser-perl (1.0-1) ... Setting up pgdg-keyring (2018.2) ... Removing apt.postgresql.org key from trusted.gpg: OK Setting up libjson-perl (2.97001-1) ... Setting up libjson-xs-perl (3.040-1) ... Setting up postgresql-client-common (238.pgdg18.04+1) ... Installing new version of config file /etc/postgresql-common/supported_versions ... Setting up postgresql-common (238.pgdg18.04+1) ... Setting up postgresql-client-14 (14.2-1.pgdg18.04+1) ... update-alternatives: using /usr/share/postgresql/14/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-14 (14.2-1.pgdg18.04+1) ... Creating new PostgreSQL cluster 14/main ... /usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "C.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/14/main ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Etc/UTC creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode Processing triggers for man-db (2.8.3-2ubuntu0.1) ... Processing triggers for ureadahead (0.100.0-21) ... Processing triggers for systemd (237-3ubuntu10.53) ...
Une fois la nouvelle instance démarrée, on arrête les deux instances (la 13 et la 14) :
postgres@sarah:~$ /usr/lib/postgresql/13/bin/pg_ctl -D /etc/postgresql/13/main stop waiting for server to shut down.... done server stopped postgres@sarah:~$ /usr/lib/postgresql/14/bin/pg_ctl -D /etc/postgresql/14/main stop waiting for server to shut down.... done server stopped
Avant de lancer notre upgrade, on vérifie que les conditions pour le faire sont bien réunies avec l’option –check de pg_upgrade :
postgres@sarah:/etc/postgresql/13/main$ /usr/lib/postgresql/14/bin/pg_upgrade --check -b/usr/lib/postgresql/13/bin/ -B/usr/lib/postgresql/14/bin/ -d/etc/postgresql/13/main -D/etc/postgresql/14/main Finding the real data directory for the source cluster ok Finding the real data directory for the target cluster ok Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for user-defined encoding conversions ok Checking for user-defined postfix operators ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok *Clusters are compatible*
S’ils sont bien notés comme compatibles, il n’y a plus qu’à lancer le transfert :
postgres@sarah:/etc/postgresql/13/main$ /usr/lib/postgresql/14/bin/pg_upgrade -b/usr/lib/postgresql/13/bin/ -B/usr/lib/postgresql/14/bin/ -d/etc/postgresql/13/main -D/etc/postgresql/14/main Finding the real data directory for the source cluster ok Finding the real data directory for the target cluster ok Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for user-defined encoding conversions ok Checking for user-defined postfix operators ok Creating dump of global objects ok Creating dump of database schemas ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok If pg_upgrade fails after this point, you must re-initdb the new cluster before continuing. Performing Upgrade ------------------ Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting oldest XID for new cluster ok Setting next transaction ID and epoch for new cluster ok Deleting files from new pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok Deleting files from new pg_multixact/members ok Copying old pg_multixact/members to new server ok Setting next multixact ID and offset for new cluster ok Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster ok Restoring global objects in the new cluster ok Restoring database schemas in the new cluster ok Copying user relation files ok Setting next OID for new cluster ok Sync data directory to disk ok Creating script to delete old cluster ok Checking for extension updates ok Upgrade Complete ---------------- Optimizer statistics are not transferred by pg_upgrade. Once you start the new server, consider running: /usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages Running this script will delete the old cluster's data files: ./delete_old_cluster.sh
Ne nous reste plus qu’à vérifier que nos utilisateurs peuvent correctement se connecter, étant donné que la base à été transférée sans problèmes.
Conlusion :
L’utilisation de l’encryption SCRAM-SHA-256 sur Postgres est une véritable avancée en terme de sécurisation de l’accès à la base de données.
Il n’est pas très difficile de la mettre en place, même sur des versions ultérieures à la version 14 ou cette encryption est rendue obligatoire.
Après recherche il n’existe pas de méthode permettant de convertir automatiquement les mots de passes de vos utilisateurs sans devoir leur demander de le changer eux même après modification, ou sans devoir passer par le script que j’ai utilisé dans cet article.
Continuez votre lecture sur le blog :
- HypoPG et les index hypothétiques (Sarah FAVEERE) [PostgreSQL]
- PostgreSQL Anonymizer (Sarah FAVEERE) [PostgreSQL]
- Comparatif des gestionnaires de VIP dans un cluster Patroni : épisode 2 (VIP-MANAGER) (David Baffaleuf) [ContainerPostgreSQL]
- Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]
- pg_dirtyread où comment réparer facilement un delete sauvage (Sarah FAVEERE) [PostgreSQL]