[TOC] > [github.com/PostgREST/postgrest](https://github.com/PostgREST/postgrest) > [教程](https://postgrest-docs-chinese.readthedocs.io/zh/latest/tutorials/tut0.html) ## postgrest - 用于將PostgreSQL數據庫轉換為RESTful API。它允許您通過HTTP協議直接訪問PostgreSQL數據庫中的數據 - 它支持標準的RESTful API操作（GET，POST，PUT，DELETE等）以及高級特性，例如過濾器、排序、分頁和聚合 ## 安裝 https://github.com/begriffs/postgrest/releases/latest 下載對應系統的版本 ## 示例 先創建數據庫,并添加內容 ``` create table todos ( id serial primary key, done boolean not null default false, task text not null, due timestamptz ); insert into todos (task) values ('finish tutorial 0'), ('pat self on back'); ``` ### 設置只讀成員 創建一個角色,對 public 的 schema 只讀權限 ``` create role web_anon nologin; grant web_anon to postgres; grant usage on schema public to web_anon; grant select on public.todos to web_anon; ``` 如何無權限的人訪問會提示 ``` {"code":"42501","details":null,"hint":null,"message":"對模式 api 權限不夠"} ``` 添加配置文件 postgrest.conf ``` # postgrest.conf # The standard connection URI format, documented at # https://www.postgresql.org/docs/current/static/libpq-connect.html#AEN45347 db-uri = "postgres://postgres:postgres@127.0.0.1:5432/tp1" # The name of which database schema to expose to REST clients db-schema = "public" # The database role to use when no client authentication is provided. # Can (and probably should) differ from user in db-uri db-anon-role = "web_anon" ``` 啟動程序 ``` > postgrest postgrest.conf ``` 執行 ``` > curl http://192.168.0.50:3000/todos [{"id":1,"done":false,"task":"finish tutorial 0","due":null}, {"id":2,"done":false,"task":"pat self on back","due":null}] ``` ### 設置完全的權限 創建一個 todo_user 角色,對 todos 有完全的權限 ``` create role todo_user nologin; grant todo_user to postgres; grant usage on schema public to todo_user; grant all on public.todos to todo_user; grant usage, select on sequence public.todos_id_seq to todo_user; ``` 創建密鑰 ``` > openssl rand -base64 32 Z7RP5u6YXgIVCTZpSuaHkExGQMkKxxKMhq7roIyrtWA= ``` 使用 https://jwt.io/#debugger-io 生成 jwt token postgrest.conf ``` ... jwt-secret = "Z7RP5u6YXgIVCTZpSuaHkExGQMkKxxKMhq7roIyrtWA=" ... ```  設置 postgrest.conf 后重啟服務 執行 ``` export TOKEN="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoidG9kb191c2VyIn0.izql0NladqglJLdyf0fo4BlFRPS4H8HgPgY3FW3PBHA" curl http://192.168.0.50:3000/todos -X POST \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{"task": "learn how to auth"}' ``` 數據庫中就會新增一條數據 ### 設置過期時間 ``` > select extract(epoch from now() + '5 minutes'::interval) :: integer; 1684228619 ``` 在jwt 中修改 ``` { "role": "todo_user", "exp": "<computed epoch value>" } ``` 到期后會返回 ``` {"message":"JWT expired"} ``` ### 立即撤銷某個token 首先需要我們在jwt 中引入用戶的郵箱概念 ``` { "role": "todo_user", "email": "disgruntled@mycompany.com" } ``` 創建一個 posgresql 函數 ``` create schema auth; grant usage on schema auth to web_anon, todo_user; create or replace function auth.check_token() returns void language plpgsql as $$ begin if current_setting('request.jwt.claim.email', true) = 'disgruntled@mycompany.com' then raise insufficient_privilege using hint = 'Nope, we are on to you'; end if; end $$; ``` 在 postgrest.conf 中進行需改 ``` // 前置操作 pre-request = "auth.check_token" ``` 請求后出現 ``` { "hint": "Nope, we are on to you", "details": null, "code": "42501", "message": "insufficient_privilege" } ```