データベースへの接続にユーザ認証させる方法
（同じサーバーから接続する場合）

Modified: 11 September 2004

データベースへの接続には、ネットワークからの接続（ＴＣＰ/ＩＰソケット）する方法と、同じサーバーから接続（ＵＮＩＸソケット）する方法がありますが、ここでは、後者のデータベースと同じサーバーからＵＮＩＸソケットを使って接続するときのユーザ認証の方法です。

管理方法は、管理者が全体を管理し、一般ユーザは、特定のデータベースのみアクセスできる環境を前提とする。
一般ユーザには、パスワードの変更はできるが、新しいDBの作成や新しいユーザの登録はできない。

認証方法の設定
パスワード付ユーザーの作成
特定のユーザしか操作できないデータベースの作成と実験

認証方法の設定

"/var/lib/pgsql/data" 内の、"pg_hba.conf" を以下のように変更します。

中身はほとんどコメントで、有効になっているのは以下の１行だけです。

local   all   ident   sameuser

データベースと同じサーバー（"local"）から、すべてのデータベース（" all"）に、IDENTを使って認証する設定です。
IDENTは、よく知りませんが、ここでは使わないので、知らなくてもとりあえずOKとします。

　誰でも好きなようにできるようにする設定

データベースと同じサーバー（"local"）から、すべてのデータベース（" all"）に、誰でも信頼（"trust"）して接続する設定です。

local   all   trust

　パスワード認証させる方法

データベースと同じサーバー（"local"）から、すべてのデータベース（" all"）に、誰でも信頼（"trust"）して接続する設定です。

local   all   md5

　パスワードには、以下の３つがあります。要は、"md5"がベストです。

password	平文でパスワードが送信される
crypt	平文で保管されるが、パスワードは暗号化されて送信される
md5	暗号化されて保管され、パスワードが暗号化されて送信される（7.2以降でサポート）

設定変更したら有効にするために再起動しますが、パスワードを設定していない場合は、スーパーユーザである"postgres"ユーザでも接続できなくなりますので、再起動の前に、"postgres"ユーザのパスワードを設定します。

# su - postgres $ psql template1 Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=# alter user postgres with encrypted password 'password'; ALTER USER template1=# \q $

以下のコマンドで、再起動します。

$ pg_ctl restart waiting for postmaster to shut down......done postmaster successfully shut down postmaster successfully started bash-2.05a$ DEBUG: database system was shut down at 2004-09-11 15:03:44 JST DEBUG: checkpoint record is at 0/109724 DEBUG: redo record is at 0/109724; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 89; next oid: 16556 DEBUG: database system is ready $

再起動後、再度接続し、パスワードが要求されることを確認してください。

$ psql template1 Password: ******** Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=# \q $

パスワード付ユーザーの作成

パスワードは、DB内に持つ方法と、外部ファイルに持つ方法があります。

DB内に持つ場合、ユーザは自分のパスワードを自由に変更できます。外部に持つ場合、すべてのパスワードは管理者しか変更できませんし、"md5"を使う場合は使えません。

したがって、ここでは、DB内に持つ方法で、"md5"を使います。

以下のコマンドで、DBに接続し、ユーザー（"tomo"）を作成します。

このとき、暗号化パスワードを指定し、データベースを作る権利や、新しいユーザを作る権利はなしにしました。

$ psql template1 Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=# create user tomo encrypted password 'password' nocreatedb nocreateuser; CREATE USER template1=# \q $

作成した新しいユーザ（"tomo"）で接続してみます。

プロンプトが、"#" でなく、">" になっています。

$ psql -U tomo template1 Password: ******** Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=>

特定のユーザしか操作できないデータベースの作成と実験

　準備

実験のため、２つのユーザ（"tomo","tomo2"）と、２つのデータベース（"testdb","testdb2"）を作ります。"testdb"は"tomo"専用、"testdb2"は"tomo2"専用に設定します。

まずデータベースを作ります。

$ createdb testdb Password: ******** CREATE DATABASE $ createdb testdb2 Password: ******** CREATE DATABASE $

"tomo2"というユーザを追加作成します。

$ psql template1 Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit template1=# create user tomo2 encrypted password 'password' nocreatedb nocreateuser; CREATE USER template1=# \q $

"/var/lib/pgsql/data" 内の、"pg_hba.conf" を以下のように変更します。

local    testdb     md5    testdb.userlist local    testdb2    md5    testdb2.userlist

"testdb"用のユーザリスト（"testdb.userlist"）を作成します。"postgres"も入れておきました。

postgres tomo

"testdb2"用のユーザリスト（"testdb2.userlist"）を作成します。

tomo2

以下のコマンドで、再起動します。

$ pg_ctl restart waiting for postmaster to shut down......done postmaster successfully shut down postmaster successfully started bash-2.05a$ DEBUG: database system was shut down at 2004-09-11 15:03:44 JST DEBUG: checkpoint record is at 0/109724 DEBUG: redo record is at 0/109724; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 89; next oid: 16556 DEBUG: database system is ready $

準備完了したので、動作テストしてみます。

　"testdb"に接続

$ psql -U tomo testdb Password: ******** Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit testdb=> \q $

上記のように、"tomo"は接続できるが、"tomo2"は以下のように拒否されます。

$ psql -U tomo2 testdb Password: ******** verify_password: user 'tomo2' not found in password file. FATAL 1: Password authentication failed for user "tomo2" psql: FATAL 1: Password authentication failed for user "tomo2" $

　"testdb2"に接続

$ psql -U tomo testdb2 Password: ******** verify_password: user 'tomo' not found in password file. FATAL 1: Password authentication failed for user "tomo" psql: FATAL 1: Password authentication failed for user "tomo" $

上記のように、"tomo"は接続できないが、"tomo2"は以下のように接続できます。

$ psql -U tomo2 testdb2 Password: ******** Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit testdb2=> \q $