1. ibara1454

    Posted

    ibara1454
Changes in title
+UbuntuでPostgreSQLをインストールからリモートアクセスまでの手順
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,205 @@
+PostgreSQLサーバの立ち上げに少しハマりましたので、メモしておきます。
+
+OS: Ubuntu14.04 LTS
+
+#インストール
+
+最初はPostgreSQLをインストールします。
+普通に`apt-get`コマンドを使えばインストールできますので、特筆すべきことはありません。
+
+```console
+$ sudo apt-get update
+$ sudo apt-get install -y postgresql-9.3
+```
+
+インストールが出来ましたら、`/etc/init.d/`に起動用のスクリプトが作成されるはずです。
+`start`コマンドでサーバを立ち上げられますが、このままでは接続できません。
+
+```console
+$ /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のインストールと同時に設定されたもので、<code>createuser <em>name</em></code>を入力しただけで名前が<em>name</em>のロールが作成されます。
+
+ここでは一つ目の方法だけを説明したいと思います。
+
+最初のインストールが出来たら、postgresというユーザかつ同名のロールが作られます。
+デフォルトでは、postgresのロールはPostgreSQLにとってのスーパユーザであり、ロール設定を変える権限を持ちます。
+
+では、postgresアカウントに切り替えて、PostgreSQLサーバにログインします。
+
+```console
+$ 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だけです。
+
+```console
+postgres=# \du
+ List of roles
+ Role name | Attributes | Member of
+-----------+------------------------------------------------+-----------
+ postgres | Superuser, Create role, Create DB, Replication | {}
+```
+
+他のロールを作らず、スーパユーザのpostgresでDBを作り、運用することは十分可能ですが、ただスーパーユーザでログインすることはセキュリティ的にあまり好ましくないので、やはりスーパーユーザ以外のロールを作ったほうが何かと便利です。
+
+新しくロールを作るには<code>CREATE ROLE <em>name</em>;</code>または<code>CREATE USER <em>name</em>;</code>のコマンドを使えば作成されます。
+作成したあとでもロールの属性を変更することはできますが、作成時に以下のキーワードを使うことで属性を付加することができます。
+
+* LOGIN
+* SUPERUSER
+* CREATEDB
+* CREATEROLE
+* REPLICATION
+* PASSWORD
+
+属性の詳しい説明はここにあります。
+https://www.postgresql.jp/document/9.3/html/role-attributes.html
+
+手始めにhellopsqlという名のロールを作成して、`LOGIN CREATEDB PASSWORD`属性を追加します。
+
+```console
+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`コマンドを使えば確認することができます。
+
+```console
+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なんとかのエラーが出てきましたら、もう一手間を加えてクライアント認証の設定を変える必要があります。
+
+```console
+$ 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
+
+```console
+$ vi /etc/postgresql/9.3/main/ph_hba.conf
+```
+
+ちなみに私の設定ファイルはこんな感じです。
+
+``` /etc/postgresql/9.3/main/ph_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`でサーバを再起動しましょう。
+
+
+```console
+$ /etc/init.d/postgresql restart
+ * Restarting PostgreSQL 9.3 database server [ OK ]
+```
+
+# リモートからの接続
+
+デフォルトの設定ではリモート接続はできません。
+リモートで運用したい場合は/etc/postgresql/9.3/main/postgresql.confの設定ファイルを変更する必要があります。
+
+```console
+$ vi /etc/postgresql/9.3/main/postgresql.conf
+```
+
+listen_addressesの欄を探して、それを下のように変えましょう。
+
+``` /etc/postgresql/9.3/main/postgresql.conf
+listen_addresses='*'
+```
+
+設定を変更したら`/etc/init.d/postgresql restart`で再起動します。
+
+```console
+$ /etc/init.d/postgresql restart
+ * Restarting PostgreSQL 9.3 database server [ OK ]
+```