BASIC 認証と PostgreSQL
〜 .htpasswd の代わりに SQL 〜
2021-08-08 作成 福島
TOP > tips > basic-pgsql
0. インストール要件
項目内容備考
OSCentOS 7.9.2009手元で稼働中の Linux。
Web サーバApache 2.4.6CentOS7 に付属のもの。
SQL サーバPostgreSQL 9.2.24
BASIC 認証ディレクトリ/var/www/html/member/-
URLhttp://example.jp/member/
データベース名前BasicLogin認証時の DB ユーザとして管理用 ID を使用するのもどうかと思うけど、どうせ CGI 等でメンバを管理するだろうから、これ (→ 4-3 の DBDParams) でもいいかな。
管理用 IDauthadmin
管理用パスワードadminpassword
テーブル名htpasswd
メンバ1IDuser01-
パスワードpassword01
1. PostgreSQL のインストール
1-1. PostgreSQL とライブラリをインストールする。
$ su
# yum install -y postgres postgres-server
# yum install -y postgresql-contrib    # SQL 拡張 'pgcrypto' を使うため。
# yum install -y apr-util-pgsql    # ドライバ 'DBDriver pgsql' を使うため。

# psql --version
psql (PostgreSQL) 9.2.24
1-2. PostgreSQL を初期化する。
# postgresql-setup initdb
# ls -ld /var/lib/pgsql/data/
1-3. PostgreSQL を設定する。
# su - postgres
/var/lib/pgsql$ vim /var/lib/pgsql/data/pg_hba.conf
ログイン条件を下記にする。
  • ユーザ postgres を peer (Linux の認証と同じ) にする。
  • すべてのユーザを md5 (データベースに入れたパスワードに従う) にする。
  • IPv6 で接続するホスト (localhost) のユーザを md5 にする。
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
#local   all             all                                    peer
local   all            postgres                          peer
local   all            all                               md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
#host    all             all            ::1/128                 ident
host    all            all          ::1/128              md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
/var/lib/pgsql$ exit
#
1-4. PostgreSQL を起動する。
# systemctl enable postgresql
# systemctl start postgresql
# systemctl status postgresql

# exit
$
2. 管理用ユーザとデータベースの作成
2-1. 管理用ユーザを作成する。
$ su
# su - postgres
/var/lib/pgsql$ createuser authadmin
/var/lib/pgsql$ echo "ALTER ROLE authadmin PASSWORD 'adminpassword'" | psql
/var/lib/pgsql$ echo "SELECT rolname,rolpassword FROM pg_authid" | psql
2-2. データベースを作成する。
/var/lib/pgsql$ createdb BasicLogin -O authadmin
・削除するときは "dropdb BasicLogin" を実行する。
SQL で crypt(), gen_salt() を使えるようにする。
/var/lib/pgsql$ echo "CREATE EXTENSION pgcrypto" | psql BasicLogin

テーブルを作成する。
/var/lib/pgsql$ psql BasicLogin authadmin << EOF
CREATE TABLE htpasswd (id text primary key, pw text not null) ;
GRANT SELECT,UPDATE,INSERT,DELETE ON htpasswd TO authadmin ;
EOF
ユーザ authadmin のパスワード:adminpassword⏎
/var/lib/pgsql$
3. メンバを登録
3-1. メンバの ID, パスワードをテーブルに登録する。
/var/lib/pgsql$ psql BasicLogin authadmin << EOF
INSERT INTO htpasswd VALUES('user01',crypt('password01',gen_salt('md5'))) ;
SELECT * FROM htpasswd ;
EOF
ユーザ authadmin のパスワード:adminpassword⏎
INSERT 0 1
   id   |                 pw
--------+------------------------------------
 user01 | $1$H/ikVAfX$.1weizvGOvGOykYCGhCTU0
(1 行)
メンバのパスワードを変更する場合は、以下を実行する。
/var/lib/pgsql$ echo "UPDATE htpasswd SET pw = crypt('AnotherPassword',gen_salt('md5')) WHERE id = 'user01'" | psql BasicLogin authadmin

メンバを削除する場合は、以下を実行する。
/var/lib/pgsql$ echo "DELETE FROM htpasswd WHERE id = 'user01'" | psql BasicLogin authadmin

/var/lib/pgsql$ exit
# exit
$
4. Apache の設定を変更
4-1. 通常ユーザからデータベースがアクセスできることを確認する。
$ PGPASSWORD=adminpassword psql --host=localhost --dbname=BasicLogin --user=authadmin
psql (9.2.24)
"help" でヘルプを表示します.

BasicLogin=> \q⏎
4-2. BASIC 認証で制限するディレクトリを作成する。
$ su
# mkdir /var/www/html/member
4-3. Apache の設定ファイルを編集する。
# vim /etc/httpd/conf/httpd.conf
Apache の設定ファイルに以下を追加する。
DBDriver pgsql
DBDParams "host=localhost dbname=BasicLogin user=authadmin password=adminpassword"
DBDMin 1        # 接続の最小数
DBDMax 10       # 接続の最大数
DBDKeep 2       # 最大持続接続数
DBDExptime 300  # アイドル接続のキープアライブ時間



<Directory /var/www/html/member>
    AuthType Basic
    AuthName "Member's area"
    AuthBasicProvider dbd
    Require valid-user
    AuthDBDUserPWQuery "SELECT pw FROM htpasswd WHERE id=%s"

    Options Indexes FollowSymLinks Includes ExecCGI    # ← 必要に応じて追加する。
</Directory>
PQconnectdb では各パラメータの区切りを空白で受理するので、DBDParams の引数はダブルクォートでひとつの文字列にしている。(DBDParams に渡せる引数は 1 つだけ)
設定の文法チェックをする。
# apachectl configcheck
4-4. Apache を起動する。
# systemctl enable httpd
# systemctl start httpd
# systemctl status httpd
4-5. SELinux にポートアクセスの許可を設定する。(もし必要なら)
# setsebool -P httpd_can_network_connect 1
SELinux によってポートアクセスが制限されている場合は、Apache のエラーログに下記が出力される。
Internal error: AH00629: Can't connect to pgsql: could not connect to server: Permission denied
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
    could not connect to server: Permission denied
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?
# exit
$