PostgreSQLでSCRAM authentication requires libpq version 10 or aboveと出るとき

PostgreSQLサーバに接続するときに次のようなエラーが出ることがあります。

SQLSTATE[08006] [7] SCRAM authentication requires libpq version 10 or above

これは「認証処理にはlibpqのバージョン10以上が必要」というエラーです。

上位バージョンのlibpqが求められるようになった理由は、PostgresSQLサーバへ接続する際の認証の仕様が変更されたためです。

PostgresSQLサーバのパスワードハッシュ化方式は、md5等が使用されていましたが、バージョン10でSCRAM-SHA-256も使用できるようになり、バージョン14ではデフォルト設定がmd5からSCRAM-SHA-256へ変更となりました。
libpqのバージョン9以下ではSCRAM-SHA-256に対応していないため、パスワード認証方式がSCRAM-SHA-256に設定されているサーバへ接続しようとするとエラーが発生するのです。
(ハッシュ化は不可逆で元の平文パスワードに戻せないので暗号化ではありません)

サーバ側を変更する方法
　パスワード認証を無効にする方法
　パスワード認証方式をSCRAM-SHA-256からMD5にする方法
 クライアント側を変更する方法
　 Windows
　CentOS7
　CentOS8 Stream
　Ubuntu
保存されているパスワード
 yumで依存関係を調べる方法
 PHPでバージョンを調べる方法
 Windowsでlibpq.dllのバージョン確認方法
 Dockerでの構築方法
　パスワード認証を無効にする場合
　パスワード認証方式をMD5にする場合

サーバ側を変更する方法

/var/lib/postgresql/data/
または
/var/lib/pgsql/【バージョン番号】/data/

にある設定ファイルを編集します。

pg_hba.conf	接続時のパスワードのエンコード方式を変更
postgresql.conf	パスワードを保存する際のエンコード方式を変更

パスワード認証を無効にする方法

pg_hba.confを編集して、認証メソッドをtrust(パスワードを見ずに信頼する)にします。

host all all all scram-sha-256
　↓
host all all all trust

sedコマンドで書き換える場合は下記のようにします。
(コメント部分も書き換わりますが)

sed -ri -e 's!md5!trust!g' /var/lib/postgresql/data/pg_hba.conf
sed -ri -e 's!scram-sha-256!trust!g' /var/lib/postgresql/data/pg_hba.conf

パスワード認証方式をSCRAM-SHA-256からMD5にする方法

設定したパスワードはハッシュ化された文字列で保存され、認証時には入力したパスワードをハッシュ化し、保存された値と一致するかを見ています。

このため下記の操作が必要となります。
・入力したパスワードのハッシュ化方式を変更する
・保存時のパスワードハッシュ化方式を変更する
・すでに保存済みのパスワードを再度ハッシュ化しなおす

pg_hba.confを編集して、認証メソッドをmd5にします。

host all all all scram-sha-256
　↓
host all all all md5

postgresql.confを編集して、保存時のパスワードハッシュ化方式をmd5にするため下記を追記します。

password_encryption = md5

コマンドで書き換える場合は下記のようにします。

sed -ri -e 's!scram-sha-256!md5!g' /var/lib/postgresql/data/pg_hba.conf
echo "password_encryption = md5" >> /var/lib/postgresql/data/postgresql.conf

PostgresSQLサーバを再起動するか、下記のコマンドで設定を反映させます。

postgres=# SELECT pg_reload_conf();

そののち保存済みのパスワードを再生成するとログインできるようになります。

postgres=# ALTER ROLE postgres PASSWORD '【パスワード】';

password_encryptionの変更はSQLコマンドでも可能です。

$ psql -U postgres
postgres=# ALTER SYSTEM SET password_encryption = 'md5';
postgres=# SELECT pg_reload_conf();
postgres=# ALTER ROLE postgres PASSWORD 'postgres';

クライアント側を変更する方法

Windows

Windowsでは、libpq.dllファイルを最新のものに変更します。
libpq.dllはPostgreSQLやPHP8のインストールファイルに含まれています。
PostgreSQL/【バージョン番号】/bin/libpq.dll

CentOS7

libpqはpostgresql-libsパッケージに含まれています。
標準のバージョンは9.2です。
共有ライブラリは下記の場所にあります。

$ ls -al /usr/lib64/libpq.so*
lrwxrwxrwx 1 root root     12  9月 28 06:15 /usr/lib64/libpq.so.5 -> libpq.so.5.5
-rwxr-xr-x 1 root root 197544  6月 25 00:25 /usr/lib64/libpq.so.5.5

新しいバージョンのlibpqはPostgreSQLのリポジトリで配信されています。

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql13-libs

共有ライブラリは下記の場所に配置されます。

ls -al /usr/pgsql-*/lib/libpq.so*
lrwxrwxrwx 1 root root     13 10月 18 17:03 /usr/pgsql-13/lib/libpq.so.5 -> libpq.so.5.13
-rwxr-xr-x 1 root root 329648  8月 10 19:07 /usr/pgsql-13/lib/libpq.so.5.13

CentOS8 Stream

libpqは単体のパッケージで得られます。
標準のバージョンは13.5です。

# dnf list | grep libpq
libpq.i686                                                        13.5-1.el8                                                 appstream
libpq.x86_64                                                      13.5-1.el8                                                 appstream
libpq-devel.i686                                                  13.5-1.el8                                                 appstream
libpq-devel.x86_64                                                13.5-1.el8                                                 appstream

Ubuntu

libpqはlibpq5のパッケージで得られます。

sudo apt list | grep libpq
libpq-dev/stable 13.8-0+deb11u1 arm64
libpq-dev/stable 13.8-0+deb11u1 armhf
libpq5/stable 13.8-0+deb11u1 arm64
libpq5/stable 13.8-0+deb11u1 armhf

パッケージ管理はaptで行えます。

sudo apt install libpq5

保存されているパスワード

pg_shadowテーブルのuserカラムのユーザごとに、passwdカラムに保存されています。

MD5では接頭辞にmd5が付いた状態で保存されています。
md5以降はパスワードを単純にmd5ハッシュ化した値ではありません。
同じパスワードであれば、同じ値になります。

postgres=# SELECT passwd FROM pg_shadow WHERE user = 'postgres';
               passwd
-------------------------------------
 md53175bce1d3201d16594cebf9d7eb3f9d
(1 row)

SCRAM-SHA-256では接頭辞にSCRAM-SHA-256とバイト数が付いた状態で保存されています。
同じパスワードでも、異なる値になります。

postgres=# SELECT passwd FROM pg_shadow WHERE user = 'postgres';
                                                                passwd
---------------------------------------------------------------------------------------------------------------------------------------
 SCRAM-SHA-256$4096:YlK5RKacECK61no0G5WWwA==$VS3hVyD1VtEwl65xUe5HV/uaEwV0C69KfLdpCEDXa7Y=:aofNPsCnLY/PgKV109LB1B9q6Hyz1E4dAPd8EhyfgQg=
(1 row)

yumで依存関係を調べる方法

yumのdeplistオプションで調べられます。

# yum deplist php-pgsql
package: php-pgsql.x86_64 7.4.30-1.el7.remi
  dependency: ld-linux-x86-64.so.2()(64bit)
   provider: glibc.x86_64 2.17-326.el7_9
  dependency: ld-linux-x86-64.so.2(GLIBC_2.3)(64bit)
   provider: glibc.x86_64 2.17-326.el7_9
  dependency: libc.so.6(GLIBC_2.14)(64bit)
   provider: glibc.x86_64 2.17-326.el7_9
  dependency: libpq.so.5()(64bit)
   provider: postgresql-libs.x86_64 9.2.24-8.el7_9
  dependency: libpthread.so.0()(64bit)
   provider: glibc.x86_64 2.17-326.el7_9
  dependency: libpthread.so.0(GLIBC_2.2.5)(64bit)
   provider: glibc.x86_64 2.17-326.el7_9
  dependency: php-pdo(x86-64) = 7.4.30-1.el7.remi
   provider: php-pdo.x86_64 7.4.30-1.el7.remi
  dependency: rtld(GNU_HASH)
   provider: glibc.x86_64 2.17-326.el7_9
   provider: glibc.i686 2.17-326.el7_9

PHPでバージョンを調べる方法

phpinfo();で確認ができます。

コマンドラインで確認するには下記のようになります。

$ php --ri pgsql

pgsql

PostgreSQL Support => enabled
PostgreSQL(libpq) Version => 9.2.24
PostgreSQL(libpq)  => PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
Multibyte character support => enabled
SSL support => enabled
Active Persistent Links => 0
Active Links => 0

Directive => Local Value => Master Value
pgsql.allow_persistent => On => On
pgsql.max_persistent => Unlimited => Unlimited
pgsql.max_links => Unlimited => Unlimited
pgsql.auto_reset_persistent => Off => Off
pgsql.ignore_notice => Off => Off
pgsql.log_notice => Off => Off

Windowsでlibpq.dllのバージョン確認方法

libpq.dllを右クリックしてプロパティを開きます。
詳細タブを選択します。

Dockerでの構築方法

パスワード認証を無効にする場合

version: '3'

volumes:
  postgres-database:
    driver: local

services:
  ### postgres ###
  postgres:
    image: postgres:14
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - postgres-database:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=hogehoge_db
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_HOST_AUTH_METHOD=trust
    hostname: postgres
    networks:
        - backend

パスワード認証方式をMD5にする場合

version: '3'

volumes:
  postgres-database:
    driver: local

services:
  ### postgres ###
  postgres:
    image: postgres:14
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - postgres-database:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=hogehoge_db
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_HOST_AUTH_METHOD=md5
      - POSTGRES_INITDB_ARGS="-A md5"
    hostname: postgres
    networks:
        - backend

イメージ作成後に下記を実行します。

docker exec -it postgres bash -l -c "export PGPASSWORD=postgres ; psql -U postgres -c \"ALTER SYSTEM SET password_encryption = 'md5';SELECT pg_reload_conf();ALTER USER postgres WITH PASSWORD 'postgres';\""