PostgreSQL8.3 クライアント認証
PostgreSQL8.3 クライアント認証Windows上で構築した開発環境をLinuxへ移行している際に、PostgreSQLのDBにログインするときの挙動が、WindowsとLinuxとで違うことに気付きました。Windows上のPostgreSQLでは、DBにログインする時にパスワードを要求されますが、Linux上のDBではパスワード要求がなかったのです。調べてみるとpg_hba.confの設定が関係していました。以下はそのときに調べたメモです。Windowsのpg_hba.conf(デフォルト)# TYPE DATABASE USER CIDR-ADDRESS METHOD# IPv4 local connections:host all all 127.0.0.1/32 md5# IPv6 local connections:#host all all ::1/128 md5linux(CentOS)のpg_hba.conf(デフォルト)# TYPE DATABASE USER CIDR-ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all all 127.0.0.1/32 trust# IPv6 local connections:host all all ::1/128 trustpg_hba.confのコメント抜粋# PostgreSQL Client Authentication Configuration File# ===================================================## Refer to the "Client Authentication" section in the# PostgreSQL documentation for a complete description# of this file. A short synopsis follows.## This file controls: which hosts are allowed to connect, how clients# are authenticated, which PostgreSQL user names they can use, which# databases they can access. Records take one of these forms:## local DATABASE USER METHOD [OPTION]# host DATABASE USER CIDR-ADDRESS METHOD [OPTION]# hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION]# hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION]## (The uppercase items must be replaced by actual values.)## The first field is the connection type: "local" is a Unix-domain socket,# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.## DATABASE can be "all", "sameuser", "samerole", a database name, or# a comma-separated list thereof.## USER can be "all", a user name, a group name prefixed with "+", or# a comma-separated list thereof. In both the DATABASE and USER fields# you can also write a file name prefixed with "@" to include names from# a separate file.## CIDR-ADDRESS specifies the set of hosts the record matches.# It is made up of an IP address and a CIDR mask that is an integer# (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies# the number of significant bits in the mask. Alternatively, you can write# an IP address and netmask in separate columns to specify the set of hosts.## METHOD can be "trust", "reject", "md5", "crypt", "password", "gss", "sspi",# "krb5", "ident", "pam" or "ldap". Note that "password" sends passwords# in clear text; "md5" is preferred since it sends encrypted passwords.Linux上のDBでパスワード要求がなかった原因は、pg_hba.confで設定する認証方式が"trust"になっていることでした。"trust"が設定されていると、DBアカウントにパスワードを設定していても無条件でログインが出来てしまいます。pg_hba.confに異なる認証方式を設定したユーザーで確認してみました。# TYPE DATABASE USER CIDR-ADDRESS METHOD# IPv4 local connections:host all sample_user1 127.0.0.1/32 md5host all sample_user2 127.0.0.1/32 rejecthost all sample_user3 127.0.0.1/32 trusthost all all 127.0.0.1/32 md5# IPv6 local connections:#host all all ::1/128 md5ユーザー名:sample_user1、パスワード要求有りc:\dev>psql -U sample_user1 -d sample_databasePassword for user sample_user1:Welcome to psql 8.3.0, the PostgreSQL interactive terminal.Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quitsample_database=>ユーザー名:sample_user2、ログイン拒否c:\dev>psql -U sample_user2 -d sample_databasepsql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "sample_user2", database "sample_database", SSL offユーザー名:sample_user3、パスワード要求なしc:\dev>psql -U sample_user3 -d sample_databaseWelcome to psql 8.3.0, the PostgreSQL interactive terminal.Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quitsample_database=>21.1. pg_hba.confファイル第 19章データベースロールと権限