UbuntuでPostgreSQLをインストールからリモートアクセスまでの手順

PostgreSQLサーバの立ち上げに少しハマりましたので、メモしておきます。

OS: Ubuntu14.04　LTS

インストール

最初はPostgreSQLをインストールします。
普通にapt-getコマンドを使えばインストールできますので、特筆すべきことはありません。

$ sudo apt-get update
$ sudo apt-get install -y postgresql-9.3

インストールが出来ましたら、/etc/init.d/に起動用のスクリプトが作成されるはずです。
startコマンドでサーバを立ち上げられますが、このままでは接続できません。

$ /etc/init.d/postgresql start
 * Starting PostgreSQL 9.3 database server                 [ OK ]
$ /etc/init.d/postgresql status
9.3/main (port 5432): online
$ psql
psql: FATAL:  role "root" does not exist

ここではスーパユーザのrootでDBに接続していますので、ロールrootは存在しませんと怒られました。

ロールの設定

PostgreSQLは権限の管理にロール(role)が使われています。
ロールはUNIXのgroupに似ていて、個々のDBに対するアクセスまたは上書きの権限が設定されています。

そのロールを作成するには二通りのやり方があります。

ひとつはユーザpostgresでPostgreSQLにログインして、ロールを設定を変えます。

もう一つはシェルでcreateuserのコマンドを使います。
このコマンドはPostgreSQLのインストールと同時に設定されたもので、createuser nameを入力しただけで名前がnameのロールが作成されます。

ここでは一つ目の方法だけを説明したいと思います。

最初のインストールが出来たら、postgresというユーザかつ同名のロールが作られます。
デフォルトでは、postgresのロールはPostgreSQLにとってのスーパユーザであり、ロール設定を変える権限を持ちます。

では、postgresアカウントに切り替えて、PostgreSQLサーバにログインします。

$ su - postgres
$ psql
psql (9.3.5)
Type "help" for help.
postgres=#

ユーザ名とDB名を与えずにそのままpsqlコマンドを使えば、オペレーティングシステムのユーザと同名のユーザとDBが使われます。ですので上のpsqlのコマンドはpsql --username=postgres --dbname=postgresと同じ意味を持ちます。そしてrootと違ってpostgresというロールはありますので、今度は無事ログインできました。

今のログインした状態でSELECT rolname FROM pg_roles;または\duコマンドで、既存のロールを見ることができます。
デフォルトでは他のロールが存在しませんので、唯一のロールはスーパユーザのpostgresだけです。

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}

他のロールを作らず、スーパユーザのpostgresでDBを作り、運用することは十分可能ですが、ただスーパーユーザでログインすることはセキュリティ的にあまり好ましくないので、やはりスーパーユーザ以外のロールを作ったほうが何かと便利です。

新しくロールを作るにはCREATE ROLE name;またはCREATE USER name;のコマンドを使えば作成されます。
作成したあとでもロールの属性を変更することはできますが、作成時に以下のキーワードを使うことで属性を付加することができます。

• LOGIN
• SUPERUSER
• CREATEDB
• CREATEROLE
• REPLICATION
• PASSWORD

属性の詳しい説明はここにあります。
https://www.postgresql.jp/document/9.3/html/role-attributes.html

手始めにhellopsqlという名のロールを作成して、LOGIN CREATEDB PASSWORD属性を追加します。

postgres=# CREATE ROLE hellopsql LOGIN CREATEDB PASSWORD 'hello';
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 hellopsql | Create DB                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

これで名前がhellopsqlでパスワードがhelloのロールが作成されました。

早速hellopsqlのロールでログインしたいのですが、まだこのロールでアクセスできるDBが作られていませんので、次はDBの作成に入りたいと思います。

DBの作成

DBの作成にも二通りのやりかたがあります。

ひとつはPostgreSQLのシェルでCREATE DATABASEのコマンドを使えば作成できます。

もう一つはインストールと同時に設定されたシェルコマンドのcreatedbを使ってDBを作成します。

また、作成したDBを確認したい場合はPostgreSQLのシェルでSELECT datname FROM pg_database;または\lコマンドを使えば確認することができます。

postgres=# CREATE DATABASE hellopsql OWNER hellopsql;
CREATE DATABASE
postgres=# \l
                              List of databases
   Name    |   Owner   | Encoding  | Collate | Ctype |   Access privileges   
-----------+-----------+-----------+---------+-------+-----------------------
 hellopsql | hellopsql | SQL_ASCII | C       | C     | 
 postgres  | postgres  | SQL_ASCII | C       | C     | 
 template0 | postgres  | SQL_ASCII | C       | C     | =c/postgres          +
           |           |           |         |       | postgres=CTc/postgres
 template1 | postgres  | SQL_ASCII | C       | C     | =c/postgres          +
           |           |           |         |       | postgres=CTc/postgres
(4 rows)

これでロールとDBの準備が整えました。
あとでpsql --username=hellopsql --password --dbname=hellopsqlのコマンドを入力しただけで接続できるはずです。

もし下のようにPeerなんとかのエラーが出てきましたら、もう一手間を加えてクライアント認証の設定を変える必要があります。

$ psql --username=hellopsql --password --dbname=hellopsql             
Password for user hellopsql: 
psql: FATAL:  Peer authentication failed for user "hellopsql"

クライアント認証の設定

これはiptablesのようなものです。どのIPからどのユーザからの接続を許すか、何の認証方式を使うかなど様々な設定ができます。

デフォルトの設定では、ローカルからの接続方式はidentになっていますので、ログインに使ったユーザ名が今クライアントのオペレーティングシステムにおけるユーザ名と同一でなければなりません。
つまりhellopsqlで接続したいのなら、オペレーティングシステムにhellopsqlというユーザを作って、そのユーザで接続しなければなりません。

このデフォルト設定のままだといろいろ不便ですので、一度/etc/postgresql/9.3/main/ph_hba.confの設定を変更したいと思います。

詳しい説明を省略しますが、詳しく知りたい方は下のサイトを参考してください。
https://www.postgresql.jp/document/9.3/html/auth-pg-hba-conf.html
http://qa.atmarkit.co.jp/q/2604

$ vi /etc/postgresql/9.3/main/pg_hba.conf

ちなみに私の設定ファイルはこんな感じです。

/etc/postgresql/9.3/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# ローカルから且つスーパーユーザの接続はpeer認証を使います
local   all             postgres                                peer
# ローカルから且つスーパーユーザ以外の接続を無条件で通す
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# リモートから且つスーパーユーザの接続を拒否します
host    all             postgres        0.0.0.0/0               reject
# TCP connections by md5
host    all             all             0.0.0.0/0               md5

設定を変更したら/etc/init.d/postgresql restartでサーバを再起動しましょう。

$ /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server                             [ OK ]

リモートからの接続

デフォルトの設定ではリモート接続はできません。
リモートで運用したい場合は/etc/postgresql/9.3/main/postgresql.confの設定ファイルを変更する必要があります。

$ vi /etc/postgresql/9.3/main/postgresql.conf

listen_addressesの欄を探して、それを下のように変えましょう。

/etc/postgresql/9.3/main/postgresql.conf

listen_addresses='*'

設定を変更したら/etc/init.d/postgresql restartで再起動します。

$ /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server                             [ OK ]