PostgREST 使用指南：一键将 PostgreSQL 变成 REST API

记录一下 PostgREST 的学习与实践过程

什么是 PostgREST？

PostgREST 是一个独立的 Web 服务器，它直接把 PostgreSQL 数据库暴露为 RESTful API。只需编写 SQL 定义表结构和权限，无需额外后端代码。

官方网站：https://postgrest.org/

核心特点：

无需编写后端代码
自动生成 CRUD API
内置 JWT 认证
行级安全策略（RLS）
支持关联查询

安装

Docker

docker run --rm \
  -p 3000:3000 \
  -e PGRST_DB_URI="postgres://user:pass@localhost:5432/dbname" \
  -e PGRST_DB_SCHEMA="api" \
  -e PGRST_DB_ANON_ROLE="anon" \
  -e PGRST_JWT_SECRET="your-secret-key" \
  postgrest/postgrest

Linux下二进制安装

# 下载 releases
wget https://github.com/PostgREST/postgrest/releases/latest/download/postgrest-linux-static.tar.xz
tar -xf postgrest-linux-static.tar.xz
# 启动执行
./postgrest postgrest.conf

快速开始

1. 创建数据库和表

-- 创建数据库
CREATE DATABASE postgrest;

-- 连接数据库
\c postgrest

-- 创建 API schema
CREATE SCHEMA api;

-- 创建测试表
create table api.todos (
  id int primary key generated by default as identity,
  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;
-- 允许匿名角色使用api schema
GRANT USAGE ON SCHEMA api TO web_anon;
-- 允许匿名角色使用api下todos表使用select
GRANT SELECT ON api.todos TO web_anon;

-- 创建认证角色（PostgREST 会使用这个角色连接）
drop role if exists authenticator;
create role authenticator noinherit login password 'test1234';

-- 认证角色继承匿名角色(允许认证角色变成web_anon)，注意该角色仅允许登录，进入数据库后需要切换身份才能有对应权限
grant web_anon to authenticator;

-- 创建auth_user认证角色
create role auth_user nologin;
-- 认证角色继承auth_user角色
grant auth_user to authenticator;

-- 赋予auth_user访问api schema及其下todos表所有权限（区别的是匿名角色只有查询权限）
grant usage on schema api to auth_user;
grant all on api.todos to auth_user;

2. 配置文件

# postgrest.conf
db-uri = "postgres://authenticator:test1234@localhost:5432/postgrest"
db-schema = "api"
db-anon-role = "web_anon"
jwt-secret = "ZXngSe3WhHr_KcJfptS7NrDLIajwB0gfrC8wKP_769A"

3. 启动

postgrest postgrest.conf

4. 访问 API

# 获取所todo项
curl http://192.168.31.132:3000/todos

# 根据 ID 查询
curl http://192.168.31.132:3000/todos?id=eq.1

# 过滤
curl http://192.168.31.132:3000/todos?id=gt.1

# 分页
curl http://192.168.31.132:3000/todos?limit=2&offset=0

# 排序
curl http://192.168.31.132:3000/todos?order=id.desc

核心概念

Schema

PostgREST 只暴露指定 schema（通常是 api）下的表和视图。

-- 设置默认 search_path
ALTER DATABASE postgrest SET search_path TO api, public;

匿名角色 vs 认证角色

角色	说明
`web_anon`	匿名角色，未认证用户使用
`auth_user`	登录用户角色，认证后用户使用
`authenticator`	认证角色，继承 web_anon 和 auth_user 权限，已登录用户使用按需切换角色身份，例如jwt token合法切换jwt内提供的role，否则默认为web_anon

-- 创建认证角色
CREATE ROLE authenticator noinherit login password 'secret';
GRANT web_anon TO authenticator;  -- 继承 web_anon 的权限

行级安全策略（RLS）

RLS 是 PostgREST 的精髓，确保用户只能访问自己的数据。

-- 用户只能访问自己的笔记
ALTER TABLE api.notes enable row level security;

CREATE POLICY "用户只能访问自己的笔记" ON api.notes
  FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

auth.uid() 返回 JWT token 解码后的用户 ID。

多 Schema 使用

官方支持的多 schema

PostgREST 确实支持多 schema，但设计初衷是多租户隔离，不是给普通项目做业务分离。

db-schemas = "auth,api,tasks,news"

多 schema 的调用方式

多 schema 时，默认只操作第一个 schema。切换 schema 必须使用 Profile header：

操作	Header	说明
GET/HEAD	`Accept-Profile: schema名`	切换读取的 schema
POST/PATCH/DELETE	`Content-Profile: schema名`	切换写入的 schema

# 默认查 auth schema 的表
curl http://localhost:3000/users

# 查 api schema 的表（必须加 header）
curl http://localhost:3000/todos \
  -H "Accept-Profile: api"

# RPC 函数也一样
curl -X POST http://localhost:3000/rpc/login \
  -H "Content-Type: application/json" \
  -H "Content-Profile: auth" \
  -d '{"username":"hao","password":"123"}'

多 schema 的局限性

调用方式	行为
不加 header	只在第一个 schema 找表/函数
加 header	切换到指定 schema

这对于多业务确实不方便，每个业务都要加 header。

实际解决方案

方案	说明	适用场景
单 schema（推荐）	所有表函数放 `api`，其他 schema 只放辅助函数	绝大多数项目
按 header 切换	严格 schema 隔离的多租户场景	SaaS 多租户
不同 API 实例	不同业务完全独立部署	微服务架构

最佳实践

一个项目 = 一个 api schema
- auth schema：只放用户认证相关（login/signup）
- 所有业务表都放 api schema

配置示例：

db-schemas = "api"
db-extra-search-path = "api"
db-anon-role = "web_anon"
jwt-secret = "your-32-char-secret"

这样配置简单，调用也不用加 header。

跨 schema 访问： 如果确实需要分离（比如 auth 和 api 分开管理），可以在函数里用全限定名访问：

-- 在 api.login 函数里访问 auth.users
SELECT * FROM auth.users WHERE ...

认证流程

1. 注册用户

-- 手动插入用户（生产环境应通过 API）
INSERT INTO auth.users (email, password)
VALUES ('test@example.com', crypt('password123', gen_salt('bf')));

2. 登录获取 Token

PostgREST 内置 /rpc/login 端点（需自定义函数），或自行创建：

-- 注册函数
create or replace function auth.register(p_username text, p_password text, p_email text default null)
returns json as $$
declare
  hashed_password text;
  new_user_id integer;
begin
  -- 检查用户名是否已存在
  if exists (select 1 from auth.users where username = p_username) then
    raise exception 'Username already exists' using errcode = '409';
  end if;
  -- 用 pgcrypto 的 crypt() 加密（自动生成 salt）
  hashed_password := crypt(p_password, gen_salt('bf'));  -- bcrypt 算法
  -- 插入用户
  insert into auth.users (username, email, password_hash)
  values (p_username, p_email, hashed_password)
  returning id into new_user_id;
  return json_build_object('success', true, 'user_id', new_user_id);
end;
$$ language plpgsql security definer;

-- 创建登录函数
create or replace function auth.login(p_username text, p_password text)
returns json as $$
declare
  user_record record;
  jwt_secret text := 'ZXngSe3WhHr_KcJfptS7NrDLIajwB0gfrC8wKP_769A';
  jwt_token text;
  payload json;
begin
  -- 验证用户名密码
  select \* into user_record 
  from auth.users 
  where users.username = p_username;
  if user_record is null then
    raise exception 'Invalid credentials' using errcode = '401';
  end if;
  -- 验证密码（crypt 会自动用 stored hash 里的 salt）
  if user_record.password_hash != crypt(p_password, user_record.password_hash) then
    raise exception 'Invalid credentials' using errcode = '401';
  end if;
  -- 生成 JWT payload
  payload := json_build_object(
    'role', user_record.role,
    'username', user_record.username,
    'exp', extract(epoch from now() + interval '24 hours')::integer
  );
  -- 签名 JWT
  jwt_token := auth.sign_jwt(payload, jwt_secret);
  return json_build_object('token', jwt_token, 'username', user_record.username);
end;
$$ language plpgsql security definer;

-- 辅助函数url_encode
create or replace function auth.url_encode(data text) returns text as $$
  select rtrim(
    translate(
      encode(convert_to(data, 'utf8'), 'base64'),
      E'+/\n',  -- 替换 +, /, 换行符
      '-_ '     -- 换成 -, _, 空格（空格在trim里去掉）
    ),
    ' ='       -- 去掉尾部空格和 =
  );
$$ language sql immutable;


-- 辅助函数 sign_jwt

create or replace function auth.sign_jwt(payload json, secret text)
returns text as $$
declare
  header text;
  payload_b64 text;
  signature text;
  payload_text text;
begin
  -- 关键：JSON 转文本时去掉空格（紧凑格式）
  payload_text := jsonb_build_object(
    'role', payload->>'role',
    'username', payload->>'username',
    'exp', (payload->>'exp')::bigint
  )::text;
  -- Base64URL header
  header := replace(replace
    replace(encode('{"alg":"HS256","typ":"JWT"}'::bytea, 'base64'), E'\n', ''),
    '+', '-'), '/', '_');
  header := rtrim(header, '=');
  -- Base64URL payload
  payload_b64 := replace(replace(
    replace(encode(payload_text::bytea, 'base64'), E'\n', ''),
    '+', '-'), '/', '_');
  payload_b64 := rtrim(payload_b64, '=');
  -- Base64URL signature（HMAC 结果直接编码，不是 hex）
  signature := replace(replace(
    replace(encode(hmac(header || '.' || payload_b64, secret, 'sha256'), 'base64'), E'\n', ''),
    '+', '-'), '/', '_');
  signature := rtrim(signature, '=');
  return header || '.' || payload_b64 || '.' || signature;
end;
$$ language plpgsql immutable;

3. 使用 Token

# 登录
curl -X POST http://localhost:3000/rpc/login \
  -H "Content-Profile: auth" \
  -H "Content-Type: application/json" \
  -d '{"username":"zhangsan","password":"password123"}'

# 响应：{"token":"eyJhbGc..."}

# 访问受保护的资源
curl http://localhost:3000/notes \
  -H "Authorization: Bearer eyJhbGc..."

高级用法

关联查询

# 获取笔记及其标签
curl http://localhost:3000/notes?select=*,note_tags(tag(name))

# 内联过滤
curl http://localhost:3000/notes?select=*,tags(t())&tags.name=eq.重要

视图

-- 创建视图暴露复杂查询
CREATE VIEW api.notes_with_tags AS
SELECT
  n.*,
  COALESCE(json_agg(t.name) FILTER (WHERE t.name IS NOT NULL), '[]') as tags
FROM api.notes n
LEFT JOIN api.note_tags nt ON nt.note_id = n.id
LEFT JOIN api.tags t ON t.id = nt.tag_id
GROUP BY n.id;

函数

-- 创建 RPC 函数
CREATE OR REPLACE FUNCTION api.search_notes(query text)
RETURNS SETOF api.notes AS $$
BEGIN
  RETURN QUERY
  SELECT * FROM api.notes
  WHERE title ILIKE '%' || query || '%'
     OR content ILIKE '%' || query || '%';
END;
$$ LANGUAGE plpgsql;

调用：

curl "http://localhost:3000/rpc/search_notes?query=postgrest"

实践

项目结构

myapp/
├── schema.sql      -- 数据库表结构
├── roles.sql       -- 角色和权限
└── postgrest.conf  -- 配置文件

schema.sql 模板

-- 创建 schema
CREATE SCHEMA IF NOT EXISTS api;
CREATE SCHEMA IF NOT EXISTS auth;

-- 认证用户表
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE TABLE IF NOT EXISTS auth.users (
  id serial primary key,
  email text not null unique,
  password text not null,
  created_at timestamptz default now()
);

-- 业务表
CREATE TABLE IF NOT EXISTS api.notes (
  id serial primary key,
  title text not null,
  content text,
  user_id int references auth.users(id),
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- 启用 RLS
ALTER TABLE api.notes enable row level security;

-- 策略
CREATE POLICY "用户只能访问自己的笔记"
  ON api.notes FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- 自动更新时间戳
CREATE OR REPLACE FUNCTION api.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notes_updated_at
  BEFORE UPDATE ON api.notes
  FOR EACH ROW EXECUTE FUNCTION api.update_updated_at();

角色配置

-- 创建角色
CREATE ROLE anon nologin;
CREATE ROLE authenticator noinherit login password 'your-jwt-secret';

-- 授予权限
GRANT USAGE ON SCHEMA api TO anon, authenticator;
GRANT USAGE ON SCHEMA auth TO anon, authenticator;
GRANT ALL ON api.notes TO authenticator;
GRANT SELECT ON api.notes TO anon;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA api TO anon, authenticator;
GRANT SELECT, INSERT ON auth.users TO anon, authenticator;

优缺点

优点

优点	说明
🚀 快速开发	零后端代码，SQL 定义即 API
🔒 安全	RLS + JWT，行级安全
📊 性能	直接数据库查询，无额外抽象层
📝 文档简洁	自动生成 OpenAPI
🐳 部署简单	单一二进制文件

缺点

缺点	说明
📦 仅 PostgreSQL	不支持其他数据库
🔧 复杂业务逻辑	需要借助 PostgreSQL 函数
📝 学习曲线	需要了解 SQL 和 RLS
🔀 不适合微服务	单一 API，难以拆分

适用场景

适合：

CRUD 为主的应用
原型快速验证
中小型项目
后端开发者稀缺的团队

不适合：

复杂业务逻辑
非关系型数据需求
需要服务间通信
高并发写入场景

调试技巧

# 查看 PostgREST 日志
tail -f /var/log/postgresql/postgrest.log

# 测试 RLS 策略
SET ROLE anon;
SELECT * FROM api.notes;  -- 应只返回空

SET ROLE authenticator;
SELECT * FROM api.notes;  -- 应返回用户自己的数据

参考资料

本文首发于 2026-03-21