用 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_authidPostgREST 的认证模型参见 auth.rst由数据库驱动PostgREST 只负责认证验证客户端身份授权完全交给数据库。客户端携带 JWT 请求时PostgREST 会解析其中的role声明并通过SET LOCAL ROLE role切换到对应的数据库角色执行查询未携带有效 JWT 时则回退到匿名角色db-anon-role默认anon。这一角色切换机制在源码 src/library/PostgREST/Auth/Jwt.hs 中体现为parseClaimsrole声明缺失时取configDbAnonRole作为默认角色。本方案的核心思路正是建立在API 用户 数据库角色这一映射之上无需专用的用户表除了 PostgreSQL 内置的pg_authid之外不再维护任何用户存储一套凭据两处复用PostgreSQL 的用户名与密码即pg_authid中保存的内容同时作为 PostgREST 层的登录凭据登录入口不变与用户表方案一样暴露一个public.login(username, password)函数校验通过后返回 JWT客户端随后用Authorization: Bearer token访问受保护资源。两个必须提前知道的前提仅支持 SCRAM-SHA-256 密码哈希pg_authid中的rolpassword列同时兼容 MD5 与 SCRAM-SHA-256 两种格式但本文的校验函数只针对 SCRAM-SHA-256。SCRAM-SHA-256 是 PostgreSQL v14 及以后的默认密码加密方式password_encryption scram-sha-256因此新装的 PostgreSQL 默认即满足条件实验性特性自担风险官方文档明确警告这是实验性的无法提供任何保证尤其是安全性方面的保证使用风险自负。在生产环境使用前请务必结合你的威胁模型仔细评估并考虑用独立数据库角色隔离应用账号。第一步搭建隔离的基础设施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_authidCREATE 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 存储格式的完整校验正则拆分存储串PostgreSQL 的rolpassword形如SCRAM-SHA-256$iter:salt$stored_key:server_key。正则^SCRAM-SHA-256\$(.*):(.*)\$(.*):(.*)$将其拆为四组迭代次数iteration_count整数、盐saltbase64 解码、stored_keybase64 解码、server_keybase64 解码重算密钥用pbkdf2(salt, password, iteration_count, 32, sha256)从客户端提供的明文密码重新推导出 32 字节的digest_key按 SCRAM 规范重算校验量stored_key SHA-256(HMAC(digest_key, Client Key))server_key HMAC(digest_key, Server Key)——这正是 SCRAM-SHA-256 协议定义的两类认证消息验证量常量时间比对把重算出的两个密钥与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 databases 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;这里有两处官方文档特别提示的细节security definer 的收益public.login定义为security definer所以匿名角色anon不需要对pg_catalog.pg_authid有任何权限——校验过程在定义者权限下完成GRANT EXECUTE的必要性文档说明这一授权仅为清晰起见可能并非必需PostgREST 的函数权限模型允许某些情况下绕过 execute 权限检查详见配置文档关于函数权限的说明保留它可以让意图更明确。请为authenticator角色选择一个强密码并记住两个硬性配置要求PostgREST 必须用authenticator连接数据库——对应配置文件的db-uri或PGRST_DB_URI环境变量anon必须被设为匿名角色——对应配置文件的db-anon-role anon或PGRST_DB_ANON_ROLE。PostgREST 侧配置文件一个最小可用的配置文件示意完整参数清单见 configuration.rstdb-uri postgres://authenticator:secretlocalhost: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-secretPostgREST 会拒绝所有认证请求支持以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/logincurl 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。与用户表方案的对比维度用户表方案sql-user-management.rst本方案pg_authid SCRAM-SHA-256用户存储自建basic_auth.users表email/pass/rolePostgreSQL 内置pg_authid用户标识邮箱email数据库角色名username密码存储应用层 bcryptpgcrypto的crypt/gen_salt(bf)PostgreSQL 原生 SCRAM-SHA-256密码校验crypt(input, stored_hash)一行搞定需 PBKDF2 重算 SCRAM 双密钥比对角色约束自建 trigger 模拟外键检查pg_roles天然一致用户就是角色依赖pgcrypto、pgjwtpgcrypto、pgjwt、PBKDF2 自定义函数成熟度官方长期推荐官方标记 experimental安全性自担两种方案共享同一套登录函数 security definer JWT 签发 角色切换架构。选择要点用户表方案不要求 PostgreSQL 版本、支持自定义用户属性如邮箱、资料字段更贴近传统 Web 应用习惯本方案则把用户管理完全交给 PostgreSQL 自身任何CREATE ROLE/ALTER ROLE ... PASSWORD操作都即时生效无需同步两张表适合希望数据库账号即 API 账号的运维场景。常见问题与注意事项登录报错或返回 NULL先确认数据库password_encryption为scram-sha-256v14 默认且目标角色的rolpassword确实以SCRAM-SHA-256$开头SELECT rolpassword FROM pg_authid WHERE rolnamefoo;可自查需要超级用户权限更换 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创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考 
长河君 · 2026/9/10 18:22:11 · 阅读 1.2k 用 PostgreSQL 原生用户与密码实现 PostgREST 的 SQL 用户管理：基于 pg_authid 与 SCRAM-SHA-256 的完整实战
用 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_authidPostgREST 的认证模型参见 auth.rst由数据库驱动PostgREST 只负责认证验证客户端身份授权完全交给数据库。客户端携带 JWT 请求时PostgREST 会解析其中的role声明并通过SET LOCAL ROLE role切换到对应的数据库角色执行查询未携带有效 JWT 时则回退到匿名角色db-anon-role默认anon。这一角色切换机制在源码 src/library/PostgREST/Auth/Jwt.hs 中体现为parseClaimsrole声明缺失时取configDbAnonRole作为默认角色。本方案的核心思路正是建立在API 用户 数据库角色这一映射之上无需专用的用户表除了 PostgreSQL 内置的pg_authid之外不再维护任何用户存储一套凭据两处复用PostgreSQL 的用户名与密码即pg_authid中保存的内容同时作为 PostgREST 层的登录凭据登录入口不变与用户表方案一样暴露一个public.login(username, password)函数校验通过后返回 JWT客户端随后用Authorization: Bearer token访问受保护资源。两个必须提前知道的前提仅支持 SCRAM-SHA-256 密码哈希pg_authid中的rolpassword列同时兼容 MD5 与 SCRAM-SHA-256 两种格式但本文的校验函数只针对 SCRAM-SHA-256。SCRAM-SHA-256 是 PostgreSQL v14 及以后的默认密码加密方式password_encryption scram-sha-256因此新装的 PostgreSQL 默认即满足条件实验性特性自担风险官方文档明确警告这是实验性的无法提供任何保证尤其是安全性方面的保证使用风险自负。在生产环境使用前请务必结合你的威胁模型仔细评估并考虑用独立数据库角色隔离应用账号。第一步搭建隔离的基础设施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_authidCREATE 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 存储格式的完整校验正则拆分存储串PostgreSQL 的rolpassword形如SCRAM-SHA-256$iter:salt$stored_key:server_key。正则^SCRAM-SHA-256\$(.*):(.*)\$(.*):(.*)$将其拆为四组迭代次数iteration_count整数、盐saltbase64 解码、stored_keybase64 解码、server_keybase64 解码重算密钥用pbkdf2(salt, password, iteration_count, 32, sha256)从客户端提供的明文密码重新推导出 32 字节的digest_key按 SCRAM 规范重算校验量stored_key SHA-256(HMAC(digest_key, Client Key))server_key HMAC(digest_key, Server Key)——这正是 SCRAM-SHA-256 协议定义的两类认证消息验证量常量时间比对把重算出的两个密钥与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 databases 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;这里有两处官方文档特别提示的细节security definer 的收益public.login定义为security definer所以匿名角色anon不需要对pg_catalog.pg_authid有任何权限——校验过程在定义者权限下完成GRANT EXECUTE的必要性文档说明这一授权仅为清晰起见可能并非必需PostgREST 的函数权限模型允许某些情况下绕过 execute 权限检查详见配置文档关于函数权限的说明保留它可以让意图更明确。请为authenticator角色选择一个强密码并记住两个硬性配置要求PostgREST 必须用authenticator连接数据库——对应配置文件的db-uri或PGRST_DB_URI环境变量anon必须被设为匿名角色——对应配置文件的db-anon-role anon或PGRST_DB_ANON_ROLE。PostgREST 侧配置文件一个最小可用的配置文件示意完整参数清单见 configuration.rstdb-uri postgres://authenticator:secretlocalhost: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-secretPostgREST 会拒绝所有认证请求支持以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/logincurl 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。与用户表方案的对比维度用户表方案sql-user-management.rst本方案pg_authid SCRAM-SHA-256用户存储自建basic_auth.users表email/pass/rolePostgreSQL 内置pg_authid用户标识邮箱email数据库角色名username密码存储应用层 bcryptpgcrypto的crypt/gen_salt(bf)PostgreSQL 原生 SCRAM-SHA-256密码校验crypt(input, stored_hash)一行搞定需 PBKDF2 重算 SCRAM 双密钥比对角色约束自建 trigger 模拟外键检查pg_roles天然一致用户就是角色依赖pgcrypto、pgjwtpgcrypto、pgjwt、PBKDF2 自定义函数成熟度官方长期推荐官方标记 experimental安全性自担两种方案共享同一套登录函数 security definer JWT 签发 角色切换架构。选择要点用户表方案不要求 PostgreSQL 版本、支持自定义用户属性如邮箱、资料字段更贴近传统 Web 应用习惯本方案则把用户管理完全交给 PostgreSQL 自身任何CREATE ROLE/ALTER ROLE ... PASSWORD操作都即时生效无需同步两张表适合希望数据库账号即 API 账号的运维场景。常见问题与注意事项登录报错或返回 NULL先确认数据库password_encryption为scram-sha-256v14 默认且目标角色的rolpassword确实以SCRAM-SHA-256$开头SELECT rolpassword FROM pg_authid WHERE rolnamefoo;可自查需要超级用户权限更换 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创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考