Cloud9にPostgRESTをインストール（PostgRESTをお題にJSON Web Tokenを学ぶ②）

#はじめにお試し感想の方から。

![](https://github.com/begriffs/postgrest/raw/master/static/logo.png)

*※図の出典:　https://github.com/begriffs/postgrest

PostgreSQLのJSON REST I/Fをインターネット上に公開可能なPostgRESTをcloud9で試しはじめている（PostgRESTの概説は[こちら](http://qiita.com/biotist/items/5e1e3b0fdb4ef4732158)）。商用でも使われているPostgREST、ローカルで試す分にはけっこうお気軽。

jqと組み合わせて使うとこんな感じになる。
> todosテーブルでidカラムが2　以上をselectした結果をresponse.json　として保存する場合:

```sh
$ curl http://localhost:3000/todos?id=gt.2 | jq '.' > response.json
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   144    0   144    0     0   1240      0 --:--:-- --:--:-- --:--:--  1252
```

レスポンスのjson:

```response.json
[
  {
    "id": 3,
    "done": false,
    "task": "IoTシステムの新規開発",
    "due": null
  },
  {
    "id": 4,
    "done": false,
    "task": "勝利の女神と商談。",
    "due": null
  }
]
```

PostgreSQL + PostgRESTの第一感、これなんてmongodb、という感じ(これなんてmongolabという方が近いかな)。

ただ、PostgreSQLはSQLデータベースにしてXMLデータベースでもJSONデータベースでもあるので、SQL視点では
PostgreSQL + PostgREST≒mongolab > mongodb
ではなく、
**PostgreSQL + PostgREST > mongolab > mongodb**
という感じだろう　（こんな比較をするのはSQLに何らかのメリットを感じている人だろうし）。

そして、速度面やスケーラビリティが絡むシナリオでは、キャッシュにKVSのredisや列指向のcassandraを用いて
**PostgreSQL + PostgREST +　redisdb or cassandra >> チューニングされていないNoSQL**
としていけるのではないかな、と思っている。
mongodbが便利な場合が多々あるのを認めるにしても、PostgreSQL + PostgREST +　redisdb(or cassandra)を使いこなせれば一生ものの知識になるのではないかと期待。

※PostgreSQLとredisdb(or cassandra)の両方をそこそこ使ったことがあることが前提だけれど。

ということで、以下、PostgRESTの導入記。

#1.PostgRESTバイナリの展開

xz形式で配布されているpostgrestのバイナリをダウンロードして展開する([参考](https://postgrest.com/en/v4.3/install.html))。
Cloud9の場合、64ビット版Ubuntu向けのファイルを落としてくる。

```sh
wget https://github.com/begriffs/postgrest/releases/download/v0.4.3.0/postgrest-v0.4.3.0-ubuntu.tar.xz
unxz postgrest-v0.4.3.0-ubuntu.tar.xz 
tar xvf postgrest-v0.4.3.0-ubuntu.tar
rm postgrest-v0.4.3.0-ubuntu.tar 
```

cloud9の容量は限られるので、tarファイルはrmしておいた。

##(推奨事項)PostgreSQLを9.5以上にアップデート
PostgRESTは、セキュリティ上の観点からPostgreSQLのバージョン9.5以上を推奨している。
Cloud9ではディフォルトがバージョン9.3だが、　[Cloud9のPostgresをアップデート](http://qiita.com/biotist/items/a48023c2740ff2e42995)
に書いたように一手間かければ9.5以降にアップデートできる。

#2.PostgREST設定ファイルの作成

tarしてできたpostgrest　をオプションなしで実行するとガイダンスが出て来る:

```sh
~ $ ./postgrest
Missing: FILENAME

Usage: postgrest FILENAME
  PostgREST 0.4.3.0 / create a REST API to an existing Postgres database

Available options:
  -h,--help                Show this help text
  FILENAME                 Path to configuration file

Example Config File:
  db-uri = "postgres://user:pass@localhost:5432/dbname"
  db-schema = "public"
  db-anon-role = "postgres"
  db-pool = 10
  
  server-host = "*4"
  server-port = 3000
  
  ## base url for swagger output
  # server-proxy-uri = ""
  
  ## choose a secret to enable JWT auth
  ## (use "@filename" to load from separate file)
  # jwt-secret = "foo"
  # secret-is-base64 = false
  
  ## limit rows in response
  # max-rows = 1000
  
  ## stored proc to exec immediately after auth
  # pre-request = "stored_proc_name"

```

これと[本家のチュートリアル](https://postgrest.com/en/v4.3/tutorials/tut0.html)を参考に自前のconfを作ってみる:

```mypostgrest.conf
  db-uri = "postgres://postgres:adminpass@localhost:5432/db1"
  db-schema = "api"
  db-anon-role = "postgres"
  db-pool = 10
  
  server-host = "*4"
  server-port = 3000
```

まずはローカルでのお試しなので、なんでもできちゃうpostgresユーザーにdb1データベースのapiスキーマへの接続を許すこととした。
    
#3.PostgreSQLの設定
    
psqlでPostgreSQLに接続し、REST接続用の設定を行っていく。
ほぼ[チュートリアル](https://postgrest.com/en/v4.3/tutorials/tut0.html)通りなので要点を中心に。

はじめに必要なのは接続ユーザー（ここではpostgresユーザー）のパスワード設定と、データベースの作成。

```sql
alter role postgres with password 'adminpass';
create database db1;
```
    
ついで、db1データベース名内でスキーマ名を設定し、スキーマ配下にテーブルを作る。

```sql
db1=# create schema api;
db1=# create table api.todos (
  id serial primary key,
  done boolean not null default false,
  task text not null,
  due timestamptz
);
```
お試しデータは日本語を含めてみた。

```sql

db1=# insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back'), ('IoTシステムの新規開発');
```                

#4.PostgREST経由での接続。

以上で準備完了なので、PostgRESTを起動する。次いで、psqlも立ち上げておくとお試しには便利。

```sh
$ ./postgrest postgrest.conf &
$ psql db1
```
別ターミナルから、curlでGET/POSTしてみる。

```sh
$ curl http://localhost:3000/todos
[{"id":1,"done":false,"task":"finish tutorial 0","due":null},{"id":2,"done":false,"task":"pat self on back","due":null},{"id":3,"done":false,"task":"IoTシステムの新規開発","due":null}]

$curl http://localhost:3000/todos -X POST \
     -H "Content-Type: application/json" \
     -d '{"task": "勝利の女神と商談。"}'
```     

psqlが立ち上がっている側はこうなる。

```
 b1=# select * from api.todos;
 id | done |         task          | due 
----+------+-----------------------+-----
  1 | f    | finish tutorial 0     | 
  2 | f    | pat self on back      | 
  3 | f    | IoTシステムの新規開発 | 
(3 rows)

db1=# 127.0.0.1 - - [16/Sep/2017:12:05:42 +0000] "GET / HTTP/1.1" 200 - "" "curl/7.35.0"
127.0.0.1 - - [16/Sep/2017:12:06:28 +0000] "GET /todos HTTP/1.1" 200 - "" "curl/7.35.0"
127.0.0.1 - - [16/Sep/2017:12:07:54 +0000] "POST /todos HTTP/1.1" 201 - "" "curl/7.35.0"

 db1=# select * from api.todos;
 id | done |         task          | due 
 
----+------+-----------------------+-----
  1 | f    | finish tutorial 0     | 
  2 | f    | pat self on back      | 
  3 | f    | IoTシステムの新規開発 | 
  4 | f    | 勝利の女神と商談。    | 
(4 rows)
```

POSTの結果もすぐに分かって良さげだけれど、ひとつ注意点があって、おなじメッセージを複数回POSTすると、その回数分だけPostgresのテーブルに行が追加される。これはちょっと怖い(POST権限を認めるシチュエーションは絞り込んだほうが良いね)。

#感想
webに公開する前にセキュリティ面はちゃんと考えておかなければな、と思いつつも、期待を感じて次に続く。

## 参考
PostgREST4.0のお試し記事:
https://ekzemplaro.org/wordpress/postgrest/

PostgreSQL データベースにおけるトータル・セキュリティー
https://www.ibm.com/developerworks/jp/opensource/library/os-postgresecurity/index.html