PostgREST的简单使用

官方文档 /

中文翻译文档 /

官方文档写的很详细，建议从头到尾过一遍。

下面是我实操过程（按照实际使用，建议关闭匿名访问，开JWT方式）

github下载二进制包

.2.8

直接下载二进制包 postgrest-v12.2.8-linux-static-x86-64.tar.xz 并解压

我这里是解压到 postgres账号的家目录下。

创建pg账号和库表

su - postgres
psql 登陆pg

create database sbtest;
\c sbtest;
create user user1 login password 'password1';
alter database sbtest owner to user1 ;
grant connect on database sbtest to user1 ;

grant all on all TABLES in schema public to user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT all ON TABLES TO user1;

create table public.sbt1(
    id serial primary key ,
    name text,
    sex text
);
insert into public.sbt1(name,sex) values('aaa','M');
insert into public.sbt1(name,sex) values('bb','F');
insert into public.sbt1(name,sex) values('张三','F');


create schema sm1;
grant usage on schema sm1 to user1 ;
grant all on all TABLES in schema sm1 to user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA sm1 GRANT all ON TABLES TO user1;

create table sm1.sbt1(
    id serial primary key ,
    name text,
    sex text
);

insert into sm1.sbt1(name,sex) values('aaa','M');

创建配置文件

配置文件： 
su - postgres

cat postgrest.conf   内容如下：
db-uri = "postgres://user1:password1@192.168.31.181:5432/sbtest"

# 需要暴露给客户端的schemas有哪些
db-schemas = "public, sm1"

# 因为 PostgREST 支持 JWT 认证，这个配置项配置在没有客户端认证的情况下 数据库使用哪个角色来进行操作
db-anon-role = "anon"

jwt-secret = "LpTtJT8aeEYGnwCNYltIZ3FZvKA5DJWA"
jwt-secret-is-base64 = false

server-port = 3000

log-level = "info"

创建一个anon账号

-- 创建一个pg账号，什么权限也不给即可（即禁止匿名访问，连查询也不让进行）
CREATE ROLE anon NOLOGIN;

生成JWT Token

参考 .html#step-3-sign-a-token

到    填入我们这里的相关的信息，然后生成token

具体如下图所示：

前台启动postgrest进行测试

./postgrest postgrest.conf

在bash命令行下进行restful调用测试

export TOKEN="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoidXNlcjEifQ.MJ2qCPbAPAG-t2OLEmgUz_hs9H00skBKqLWVBIpJWmo"


三种常用的例子（public schema）：
# 全表查询
curl http://127.0.0.1:3000/sbt1 -X GET \
     -H "Authorization: Bearer $TOKEN"   \
     -H "Content-Type: application/json"

# 插入记录
curl http://127.0.0.1:3000/sbt1 -X POST \
     -H "Authorization: Bearer $TOKEN"   \
     -H "Content-Type: application/json" \
     -d '{"name": "张三","sex":"M"}'
	
# 全表更新
curl http://127.0.0.1:3000/sbt1 -X PATCH \
     -H "Authorization: Bearer $TOKEN"    \
     -H "Content-Type: application/json"  \
     -d '{"sex": "F"}'
	 

对于非public schema的请求，需要带请求头：
    对于GET或HEAD，使用以下命令选择架构 Accept-Profile 。
        curl "http://127.0.0.1:3000/items" \
        -H "Accept-Profile: schema_aa"

    对于POST、PATCH、PUT和DELETE，选择模式 Content-Profile 。
        curl "http://127.0.0.1:3000/items" \
        -X POST -H "Content-Type: application/json" \
        -H "Content-Profile: schema_bb" \
        -d '{...}'

下面是执行多次数据插入后，去pg数据库查看的结果：

当然，还有很多复杂的restful增删改查的写法，具体参考官方文档即可。

配置systemd开机自启动

测试没问题后，就可以把配置文件和二进制文件移动到自定义的目录下（根据自己习惯定）

配置system开机自启动： 
useradd -M -U -d /nonexistent -s /usr/sbin/nologin postgrest

vim /etc/systemd/system/postgrest.service
[Unit]
Description=PostgREST API server
After=network.target postgresql.service

[Service]
Type=simple
User=postgrest
Group=postgrest
ExecStart=/bin/postgrest /etc/postgrest.conf
ExecReload=/bin/kill -SIGUSR1 $MAINPID
Restart=always
RestartSec=5
LimitNOFILE=10000

[Install]
WantedBy=multi-user.target    

注意：Schema 缓存问题

默认情况下，PostgREST在启动的时候，会加载一次schema信息到自身缓存里面，后续PG里面如果有新增表，则PostgREST是无法识别的（直接反映就是通过api调用这个新的表会报404错误）。

解决方法也很简单，

方法1、给进程发送 -SIGUSR1 信号

killall -SIGUSR1 postgrest

方法2、使用触发器方式，

-- Create an event trigger function
CREATE OR REPLACE FUNCTION pgrst_watch() RETURNS event_trigger
  LANGUAGE plpgsql
  AS $$
BEGIN
  NOTIFY pgrst, 'reload schema';
END;
$$;

-- This event trigger will fire after every ddl_command_end event
CREATE EVENT TRIGGER pgrst_watch
  ON ddl_command_end
  EXECUTE PROCEDURE pgrst_watch();

更详细的，可以参考官方文档 .html