はじめに
PostgreSQLを触り始めると、「psqlでログインしようとしたら認証エラーになった」「日本語が正しく保存できない」といったつまずきに出会うことがあります。原因の多くは、PostgreSQL特有の認証方式と文字コード設定の仕組みを知らないことにあります。本記事ではこの2つを整理します。
PostgreSQLの認証方式
PostgreSQLへの接続を許可するかどうかは、pg_hba.conf(host-based authentication)という設定ファイルで制御されています。このファイルには、「どの接続方法・どのユーザー・どのデータベースに対して、どの認証方式を使うか」がルールとして記述されています。
代表的な認証方式は次の通りです。
| 方式 | 内容 |
|---|---|
peer |
OSのユーザー名とPostgreSQLのロール名が一致する場合にのみ許可する。Unixドメインソケット経由のローカル接続でのみ使える |
scram-sha-256 |
チャレンジ・レスポンス方式のパスワード認証。パスワードそのものをネットワーク上に流さずに検証できる、現在推奨されている方式 |
md5 |
旧来のパスワードハッシュ方式。scram-sha-256より脆弱とされ、現在は非推奨 |
trust |
無条件に接続を許可する(パスワード不要)。開発用途以外では基本的に使うべきでない |
peer認証でハマりやすいポイント
Linux上でPostgreSQLをインストールした直後、ローカル接続(Unixドメインソケット経由)にはpeer認証が設定されていることが多くあります。この方式は「今ログインしているOSのユーザー名」と「接続しようとしているPostgreSQLのロール名」が一致していることを要求します。
# rootユーザーのまま実行すると、OSユーザー名(root)とロール名(postgres)が一致せず失敗する
$ psql -U postgres
psql: error: FATAL: Peer authentication failed for user "postgres"
これを解決するには、OSユーザー自体をpostgresに切り替えてからpsqlを実行します。
su - postgres
psql
# または1コマンドで
sudo -u postgres psql
scram-sha-256でのパスワード認証エラー
別のホストから-hオプションでネットワーク経由で接続する場合はpeer認証が使えないため、通常はパスワードベースのscram-sha-256が使われます。この場合の認証エラー(password authentication failed)は、設定ミスというより単純な入力パスワードの間違いであることが多いです。
パスワードを忘れた場合は、管理者権限で接続し直してパスワードをリセットできます。
ALTER USER myuser WITH PASSWORD '新しいパスワード';
特殊文字(' " \ $等)を含むパスワードはシェルやアプリの設定ファイル経由での入力ミスを誘発しやすいため、まず英数字のみの単純なパスワードで疎通確認してから本番用の値に変更する、という切り分け方法も有効です。
文字コード(ENCODING/LOCALE)の基礎
CREATE DATABASEとテンプレート
PostgreSQLで新しいデータベースを作成するCREATE DATABASEは、実は指定がない限り既存のテンプレートデータベース(template1)の設定を継承します。このテンプレートの文字コード・照合順序(LOCALE)がそのまま新しいデータベースに引き継がれるため、環境によっては意図しない文字コードでデータベースが作られてしまうことがあります。
-- ENCODING/LOCALEを省略すると、template1の設定を継承してしまう
CREATE DATABASE mydb OWNER myuser;
環境によっては、この結果SQL_ASCII(文字コードの整合性検査をほぼ行わない、後方互換のための特殊なエンコーディング)になってしまうことがあり、この状態で日本語などのマルチバイト文字を保存すると、データの破損や検索の不具合につながる恐れがあります。
明示的にUTF-8で作成する
これを避けるには、ENCODING・LOCALE(またはLC_COLLATE/LC_CTYPE)を明示的に指定し、テンプレートにはtemplate0を指定します。
CREATE DATABASE mydb
OWNER myuser
ENCODING 'UTF8'
LC_COLLATE 'C.UTF-8'
LC_CTYPE 'C.UTF-8'
TEMPLATE template0;
template0は、ユーザーによる変更が一切加えられていない「まっさらな」テンプレートであり、template1と異なり、指定したENCODING・LOCALEと矛盾するデータを含んでいる心配がありません。公式ドキュメントでも、ENCODING・LOCALEをtemplate1と異なる値にしたい場合はtemplate0を指定する必要があると明記されています。
作成後の確認
作成後は、psqlの\lコマンドでデータベース一覧とその文字コード設定を確認する習慣をつけておくと安心です。
\l
List of databases
Name | Owner | Encoding | Collate | Ctype |
-----------+----------+----------+-------------+-------------+
mydb | myuser | UTF8 | C.UTF-8 | C.UTF-8 |
まとめ
| 用語 | 内容 |
|---|---|
pg_hba.conf |
接続元・ユーザー・DBごとに認証方式を定義する設定ファイル |
peer認証 |
OSユーザー名とロール名の一致で認証するローカル専用の方式 |
scram-sha-256 |
パスワードを安全にやり取りする、現在推奨されているパスワード認証方式 |
template0/template1
|
DB作成時のひな形。template1はカスタマイズされている可能性があるため、ENCODING/LOCALEを明示指定する場合はtemplate0を使う |
SQL_ASCII |
文字コード検査をほぼ行わない特殊なエンコーディング。意図せずこれになるとマルチバイト文字の扱いで問題が起きやすい |