30
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

PostgreSQLを操作するAPIを提供する、PostgRESTとPostGraphileを両方試してみた

Last updated at Posted at 2019-03-08

PostgREST、PostGraphileって何をするの?

  • PostgRESTは、WebからPostgreSQLを操作するRESTful APIを提供します。
  • PostGraphileは、WebからPostgreSQLを操作するGraphQL APIを提供します。

PostGraphileは、PostgRESTからインスピレーションを得て開発されたそうです。

この記事で行うこと

PostgRESTのチュートリアルに沿って、試しにPostgRESTとPostGraphileの両方を動かします。

動作環境

LaradockにPostgRESTとPostGraphileを組み込みました。
DockerのホストマシンはLinuxを想定しています。

※ Laradockとは

DockerでLaravelを動かすためのプロジェクトです。私はまだLaravelを使う予定がないにも関わらず、開発用にLaradockを使わせていただいております。Laravel以外の機能もてんこ盛りで、開発初期に手っ取り早く開発環境を構築するには便利です。

※ Laradock環境構築で参考にしたページ(感謝します)

Laravelの開発環境をLaradockを使って構築する

環境構築その1:PostgreSQL関連まで

Laradockのダウンロード

cd ~
mkdir work
cd work
git clone https://github.com/laradock/laradock.git

Docker環境の設定

cd laradock
cp env-example .env

PostgreSQL の設定(.envファイルを編集します)
※ 参考ページ:Laradockを用いてDocker + CentOS + Apache + PHP7 + PostgreSQLな環境を作る

~/work/laradock/.env
( ~/work/laradock/.env )

# PostgreSQLクライアントをインストールする
- WORKSPACE_INSTALL_PG_CLIENT=false
+ WORKSPACE_INSTALL_PG_CLIENT=true

# PostgreSQLドライバーをインストールする
- PHP_FPM_INSTALL_PGSQL=false
+ PHP_FPM_INSTALL_PGSQL=true

# PostgreSQLクライアントをインストールする
- PHP_FPM_INSTALL_PG_CLIENT=false
+ PHP_FPM_INSTALL_PG_CLIENT=true

# PostgreSQLドライバーをインストールする
- PHP_WORKER_INSTALL_PGSQL=false
+ PHP_WORKER_INSTALL_PGSQL=true

PostgreSQL接続情報は、.envファイルを確認すると以下の通り。

データベース: default
ユーザー名: default
パスワード: secret

proxy経由でインターネット接続している場合、workspaceのDockerfileで環境変数を追加設定しておく。これをしないと、build中にエラーでストップしました。

( ~/work/laradock/workspace/Dockerfile )

# Set Environment Variables
ENV DEBIAN_FRONTEND noninteractive
+ 
+ # proxy
+ ENV http_proxy 'http://proxy:8080'
+ ENV https_proxy 'http://proxy:8080'

Docker環境の構築

docker-compose up -d postgres pgadmin workspace

しばらく時間がかかります。正常終了したら、

docker-compose ps

以下のように表示されました。StateがUpになっているのを確認します。

           Name                          Command              State           Ports         
--------------------------------------------------------------------------------------------
laradock_docker-in-docker_1   dockerd-entrypoint.sh           Up      2375/tcp              
laradock_pgadmin_1            docker-entrypoint.sh pgadmin4   Up      0.0.0.0:5050->5050/tcp
laradock_postgres_1           docker-entrypoint.sh postgres   Up      0.0.0.0:5432->5432/tcp
laradock_workspace_1          /sbin/my_init                   Up      0.0.0.0:2222->22/tcp  

pgAdmin4を使うなら、ブラウザで http://localhost:5050/ にアクセスします。

pgAdmin4にログインするためのアカウント情報は、~/work/laradock/pgadmin/Dockerfile を参照すると以下の通り。

user: pgadmin4@pgadmin.org
password: admin

pgAdmin4にログイン後に「新しいサーバを追加」から接続を新規作成するための情報は、以下の通り。

名称: 任意
ホスト名/アドレス: postgres
ポート番号: 5432
データベースの管理: postgres
ユーザ名: default
パスワード: secret

各ソフトウェアのバージョンを確認してみます。
psqlのバージョンは、

docker-compose run postgres psql -V

psql (PostgreSQL) 11.2

workspaceのOSのバージョンは、

docker-compose run workspace cat /etc/issue

Starting laradock_docker-in-docker_1 ... done
Ubuntu 16.04.6 LTS \n \l

PHPのバージョンは、

docker-compose run workspace php -v

Starting laradock_docker-in-docker_1 ... done
PHP 7.2.16-1+ubuntu16.04.1+deb.sury.org+1 (cli) (built: Mar 7 2019 20:23:06) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.2.16-1+ubuntu16.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

環境構築その2:チュートリアル用DB作成

Laradockのworkspaceに入る

docker-compose exec --user=laradock workspace bash

更にpsqlに入る。defaultユーザのパスワード: secret

psql -h postgres -U default

チュートリアル用DB(今回、PostGraphileとPostgRESTで共用)を作成し、接続できることを確認したら、一旦psqlから抜ける

create database tutorialdb;
\connect tutorialdb
\q

DB名をtutorialdbとしましたが、名前は何でも良いです。

Laradockのworkspace内で、PostGraphileのチュートリアル用スキーマ&データを作成するスクリプトを流す

cd ~
git clone https://github.com/graphile/postgraphile
cd ~/postgraphile/examples/forum

スクリプトを以下の内容で新規作成。scripts/run-schemaをアレンジしました。

( ~/postgraphile/examples/forum/scripts/myrun )

#!/usr/bin/env bash

set -e

scripts/build-schema

psql -h postgres -U default -d tutorialdb -f schema-drop.sql $@
psql -h postgres -U default -d tutorialdb -f schema.sql $@
psql -h postgres -U default -d tutorialdb -f data.sql $@

作ったスクリプトを実行する。
途中、パスワードを3回訊かれるので、defaultユーザのパスワードを入力する

chmod u+x scripts/myrun
scripts/myrun

正常に動いた様子なら、再びLaradockのworkspaceからpsqlに入る

psql -h postgres -U default -d tutorialdb

今度はPostgRESTのチュートリアル用スキーマ&データ作成。
スキーマ名とロール名は、先ほど作成したPostGraphileのものを使用するため、PostgRESTチュートリアルページのSQL文に出てくる名前を以下のように変更しました。

(schema) api --> forum_example
(role) web_anon --> forum_example_anonymous
(role) todo_user --> forum_example_person
(role) authenticator --> forum_example_postgraphile

PostgREST Tutorial 0 - Get it Running
「Step 4. Create Database for API」のコマンドを少し変更。
psqlで以下を実行。

create table forum_example.todos (
  id serial primary key,
  done boolean not null default false,
  task text not null,
  due timestamptz
);

insert into forum_example.todos (task) values
  ('finish tutorial 0'), ('pat self on back');

grant select on forum_example.todos to forum_example_anonymous;

PostgREST Tutorial 1 - The Golden Key
「Step 1. Add a Trusted User」のコマンドを少し変更。
psqlで以下も実行。

grant all on forum_example.todos to forum_example_person;

grant usage, select on sequence forum_example.todos_id_seq to forum_example_person;

\q

以上の作業が正常に行えたら、Laradockのworkspaceからもexitしてホストに戻っておきます。

環境構築その3:PostgREST、PostGraphile、nginx関連

認証にJWTを使用します。
JWT用共通鍵(秘密鍵)を作成(Linuxコマンド)。この共通鍵は、トークン作成とトークン検証の両方で共通して使用されます。

LC_CTYPE=C < /dev/urandom tr -dc A-Za-z0-9 | head -c32

本ページでは、以下の共通鍵を使用します。PostgRESTとPostGraphileで同じ共通鍵を使います。これは秘密鍵なので、本番環境では取り扱い要注意。

K3INqoxbNWCB4hYiDnX2zlbIpm8UA5zR

Laradockのdocker-compose.ymlファイルの最後に以下を追加。スマートな書き方ではありませんが、チュートリアルなのでとりあえずよしとします。PostgRESTとPostGraphileで、同じJWT用共通鍵を設定しています。

~/work/laradock/docker-compose.yml
( ~/work/laradock/docker-compose.yml )

### PostgREST ################################################
    postgrest_tutorial:
      image: postgrest/postgrest:latest
      expose:
        - "13000"
      environment:
        PGRST_SERVER_PORT: 13000
        PGRST_DB_URI: "postgres://forum_example_postgraphile:xyz@postgres:${POSTGRES_PORT}/tutorialdb"
        # PGRST_DB_SCHEMA: api
        PGRST_DB_SCHEMA: forum_example
        # PGRST_DB_ANON_ROLE: web_anon
        PGRST_DB_ANON_ROLE: forum_example_anonymous
        PGRST_JWT_SECRET: K3INqoxbNWCB4hYiDnX2zlbIpm8UA5zR
      depends_on:
        - postgres
      networks:
        - backend

### PostGraphile ################################################
    postgraphile_tutorial:
      image: graphile/postgraphile
      environment:
        DATABASE_URL: "postgres://forum_example_postgraphile:xyz@postgres:${POSTGRES_PORT}/tutorialdb"
      expose:
        - "15000"
      command: ["--watch", "--connection", "postgres://forum_example_postgraphile:xyz@postgres:${POSTGRES_PORT}/tutorialdb", "--host", "0.0.0.0", "--port", "15000", "--schema", "forum_example", "--default-role", "forum_example_anonymous", "--secret", "K3INqoxbNWCB4hYiDnX2zlbIpm8UA5zR", "--token", "forum_example.jwt_token"]
      ports:
        - "15000:15000"
      depends_on:
        - postgres
      restart: on-failure:20
      networks:
        - backend

nginxのリバースプロキシ設定。~/work/laradock/nginx/sites/default.confファイルを編集します。

~/work/laradock/nginx/sites/default.conf
( ~/work/laradock/nginx/sites/default.conf )

    location /.well-known/acme-challenge/ {
        root /var/www/letsencrypt/;
        log_not_found off;
    }
+
+    location /postgrest/tutorial/ {
+        proxy_pass http://postgrest_tutorial:13000/;
+    }
+
+    location /postgraphile/tutorial/ {
+        proxy_pass http://postgraphile_tutorial:15000/;
+    }
}

Docker環境の構築

docker-compose up -d postgres pgadmin workspace nginx postgrest_tutorial postgraphile_tutorial

しばらく時間がかかります。終了したら、

docker-compose ps

以下のように表示されました。StateがUpになっているのを確認します。

              Name                            Command               State                    Ports                  
--------------------------------------------------------------------------------------------------------------------
laradock_docker-in-docker_1        dockerd-entrypoint.sh            Up      2375/tcp                                
laradock_nginx_1                   /bin/bash /opt/startup.sh        Up      0.0.0.0:443->443/tcp, 0.0.0.0:80->80/tcp
laradock_pgadmin_1                 docker-entrypoint.sh pgadmin4    Up      0.0.0.0:5050->5050/tcp                  
laradock_php-fpm_1                 docker-php-entrypoint php-fpm    Up      9000/tcp                                
laradock_postgraphile_tutorial_1   postgraphile -n 0.0.0.0 po ...   Up      0.0.0.0:15000->15000/tcp, 5000/tcp      
laradock_postgres_1                docker-entrypoint.sh postgres    Up      0.0.0.0:5432->5432/tcp                  
laradock_postgrest_tutorial_1      /bin/sh -c exec postgrest  ...   Up      13000/tcp, 3000/tcp                     
laradock_workspace_1               /sbin/my_init                    Up      0.0.0.0:2222->22/tcp                    

PostgREST が postgres に接続できているか確認

docker logs laradock_postgrest_tutorial_1

PostgreSQLに接続できるまで、何回かリトライすることがありますが、最終的に「Connection successful」が出ればOK

Listening on port 13000
Attempting to connect to the database...
Connection successful

PostGraphile が postgres に接続できているか確認

docker logs laradock_postgraphile_tutorial_1

以下のように表示されました。エラーっぽいメッセージが出ていますが、ここは追究せずに、このまま先に進むことにします。

PostGraphile v4.3.2 server listening on port 15000 🚀

  ‣ GraphQL API:         http://0.0.0.0:15000/graphql
  ‣ GraphiQL GUI/IDE:    http://0.0.0.0:15000/graphiql (enhance with '--enhance-graphiql')
  ‣ Postgres connection: postgres://forum_example_postgraphile:[SECRET]@postgres/tutorialdb
  ‣ Postgres schema(s):  forum_example
  ‣ Documentation:       https://graphile.org/postgraphile/introduction/
  ‣ Join Jimmy McBroom in supporting PostGraphile development: https://graphile.org/sponsor/

* * *

Failed to setup watch fixtures in Postgres database ️️⚠️
This is likely because your Postgres user is not a superuser. If the
fixtures already exist, the watch functionality may still work.
Enable DEBUG='graphile-build-pg' to see the error

nginxのバージョンを確認してみる。

docker-compose run nginx nginx -v

Starting laradock_docker-in-docker_1 ... done
Starting laradock_workspace_1 ... done
Starting laradock_php-fpm_1 ... done
nginx version: nginx/1.15.9

チュートリアル実行

以後、proxy経由でインターネットに接続している場合は、curlでlocalhostに接続するために、頭に「http_proxy=""」をつける等の方法がある。例えば、

http_proxy="" curl http://localhost/postgrest/tutorial/todos

チュートリアル0:select文発行

PostgREST Tutorial 0 - Get it Running
「Step 5. Run PostgREST」を、PostgRESTとPostGraphileの両方で動かします。

チュートリアル0をPostgRESTで動かす

ホストで端末から

curl \
    http://localhost/postgrest/tutorial/todos

テーブルのデータのjsonが返ってきたら成功です。

[{"id":1,"done":false,"task":"finish tutorial 0","due":null},
{"id":2,"done":false,"task":"pat self on back","due":null}]

select文以外(ここではinsert文)を発行するとどうなるか試してみます。

curl \
    -X POST \
    -H "Content-Type: application/json" \
    -d '{"task": "do bad thing"}' \
    http://localhost/postgrest/tutorial/todos

以下のように、エラーメッセージのjsonが返ってきました。

{"hint":null,"details":null,"code":"42501","message":"permission denied for table todos"}

参考までに、テーブル名を書かないでリクエストしてみたら、

curl \
    http://localhost/postgrest/tutorial/

こんなjsonが返ってきました。うーん...?

{"swagger":"2.0","info":{"version":"5.2.0 (1e732ac)","title":"PostgREST API","description":"This is a dynamic API generated by PostgREST"},"host":"0.0.0.0:13000","basePath":"/","schemes":["http"],"consumes":["application/json","application/vnd.pgrst.object+json","text/csv"],"produces":["application/json","application/vnd.pgrst.object+json","text/csv"],"paths":{"/":{"get":{"tags":["Introspection"],"summary":"OpenAPI description (this document)","produces":["application/openapi+json","application/json"],"responses":{"200":{"description":"OK"}}}},"/person":{"get":{"tags":["person"],"summary":"A user of the forum.","parameters":[{"$ref":"#/parameters/rowFilter.person.id"},{"$ref":"#/parameters/rowFilter.person.first_name"},{"$ref":"#/parameters/rowFilter.person.last_name"},{"$ref":"#/parameters/rowFilter.person.about"},{"$ref":"#/parameters/rowFilter.person.created_at"},{"$ref":"#/parameters/rowFilter.person.updated_at"},{"$ref":"#/parameters/select"},{"$ref":"#/parameters/order"},{"$ref":"#/parameters/range"},{"$ref":"#/parameters/rangeUnit"},{"$ref":"#/parameters/offset"},{"$ref":"#/parameters/limit"},{"$ref":"#/parameters/preferCount"}],"responses":{"206":{"description":"Partial Content"},"200":{"schema":{"items":{"$ref":"#/definitions/person"},"type":"array"},"description":"OK"}}},"post":{"tags":["person"],"summary":"A user of the forum.","parameters":[{"$ref":"#/parameters/body.person"},{"$ref":"#/parameters/preferReturn"}],"responses":{"201":{"description":"Created"}}},"delete":{"tags":["person"],"summary":"A user of the forum.","parameters":[{"$ref":"#/parameters/rowFilter.person.id"},{"$ref":"#/parameters/rowFilter.person.first_name"},{"$ref":"#/parameters/rowFilter.person.last_name"},{"$ref":"#/parameters/rowFilter.person.about"},{"$ref":"#/parameters/rowFilter.person.created_at"},{"$ref":"#/parameters/rowFilter.person.updated_at"},{"$ref":"#/parameters/preferReturn"}],"responses":{"204":{"description":"No Content"}}},"patch":{"tags":["person"],"summary":"A user of the forum.","parameters":[{"$ref":"#/parameters/rowFilter.person.id"},{"$ref":"#/parameters/rowFilter.person.first_name"},{"$ref":"#/parameters/rowFilter.person.last_name"},{"$ref":"#/parameters/rowFilter.person.about"},{"$ref":"#/parameters/rowFilter.person.created_at"},{"$ref":"#/parameters/rowFilter.person.updated_at"},{"$ref":"#/parameters/body.person"},{"$ref":"#/parameters/preferReturn"}],"responses":{"204":{"description":"No Content"}}}},"/post":{"get":{"tags":["post"],"summary":"A forum post written by a user.","parameters":[{"$ref":"#/parameters/rowFilter.post.id"},{"$ref":"#/parameters/rowFilter.post.author_id"},{"$ref":"#/parameters/rowFilter.post.headline"},{"$ref":"#/parameters/rowFilter.post.body"},{"$ref":"#/parameters/rowFilter.post.topic"},{"$ref":"#/parameters/rowFilter.post.created_at"},{"$ref":"#/parameters/rowFilter.post.updated_at"},{"$ref":"#/parameters/select"},{"$ref":"#/parameters/order"},{"$ref":"#/parameters/range"},{"$ref":"#/parameters/rangeUnit"},{"$ref":"#/parameters/offset"},{"$ref":"#/parameters/limit"},{"$ref":"#/parameters/preferCount"}],"responses":{"206":{"description":"Partial Content"},"200":{"schema":{"items":{"$ref":"#/definitions/post"},"type":"array"},"description":"OK"}}},"post":{"tags":["post"],"summary":"A forum post written by a user.","parameters":[{"$ref":"#/parameters/body.post"},{"$ref":"#/parameters/preferReturn"}],"responses":{"201":{"description":"Created"}}},"delete":{"tags":["post"],"summary":"A forum post written by a user.","parameters":[{"$ref":"#/parameters/rowFilter.post.id"},{"$ref":"#/parameters/rowFilter.post.author_id"},{"$ref":"#/parameters/rowFilter.post.headline"},{"$ref":"#/parameters/rowFilter.post.body"},{"$ref":"#/parameters/rowFilter.post.topic"},{"$ref":"#/parameters/rowFilter.post.created_at"},{"$ref":"#/parameters/rowFilter.post.updated_at"},{"$ref":"#/parameters/preferReturn"}],"responses":{"204":{"description":"No Content"}}},"patch":{"tags":["post"],"summary":"A forum post written by a user.","parameters":[{"$ref":"#/parameters/rowFilter.post.id"},{"$ref":"#/parameters/rowFilter.post.author_id"},{"$ref":"#/parameters/rowFilter.post.headline"},{"$ref":"#/parameters/rowFilter.post.body"},{"$ref":"#/parameters/rowFilter.post.topic"},{"$ref":"#/parameters/rowFilter.post.created_at"},{"$ref":"#/parameters/rowFilter.post.updated_at"},{"$ref":"#/parameters/body.post"},{"$ref":"#/parameters/preferReturn"}],"responses":{"204":{"description":"No Content"}}}},"/todos":{"get":{"tags":["todos"],"parameters":[{"$ref":"#/parameters/rowFilter.todos.id"},{"$ref":"#/parameters/rowFilter.todos.done"},{"$ref":"#/parameters/rowFilter.todos.task"},{"$ref":"#/parameters/rowFilter.todos.due"},{"$ref":"#/parameters/select"},{"$ref":"#/parameters/order"},{"$ref":"#/parameters/range"},{"$ref":"#/parameters/rangeUnit"},{"$ref":"#/parameters/offset"},{"$ref":"#/parameters/limit"},{"$ref":"#/parameters/preferCount"}],"responses":{"206":{"description":"Partial Content"},"200":{"schema":{"items":{"$ref":"#/definitions/todos"},"type":"array"},"description":"OK"}}},"post":{"tags":["todos"],"parameters":[{"$ref":"#/parameters/body.todos"},{"$ref":"#/parameters/preferReturn"}],"responses":{"201":{"description":"Created"}}},"delete":{"tags":["todos"],"parameters":[{"$ref":"#/parameters/rowFilter.todos.id"},{"$ref":"#/parameters/rowFilter.todos.done"},{"$ref":"#/parameters/rowFilter.todos.task"},{"$ref":"#/parameters/rowFilter.todos.due"},{"$ref":"#/parameters/preferReturn"}],"responses":{"204":{"description":"No Content"}}},"patch":{"tags":["todos"],"parameters":[{"$ref":"#/parameters/rowFilter.todos.id"},{"$ref":"#/parameters/rowFilter.todos.done"},{"$ref":"#/parameters/rowFilter.todos.task"},{"$ref":"#/parameters/rowFilter.todos.due"},{"$ref":"#/parameters/body.todos"},{"$ref":"#/parameters/preferReturn"}],"responses":{"204":{"description":"No Content"}}}},"/rpc/register_person":{"post":{"tags":["(rpc) register_person"],"summary":"Registers a single user and creates an account in our forum.","produces":["application/json","application/vnd.pgrst.object+json"],"parameters":[{"required":true,"schema":{"required":["first_name","last_name","email","password"],"type":"object","description":"Registers a single user and creates an account in our forum.","properties":{"email":{"format":"text","type":"string"},"first_name":{"format":"text","type":"string"},"password":{"format":"text","type":"string"},"last_name":{"format":"text","type":"string"}}},"in":"body","name":"args"},{"$ref":"#/parameters/preferParams"}],"responses":{"200":{"description":"OK"}}}},"/rpc/post_summary":{"post":{"tags":["(rpc) post_summary"],"summary":"A truncated version of the body for summaries.","produces":["application/json","application/vnd.pgrst.object+json"],"parameters":[{"required":true,"schema":{"required":["post"],"type":"object","description":"A truncated version of the body for summaries.","properties":{"length":{"format":"integer","type":"integer"},"post":{"format":"post","type":"string"},"omission":{"format":"text","type":"string"}}},"in":"body","name":"args"},{"$ref":"#/parameters/preferParams"}],"responses":{"200":{"description":"OK"}}}},"/rpc/current_person":{"post":{"tags":["(rpc) current_person"],"summary":"Gets the person who was identified by our JWT.","produces":["application/json","application/vnd.pgrst.object+json"],"parameters":[{"required":true,"schema":{"type":"object","description":"Gets the person who was identified by our JWT."},"in":"body","name":"args"},{"$ref":"#/parameters/preferParams"}],"responses":{"200":{"description":"OK"}}}},"/rpc/person_latest_post":{"post":{"tags":["(rpc) person_latest_post"],"summary":"Gets the latest post written by the person.","produces":["application/json","application/vnd.pgrst.object+json"],"parameters":[{"required":true,"schema":{"required":["person"],"type":"object","description":"Gets the latest post written by the person.","properties":{"person":{"format":"person","type":"string"}}},"in":"body","name":"args"},{"$ref":"#/parameters/preferParams"}],"responses":{"200":{"description":"OK"}}}},"/rpc/person_full_name":{"post":{"tags":["(rpc) person_full_name"],"summary":"A person’s full name which is a concatenation of their first and last name.","produces":["application/json","application/vnd.pgrst.object+json"],"parameters":[{"required":true,"schema":{"required":["person"],"type":"object","description":"A person’s full name which is a concatenation of their first and last name.","properties":{"person":{"format":"person","type":"string"}}},"in":"body","name":"args"},{"$ref":"#/parameters/preferParams"}],"responses":{"200":{"description":"OK"}}}},"/rpc/search_posts":{"post":{"tags":["(rpc) search_posts"],"summary":"Returns posts containing a given search term.","produces":["application/json","application/vnd.pgrst.object+json"],"parameters":[{"required":true,"schema":{"required":["search"],"type":"object","description":"Returns posts containing a given search term.","properties":{"search":{"format":"text","type":"string"}}},"in":"body","name":"args"},{"$ref":"#/parameters/preferParams"}],"responses":{"200":{"description":"OK"}}}},"/rpc/authenticate":{"post":{"tags":["(rpc) authenticate"],"summary":"Creates a JWT token that will securely identify a person and give them certain permissions.","produces":["application/json","application/vnd.pgrst.object+json"],"parameters":[{"required":true,"schema":{"required":["email","password"],"type":"object","description":"Creates a JWT token that will securely identify a person and give them certain permissions.","properties":{"email":{"format":"text","type":"string"},"password":{"format":"text","type":"string"}}},"in":"body","name":"args"},{"$ref":"#/parameters/preferParams"}],"responses":{"200":{"description":"OK"}}}}},"definitions":{"person":{"description":"A user of the forum.","required":["id","first_name"],"properties":{"id":{"format":"integer","type":"integer","description":"The primary unique identifier for the person.\n\nNote:\nThis is a Primary Key.<pk/>"},"first_name":{"format":"text","type":"string","description":"The person’s first name."},"last_name":{"format":"text","type":"string","description":"The person’s last name."},"about":{"format":"text","type":"string","description":"A short description about the user, written by the user."},"created_at":{"format":"timestamp without time zone","type":"string","description":"The time this person was created."},"updated_at":{"format":"timestamp without time zone","type":"string"}},"type":"object"},"post":{"description":"A forum post written by a user.","required":["id","author_id","headline"],"properties":{"id":{"format":"integer","type":"integer","description":"The primary key for the post.\n\nNote:\nThis is a Primary Key.<pk/>"},"author_id":{"format":"integer","type":"integer","description":"The id of the author user.\n\nNote:\nThis is a Foreign Key to `person.id`.<fk table='person' column='id'/>"},"headline":{"format":"text","type":"string","description":"The title written by the user."},"body":{"format":"text","type":"string","description":"The main body text of our post."},"topic":{"format":"forum_example.post_topic","type":"string","enum":["discussion","inspiration","help","showcase"],"description":"The topic this has been posted in."},"created_at":{"format":"timestamp without time zone","type":"string","description":"The time this post was created."},"updated_at":{"format":"timestamp without time zone","type":"string"}},"type":"object"},"todos":{"required":["id","done","task"],"properties":{"id":{"format":"integer","type":"integer","description":"Note:\nThis is a Primary Key.<pk/>"},"done":{"default":false,"format":"boolean","type":"boolean"},"task":{"format":"text","type":"string"},"due":{"format":"timestamp with time zone","type":"string"}},"type":"object"}},"parameters":{"preferParams":{"name":"Prefer","description":"Preference","required":false,"in":"header","type":"string","enum":["params=single-object"]},"preferReturn":{"name":"Prefer","description":"Preference","required":false,"in":"header","type":"string","enum":["return=representation","return=minimal","return=none"]},"preferCount":{"name":"Prefer","description":"Preference","required":false,"in":"header","type":"string","enum":["count=none"]},"select":{"name":"select","description":"Filtering Columns","required":false,"in":"query","type":"string"},"order":{"name":"order","description":"Ordering","required":false,"in":"query","type":"string"},"range":{"name":"Range","description":"Limiting and Pagination","required":false,"in":"header","type":"string"},"rangeUnit":{"name":"Range-Unit","description":"Limiting and Pagination","required":false,"default":"items","in":"header","type":"string"},"offset":{"name":"offset","description":"Limiting and Pagination","required":false,"in":"query","type":"string"},"limit":{"name":"limit","description":"Limiting and Pagination","required":false,"in":"query","type":"string"},"body.person":{"name":"person","description":"person","required":false,"schema":{"$ref":"#/definitions/person"},"in":"body"},"rowFilter.person.id":{"name":"id","description":"The primary unique identifier for the person.","required":false,"format":"integer","in":"query","type":"string"},"rowFilter.person.first_name":{"name":"first_name","description":"The person’s first name.","required":false,"format":"text","in":"query","type":"string"},"rowFilter.person.last_name":{"name":"last_name","description":"The person’s last name.","required":false,"format":"text","in":"query","type":"string"},"rowFilter.person.about":{"name":"about","description":"A short description about the user, written by the user.","required":false,"format":"text","in":"query","type":"string"},"rowFilter.person.created_at":{"name":"created_at","description":"The time this person was created.","required":false,"format":"timestamp without time zone","in":"query","type":"string"},"rowFilter.person.updated_at":{"name":"updated_at","required":false,"format":"timestamp without time zone","in":"query","type":"string"},"body.post":{"name":"post","description":"post","required":false,"schema":{"$ref":"#/definitions/post"},"in":"body"},"rowFilter.post.id":{"name":"id","description":"The primary key for the post.","required":false,"format":"integer","in":"query","type":"string"},"rowFilter.post.author_id":{"name":"author_id","description":"The id of the author user.","required":false,"format":"integer","in":"query","type":"string"},"rowFilter.post.headline":{"name":"headline","description":"The title written by the user.","required":false,"format":"text","in":"query","type":"string"},"rowFilter.post.body":{"name":"body","description":"The main body text of our post.","required":false,"format":"text","in":"query","type":"string"},"rowFilter.post.topic":{"name":"topic","description":"The topic this has been posted in.","required":false,"format":"forum_example.post_topic","in":"query","type":"string"},"rowFilter.post.created_at":{"name":"created_at","description":"The time this post was created.","required":false,"format":"timestamp without time zone","in":"query","type":"string"},"rowFilter.post.updated_at":{"name":"updated_at","required":false,"format":"timestamp without time zone","in":"query","type":"string"},"body.todos":{"name":"todos","description":"todos","required":false,"schema":{"$ref":"#/definitions/todos"},"in":"body"},"rowFilter.todos.id":{"name":"id","required":false,"format":"integer","in":"query","type":"string"},"rowFilter.todos.done":{"name":"done","required":false,"format":"boolean","in":"query","type":"string"},"rowFilter.todos.task":{"name":"task","required":false,"format":"text","in":"query","type":"string"},"rowFilter.todos.due":{"name":"due","required":false,"format":"timestamp with time zone","in":"query","type":"string"}},"externalDocs":{"url":"https://postgrest.org/en/v2/api.html","description":"PostgREST Documentation"}}

チュートリアル0をPostGraphileで動かす

ホストで端末から

curl \
    -X POST \
    -H 'Content-Type: application/json' \
    --data '{ "query": "{ allTodos { nodes { id done task } } }" }' \
    http://localhost/postgraphile/tutorial/graphql

以下のように、テーブルのデータのjsonが返ってきました。

{"data":{"allTodos":{"nodes":[{"id":1,"done":false,"task":"finish tutorial 0"},{"id":2,"done":false,"task":"pat self on back"}]}}}

ブラウザでGraphiQL( http://localhost/postgraphile/tutorial/graphiql )にアクセスして、以下のクエリーを投げてみます。nginxをリバースプロキシにしたURLです。

{
  allTodos {
    nodes {
      id
      done
      task
    }
  }
}

以下のエラーメッセージが返ってきました。

SyntaxError: JSON.parse: unexpected character at line 1 column 1 of the JSON data

よくわからないので、リバースプロキシのURLはやめて、ローカルポートフォワードのGraphiQLのURL( http://localhost:15000/graphiql )で同じクエリーを投げてみます。

{
  allTodos {
    nodes {
      id
      done
      task
    }
  }
}

以下のように、テーブルのデータのjsonが返ってきました。めでたし。

{
  "data": {
    "allTodos": {
      "nodes": [
        {
          "id": 1,
          "done": false,
          "task": "finish tutorial 0"
        },
        {
          "id": 2,
          "done": false,
          "task": "pat self on back"
        }
      ]
    }
  }
}

チュートリアル1:データの更新

PostgREST Tutorial 1 - The Golden Key
「Step 3. Sign a Token」
「Step 4. Make a Request」
を、PostgRESTとPostGraphileの両方で動かします。

認証にJWTを使用します。

チュートリアル1をPostgRESTで動かす

PostgRESTチュートリアルのページでは、https://jwt.io/#debugger-ioで手動でJWTを作成する内容になっていますが、PostGraphileにはJWTを生成する機能があるので、手動はやめてその機能を使ってみます。
ブラウザからローカルポートフォワードのGraphiQLのURL( http://localhost:15000/graphiql )に行き、以下のクエリーを発行します。
このクエリーの意味を理解するためには、PostGraphileチュートリアルのドキュメントをある程度読む必要があります。

mutation authenticate {
  authenticate(input: { email: "spowell0@noaa.gov", password: "iFbWWlc" }) {
    jwtToken
  }
}

以下のように、JWTを含むjsonが返りました。

{
  "data": {
    "authenticate": {
      "jwtToken": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9ydW1fZXhhbXBsZV9wZXJzb24iLCJwZXJzb25faWQiOjEsImlhdCI6MTU1MjA2NTEyOCwiZXhwIjoxNTUyMTUxNTI4LCJhdWQiOiJwb3N0Z3JhcGhpbGUiLCJpc3MiOiJwb3N0Z3JhcGhpbGUifQ.Spj-pQ805un1awWafvcKgbkN2gByP6nLv_mkq680lYA"
    }
  }
}

GraphiQLでなくcurlから実行するなら、

curl \
    -X POST \
    -H 'Content-Type: application/json' \
    --data '{ "query": "mutation authenticate { authenticate(input: { email: \"spowell0@noaa.gov\", password: \"iFbWWlc\" }) { jwtToken } }" }' \
    http://localhost/postgraphile/tutorial/graphql

以下のように、JWTを含むjsonが返りました。

{"data":{"authenticate":{"jwtToken":"eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9ydW1fZXhhbXBsZV9wZXJzb24iLCJwZXJzb25faWQiOjEsImlhdCI6MTU1MjA2NTE4MiwiZXhwIjoxNTUyMTUxNTgyLCJhdWQiOiJwb3N0Z3JhcGhpbGUiLCJpc3MiOiJwb3N0Z3JhcGhpbGUifQ.Lg2j2SjkIr3lgzoT4QW0R5lHF_N23IRISebOrusPmXg"}}}

今回はGraphiQLで返ってきた方のトークンを環境変数にセット。なお本番環境では、JWTは取り扱い要注意です。

export TOKEN="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9ydW1fZXhhbXBsZV9wZXJzb24iLCJwZXJzb25faWQiOjEsImlhdCI6MTU1MjA2NTEyOCwiZXhwIjoxNTUyMTUxNTI4LCJhdWQiOiJwb3N0Z3JhcGhpbGUiLCJpc3MiOiJwb3N0Z3JhcGhpbGUifQ.Spj-pQ805un1awWafvcKgbkN2gByP6nLv_mkq680lYA"

insert文発行

curl \
    -X POST \
     -H "Authorization: Bearer $TOKEN"   \
     -H "Content-Type: application/json" \
     -d '{"task": "learn how to auth"}' \
    http://localhost/postgrest/tutorial/todos

データ確認

curl \
    http://localhost/postgrest/tutorial/todos

以下のように、1行増えました。

[{"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":"learn how to auth","due":null}]

今回のチュートリアルでは、PostGraphileで発行したJWTがPostgRESTでも使えました。ただしどんなケースでも使えるとは限りません。JWTの生成時に、ペイロードに何の情報を入れるかに依存するのかも?

次に、update文発行

curl \
    -X PATCH \
     -H "Authorization: Bearer $TOKEN"   \
     -H "Content-Type: application/json" \
     -d '{"done": true}' \
    http://localhost/postgrest/tutorial/todos

データ確認

curl \
    http://localhost/postgrest/tutorial/todos

以下のように、done列が全ての行でtrueになりました。

[{"id":1,"done":true,"task":"finish tutorial 0","due":null},
{"id":2,"done":true,"task":"pat self on back","due":null},
{"id":3,"done":true,"task":"learn how to auth","due":null}]

チュートリアル1をPostGraphileで動かす

クエリーの参考ページ

今回はGraphiQLからJWTを使うのを避けて(やり方はあるらしい)、curlから実行することにします。
insert文のクエリーは、

mutation {
  createTodo(
    input: { todo: { task: "スーパーに買い物に行く" } }
  ) {
    todo {
      task
    }
  }
}

このクエリーをcurlで実行

curl \
    -X POST \
    -H "Authorization: Bearer $TOKEN"   \
    -H 'Content-Type: application/json' \
    --data '{ "query": "mutation { createTodo( input: { todo: { task: \"スーパーに買い物に行く\" } } ) { todo { task } } }" }' \
    http://localhost/postgraphile/tutorial/graphql

以下のjsonが返りました。

{"data":{"createTodo":{"todo":{"task":"スーパーに買い物に行く"}}}}

データ確認

curl \
    http://localhost/postgrest/tutorial/todos

以下のように、1行増えました。

[{"id":1,"done":true,"task":"finish tutorial 0","due":null},
{"id":2,"done":true,"task":"pat self on back","due":null},
{"id":3,"done":true,"task":"learn how to auth","due":null},
{"id":4,"done":false,"task":"スーパーに買い物に行く","due":null}]

次に、update文のクエリーは、

mutation {
  updateTodoById(
    input: { id: 4, todoPatch: { done: true } }
  ) {
    todo {
      id
      done
    }
  }
}

このクエリーをcurlで実行

curl \
    -X POST \
    -H "Authorization: Bearer $TOKEN"   \
    -H 'Content-Type: application/json' \
    --data '{ "query": "mutation { updateTodoById( input: { id: 4, todoPatch: { done: true } } ) { todo { id done } } }" }' \
    http://localhost/postgraphile/tutorial/graphql

以下のjsonが返りました。

{"data":{"updateTodoById":{"todo":{"id":4,"done":true}}}}

データ確認

curl \
    http://localhost/postgrest/tutorial/todos

以下のように、id=4のdone列がtrueになりました。

[{"id":1,"done":true,"task":"finish tutorial 0","due":null},
{"id":2,"done":true,"task":"pat self on back","due":null},
{"id":3,"done":true,"task":"learn how to auth","due":null},
{"id":4,"done":true,"task":"スーパーに買い物に行く","due":null}]

次に、delete文のクエリーは、

mutation {
  deleteTodoById(input: { id: 4 }) {
    deletedTodoId
  }
}

このクエリーをcurlで実行

curl \
    -X POST \
    -H "Authorization: Bearer $TOKEN"   \
    -H 'Content-Type: application/json' \
    --data '{ "query": "mutation { deleteTodoById(input: { id: 4 }) { deletedTodoId } }" }' \
    http://localhost/postgraphile/tutorial/graphql

以下のjsonが返りました。

{"data":{"deleteTodoById":{"deletedTodoId":"WyJ0b2RvcyIsNF0="}}}

データ確認

curl \
    http://localhost/postgrest/tutorial/todos

以下のように、id=4の行が削除されました。

[{"id":1,"done":true,"task":"finish tutorial 0","due":null},
{"id":2,"done":true,"task":"pat self on back","due":null},
{"id":3,"done":true,"task":"learn how to auth","due":null}]

自作プログラムからJWTを調べたり作ったり

Python3を使用します。Python2では動かないと思います。
Pythonはまだ使い慣れていないので、変なところがあったらお許しを。

まず、PostGraphileが発行したJWTの中身を調べるプログラムを作ってみました。

jwt_info.py
( jwt_info.py )

import sys
import os
import datetime
import json
import base64
import hashlib
import hmac

def base64url_decode(input):
    rem = len(input) % 4
    if 0 < rem:
        input += b'=' * (4 - rem)
    return base64.urlsafe_b64decode(input)

if len(sys.argv) - 1 == 1:
    token = sys.argv[1]
else:
    print('Usage:')
    print('     (必須)コマンドライン引数[1]:token')
    sys.exit()

header, payload, signature = token.split('.', 2)
header_encode : bytes = header.encode('utf-8')
payload_encode : bytes = payload.encode('utf-8')
signature_encode : bytes = signature.encode('utf-8')

header_decode : bytes = base64url_decode(header_encode)
header_dict = json.loads(header_decode.decode('utf-8'))
print('header')
print(json.dumps(header_dict, indent = 2))

payload_decode : bytes = base64url_decode(payload_encode)
payload_dict = json.loads(payload_decode.decode('utf-8'))
print('payload')
print(json.dumps(payload_dict, indent = 2))

if 'exp' in payload_dict:
    exp = payload_dict['exp']
    now = int(datetime.datetime.now().timestamp())

    rest = exp - now
    if 0 < rest:
        print('トークンの有効期限は残り' + str(rest) + '秒です')
    else:
        print('トークンは有効期限切れです')
    if 'iat' in payload_dict:
        iat = payload_dict['iat']
        print('  発行時刻:' + str(datetime.datetime.fromtimestamp(iat)))
    print('  失効時刻:' + str(datetime.datetime.fromtimestamp(exp)))
    print('  現在時刻:' + str(datetime.datetime.fromtimestamp(now)))

signature_decode : bytes = base64url_decode(signature_encode)

key : bytes = os.environ['SIGNINGKEY'].encode('utf-8')
msg : bytes = header_encode + b'.' + payload_encode
signature_decode_recalc : bytes = hmac.new(key, msg, hashlib.sha256).digest()

if hmac.compare_digest(signature_decode, signature_decode_recalc):
    print('改竄されていません')
else:
    print('!!!!!!!!!!改竄されています!!!!!!!!!!')
    signature_encode_recalc: bytes = base64.urlsafe_b64encode(signature_decode_recalc).replace(b'=', b'')
    print(signature_encode)
    print(signature_encode_recalc)

ではこのプログラムを動かしてみます。
JWT用共通鍵(秘密鍵)を環境変数に設定します。

export SIGNINGKEY="K3INqoxbNWCB4hYiDnX2zlbIpm8UA5zR"

JWTはこの記事でチュートリアルを動かしたときに環境変数TOKENに設定しましたので、それをプログラムに渡して実行します。Python3で動かす必要があります。

python jwt_info.py $TOKEN

以下のようにJWTの情報が表示されました。

header
{
  "alg": "HS256",
  "typ": "JWT"
}
payload
{
  "role": "forum_example_person",
  "person_id": 1,
  "iat": 1552065128,
  "exp": 1552151528,
  "aud": "postgraphile",
  "iss": "postgraphile"
}
トークンの有効期限は残り83891秒です
  発行時刻:2019-03-09 02:12:08
  失効時刻:2019-03-10 02:12:08
  現在時刻:2019-03-09 02:53:57
改竄されていません

次はPostGraphileが発行したJWTと同等のJWTを作るプログラムです。チュートリアルでは、authenticateというストアドファンクションに引数としてemailとpasswordを渡すと、person_idを検索してJWTのペイロードに埋め込まれるようにしていました。今回のプログラムは練習用ということで、コマンドライン引数に直接person_idを渡してしまいます(手抜き)。

jwt_create_tut.py
( jwt_create_tut.py )

import sys
import os
import datetime
import json
import base64
import hashlib
import hmac

if len(sys.argv) - 1 == 2:
    role = sys.argv[1]
    person_id = int(sys.argv[2])
else:
    print('Usage:')
    print('     (必須)コマンドライン引数[1]:role')
    print('     (必須)コマンドライン引数[2]:person_id')
    sys.exit()

header_dict = {
    'alg': 'HS256',
    'typ': 'JWT'
}
header_decode : bytes = json.dumps(header_dict, separators=(',', ':')).encode('utf-8')
header_encode : bytes = base64.urlsafe_b64encode(header_decode).replace(b'=', b'')

iat = int(datetime.datetime.now().timestamp())
exp = iat + 24 * 60 * 60
payload_dict = {
    'role': role,
    'person_id': person_id,
    'iat': iat,
    'exp': exp,
    'aud': 'postgraphile',
    'iss': 'postgraphile'
}
payload_decode : bytes = json.dumps(payload_dict, separators=(',', ':')).encode('utf-8')
payload_encode : bytes = base64.urlsafe_b64encode(payload_decode).replace(b'=', b'')

key = os.environ['SIGNINGKEY'].encode('utf-8')
signature_decode : bytes = hmac.new(key, header_encode + b'.' + payload_encode, hashlib.sha256).digest()
signature_encode : bytes = base64.urlsafe_b64encode(signature_decode).replace(b'=', b'')

token = header_encode + b'.' + payload_encode + b'.' + signature_encode
print(token)

Python3でこのプログラムを動かしてJWTを生成してみます。roleにはforum_example_personを渡してください。

python jwt_create_tut.py forum_example_person 1

以下のようにトークンが返りました。

b'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9ydW1fZXhhbXBsZV9wZXJzb24iLCJwZXJzb25faWQiOjEsImlhdCI6MTU1MjA2ODA4MiwiZXhwIjoxNTUyMTU0NDgyLCJhdWQiOiJwb3N0Z3JhcGhpbGUiLCJpc3MiOiJwb3N0Z3JhcGhpbGUifQ.49GV9wAxU-bkrjx3F2R0yM7ZOiJjnEuNbqXV4kcWid0'

このトークンを使ってみます。

export TOKEN="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9ydW1fZXhhbXBsZV9wZXJzb24iLCJwZXJzb25faWQiOjEsImlhdCI6MTU1MjA2ODA4MiwiZXhwIjoxNTUyMTU0NDgyLCJhdWQiOiJwb3N0Z3JhcGhpbGUiLCJpc3MiOiJwb3N0Z3JhcGhpbGUifQ.49GV9wAxU-bkrjx3F2R0yM7ZOiJjnEuNbqXV4kcWid0"

PostgRESTでinsert文発行

curl \
    -X POST \
     -H "Authorization: Bearer $TOKEN"   \
     -H "Content-Type: application/json" \
     -d '{"task": "図書館に本を返しに行く"}' \
    http://localhost/postgrest/tutorial/todos

curl \
    http://localhost/postgrest/tutorial/todos

以下のように、1行増えました。Pythonプログラムで作ったJWTがPostgRESTで使えました。

[{"id":1,"done":true,"task":"finish tutorial 0","due":null},
{"id":2,"done":true,"task":"pat self on back","due":null},
{"id":3,"done":true,"task":"learn how to auth","due":null},
{"id":5,"done":false,"task":"図書館に本を返しに行く","due":null}]

次はPostGraphileでinsert文発行

curl \
    -X POST \
    -H "Authorization: Bearer $TOKEN"   \
    -H 'Content-Type: application/json' \
    --data '{ "query": "mutation { createTodo( input: { todo: { task: \"PCを修理する\" } } ) { todo { task } } }" }' \
    http://localhost/postgraphile/tutorial/graphql

curl \
    http://localhost/postgrest/tutorial/todos

以下のように、1行増えました。Pythonプログラムで作ったJWTがPostGraphileでも使えました。

[{"id":1,"done":true,"task":"finish tutorial 0","due":null},
{"id":2,"done":true,"task":"pat self on back","due":null},
{"id":3,"done":true,"task":"learn how to auth","due":null},
{"id":5,"done":false,"task":"図書館に本を返しに行く","due":null},
{"id":6,"done":false,"task":"PCを修理する","due":null}]

30
32
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
30
32

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?