[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" } ```