postgreSQL 14のパスワード認証が、Ubuntu22.04/WSL2では失敗するが、Ubuntu22.04/EC2では問題が起こらない

Question

実現したいこと

1. Ubuntu22.04にPostgreSQLをインストール
2. データベースdb_testとロールrole_testを作成し、db_testの所有者をrole_testに変更
3. 作成したデータベースとユーザーでPostgreSQLに接続

sudo apt install postgresql postgresql-contrib libpq-dev

sudo -u postgres psql
create database db_test;
create role role_test with login password '1234';
alter database db_test owner to role_test;
exit

psql -h localhost -p 5432 -U role_test -d db_test

発生している問題・分からないこと

• WSL2上のUbuntu22.04では、上記手順 3. で認証エラーが発生して接続できない。
• EC2上のUbuntu22.04では、同じ手順で問題なく接続可能。

WSL2の場合

ubuntu@DESKTOP-xxxxxxx:~$ psql -h localhost -p 5432 -U role_test -d db_test
Password for user role_test:
# (パスワードを入力)
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "role_test"
connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  password authentication failed for user "role_test"

EC2の場合

ubuntu@ip-x-x-x-x:~$ psql -h localhost -p 5432 -U role_test -d db_test
Password for user role_test: 
# (パスワードを入力)
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

db_test=> 

エラーメッセージ

上記参照______

該当のソースコード

特になし

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

WSL2もEC2も、以下は同一でした。

• Ubuntuのバージョン
• PostgreSQLのバージョン
• pg_hba.confの内容

WSL2の場合

ubuntu@DESKTOP-xxxxxxx:~/Project$ cat /etc/os-release
PRETTY_NAME="Ubuntu 22.04.3 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.3 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy

# =========================================
psql -V
psql (PostgreSQL) 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1)

sudo nano /etc/postgresql/14/main/pg_hba.conf

# ...
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

EC2の場合

ubuntu@ip-x-x-x-x:~$ cat /etc/os-release
PRETTY_NAME="Ubuntu 22.04.3 LTS"
NAME="Ubuntu"
VERSION_ID="22.04"
VERSION="22.04.3 LTS (Jammy Jellyfish)"
VERSION_CODENAME=jammy
ID=ubuntu
ID_LIKE=debian
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
UBUNTU_CODENAME=jammy

# =========================================
psql -V
psql (PostgreSQL) 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1)

sudo nano /etc/postgresql/14/main/pg_hba.conf

# ...
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

補足

WSLでは、Ubuntuシステム上に以下の手順でユーザーubuntuというを作成し、システムのデフォルトユーザーを変更してあります。

sudo adduser ubuntu
sudo gpasswd -a ubuntu sudo

sudo nano /etc/wsl.conf
# 以下追記
[user]
default=ubuntu

Answer 1

WSLのPostgreSQLがポート5432ではなく、5434で起動していた事が原因でした。(そんなー！？)

ubuntu@DESKTOP-xxxxxxx:~$ sudo cat /var/log/postgresql/postgresql-14-main.log
2023-12-30 07:23:52.652 JST [3630] LOG:  starting PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
2023-12-30 07:23:52.653 JST [3630] LOG:  listening on IPv4 address "127.0.0.1", port 5434
2023-12-30 07:23:52.659 JST [3630] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5434"
2023-12-30 07:23:52.671 JST [3631] LOG:  database system was shut down at 2023-12-30 07:23:49 JST
2023-12-30 07:23:52.679 JST [3630] LOG:  database system is ready to accept connections

というわけで、

ubuntu@DESKTOP-xxxxxxx:~$ psql -h localhost -p 5434 -U role_test -d db_test
Password for user role_test:
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

db_test=> 

解決しました。