![[初心者向け] ローカルの PostgreSQL へのパスワード あり/なし 接続](https://devio2024-media.developers.io/image/upload/f_auto,q_auto,w_3840/v1769990573/user-gen-eyecatch/piu2yn8k3417byoynkmv.webp)
[初心者向け] ローカルの PostgreSQL へのパスワード あり/なし 接続
今回は、EC2 (AL2023) にインストールした PostgreSQL にローカルから接続していきます。
インストール手順については、以下の過去ブログをご参照ください。
ローカルの PostgreSQL へ接続（パスワードなし）
ローカルで起動している PostgreSQL サーバーに対し、パスワードなしで接続していきます。
EC2 にアクセスし、PostgreSQL サーバーが起動しているか確認
Active: running なので OK。もし起動していなければ sudo systemctl start postgresql を実行します。
$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
...
Active: active (running) since Fri 2026-01-16 04:46:52 UTC; 28s ago
...
OS ユーザー postgres として psql コマンドを実行 -> PostgreSQL サーバーへ接続できた
[ec2-user@ip-10-0-11-35 ~]$ sudo -u postgres psql
psql (17.7)
Type "help" for help.
postgres=#
上記のように psql コマンドを単体で（オプションなしで）使うとデフォルトでは、現在の OS ユーザー名（上記の例では postgres）をデータベースユーザー名および接続先のデータベース名として利用する動きになっています。すなわち sudo -u postgres psql -U postgres -d postgres と同じ意味です。
ホスト名を省略した場合、psqlはUnixドメインソケット経由でローカルホスト上のサーバに、WindowsではlocalhostにTCP/IP経由で接続します。 ... デフォルトのデータベースユーザ名はOSのユーザ名です。 データベースユーザ名が決まれば、デフォルトのデータベース名として使われます。
接続後、\conninfo で接続情報を確認
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
# 日本語訳
# ユーザー「postgres」として、ソケット「/var/run/postgresql」経由でポート「5432」にてデータベース「postgres」に接続しています。
上記よりソケットファイル経由でローカル接続していることがわかります。
接続に使用されているソケットファイルは以下で確認できます。
$ ls -al /var/run/postgresql/
...
srwxrwxrwx. 1 postgres postgres 0 Jan 22 00:27 .s.PGSQL.5432
-rw-------. 1 postgres postgres 61 Jan 22 00:27 .s.PGSQL.5432.lock
ちなみに postgreSQL サーバーを停止するとソケットファイルは無くなります。
# PostgreSQLを停止
$ sudo systemctl stop postgresql
# ソケットファイルを確認
$ ls -al /var/run/postgresql/
total 0
（ソケットファイルが表示されない）
サーバー停止状態（ソケットファイルが無い状態）で psql すると以下のようにソケットファイルが見つからないとのエラーが出ます。
$ sudo -u postgres psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
Is the server running locally and accepting connections on that socket?
# 日本語訳
# psql: エラー: ソケット 「/var/run/postgresql/.s.PGSQL.5432」 上のサーバーへの接続に失敗しました: ファイルまたはディレクトリが見つかりません
# サーバーはローカルで実行され、そのソケットで接続を受け入れていますか？
再度サーバーを起動すると、ソケットファイルが作成され、無事 psql でサーバーへ接続できるようになります。
$ sudo systemctl start postgresql
$ ls -al /var/run/postgresql/
...
srwxrwxrwx. 1 postgres postgres 0 Jan 22 01:40 .s.PGSQL.5432
-rw-------. 1 postgres postgres 61 Jan 22 01:40 .s.PGSQL.5432.lock
$ sudo -u postgres psql
psql (17.7)
Type "help" for help.
postgres=#
ローカルの PostgreSQL へ接続（パスワードあり）
前項では、パスワードなしでの PostgreSQL 接続をしました。
本項では、パスワードを設定し、そのパスワード認証にて接続させるような状態に持っていきます。
まず現在の状態を before として確認します。
- OS ユーザー
ec2-userのままで接続しようとするとエラーになる（sudo -u postgresを付けないやり方） - OS ユーザー
postgresから psql を実行すると接続できる
[ec2-user@ip-10-0-11-35 ~]$ whoami
ec2-user
[ec2-user@ip-10-0-11-35 ~]$ psql -U postgres -d postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "postgres"
# 訳
# psql: エラー: ソケット 「/var/run/postgresql/.s.PGSQL.5432」 上のサーバーへの接続に失敗しました: FATAL: ユーザー 「postgres」 のピア認証に失敗しました
[ec2-user@ip-10-0-11-35 ~]$ sudo -u postgres psql -U postgres -d postgres
psql (17.7)
Type "help" for help.
postgres=# exit
[ec2-user@ip-10-0-11-35 ~]$ sudo -u postgres psql
psql (17.7)
Type "help" for help.
postgres=#
上記のように「パスワードなしで接続できる」かつ「OS ユーザーが ec2-user の状態では接続不可になる」のは接続の認証方式が peer 認証となっているためです。
こちらの設定は pg_hba.conf で変更できます。変更手順は後述します。
peer 接続では、OS ユーザー名が postgres になっているかをチェックする認証になっているため、ec2-user の状態で psql 接続しても通りません。構図としては以下です。
peer認証:
OSユーザー名（ec2-user）≠ PostgreSQLユーザー名（postgres）
→ 拒否
ドキュメントは以下です。
peer
クライアントのオペレーティングシステムにおけるユーザ名をオペレーティングシステムから取得し、ユーザ名が要求されたデータベースユーザ名と一致するか検査します。 これはローカル接続の時にのみ使用可能です。https://www.postgresql.jp/document/17/html/auth-pg-hba-conf.html
なので、パスワード認証をさせるため、以下の 2 つを実行します。
- データベースユーザ
postgresにパスワードを設定する - pg_hba.conf を編集し、既存の peer 設定をパスワード認証が必要となる別の設定に変更する
postgres ユーザーのパスワード設定
まず、データベースユーザ postgres の現在のパスワードを確認するため、pg_authid の内容をクエリします。
pg_authid は ロール（データベースユーザーおよびグループの総称）の詳細情報を見ることができ、その中にパスワードの情報もあります。
pg_authidカタログはデータベース認証識別子（ロール）の情報を保持します。 ロールは「ユーザ」と「グループ」の概念を包括しています。...
このカタログはパスワードを含んでいるため、第三者が内容を読むことができないようにしなければいけません。https://www.postgresql.jp/document/17/html/catalog-pg-authid.html
pg_authid から、現在の PostgreSQL サーバーの postgres ユーザーのパスワードを確認。
rolpasswordが空なので、パスワードが未設定であることがわかります。
# PostgreSQLに接続（まだpeer認証なのでパスワード不要）
[ec2-user@ip-10-0-11-35 ~]$ sudo -u postgres psql
psql (17.7)
Type "help" for help.
-- 現在のパスワード状態を確認
postgres=# SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'postgres';
rolname | rolpassword
----------+-------------
postgres |
(1 row)
ALTER ROLE {role名} WITH PASSWORD {パスワード} にて postgres ユーザーのパスワードを設定します。
-- パスワードを設定
postgres=# ALTER ROLE postgres WITH PASSWORD 'Pass!123';
ALTER ROLE
ALTER ROLE
ALTER ROLE — データベースロールを変更する
...
例
ロールのパスワードを変更します。
ALTER ROLE davide WITH PASSWORD 'hu8jmn3';https://www.postgresql.jp/document/17/html/sql-alterrole.html
パスワードが設定されたか確認。
rolpassword にパスワードのハッシュ値が入っているので、設定できています。
# 表示結果を見やすくするため、\x で拡張出力モードに切り替え
postgres=# \x
Expanded display is on.
# パスワードが設定されているか確認
postgres=# SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'postgres';
-[ RECORD 1 ]--------------------------------------------------------------------------------------------------------------------------------------
rolname | postgres
rolpassword | SCRAM-SHA-256$4096:3U87CT4lubFP3YrTIz7hcg==$FKe2MioRy1c4OYMtKUbKwH2kCCNie9ewjzvxNYWUC5k=:SlATWuCZbbYWeQmXAo7qaYv/QUfN3UQ5o5Anz9Q3dgA=
上記のクエリ前に実行している \x(拡張出力モード)については以下の外部記事を参照
パスワード設定できたので、PostgreSQL サーバーから exit し、再接続してみます。
一方で、現状では未だパスワードは聞かれずに、ログインすることができます。
postgres=# exit
[ec2-user@ip-10-0-11-35 ~]$ sudo -u postgres psql
psql (17.7)
Type "help" for help.
postgres=#
パスワード認証させるよう pg_hba.conf を設定変更する
クライアントから PostgreSQL サーバーへの接続設定は、pg_hba.conf ファイルに設定されています。
このファイルを編集してパスワードが必要となる認証方式に設定変更します。
20.1. pg_hba.confファイル
クライアント認証はデータベースクラスタのデータディレクトリ内の、伝統的にpg_hba.confという名前の設定ファイルで管理されています。 （HBAとは、host-based authentication: ホストベース認証の略です。）https://www.postgresql.jp/document/17/html/auth-pg-hba-conf.html
pg_hba.conf の編集前にバックアップを作成
# pg_hba.conf を編集する前にバックアップを取る
[ec2-user@ip-10-0-11-35 ~]$ sudo cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.backup
# バックアップされたか念の為確認。ファイルがあるので OK
[ec2-user@ip-10-0-11-35 ~]$ sudo ls /var/lib/pgsql/data/
...
pg_hba.conf
pg_hba.conf.backup
設定ファイルの変更箇所は、TYPE が local になっている部分。変更前の出力では METHOD が peer になっている。
[ec2-user@ip-10-0-11-35 ~]$ sudo cat /var/lib/pgsql/data/pg_hba.conf
(略)
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
...
ここを peer から md5 に変更する。
# vim で編集
[ec2-user@ip-10-0-11-35 ~]$ sudo vi /var/lib/pgsql/data/pg_hba.conf
# 変更できているか確認
[ec2-user@ip-10-0-11-35 ~]$ sudo cat /var/lib/pgsql/data/pg_hba.conf
(略)
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
...
以下ドキュメントの通り、接続メソッドを md5 にすることで、ログイン時にパスワード認証に切り替えることができます。
md5
ユーザのパスワードを検証するために、SCRAM-SHA-256あるいはMD5認証を実行します。 詳細は20.5を参照してください。https://www.postgresql.jp/document/17/html/auth-pg-hba-conf.html
pg_hba.conf ファイルを編集後、設定を反映させるために PostgreSQL サーバーをリロード
# PostgreSQLに設定を再読み込みさせる
[ec2-user@ip-10-0-11-35 ~]$ sudo systemctl reload postgresql
再度ログインコマンドを実行する。すると今度はパスワードを聞かれるようになります。
[ec2-user@ip-10-0-11-35 ~]$ sudo -u postgres psql
Password for user postgres:
psql (17.7)
Type "help" for help.
postgres=#
ec2-user の状態でも再度 psql を実行してみます。するとパスワード認証後、ログインが通るようになりました。
[ec2-user@ip-10-0-11-35 ~]$ whoami
ec2-user
[ec2-user@ip-10-0-11-35 ~]$ psql -U postgres -d postgres
Password for user postgres:
psql (17.7)
Type "help" for help.
postgres=#
peer 認証から md5 認証に変更することで、パスワードありの安全な接続ができるようになりました。
検証は以上です。
終わりに
今回は、EC2(AL2023) にインストールした PostgreSQL にローカルからパスワードありなしで接続する部分を手を動かしてやってみました。
普段なかなか触らない部分なので、ソケットファイルや pg_hba.conf の内容などに触れることができてとても有意義な検証でした。
接続周りは初学者には難しい部分が多いと思うので、本記事が自分と同じような方のお役に立てば幸いです。
参考情報
