PostgreSQLをREST APIで叩きたい

なんやらPostgreSQLにREST APIでアクセスしたいという話があったので、PostgREST + Azure Database for PostgreSQL Flexible Serverで遊んでみた。

まず、AzureポータルからAzure Database for PostgreSQL Flexible Serverをデプロイする。次にデプロイしたリソースグループにUbuntu 20のLinux VMをデプロイしてsshでログインする。psqlが無いと困るんで、とりまインストールする。あとでJSON使うんで、jqも。

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

んで、Azure Database for PostgreSQLのConnection Stringをコピーしてきて接続する。Networking設定で接続できるようにしておくこと。接続確認が終わったら切断する。

$ psql "host=riopostgrest.postgres.database.azure.com port=5432 dbname={your_database} user=rifujita password={your_password} sslmode=require"

接続できたら一連のクエリを流すんだけど、面倒くさいから以下で一気に。

$ psql "host=riopostgrest.postgres.database.azure.com port=5432 dbname={your_database} user=rifujita password={your_password} sslmode=require" -c "create schema api;
create table api.todos (
  id serial primary key,
  done boolean not null default false,
  task text not null,
  due timestamptz
);

insert into api.todos (task) values
  ('finish tutorial 0'), ('pat self on back');
create role web_anon nologin;

grant usage on schema api to web_anon;
grant select on api.todos to web_anon;

create role authenticator noinherit login password 'mysecretpassword';
grant web_anon to authenticator;"

で、postgrestのconfigファイル、tutorial.confを作成する。

db-uri = "postgres://authenticator:mysecretpassword@riopostgrest.postgres.database.azure.com:5432/postgres"
db-schema = "api"
db-anon-role = "web_anon"

次にpostgrestをDLする。

$ wget https://github.com/PostgREST/postgrest/releases/download/v8.0.0/postgrest-v8.0.0-linux-x64-static.tar.xz
$ tar xvf postgrest-v8.0.0-linux-x64-static.tar.xz
$ sudo mv postgrest /usr/local/sbin/

で、実行。

$ postgresq tutorial.conf
09/Nov/2021:05:42:29 +0000: Attempting to connect to the database...
09/Nov/2021:05:42:29 +0000: Connection successful
09/Nov/2021:05:42:29 +0000: Listening on port 3000
09/Nov/2021:05:42:29 +0000: Config re-loaded
09/Nov/2021:05:42:29 +0000: Listening for notifications on the pgrst channel
09/Nov/2021:05:42:29 +0000: Schema cache loaded

3000/tcpでリッスンしてるんで、curlで叩いてみる。これはSELECT *だね。

$ curl -s http://localhost:3000/todos | jq
[
  {
    "id": 1,
    "done": false,
    "task": "finish tutorial 0",
    "due": null
  },
  {
    "id": 2,
    "done": false,
    "task": "pat self on back",
    "due": null
  }
]

なるほど？ WHERE id=1か。

$ curl -s http://localhost:3000/todos?id=eq.1| jq
[
  {
    "id": 1,
    "done": false,
    "task": "finish tutorial 0",
    "due": null
  }
]

ORDER BY

$ curl -s http://localhost:3000/todos?order=id.desc | jq
[
  {
    "id": 2,
    "done": false,
    "task": "pat self on back",
    "due": null
  },
  {
    "id": 1,
    "done": false,
    "task": "finish tutorial 0",
    "due": null
  }
]

なるほど、JWTを使ってクライアント認証をしてないからINSERTは出来ない、と。

$ curl -s http://localhost:3000/todos \
-X POST \
-H "Content-Type: application/json" \
-d '{"task": "play with postgrest"}'| jq
{
  "hint": null,
  "details": null,
  "code": "42501",
  "message": "permission denied for table todos"
}

postgrest自体をsystemdでデーモンにすればなんか使えそうではある。

関連