postgres tips: enable md5 authentication

step1. Add user

postgres@maniac:~$ createuser test
新しいロールをスーパーユーザとしますか? (y/n) n
新しいロールにデータベース作成権限を与えますか? (y/n) y
新しいロールにロールを作成する権限を与えますか? (y/n) y

step2. Set password for user

postgres@maniac:~$ psql
psql (8.4.8)
"help" でヘルプを表示します.

postgres=# alter user postgres with encrypted password 'test';
ALTER ROLE

step3. Edit pg_hba.conf (hba: host based authentication)
[default configuration]

# Database administrative login by UNIX sockets
local   all         postgres                          ident

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

[configuration enables md5 authentication for user 'postgres']

# Database administrative login by UNIX sockets
local   all         postgres                          ident
local   all         test                              md5

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

step4. Restart postgresql

sudo /etc/init.d/postgresql-8.4 restart
* Restarting PostgreSQL 8.4 database server    [ OK ]
yaboo@maniac:~$ createdb -U test sampledb
パスワード: 
yaboo@maniac:~$ psql -U test sampledb
ユーザ test のパスワード: 
psql (8.4.8)
"help" でヘルプを表示します.

sampledb=>