企业官网建设流程全解析

用 PostgreSQL 原生用户与密码实现 PostgREST 的 SQL 用户管理：基于 pg_authid 与 SCRAM-SHA-256 的完整实战

【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrest

导读

本文以 PostgREST 官方 How-To 文档 sql-user-management-using-postgres-users-and-passwords.rst 为核心，完整讲解一种不建用户表的登录方案：直接复用 PostgreSQL 系统目录pg_catalog.pg_authid中保存的数据库角色（用户）与 SCRAM-SHA-256 密码哈希，在数据库内部完成密码校验并签发 JWT，从而让 PostgreSQL 的"用户/密码"同时充当 PostgREST API 的认证凭据。读完本文，你将掌握从扩展安装、PBKDF2 推导函数、密码校验函数、登录函数到权限授予、SQL 层与 REST 层测试的完整可落地步骤，并理解其背后的 JWT 认证与角色切换原理。

说明：本文是 SQL User Management（用户表方案） 的替代方案。后文会专门对比两者差异，便于你按需选择。

方案概览：为什么可以直接用 pg_authid

PostgREST 的认证模型（参见 auth.rst）由数据库驱动：PostgREST 只负责"认证"（验证客户端身份），授权完全交给数据库。客户端携带 JWT 请求时，PostgREST 会解析其中的role声明，并通过SET LOCAL ROLE <role>切换到对应的数据库角色执行查询；未携带有效 JWT 时则回退到匿名角色（db-anon-role，默认anon）。这一角色切换机制在源码 src/library/PostgREST/Auth/Jwt.hs 中体现为parseClaims：role声明缺失时取configDbAnonRole作为默认角色。

本方案的核心思路正是建立在"API 用户 == 数据库角色"这一映射之上：

• 无需专用的用户表：除了 PostgreSQL 内置的pg_authid之外，不再维护任何用户存储；
• 一套凭据两处复用：PostgreSQL 的用户名与密码（即pg_authid中保存的内容）同时作为 PostgREST 层的登录凭据；
• 登录入口不变：与用户表方案一样，暴露一个public.login(username, password)函数，校验通过后返回 JWT，客户端随后用Authorization: Bearer <token>访问受保护资源。

两个必须提前知道的前提

1. 仅支持 SCRAM-SHA-256 密码哈希：pg_authid中的rolpassword列同时兼容 MD5 与 SCRAM-SHA-256 两种格式，但本文的校验函数只针对 SCRAM-SHA-256。SCRAM-SHA-256 是 PostgreSQL v14 及以后的默认密码加密方式（password_encryption = scram-sha-256），因此新装的 PostgreSQL 默认即满足条件；
2. 实验性特性，自担风险：官方文档明确警告"这是实验性的，无法提供任何保证，尤其是安全性方面的保证，使用风险自负"。在生产环境使用前，请务必结合你的威胁模型仔细评估，并考虑用独立数据库角色隔离应用账号。

第一步：搭建隔离的基础设施（schema 与扩展）

为避免把内部实现暴露给 API 客户端，所有内部辅助对象都放进一个独立的basic_authschema：

-- We put things inside the basic_auth schema to hide -- them from public view. Certain public procs/views will -- refer to helpers and tables inside. CREATE SCHEMA basic_auth;

接下来安装pgcrypto与pgjwt两个扩展。与用户表方案直接CREATE EXTENSION pgcrypto;不同，这里将扩展装进独立 schema，以便与 API 暴露的 schema 隔离：

CREATE SCHEMA ext_pgcrypto; ALTER SCHEMA ext_pgcrypto OWNER TO postgres; CREATE EXTENSION pgcrypto WITH SCHEMA ext_pgcrypto;

CREATE SCHEMA ext_pgjwt; ALTER SCHEMA ext_pgjwt OWNER TO postgres; CREATE EXTENSION pgjwt WITH SCHEMA ext_pgjwt;

pgjwt扩展负责在 SQL 内签发 JWT（其用法在 sql-user-management.rst 的"JWT from SQL"一节有说明：它是纯 SQL 实现，仅依赖 pgcrypto，即便在 Amazon RDS 这类不支持自装扩展的环境，也可以手动执行其安装脚本）。如果你的环境无法安装扩展，可以参照该节手动创建pgjwt提供的函数。

第二步：PBKDF2 密钥推导函数

SCRAM-SHA-256 不是简单地对密码做一次哈希，而是基于PBKDF2密钥推导函数（以 SHA-256 为伪随机函数、迭代 4096 次、盐长度为 16 字节）生成密钥。为了在 SQL 中复算并校验这个哈希，需要一个 PBKDF2 的 PL/pgSQL 实现（本方案采用 Stack Overflow 上公开的实现）：

CREATE FUNCTION basic_auth.pbkdf2(salt bytea, pw text, count integer, desired_length integer, algorithm text) RETURNS bytea LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE hash_length integer; block_count integer; output bytea; the_last bytea; xorsum bytea; i_as_int32 bytea; i integer; j integer; k integer; BEGIN algorithm := lower(algorithm); CASE algorithm WHEN 'md5' then hash_length := 16; WHEN 'sha1' then hash_length = 20; WHEN 'sha256' then hash_length = 32; WHEN 'sha512' then hash_length = 64; ELSE RAISE EXCEPTION 'Unknown algorithm "%"', algorithm; END CASE; -- block_count := ceil(desired_length::real / hash_length::real); -- FOR i in 1 .. block_count LOOP i_as_int32 := E'\\000\\000\\000'::bytea || chr(i)::bytea; i_as_int32 := substring(i_as_int32, length(i_as_int32) - 3); -- the_last := salt::bytea || i_as_int32; -- xorsum := ext_pgcrypto.HMAC(the_last, pw::bytea, algorithm); the_last := xorsum; -- FOR j IN 2 .. count LOOP the_last := ext_pgcrypto.HMAC(the_last, pw::bytea, algorithm); -- xor the two FOR k IN 1 .. length(xorsum) LOOP xorsum := set_byte(xorsum, k - 1, get_byte(xorsum, k - 1) # get_byte(the_last, k - 1)); END LOOP; END LOOP; -- IF output IS NULL THEN output := xorsum; ELSE output := output || xorsum; END IF; END LOOP; -- RETURN substring(output FROM 1 FOR desired_length); END $$; ALTER FUNCTION basic_auth.pbkdf2(salt bytea, pw text, count integer, desired_length integer, algorithm text) OWNER TO postgres;

要点说明：

• 它调用的是ext_pgcrypto.HMAC，这印证了将扩展放入独立 schema 的写法：函数内部通过带 schema 前缀的方式引用扩展对象；
• algorithm参数支持md5/sha1/sha256/sha512，本文场景固定使用'sha256'；
• 函数被标记为IMMUTABLE，因为同样的输入必然得到同样的输出，这有助于 PostgreSQL 在表达式索引或计划阶段优化。

第三步：check_user_pass —— 解析 SCRAM 哈希并校验密码

用户表方案中对应的辅助函数是basic_auth.user_role(email, pass)（查询basic_auth.users表并用crypt比对）。本方案换用不同的函数名与签名——因为我们想要的是用户名而非邮箱——并且不查任何用户表，直接读取pg_catalog.pg_authid：

CREATE FUNCTION basic_auth.check_user_pass(username text, password text) RETURNS name LANGUAGE sql AS $$ SELECT rolname AS username FROM pg_authid -- regexp-split scram hash: CROSS JOIN LATERAL regexp_match(rolpassword, '^SCRAM-SHA-256\$(.*):(.*)\$(.*):(.*)$') AS rm -- identify regexp groups with sane names: CROSS JOIN LATERAL (SELECT rm[1]::integer AS iteration_count, decode(rm[2], 'base64') as salt, decode(rm[3], 'base64') AS stored_key, decode(rm[4], 'base64') AS server_key, 32 AS digest_length) AS stored_password_part -- calculate pbkdf2-digest: CROSS JOIN LATERAL (SELECT basic_auth.pbkdf2(salt, check_user_pass.password, iteration_count, digest_length, 'sha256')) AS digest_key(digest_key) -- based on that, calculate hashed passwort part: CROSS JOIN LATERAL (SELECT ext_pgcrypto.digest(ext_pgcrypto.hmac('Client Key', digest_key, 'sha256'), 'sha256') AS stored_key, ext_pgcrypto.hmac('Server Key', digest_key, 'sha256') AS server_key) AS check_password_part WHERE rolpassword IS NOT NULL AND pg_authid.rolname = check_user_pass.username -- verify password: AND check_password_part.stored_key = stored_password_part.stored_key AND check_password_part.server_key = stored_password_part.server_key; $$; ALTER FUNCTION basic_auth.check_user_pass(username text, password text) OWNER TO postgres;

这个函数值得逐层拆解——它用一条纯 SQL 语句完成了对 SCRAM-SHA-256 存储格式的完整校验：

1. 正则拆分存储串：PostgreSQL 的rolpassword形如SCRAM-SHA-256$<iter>:<salt>$<stored_key>:<server_key>。正则^SCRAM-SHA-256\$(.*):(.*)\$(.*):(.*)$将其拆为四组：迭代次数（iteration_count，整数）、盐（salt，base64 解码）、stored_key（base64 解码）、server_key（base64 解码）；
2. 重算密钥：用pbkdf2(salt, password, iteration_count, 32, 'sha256')从客户端提供的明文密码重新推导出 32 字节的digest_key；
3. 按 SCRAM 规范重算校验量：stored_key = SHA-256(HMAC(digest_key, 'Client Key'))，server_key = HMAC(digest_key, 'Server Key')——这正是 SCRAM-SHA-256 协议定义的两类认证消息验证量；
4. 常量时间比对：把重算出的两个密钥与pg_authid中存储的两个密钥分别比对，全部相等才返回该角色名；否则查询结果为空（NULL）。

注意stored_password_part.digest_length = 32与算法'sha256'是写死的——这再次呼应了文档开头的限制：本方案只支持 SCRAM-SHA-256，不适用于 MD5 或其他算法。

第四步：公开登录接口 public.login

现在创建对外暴露的登录函数。它接收用户名与密码，凭据正确时返回 JWT：

-- if you are not using psql, you need to replace :DBNAME with the current database's name. ALTER DATABASE :DBNAME SET "app.jwt_secret" to 'reallyreallyreallyreallyverysafe'; CREATE FUNCTION public.login(username text, password text, OUT token text) LANGUAGE plpgsql security definer AS $$ DECLARE _role name; BEGIN -- check email and password SELECT basic_auth.check_user_pass(username, password) INTO _role; IF _role IS NULL THEN RAISE invalid_password USING message = 'invalid user or password'; END IF; -- SELECT ext_pgjwt.sign( row_to_json(r), current_setting('app.jwt_secret') ) AS token FROM ( SELECT login.username as role, extract(epoch FROM now())::integer + 60*60 AS exp ) r INTO token; END; $$; ALTER FUNCTION public.login(username text, password text) OWNER TO postgres;

这段代码里有几个关键点：

• JWT secret 存于数据库：ALTER DATABASE :DBNAME SET "app.jwt_secret" ...把签名密钥保存为数据库属性（GUC），登录函数通过current_setting('app.jwt_secret')读取，从而避免把密钥硬编码进函数体。这沿用了用户表方案中"JWT from SQL"一节的推荐做法；
• JWT 载荷：role声明取当前登录用户名，exp声明为当前时间 + 3600 秒（1 小时）。PostgREST 在 Jwt.hs 的validateClaims中会校验exp/nbf/iat等基于时间的声明（允许 30 秒时钟偏移），所以exp是必须正确设置的关键声明；
• security definer：函数以定义者（这里是postgres超级用户）权限执行，因此匿名角色无需直接访问pg_authid也能完成校验（详见下文权限小节）；
• 统一错误提示：用户名不存在与密码错误统一抛出invalid_password USING message = 'invalid user or password'，避免通过报错差异泄露"用户是否存在"这一信息。

第五步：角色与权限配置

数据库侧：authenticator 与 anon

回忆 auth.rst 的角色体系：PostgREST 使用authenticator角色连接数据库，再按请求身份切换为匿名角色或 JWT 指定的用户角色。以下配置允许匿名用户调用login尝试登录：

CREATE ROLE anon NOINHERIT; CREATE role authenticator NOINHERIT LOGIN PASSWORD 'secret'; GRANT anon TO authenticator; GRANT EXECUTE ON FUNCTION public.login(username text, password text) TO anon;

这里有两处官方文档特别提示的细节：

1. security definer 的收益：public.login定义为security definer，所以匿名角色anon不需要对pg_catalog.pg_authid有任何权限——校验过程在定义者权限下完成；
2. GRANT EXECUTE的必要性：文档说明这一授权"仅为清晰起见，可能并非必需"（PostgREST 的函数权限模型允许某些情况下绕过 execute 权限检查，详见配置文档关于函数权限的说明），保留它可以让意图更明确。

请为authenticator角色选择一个强密码，并记住两个硬性配置要求：

• PostgREST 必须用authenticator连接数据库——对应配置文件的db-uri（或PGRST_DB_URI环境变量）；
• anon必须被设为匿名角色——对应配置文件的db-anon-role = "anon"（或PGRST_DB_ANON_ROLE）。

PostgREST 侧：配置文件

一个最小可用的配置文件示意（完整参数清单见 configuration.rst）：

db-uri = "postgres://authenticator:secret@localhost:5432/postgres" db-anon-role = "anon" jwt-secret = "reallyreallyreallyreallyverysafe" server-host = "127.0.0.1" server-port = 3000

关于jwt-secret的注意点（取自 configuration.rst）：

• 出于安全考虑，密钥必须至少 32 个字符；本示例中的reallyreallyreallyreallyverysafe只是官方文档演示值，上线前必须替换；
• 若未配置jwt-secret，PostgREST 会拒绝所有认证请求；
• 支持以@filename方式从外部文件读取密钥，便于自动化部署；二进制密钥需 base64 编码（可配合jwt-secret-is-base64 = true）。

第六步：端到端测试

创建测试用户

CREATE ROLE foo PASSWORD 'bar';

注意：PostgreSQL v14+ 默认password_encryption = scram-sha-256，因此foo的密码将以 SCRAM-SHA-256 形式存入pg_authid，这正是校验函数所要求的格式。

SQL 层测试

执行登录函数：

SELECT * FROM public.login('foo', 'bar');

应返回一个标量字段，形如：

token ----------------------------------------------------------------------------------------------------------------------------- eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9vIiwiZXhwIjoxNjY4MTg4ODQ3fQ.idBBHuDiQuN_S7JJ2v3pBOr9QypCliYQtCgwYOzAqEk (1 row)

REST 层测试

对应的 API 调用是 POST/rpc/login：

curl "http://localhost:3000/rpc/login" \ -X POST -H "Content-Type: application/json" \ -d '{ "username": "foo", "password": "bar" }'

响应形如（可到 jwt.io 用reallyreallyreallyreallyverysafe解码验证，生产环境务必替换该密钥）：

{ "token": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoic2VwcCIsImV4cCI6MTY2ODE4ODQzN30.WSytcouNMQe44ZzOQit2AQsqTKFD5mIvT3z2uHwdoYY" }

更进阶的 REST 层测试：受保护资源

先为foo用户准备一张表：

CREATE TABLE public.foobar(foo int, bar text, baz float); ALTER TABLE public.foobar owner TO postgres;

然后不带任何认证信息请求：

curl "http://localhost:3000/foobar"

失败是预期行为：未指定用户时 PostgREST 回退到anon角色，而anon没有该表的任何权限，访问被拒。

接下来带上Authorization头（请替换为上面登录接口实际返回的 token，而非示例值）：

curl "http://localhost:3000/foobar" \ -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiZm9vIiwiZXhwIjoxNjY4MTkyMjAyfQ.zzdHCBjfkqDQLQ8D7CHO3cIALF6KBCsfPTWgwhCiHCY"

依然失败，报Permission denied to set role。原因：authenticator 角色还没有被允许切换到foo。执行：

GRANT foo TO authenticator;

再执行上一条 REST 请求，仍然失败——这次是因为foo对该表没有权限。执行：

GRANT SELECT ON TABLE public.foobar TO foo;

再次请求，成功返回空 JSON 数组[]。

这三次失败→修复→成功的迭代，完整展示了 PostgREST 的授权链路：JWT 身份解析 → 角色切换（GRANT ... TO authenticator）→ 表级权限（GRANT ... ON TABLE ...）。三者缺一不可，这正是"数据库负责授权"设计哲学的体现（可进一步阅读 db_authz.rst）。

与用户表方案的对比

两种方案共享同一套"登录函数 + security definer + JWT 签发 + 角色切换"架构。选择要点：用户表方案不要求 PostgreSQL 版本、支持自定义用户属性（如邮箱、资料字段），更贴近传统 Web 应用习惯；本方案则把用户管理完全交给 PostgreSQL 自身，任何CREATE ROLE/ALTER ROLE ... PASSWORD操作都即时生效，无需同步两张表，适合希望"数据库账号即 API 账号"的运维场景。

常见问题与注意事项

• 登录报错或返回 NULL：先确认数据库password_encryption为scram-sha-256（v14+ 默认），且目标角色的rolpassword确实以SCRAM-SHA-256$开头；SELECT rolpassword FROM pg_authid WHERE rolname='foo';可自查（需要超级用户权限）；
• 更换 JWT 密钥：同时修改数据库属性app.jwt_secret与配置文件jwt-secret，两者必须一致，否则登录函数签出的 token 会被 PostgREST 判定为无效签名；修改后旧 token 立即全部失效；
• 匿名访问：本方案只开放了login给anon，请确保anon对其他对象没有任何权限，否则未登录客户端可绕过认证直接读数据；
• 密码轮换：ALTER ROLE foo PASSWORD 'new';后无需任何额外同步，下次登录即使用新密码——这是本方案相对用户表方案最直观的运维优势；
• 安全边界：本方案要求把pg_authid（含全部数据库账号哈希）暴露给security definer函数路径，属于实验性设计；官方未提供安全担保，请勿在未充分评估的敏感环境直接照搬。

进一步参考：

• 认证与角色体系总览：docs/references/auth.rst
• 全部配置参数（db-uri、db-anon-role、jwt-secret 等）：docs/references/configuration.rst
• 数据库授权模型：docs/explanations/db_authz.rst
• 用户表方案对照：docs/how-tos/sql-user-management.rst
• 外部认证服务方案：docs/explanations/external_auth.rst

【免费下载链接】postgrestREST API for any Postgres database项目地址: https://gitcode.com/GitHub_Trending/po/postgrest