PostgreSQL

冒頭まとめ PostgreSQLへの接続時に次のエラーが出る場合、pg_hba.confに接続条件と一致する規則がありません。 FATAL: no pg_hba.conf entry for host "192.168.1.10", user "app", database "mydb", no encryption エラーに表示された接続元IP、利用者名、データベース名、暗号化状態を確認し、この4つに合う規則を探してください。規則を追加する場合は、接続元を必要な範囲に絞り、scram-sha-256など適切な認証方式を指定します。 Linuxなどでは、ファイルの保存後に設定の再読み込みが必要です。Microsoft Windowsでは、変更内容がその後の新しい接続へ直ちに適用されます。 no pg_hba.conf entry for hostの意味 pg_hba.confは、PostgreSQLへ接続できる利用者、データベース、接続元、認証方式を定めるファイルです。HBAはhost-based authenticationの略で、接続元に基づく認証設定を指します。 PostgreSQLは、接続種別、接続元アドレス、要求されたデータベース、利用者名の4つを上から順に照合します。一致する規則が1つもなければ、接続を拒否してno pg_hba.conf entry for hostを返します。 このエラーのSQLSTATEは28000、条件名はinvalid_authorization_specificationです。パスワードが違う場合の28P01とは別のエラーです。PostgreSQL公式のエラーコード一覧で確認できます。 エラー文の4項目を確認する エラー文には、原因を絞るための情報が含まれています。 host "192.168.1.10" user "app" database "mydb" no encryption hostの値は、PostgreSQLから見た接続元IPです。利用者が想定していたIPではなく、エラーに表示された値を基準にしてください。 userとdatabaseは、接続時に指定されたPostgreSQLの利用者名とデータベース名です。似た名前の別環境へ接続していないかも確認します。 末尾のno encryptionは暗号化されていない接続です。環境によってはSSL encryptionまたはGSS encryptionと表示されます。これは単なる補足ではなく、hostsslなどの接続種別と照合する条件です。 pg_hba.confの場所を確認する 編集すべきファイルの場所は、接続済みの管理用セッションから確認できます。 SHOW hba_file; PostgreSQLは認証設定ファイルを別の場所へ移せるため、想像した場所のファイルを編集すると反映されないことがあります。必ず実際に使用されているパスを確認してください。 接続できる管理用セッションがない場合は、サーバーの設定管理、コンテナのマウント設定、クラウドサービスの管理画面などで使用中の認証設定を確認します。管理サービスではpg_hba.confを直接編集できない場合があります。 読み込める規則と書式エラーを調べる pg_hba_file_rulesを使うと、pg_hba.confの規則と書式エラーを確認できます。既定ではスーパーユーザーだけが参照できます。 SELECT rule_number, file_name, line_number, type, database, user_name, address, auth_method, error FROM pg_hba_file_rules ORDER BY file_name, line_number; errorがNULLではない行には、読み取れない理由が入ります。誤った行では、line_numberとerror以外が空になることがあります。 ...

冒頭まとめ PostgreSQLへの接続時に次のエラーが出る場合、利用できる接続枠がすべて使われています。 FATAL: sorry, too many clients already まず、接続済みの管理用セッションがあればpg_stat_activityで内訳を確認します。不要な接続を安全に終了し、アプリケーションが接続を閉じているか、複数の接続プールの上限が大きすぎないかを調べてください。 max_connectionsを上げるだけでは、接続の増え続ける原因は解消しません。この設定の変更にはPostgreSQLの再起動が必要で、値を増やすと共有メモリを含む資源の割り当ても増えます。先に接続の使い方を直し、それでも必要な場合に限って上限を見直します。 sorry, too many clients alreadyの意味 PostgreSQLは、同時に受け付ける接続数をmax_connectionsで制限しています。利用可能な接続枠を使い切ると、新しい接続を受け付けられず、sorry, too many clients alreadyを返します。 このエラーのSQLSTATEは53300、条件名はtoo_many_connectionsです。アプリケーションでエラーを判定するときは、表示文ではなくSQLSTATEを使うと、言語設定や文言の違いに影響されにくくなります。PostgreSQL公式のエラーコード一覧でも確認できます。 PostgreSQL本体では、接続処理に必要な領域を確保できなかったときに、このエラーを返します。処理はPostgreSQL本体のproc.cで確認できます。 似た2つのエラーとの違い 接続枠が少なくなった段階では、別の文言が表示されることがあります。 FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute この場合は接続枠が完全になくなったわけではなく、残りがスーパーユーザー用に確保されています。スーパーユーザーで管理用接続を確立し、接続状況を調査できる可能性があります。 環境によっては、次の文言が表示されます。 FATAL: remaining connection slots are reserved for roles with privileges of the "pg_use_reserved_connections" role これは、残りがpg_use_reserved_connectionsの権限を持つ役割とスーパーユーザー向けに確保されている状態です。 一方、sorry, too many clients alreadyまで進むと、接続処理に使える枠自体が残っていません。予約された権限を持つ利用者でも、新しい接続に失敗する可能性があります。予約枠の判定と文言はPostgreSQL本体のpostinit.cで確認できます。 接続数と設定値を確認する まだ利用できる管理用接続や既存の管理画面がある場合は、最初に現在値を確認します。 SHOW max_connections; SHOW superuser_reserved_connections; reserved_connectionsに対応する環境では、次の値も確認してください。未対応の版では設定項目が存在しないため、エラーになります。 SHOW reserved_connections; 現在のクライアント接続数は、次のSQLで確認できます。 SELECT count(*) AS client_connections FROM pg_stat_activity WHERE backend_type = 'client backend'; pg_stat_activityにはサーバープロセスごとの状態が表示されます。PostgreSQL公式文書にも、現在の状態や問い合わせを確認する統計ビューとして説明されています。pg_stat_activityの公式資料 ...

冒頭まとめ PostgreSQLで次のエラーが出た場合、複数のトランザクションが互いのロック解放を待っています。 ERROR: deadlock detected DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 4321. Process 4321 waits for ShareLock on transaction 5679; blocked by process 1234. HINT: See server log for query details. 最も重要な対策は、複数の行や表を更新する順序をすべての処理で統一することです。そのうえで、SQLSTATE 40P01を検出したらトランザクション全体を再試行します。 deadlock_timeoutを長くしたり短くしたりしても、デッドロックの原因そのものはなくなりません。まずサーバーログで衝突した問い合わせを特定し、ロックを取る順序を直してください。 deadlock detectedとは デッドロックは、2つ以上のトランザクションが互いに必要なロックを持ち、どちらも先へ進めなくなった状態です。 たとえば、トランザクションAが行1を更新してから行2を待ち、トランザクションBが行2を更新してから行1を待つと、待ち合わせの輪ができます。PostgreSQLはこの状態を自動で検出し、関係するトランザクションの1つを中断して処理を進めます。 どのトランザクションが中断されるかは予測しにくく、アプリケーション側で決めつけてはいけません。明示的にLOCK TABLEを使っていなくても、通常のUPDATEが取得する行ロックだけで発生します。これらの動作はPostgreSQL公式文書のDeadlocksで説明されています。 このエラーのSQLSTATEは40P01、条件名はdeadlock_detectedです。アプリケーションでは文章ではなくSQLSTATEで判定すると、表示言語や文言の変化に影響されにくくなります。PostgreSQLのエラーコード一覧でも40P01を確認できます。 最初にサーバーログを確認する 手元のエラーに表示されるDETAILには、待っているプロセス番号、ロックの種類、トランザクション番号などが並びます。ただし、衝突した問い合わせ文がクライアント側の出力に含まれない場合があります。 HINT: See server log for query details.と表示されたら、PostgreSQLのサーバーログを確認してください。PostgreSQL本体は、クライアント向けの詳細とログ向けの詳細を分けて組み立て、ログ側には各プロセスの問い合わせ文を追加します。この処理はPostgreSQL本体のdeadlock.cで確認できます。 ログには次のような情報が記録されます。 Process 1234: UPDATE accounts SET balance = balance - 100 WHERE acctnum = 22222; Process 4321: UPDATE accounts SET balance = balance + 100 WHERE acctnum = 11111; プロセス番号だけを見て原因を決めず、各問い合わせがどの順序で行や表を操作しているかを比べます。 ...

結論 relation "users" does not exist は、対象がこの世に無いという意味ではありません。指定した名前を、今の接続から解決できなかったという意味です。SQL の状態コードは 42P01、名称は undefined_table です。 relation はテーブルだけを指しません。公式ドキュメントは、pg_class がインデックス、シーケンス、ビュー、実体化ビューなども扱い、これらをまとめて relation と呼ぶと説明しています。 読み分けの起点は、文言の中に点があるかどうかです。スキーマ名を自分で書いた場合は relation "public.users" does not exist と点付きになり、書かなかった場合は relation "users" does not exist と名前だけになります。前者は指定したスキーマの中で見つからず、後者は検索経路をたどって見つからなかったという意味です。 そして、この文言は「無い」と「見えない」を区別しません。権限が足りずに検索経路から外れたスキーマも、存在しないスキーマも同じ結果です。 最初に確認すること 今の接続がどこを見ているかを確認します。 SELECT current_database(), current_user; SHOW search_path; 初期状態の検索経路は "$user", public です。先頭は利用者名と同じスキーマを指します。 次に、対象の所在を調べます。 SELECT n.nspname, c.relname, c.relkind FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 'users'; 行が返らなければ、このデータベースには登録されていません。返るのに参照できないなら、スキーマか綴りの問題です。 原因別の確認方法と解決策 原因1：対象が検索経路に入っていないスキーマにある 上の照会で nspname が public 以外になっている場合です。修飾しない参照は検索経路を順にたどり、最初に一致したものを使います。経路に入っていないスキーマの中身は参照できません。 対処は、呼び出し側で修飾するか経路へ加えるかです。 SELECT * FROM app.users; SET search_path TO app, public; SET はそのセッションの間だけ有効です。毎回同じ状態にしたい場合は、ロールやデータベースへ既定値を設定します。 原因2：スキーマへの USAGE 権限が無い 同じクエリが、利用者を変えると成功する場合です。公式ドキュメントは、自分が所有していないスキーマの中身へ既定では触れられず、所有者が USAGE 権限を与える必要があると説明しています。 ...

結論 connection refused は PostgreSQL が生成した文言ではありません。接続先の OS が接続要求を拒んだときの ECONNREFUSED を、libpq が strerror() の結果としてそのまま転記したものです。実装では、接続に失敗した箇所で SOCK_STRERROR(errorno, ...) の結果を先頭に置き、その後ろに助言の1行を足しています。 この由来から、確認すべき範囲がほぼ確定します。要求は指定した相手とポートまで届いており、そこで待ち受けている相手がいなかったという意味です。したがって、パスワード、pg_hba.conf、ロール、データベース名は一切関係ありません。これらの問題であれば、接続自体は成立したうえで PostgreSQL 側の FATAL が返ります。 見るべきは3つです。サーバーが動いているか、待ち受けている住所に自分が届いているか、ポート番号が合っているかです。 エラーが発生する処理段階 クライアントが接続を確立するまでには段階があります。connection refused は最初の段階、つまり connect() の呼び出しで止まっています。 第一段階は接続先の決定です。libpq は host から名前を引き、hostaddr があればそちらを使います。第二段階が実際の接続で、ここで拒否されると connection refused になります。第三段階が起動時のやり取りで、データベース名と利用者名を送ります。第四段階が認証で、pg_hba.conf の照合とパスワードの確認が行われます。 第二段階で止まっているということは、PostgreSQL のプロセスがこの要求を一度も見ていないということです。サーバーのログにも何も残りません。ログを探しても記録が無いのは異常ではなく、この段階で止まっている証拠です。 最初に確認すること まず、どの相手のどのポートに対して拒否されたのかを、エラー文からそのまま読み取ります。 psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections? 括弧の中は、名前を引いた結果の実際の住所です。ここが ::1 になっているのに待ち受けが IPv4 だけ、という食い違いはよく起きます。localhost は環境によって IPv6 を先に返すためです。 ...

結論 password authentication failed for user "app" は、原因を意図的に伏せた文言です。実装では、pg_hba.conf の照合方式が password・md5・scram-sha-256 のいずれかであれば、失敗の中身にかかわらずこの1文が返ります。 伏せる理由は実装のコメントに書かれています。パスワードの取得に失敗しても、利用者が存在しないことをクライアントに悟らせないために、認証の手順を最後まで進めるという作りです。したがって、存在しないロール名で接続しても、パスワードを間違えた場合とまったく同じ文言が返ります。 本当の理由は errdetail_log() で出力されており、これはサーバーのログにだけ書かれます。クライアントには届きません。しかも、この詳細には照合に使われた pg_hba.conf の経路と行番号、その行の原文まで含まれています。 つまり最初にすべきことはパスワードの再入力ではなく、サーバーのログを開くことです。 エラーが発生する処理段階 このエラーは接続が成立したあとに出ます。手前の段階はすべて通っています。 まず TCP または Unix ドメインソケットの接続が確立します。ここで拒まれれば connection refused になり、この文言は出ません。次に起動時のやり取りで、データベース名と利用者名がサーバーへ送られます。 続いて pg_hba.conf の照合です。上から順に、接続の種類・データベース・利用者・接続元を見て、最初に一致した行が採用されます。一致する行が1つも無ければ no pg_hba.conf entry for host になります。 一致した行の方式がパスワード系であれば、ここで確認が行われます。失敗するとこの文言が返ります。データベースの存在確認や接続権限の確認は、さらに後の段階です。 最初に確認すること クライアント側の表示には、状態コード以上の情報はありません。 psql: error: connection to server at "db.internal" (10.0.1.5), port 5432 failed: FATAL: password authentication failed for user "app" サーバーのログを開くと、同じ時刻に DETAIL が並んで出ています。 sudo tail -n 50 /var/log/postgresql/postgresql-16-main.log 出力はこの形になります。 FATAL: password authentication failed for user "app" DETAIL: Password does not match for user "app". Connection matched file "/etc/postgresql/16/main/pg_hba.conf" line 96: "host all all 10.0.1.0/24 scram-sha-256" 1行目の DETAIL が本当の理由です。実装で定義されている文言は6種類で、Role "app" does not exist.、User "app" has no password assigned.、User "app" has an expired password.、User "app" has a password that cannot be used with MD5 authentication.、Password does not match for user "app".、Password of user "app" is in unrecognized format. です。どれが出ているかで、次に見る場所が確定します。 ...