Postgres(psycopg2)如何阻止用户修改运行时配置实现完全只读连接
问题根因拆解
- 你之前使用的
set_session(readonly=True)本质是传递PostgreSQL的default_transaction_read_only参数,仅能拦截事务内对表数据的增删改操作,完全不拦截SET类配置修改命令。SET本身属于会话层配置操作,不属于事务内的数据写入行为,因此这个配置根本无法阻止你观察到的参数持久化问题。 - 普通数据库角色只要具备连接权限,默认就拥有修改绝大多数会话级运行时参数的能力,包括
search_path、work_mem、statement_timeout等,这类权限和你单独授予的表级SELECT权限完全无关,属于角色默认的基础会话能力。 - 之前尝试通过psql查找权限回收选项找不到是正常的:PostgreSQL默认没有提供直接回收“会话参数修改权限”的开关,这类管控需要通过事件触发器强制实现,不是简单的GRANT/REVOKE语句能完成的。
可落地实现方案
服务端硬拦截方案(一劳永逸,推荐优先配置)
这是唯一能从数据库层面真正禁止只读用户修改会话参数的方案,不受连接客户端类型影响:
创建针对只读角色的事件触发器,拦截所有SET/RESET命令执行
-- 定义触发函数,判断当前连接角色后拦截配置修改操作 CREATE OR REPLACE FUNCTION block_set_for_readonly() RETURNS event_trigger AS $$ BEGIN -- 替换为你的实际只读数据库用户名 IF current_user = 'your_readonly_account' THEN RAISE EXCEPTION '只读用户禁止修改会话运行参数: %', tg_tag; END IF; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- 绑定事件触发器,匹配所有SET/RESET类命令 CREATE EVENT TRIGGER block_readonly_set ON ddl_command_start WHEN TAG IN ('SET', 'RESET', 'SET SESSION AUTHORIZATION') EXECUTE FUNCTION block_set_for_readonly();注意:上述事件触发器拦截SET命令的能力需要PostgreSQL 11及以上版本支持,更低版本的SET命令不会进入DDL事件捕获流程,需要结合连接层的鉴权逻辑实现拦截。
配置完成后,只要这个只读账号尝试执行任何修改会话参数的语句,会直接被数据库抛出错误终止执行,根本没有生效的机会。
给只读角色绑定固定的会话参数配置,每次连接自动加载
-- 替换为实际的用户名、数据库名、固定schema路径 ALTER ROLE your_readonly_account IN DATABASE your_target_db SET default_transaction_read_only = on SET search_path = 'my_schema,public' SET statement_timeout = '30s';这个配置会在用户每次建立连接时自动注入,不需要用户手动执行任何命令,同时避免用户手动修改参数绕过配置。
连接侧补充配置
在服务端配置完成的基础上,psycopg2连接时可以通过连接参数进一步强化只读能力,不需要在游标中手动执行设置语句:
import psycopg2 # 连接建立时直接传入只读相关参数,避免后续代码误操作 readonly_conn = psycopg2.connect( READONLY_DATABASE_URL, options="-c default_transaction_read_only=on " "-c search_path=my_schema,public" )
注意这部分配置只是补充,没有服务端的事件触发器拦截的话,用户依然可以在连接建立后手动执行SET命令覆盖这些连接参数。
常见避坑提示
- 不要误以为表级SELECT权限等于完全只读:会话层的参数修改权限是独立于表权限存在的,仅靠GRANT SELECT无法实现完整的只读管控。
- 不要依赖psycopg2层面的只读拦截:所有客户端层面的配置都可以被用户执行的SET命令覆盖,必须把拦截逻辑放到数据库服务端才能真正生效。
内容的提问来源于stack exchange,提问作者Sachin Raja
相关产品推荐
相关产品推荐

