{"id":9029,"date":"2022-05-19T09:09:50","date_gmt":"2022-05-19T08:09:50","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=9029"},"modified":"2022-05-19T09:09:50","modified_gmt":"2022-05-19T08:09:50","slug":"postgresql-14-et-le-scram-sha-256","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/","title":{"rendered":"PostgreSQL 14 et le scram-sha-256"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Mettre \u00e0 jour son mot de passe de MD5 vers SCRAM-SHA-256 :<\/h1>\n

Avec la nouvelle version de PostgreSQL, l’encryption des mots de passe par d\u00e9faut est \u00e0 pr\u00e9sent le SCRAM-SHA-256.<\/p>\n

Il faut donc que vos utilisateurs changent leur mot de passe, ou que l’encodage de ceux-ci soit modifi\u00e9.<\/p>\n

Pour faire cela, voici une proc\u00e9dure d’\u00e9volution de l’encryptage. Nous partons d’une base Postgresql 13 que nous allons faire \u00e9voluer vers une base 14. Mais avant de proc\u00e9der \u00e0 cette \u00e9volution, il faut que nos mots de passes utilisateurs soient pr\u00eats \u00e0 changer d’encryptage.<\/p>\n

Commen\u00e7ons par installer notre instance Postgresql 13 :<\/p>\n

\r\n\r\nroot@sarah:~# sudo apt-get -y install postgresql-13\r\nReading package lists... Done\r\nBuilding dependency tree\r\nReading state information... Done\r\nThe following package was automatically installed and is no longer required:\r\nlibfreetype6\r\nUse 'sudo apt autoremove' to remove it.\r\nThe following additional packages will be installed:\r\npostgresql-client-13\r\nSuggested packages:\r\npostgresql-doc-13\r\nThe following NEW packages will be installed:\r\npostgresql-13 postgresql-client-13\r\n0 upgraded, 2 newly installed, 0 to remove and 6 not upgraded.\r\nNeed to get 16.4 MB of archives.\r\nAfter this operation, 54.1 MB of additional disk space will be used.\r\nGet:1 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-client-13 amd64 13.6-1.pgdg18.04+1 [1514 kB]\r\nGet:2 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-13 amd64 13.6-1.pgdg18.04+1 [14.9 MB]\r\nFetched 16.4 MB in 2s (6613 kB\/s)\r\nPreconfiguring packages ...\r\nSelecting previously unselected package postgresql-client-13.\r\n(Reading database ... 36725 files and directories currently installed.)\r\nPreparing to unpack ...\/postgresql-client-13_13.6-1.pgdg18.04+1_amd64.deb ...\r\nUnpacking postgresql-client-13 (13.6-1.pgdg18.04+1) ...\r\nSelecting previously unselected package postgresql-13.\r\nPreparing to unpack ...\/postgresql-13_13.6-1.pgdg18.04+1_amd64.deb ...\r\nUnpacking postgresql-13 (13.6-1.pgdg18.04+1) ...\r\nSetting up postgresql-client-13 (13.6-1.pgdg18.04+1) ...\r\nupdate-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\r\nSetting up postgresql-13 (13.6-1.pgdg18.04+1) ...\r\nCreating new PostgreSQL cluster 13\/main ...\r\n\/usr\/lib\/postgresql\/13\/bin\/initdb -D \/var\/lib\/postgresql\/13\/main --auth-local peer --auth-host md5\r\nThe files belonging to this database system will be owned by user "postgres".\r\nThis user must also own the server process.\r\n\r\nThe database cluster will be initialized with locale "C.UTF-8".\r\nThe default database encoding has accordingly been set to "UTF8".\r\nThe default text search configuration will be set to "english".\r\n\r\nData page checksums are disabled.\r\n\r\nfixing permissions on existing directory \/var\/lib\/postgresql\/13\/main ... ok\r\ncreating subdirectories ... ok\r\nselecting dynamic shared memory implementation ... posix\r\nselecting default max_connections ... 100\r\nselecting default shared_buffers ... 128MB\r\nselecting default time zone ... Etc\/UTC\r\ncreating configuration files ... ok\r\nrunning bootstrap script ... ok\r\nperforming post-bootstrap initialization ... ok\r\nsyncing data to disk ... ok\r\n\r\nSuccess. You can now start the database server using:\r\n\r\n\/usr\/lib\/postgresql\/13\/bin\/pg_ctl -D \/var\/lib\/postgresql\/13\/main -l logfile start\r\n\r\nVer Cluster Port Status Owner Data directory Log file\r\n13 main 5433 down postgres \/var\/lib\/postgresql\/13\/main \/var\/log\/postgresql\/postgresql-13-main.log\r\nupdate-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\r\nProcessing triggers for postgresql-common (190ubuntu0.1) ...\r\nBuilding PostgreSQL dictionaries from installed myspell\/hunspell packages...\r\nRemoving obsolete dictionary files:\r\n\r\n<\/pre>\n
Une fois notre instance install\u00e9e, nous allons nous y connecter et cr\u00e9er une base de donn\u00e9es et y cr\u00e9er des comptes utilisateurs avec leurs mots de passes uniques pour chacun :<\/p>\n
\r\n\r\nroot@sarah:~# su - postgres\r\npostgres@sarah:~$ psql\r\npsql (13.6 (Ubuntu 13.6-1.pgdg18.04+1))\r\nType "help" for help.\r\n\r\npostgres=# create database exemple;\r\nCREATE DATABASE\r\npostgres=# create user test1 with password 'test1';\r\nCREATE ROLE\r\npostgres=# create user test2 with password 'password';\r\nCREATE ROLE\r\npostgres=# create user test3 with password 'jw8s0F4';\r\nCREATE ROLE\r\n\r\n<\/pre>\n
Pour r\u00e9aliser notre premi\u00e8re \u00e9tape de notre configuration en SCRAM, il faut se rendre dans le postgresql.conf. A l’int\u00e9rieur du fichier de configuration, il existe une ligne qui permet de d\u00e9finir l’encryption des mots de passe utilisateur.<\/p>\n
\r\n\r\npassword_encryption = scram-sha-256 # md5 or scram-sha-256\r\n\r\n<\/pre>\n
Une fois ce param\u00e8tre positionn\u00e9, il faut commencer par identifier quels utilisateurs ont besoin d’une mise \u00e0 niveau de mot de passe. Pour se faire, vous pouvez utiliser la requ\u00eate suivante :<\/p>\n
\r\nSELECT\r\n    rolname,~'^SCRAM-SHA-256\\$'AS has_upgraded\r\nFROM\r\nWHERE rolcanlogin;\r\n\r\n<\/pre>\n
Dans notre cas, cela donne pour r\u00e9sultat (vous pouvez cliquer pour agrandir) :<\/p>\n
\"\"<\/a><\/p>\n
Cette requ\u00eate permet pour chaque utilisateur de v\u00e9rifier si il dispose d’un mot de passe compatible avec la nouvelle encryption d\u00e9finie. Quand le has_upgraded retourne faux, cela veut dire qu’une mise \u00e0 jour du mot de passe est necessaire.<\/p>\n
Pour cela, deux m\u00e9thodes s’offrent \u00e0 nous.<\/p>\n
La premi\u00e8re est de demander \u00e0 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 :<\/p>\n
\r\n\\password username\r\n<\/pre>\n
Quand l’utilisateur se connectera la prochaine fois, son mot de passe devra \u00eatre chang\u00e9. Quand il en aura saisit un nouveau, celui-ci sera encrypt\u00e9 avec la bonne cl\u00e9.<\/p>\n
La deuxi\u00e8me m\u00e9thode est celle du script de Jonathan S. Katz disponible ici\u00a0<\/a><\/p>\n
Il s’agit d’un script Python qui permet d’encrypter vous m\u00eame vos mots de passes avec la m\u00e9thode SCRAM-SHA-256.<\/p>\n
Pour faire fonctionner ce script, qui est une classe python, j’ai cr\u00e9\u00e9 un autre petit script que voici :<\/p>\n
\r\n\r\nfrom encrypt_password import EncryptPassword\r\n\r\nlogin = input("Entrez votre login : ")\r\nmdp = input("Entrez votre mot de passe : ")\r\n\r\npw = EncryptPassword(\r\nuser=login,\r\npassword=mdp,\r\nalgorithm="scram-sha-256",\r\n)\r\nprint( pw.encrypt())\r\n\r\n<\/pre>\n
Pour le faire fonctionner, il faut que l’utilisateur entre son login de connexion et son mot de passe, et en r\u00e9ponse, le script renvoie une cl\u00e9 SCRAM-SHA-256<\/p>\n
\r\npostgres@sarah:~$ python3 main.py\r\nEntrez votre login :Test\r\nEntrez votre mot de passe :Test1\r\nb'SCRAM-SHA-256$4096:ovbMOR5cIHTXlqDgRqM6GQ==$eG7O1ysNZPMiVrixAotiuXXo+EjuRifDtrPrBWt9mLE=:DXRFSXIGKrvT+bygrWToV9k\/Nt9Mqlg9RLVJjoUxJzs='\r\n<\/pre>\n
Apr\u00e8s cela, il n’y a plus qu’\u00e0 remplacer le mot de passe actuel par celui g\u00e9n\u00e9r\u00e9 par le script.<\/p>\n
\r\n\r\nAlter role Test password 'SCRAM-SHA-256$4096:ovbMOR5cIHTXlqDgRqM6GQ==$eG7O1ysNZPMiVrixAotiuXXo+EjuRifDtrPrBWt9mLE=:DXRFSXIGKrvT+bygrWToV9k\/Nt9Mqlg9RLVJjoUxJzs=';\r\n\r\n<\/pre>\n
R\u00e9alisons l’action pour nos trois comptes de test :<\/p>\n
\r\npostgres@sarah:~$ python3 main.py\r\nEntrez votre login : test1\r\nEntrez votre mot de passe : test1\r\nb'SCRAM-SHA-256$4096:g0+esG9BTykOOzM\/rx4QAw==$JJqMWuPABqT2vLngzaM08\/FtQXqlkbkxtlon5FWI+rM=:+aFW1HoU5X2xB0cEVV+dZZwfOPgUuPmj34mZNSu\/RIs='\r\npostgres@sarah:~$ python3 main.py\r\nEntrez votre login : test2\r\nEntrez votre mot de passe : password\r\nb'SCRAM-SHA-256$4096:\/cbTyOf95U5SF3ECFewc0Q==$13iERuaUDh63ygjMU69\/z0Cvr7iyrykhhZgAXCe74lU=:qHW48AfSI2bs9AdsJDZ0NKUOTdbcod6w12x7jGK7sWM='\r\npostgres@sarah:~$ python3 main.py\r\nEntrez votre login : test3\r\nEntrez votre mot de passe : jw8s0F4\r\nb'SCRAM-SHA-256$4096:2ZAA+2SlIji3nriUl\/Qnug==$yLcdUr2nYCAijySpl3YqE2iEaqAhsP39d3nPMHUHYBo=:fibdrIi0j43xsonNIQNXKTTHWvwwnUjrg9XIpmK7p0U='\r\npostgres@sarah:~$ psql\r\npsql (13.6 (Ubuntu 13.6-1.pgdg18.04+1))\r\nType "help" for help.\r\n\r\npostgres=# alter role test1 password 'SCRAM-SHA-256$4096:g0+esG9BTykOOzM\/rx4QAw==$JJqMWuPABqT2vLngzaM08\/FtQXqlkbkxtlon5FWI+rM=:+aFW1HoU5X2xB0cEVV+dZZwfOPgUuPmj34mZNSu\/RIs=';\r\nALTER ROLE\r\npostgres=# alter role test2 password 'SCRAM-SHA-256$4096:\/cbTyOf95U5SF3ECFewc0Q==$13iERuaUDh63ygjMU69\/z0Cvr7iyrykhhZgAXCe74lU=:qHW48AfSI2bs9AdsJDZ0NKUOTdbcod6w12x7jGK7sWM=';\r\nALTER ROLE\r\npostgres=# alter role test3 password 'SCRAM-SHA-256$4096:2ZAA+2SlIji3nriUl\/Qnug==$yLcdUr2nYCAijySpl3YqE2iEaqAhsP39d3nPMHUHYBo=:fibdrIi0j43xsonNIQNXKTTHWvwwnUjrg9XIpmK7p0U='&nbsp;;\r\nALTER ROLE\r\n\r\n<\/pre>\n
Une fois que les mots de passe ont bien tous \u00e9t\u00e9 modifi\u00e9s, on peut repasser la requ\u00eate du d\u00e9but afin de voir si les modifications ont bien \u00e9t\u00e9 prises en compte.<\/p>\n
\r\nSELECT rolname,~'^SCRAM-SHA-256\\$'AS has_upgraded FROM WHERE rolcanlogin;\r\n<\/pre>\n
Ce qui nous donne ce r\u00e9sultat : (vous pouvez cliquer pour agrandir)<\/em><\/p>\n
\"\"<\/a><\/p>\n
On voit donc bien que tout nos comptes sont pr\u00eats \u00e0 passer \u00e0 une authentification SCRAM-SHA-256. Ne nous reste plus qu’\u00e0 modifier le pg_hba.conf pour sp\u00e9cifier qu’on acceptera plus que les connexions avec cette m\u00e9thode : (vous pouvez cliquer pour agrandir)<\/em><\/p>\n
\"\"<\/a><\/p>\n
Apr\u00e8s cela, il n’y a plus qu’\u00e0 faire un petit rechargement de la configuration, et ce sera ok.<\/p>\n
\r\n\r\npostgres@sarah:\/etc\/postgresql\/13\/main\/data$ \/usr\/lib\/postgresql\/13\/bin\/pg_ctl reload -D \/etc\/postgresql\/13\/main\r\n\r\n<\/pre>\n
Pour v\u00e9rifier que tout a bien fonctionn\u00e9, il ne nous reste plus qu’\u00e0 nous connecter avec l’un des comptes de test pour voir si on arrive bien \u00e0 se connecter avec notre ancien mot de passe qui a \u00e9t\u00e9 modifi\u00e9 gr\u00e2ce au script qui l’a encrypt\u00e9.<\/p>\n
\r\npostgres@sarah:\/etc\/postgresql\/13\/main$ psql -d exemple -U test1\r\nPassword for user test1:\r\npsql (13.6 (Ubuntu 13.6-1.pgdg18.04+1))\r\nType "help" for help.\r\n\r\nexemple=>\r\n\r\n<\/pre>\n
Pour faire le test, nous avons choisi de faire un pg_upgrade sur la base de donn\u00e9e vers 13 pour la passer en version 14. Afin de voir si nos utilisateurs pouvaient continuer \u00e0 se connecter m\u00eame apr\u00e8s que l’encryption SCRAM-SHA soit devenue obligatoire.
\nNous avons donc install\u00e9 une version 14 de postgres :<\/p>\n
<\/pre>\r\nroot@sarah:~# sudo apt-get -y install postgresql14\r\nReading package lists... Done\r\nBuilding dependency tree\r\nReading state information... Done\r\nE: Unable to locate package postgresql14\r\nroot@sarah:~# sudo apt-get -y install postgresql-14\r\nReading package lists... Done\r\nBuilding dependency tree\r\nReading state information... Done\r\nThe following package was automatically installed and is no longer required:\r\nlibfreetype6\r\nUse 'sudo apt autoremove' to remove it.\r\nThe following additional packages will be installed:\r\nlibcommon-sense-perl libjson-perl libjson-xs-perl libtypes-serialiser-perl pgdg-keyring postgresql-client-14\r\npostgresql-client-common postgresql-common\r\nSuggested packages:\r\npostgresql-doc-14\r\nThe following NEW packages will be installed:\r\nlibcommon-sense-perl libjson-perl libjson-xs-perl libtypes-serialiser-perl pgdg-keyring postgresql-14\r\npostgresql-client-14\r\nThe following packages will be upgraded:\r\npostgresql-client-common postgresql-common\r\n2 upgraded, 7 newly installed, 0 to remove and 4 not upgraded.\r\nNeed to get 17.7 MB of archives.\r\nAfter this operation, 57.6 MB of additional disk space will be used.\r\nGet:1 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 pgdg-keyring all 2018.2 [10.7 kB]\r\nGet:2 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-common all 238.pgdg18.04+1 [229 kB]\r\nGet:3 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-client-common all 238.pgdg18.04+1 [91.9 kB]\r\nGet:4 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-client-14 amd64 14.2-1.pgdg18.04+1 [1619 kB]\r\nGet:5 http:\/\/archive.ubuntu.com\/ubuntu bionic\/universe amd64 libcommon-sense-perl amd64 3.74-2build2 [20.3 kB]\r\nGet:6 http:\/\/apt.postgresql.org\/pub\/repos\/apt bionic-pgdg\/main amd64 postgresql-14 amd64 14.2-1.pgdg18.04+1 [15.5 MB]\r\nGet:7 http:\/\/archive.ubuntu.com\/ubuntu bionic\/universe amd64 libjson-perl all 2.97001-1 [73.3 kB]\r\nGet:8 http:\/\/archive.ubuntu.com\/ubuntu bionic\/universe amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB]\r\nGet:9 http:\/\/archive.ubuntu.com\/ubuntu bionic\/universe amd64 libjson-xs-perl amd64 3.040-1 [81.2 kB]\r\nFetched 17.7 MB in 1s (17.1 MB\/s)\r\nPreconfiguring packages ...\r\nSelecting previously unselected package libcommon-sense-perl.\r\n(Reading database ... 38588 files and directories currently installed.)\r\nPreparing to unpack ...\/0-libcommon-sense-perl_3.74-2build2_amd64.deb ...\r\nUnpacking libcommon-sense-perl (3.74-2build2) ...\r\nSelecting previously unselected package libjson-perl.\r\nPreparing to unpack ...\/1-libjson-perl_2.97001-1_all.deb ...\r\nUnpacking libjson-perl (2.97001-1) ...\r\nSelecting previously unselected package libtypes-serialiser-perl.\r\nPreparing to unpack ...\/2-libtypes-serialiser-perl_1.0-1_all.deb ...\r\nUnpacking libtypes-serialiser-perl (1.0-1) ...\r\nSelecting previously unselected package libjson-xs-perl.\r\nPreparing to unpack ...\/3-libjson-xs-perl_3.040-1_amd64.deb ...\r\nUnpacking libjson-xs-perl (3.040-1) ...\r\nSelecting previously unselected package pgdg-keyring.\r\nPreparing to unpack ...\/4-pgdg-keyring_2018.2_all.deb ...\r\nUnpacking pgdg-keyring (2018.2) ...\r\nPreparing to unpack ...\/5-postgresql-common_238.pgdg18.04+1_all.deb ...\r\nLeaving 'diversion of \/usr\/bin\/pg_config to \/usr\/bin\/pg_config.libpq-dev by postgresql-common'\r\nUnpacking postgresql-common (238.pgdg18.04+1) over (190ubuntu0.1) ...\r\nPreparing to unpack ...\/6-postgresql-client-common_238.pgdg18.04+1_all.deb ...\r\nUnpacking postgresql-client-common (238.pgdg18.04+1) over (190ubuntu0.1) ...\r\nSelecting previously unselected package postgresql-client-14.\r\nPreparing to unpack ...\/7-postgresql-client-14_14.2-1.pgdg18.04+1_amd64.deb ...\r\nUnpacking postgresql-client-14 (14.2-1.pgdg18.04+1) ...\r\nSelecting previously unselected package postgresql-14.\r\nPreparing to unpack ...\/8-postgresql-14_14.2-1.pgdg18.04+1_amd64.deb ...\r\nUnpacking postgresql-14 (14.2-1.pgdg18.04+1) ...\r\nSetting up libcommon-sense-perl (3.74-2build2) ...\r\nSetting up libtypes-serialiser-perl (1.0-1) ...\r\nSetting up pgdg-keyring (2018.2) ...\r\nRemoving apt.postgresql.org key from trusted.gpg: OK\r\nSetting up libjson-perl (2.97001-1) ...\r\nSetting up libjson-xs-perl (3.040-1) ...\r\nSetting up postgresql-client-common (238.pgdg18.04+1) ...\r\nInstalling new version of config file \/etc\/postgresql-common\/supported_versions ...\r\nSetting up postgresql-common (238.pgdg18.04+1) ...\r\nSetting up postgresql-client-14 (14.2-1.pgdg18.04+1) ...\r\nupdate-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\r\nSetting up postgresql-14 (14.2-1.pgdg18.04+1) ...\r\nCreating new PostgreSQL cluster 14\/main ...\r\n\/usr\/lib\/postgresql\/14\/bin\/initdb -D \/var\/lib\/postgresql\/14\/main --auth-local peer --auth-host scram-sha-256 --no-instructions\r\nThe files belonging to this database system will be owned by user "postgres".\r\nThis user must also own the server process.\r\n\r\nThe database cluster will be initialized with locale "C.UTF-8".\r\nThe default database encoding has accordingly been set to "UTF8".\r\nThe default text search configuration will be set to "english".\r\n\r\nData page checksums are disabled.\r\n\r\nfixing permissions on existing directory \/var\/lib\/postgresql\/14\/main ... ok\r\ncreating subdirectories ... ok\r\nselecting dynamic shared memory implementation ... posix\r\nselecting default max_connections ... 100\r\nselecting default shared_buffers ... 128MB\r\nselecting default time zone ... Etc\/UTC\r\ncreating configuration files ... ok\r\nrunning bootstrap script ... ok\r\nperforming post-bootstrap initialization ... ok\r\nsyncing data to disk ... ok\r\nupdate-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\r\nProcessing triggers for man-db (2.8.3-2ubuntu0.1) ...\r\nProcessing triggers for ureadahead (0.100.0-21) ...\r\nProcessing triggers for systemd (237-3ubuntu10.53) ...\r\n\r\n<\/pre>\n
Une fois la nouvelle instance d\u00e9marr\u00e9e, on arr\u00eate les deux instances (la 13 et la 14) :<\/p>\n
\r\n\r\npostgres@sarah:~$ \/usr\/lib\/postgresql\/13\/bin\/pg_ctl -D \/etc\/postgresql\/13\/main stop\r\nwaiting for server to shut down.... done\r\nserver stopped\r\npostgres@sarah:~$ \/usr\/lib\/postgresql\/14\/bin\/pg_ctl -D \/etc\/postgresql\/14\/main stop\r\nwaiting for server to shut down.... done\r\nserver stopped\r\n\r\n<\/pre>\n
Avant de lancer notre upgrade, on v\u00e9rifie que les conditions pour le faire sont bien r\u00e9unies avec l’option –check de pg_upgrade :<\/p>\n
\r\n\r\npostgres@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\r\nFinding the real data directory for the source cluster ok\r\nFinding the real data directory for the target cluster ok\r\nPerforming Consistency Checks\r\n-----------------------------\r\nChecking cluster versions ok\r\nChecking database user is the install user ok\r\nChecking database connection settings ok\r\nChecking for prepared transactions ok\r\nChecking for system-defined composite types in user tables ok\r\nChecking for reg* data types in user tables ok\r\nChecking for contrib\/isn with bigint-passing mismatch ok\r\nChecking for user-defined encoding conversions ok\r\nChecking for user-defined postfix operators ok\r\nChecking for presence of required libraries ok\r\nChecking database user is the install user ok\r\nChecking for prepared transactions ok\r\nChecking for new cluster tablespace directories ok\r\n\r\n*Clusters are compatible*\r\n\r\n<\/pre>\n
S’ils sont bien not\u00e9s comme compatibles, il n’y a plus qu’\u00e0 lancer le transfert :<\/p>\n
\r\n\r\npostgres@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\r\nFinding the real data directory for the source cluster ok\r\nFinding the real data directory for the target cluster ok\r\nPerforming Consistency Checks\r\n-----------------------------\r\nChecking cluster versions ok\r\nChecking database user is the install user ok\r\nChecking database connection settings ok\r\nChecking for prepared transactions ok\r\nChecking for system-defined composite types in user tables ok\r\nChecking for reg* data types in user tables ok\r\nChecking for contrib\/isn with bigint-passing mismatch ok\r\nChecking for user-defined encoding conversions ok\r\nChecking for user-defined postfix operators ok\r\nCreating dump of global objects ok\r\nCreating dump of database schemas\r\nok\r\nChecking for presence of required libraries ok\r\nChecking database user is the install user ok\r\nChecking for prepared transactions ok\r\nChecking for new cluster tablespace directories ok\r\n\r\nIf pg_upgrade fails after this point, you must re-initdb the\r\nnew cluster before continuing.\r\n\r\nPerforming Upgrade\r\n------------------\r\nAnalyzing all rows in the new cluster ok\r\nFreezing all rows in the new cluster ok\r\nDeleting files from new pg_xact ok\r\nCopying old pg_xact to new server ok\r\nSetting oldest XID for new cluster ok\r\nSetting next transaction ID and epoch for new cluster ok\r\nDeleting files from new pg_multixact\/offsets ok\r\nCopying old pg_multixact\/offsets to new server ok\r\nDeleting files from new pg_multixact\/members ok\r\nCopying old pg_multixact\/members to new server ok\r\nSetting next multixact ID and offset for new cluster ok\r\nResetting WAL archives ok\r\nSetting frozenxid and minmxid counters in new cluster ok\r\nRestoring global objects in the new cluster ok\r\nRestoring database schemas in the new cluster\r\nok\r\nCopying user relation files\r\nok\r\nSetting next OID for new cluster ok\r\nSync data directory to disk ok\r\nCreating script to delete old cluster ok\r\nChecking for extension updates ok\r\n\r\nUpgrade Complete\r\n----------------\r\nOptimizer statistics are not transferred by pg_upgrade.\r\nOnce you start the new server, consider running:\r\n\/usr\/lib\/postgresql\/14\/bin\/vacuumdb --all --analyze-in-stages\r\n\r\nRunning this script will delete the old cluster's data files:\r\n.\/delete_old_cluster.sh\r\n\r\n<\/pre>\n
Ne nous reste plus qu’\u00e0 v\u00e9rifier que nos utilisateurs peuvent correctement se connecter, \u00e9tant donn\u00e9 que la base \u00e0 \u00e9t\u00e9 transf\u00e9r\u00e9e sans probl\u00e8mes.<\/p>\n
 <\/p>\n
Conlusion :<\/h1>\n
 <\/p>\n
L’utilisation de l’encryption SCRAM-SHA-256 sur Postgres est une v\u00e9ritable avanc\u00e9e en terme de s\u00e9curisation de l’acc\u00e8s \u00e0 la base de donn\u00e9es.<\/p>\n
Il n’est pas tr\u00e8s difficile de la mettre en place, m\u00eame sur des versions ult\u00e9rieures \u00e0 la version 14 ou cette encryption est rendue obligatoire.<\/p>\n
Apr\u00e8s recherche il n’existe pas de m\u00e9thode permettant de convertir automatiquement les mots de passes de vos utilisateurs sans devoir leur demander de le changer eux m\u00eame apr\u00e8s modification, ou sans devoir passer par le script que j’ai utilis\u00e9 dans cet article.<\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Mettre \u00e0 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\u00e9faut est \u00e0 pr\u00e9sent le SCRAM-SHA-256. Il faut donc que vos utilisateurs changent leur mot de… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":41,"featured_media":9069,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[266],"tags":[401,403,402],"class_list":["post-9029","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-encryption","tag-postgresql-14","tag-scram-sha-256"],"yoast_head":"\nPostgreSQL 14 et le scram-sha-256 - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 14 et le scram-sha-256 - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Mettre \u00e0 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\u00e9faut est \u00e0 pr\u00e9sent le SCRAM-SHA-256. Il faut donc que vos utilisateurs changent leur mot de… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2022-05-19T08:09:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/05\/chrome_mr3Dne2UT3.png\" \/>\n\t<meta property=\"og:image:width\" content=\"531\" \/>\n\t<meta property=\"og:image:height\" content=\"395\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Sarah FAVEERE\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sarah FAVEERE\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"15 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/\"},\"author\":{\"name\":\"Sarah FAVEERE\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2\"},\"headline\":\"PostgreSQL 14 et le scram-sha-256\",\"datePublished\":\"2022-05-19T08:09:50+00:00\",\"dateModified\":\"2022-05-19T08:09:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/\"},\"wordCount\":2914,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"encryption\",\"PostgreSQL 14\",\"scram-sha-256\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/\",\"name\":\"PostgreSQL 14 et le scram-sha-256 - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2022-05-19T08:09:50+00:00\",\"dateModified\":\"2022-05-19T08:09:50+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 14 et le scram-sha-256\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2\",\"name\":\"Sarah FAVEERE\",\"sameAs\":[\"http:\/\/blog.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/sfaveere\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL 14 et le scram-sha-256 - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/","og_locale":"fr_FR","og_type":"article","og_title":"PostgreSQL 14 et le scram-sha-256 - Capdata TECH BLOG","og_description":"Mettre \u00e0 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\u00e9faut est \u00e0 pr\u00e9sent le SCRAM-SHA-256. Il faut donc que vos utilisateurs changent leur mot de… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2022-05-19T08:09:50+00:00","og_image":[{"width":531,"height":395,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/05\/chrome_mr3Dne2UT3.png","type":"image\/png"}],"author":"Sarah FAVEERE","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Sarah FAVEERE","Dur\u00e9e de lecture estim\u00e9e":"15 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/"},"author":{"name":"Sarah FAVEERE","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2"},"headline":"PostgreSQL 14 et le scram-sha-256","datePublished":"2022-05-19T08:09:50+00:00","dateModified":"2022-05-19T08:09:50+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/"},"wordCount":2914,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["encryption","PostgreSQL 14","scram-sha-256"],"articleSection":["PostgreSQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/","url":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/","name":"PostgreSQL 14 et le scram-sha-256 - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2022-05-19T08:09:50+00:00","dateModified":"2022-05-19T08:09:50+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-14-et-le-scram-sha-256\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 14 et le scram-sha-256"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/686f2452f7ec79115d31e41c230a9da2","name":"Sarah FAVEERE","sameAs":["http:\/\/blog.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/sfaveere\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9029","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/41"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=9029"}],"version-history":[{"count":22,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9029\/revisions"}],"predecessor-version":[{"id":9068,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9029\/revisions\/9068"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/9069"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=9029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=9029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=9029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}