暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / PostgreSQL 中 Row-Level Security 原理简析

PostgreSQL 中 Row-Level Security 原理简析

内核开发者 2023-12-05
668

1. 简介

RLS（ROW Level Security）是PostgreSQL 9.5版本中新增特性，提供了基于行的安全策略，限制数据库用户的查看表数据权限。在 9.5 版本之前，数据库中对用户的权限管控在表级别，例如：限制某个用户是否允许查询某个表。采用RLS后，不同的用户可以访问到一个表中不同的数据。

默认情况下，表没有任何安全策略限制，对于 DML 来说表中所有的行都是平等的。当在一个表上启用行级别安全测试后，所有对该表的 DML 操作都必须被一条行安全性策略所允许（默认情况下，table owner 、bypassrls = true 与 superuser 不受 RLS 限制）。行级别安全策略可以指定特定的 statements (SELECT UPDATE DELETE INSERT 及其各种组合)、表名、用户列表，一个策略可应用到多个用户，并且继承规则也适用。

2. 使用方式

2.1 DDL 命令

  • CREATE POLICY：创建策略；

CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
复制

  • ALTER POLICY：修改策略；

ALTER POLICY name ON table_name RENAME TO new_name

ALTER POLICY name ON table_name
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
复制

  • DROP POLICY：删除策略；

DROP POLICY [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
复制

  • ALTER TABLE：用于行级安全性的启用 / 禁用；

ALTER TABLE name [ ENABLE | DISABLE ] ROW LEVEL SECURITY
复制

每个策略都有一个名字，每个表可以定义多个策略，因为策略是针对表的，所以表内的多个策略名字必须唯一，但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR / AND 的关系。

2.2 效果

本文主要为介绍 RLS 实现原理，因此此处仅简单介绍下 RLS 的使用效果。

3. 原理

3.1 策略提取

DML 语句在使用时会涉及到至少一个表，以 SELECT 为例，通常会涉及到以下调用：

#0  RelationBuildRowSecurity (relation=0x7f3e22b04358) at policy.c:199
#1  0x0000000000a80488 in RelationBuildDesc (targetRelId=24582, insertIt=true) at relcache.c:1236
#2  0x0000000000a81a6a in RelationIdGetRelation (relationId=24582) at relcache.c:2107
#3  0x000000000049dafb in relation_open (relationId=24582, lockmode=0) at relation.c:59
#4  0x000000000049dd05 in relation_openrv_extended (relation=0x2882a30, lockmode=1, missing_ok=true) at relation.c:193
#5  0x0000000000535be9 in table_openrv_extended (relation=0x2882a30, lockmode=1, missing_ok=true) at table.c:137
#6  0x000000000061b173 in parserOpenTable (pstate=0x2882c80, relation=0x2882a30, lockmode=1) at parse_relation.c:1367
#7  0x000000000061b38d in addRangeTableEntry (pstate=0x2882c80, relation=0x2882a30, alias=0x0, inh=true, inFromCl=true)
    at parse_relation.c:1443
#8  0x00000000005fb2ef in transformTableEntry (pstate=0x2882c80, r=0x2882a30) at parse_clause.c:397
#9  0x00000000005fcc39 in transformFromClauseItem (pstate=0x2882c80, n=0x2882a30, top_nsitem=0x7ffcc8b38d38, namespace=0x7ffcc8b38d30)
    at parse_clause.c:1074
#10 0x00000000005fabcd in transformFromClause (pstate=0x2882c80, frmList=0x2882a80) at parse_clause.c:132
#11 0x00000000005c6358 in transformSelectStmt (pstate=0x2882c80, stmt=0x2882af0) at analyze.c:1313
#12 0x00000000005c48ef in transformStmt (pstate=0x2882c80, parseTree=0x2882af0) at analyze.c:365
#13 0x00000000005c47d9 in transformOptionalSelectInto (pstate=0x2882c80, parseTree=0x2882af0) at analyze.c:305
#14 0x00000000005c46d1 in transformTopLevelStmt (pstate=0x2882c80, parseTree=0x2882c00) at analyze.c:255
#15 0x00000000005c43d1 in parse_analyze_fixedparams (parseTree=0x2882c00, sourceText=0x2881ed8 "select * from t;", paramTypes=0x0,
    numParams=0, queryEnv=0x0) at analyze.c:123
#16 0x000000000090616d in pg_analyze_and_rewrite_fixedparams (parsetree=0x2882c00, query_string=0x2881ed8 "select * from t;", paramTypes=0x0,
    numParams=0, queryEnv=0x0) at postgres.c:657
#17 0x0000000000906897 in exec_simple_query (query_string=0x2881ed8 "select * from t;") at postgres.c:1166
#18 0x000000000090ad09 in PostgresMain (dbname=0x28ad680 "postgres", username=0x28ad668 "normal") at postgres.c:4593
#19 0x000000000085cb21 in BackendRun (port=0x28a4e50) at postmaster.c:4511
#20 0x000000000085c4ba in BackendStartup (port=0x28a4e50) at postmaster.c:4239
#21 0x0000000000858e03 in ServerLoop () at postmaster.c:1806
#22 0x00000000008586df in PostmasterMain (argc=3, argv=0x287ca50) at postmaster.c:1478
#23 0x000000000076ddd6 in main (argc=3, argv=0x287ca50) at main.c:202
复制

transformSelectStmt

-> transformFromClause

...

-> relation_open

-> RelationIdGetRelation

-> RelationBuildDesc （relation 没在 cache 中，重新 build 出来）

-> RelationBuildRowSecurity

Relation 部分结构如下，rd_rsdesc 字段会：

typedef struct RelationData
{
	RelFileNode rd_node;		/* relation physical identifier */

    	...

	/* use "struct" here to avoid needing to include rowsecurity.h: */
	struct RowSecurityDesc *rd_rsdesc;	/* row security policies, or NULL */

    	...

	/* use "struct" here to avoid needing to include pgstat.h: */
	struct PgStat_TableStatus *pgstat_info; /* statistics collection area */
} RelationData;
复制

在 relation_open 时，会通过 pg_policy 系统表进行 systable scan，将定义在该表上的所有 RLS policy 读取并使用 RowSecurityPolicy 结构体进行保存，该结构体主要包含策略名、statements 类型、用户列表以及策略内容等。

typedef struct RowSecurityPolicy
{
	char	     *policy_name;      /* Name of the policy */
	char		    polcmd;           /* Type of command policy is for */
	ArrayType  *roles;            /* Array of roles policy is for */
	bool		    permissive;       /* restrictive or permissive policy */
	Expr	     *qual;             /* Expression to filter rows */
	Expr	     *with_check_qual;	/* Expression to limit rows allowed */
	bool		    hassublinks;      /* If either expression has sublinks */
} RowSecurityPolicy;
复制

因此，在表通过 relation_open 后 RLS 相关部分结构如下图：

3.2 策略应用

在 parsetree list 解析完成后，会对语法树进行查询重写，即 pg_analyze_and_rewrite。该过程中，会对 RLS 进行应用。

/*
 * QueryRewrite -
 *	  Primary entry point to the query rewriter.
 *	  Rewrite one query via query rewrite system, possibly returning 0
 *	  or many queries.
 *
 * NOTE: the parsetree must either have come straight from the parser,
 * or have been scanned by AcquireRewriteLocks to acquire suitable locks.
 */
List *
QueryRewrite(Query *parsetree)
{
  	... 

	/*
	 * Step 2
	 *
	 * Apply all the RIR rules on each query
	 *
	 * This is also a handy place to mark each query with the original queryId
	 */
	results = NIL;
	foreach(l, querylist)
	{
		Query	   *query = (Query *) lfirst(l);

		query = fireRIRrules(query, NIL);

		query->queryId = input_query_id;

		results = lappend(results, query);
	}

   ...
}
复制

调用栈：

#0  get_row_security_policies
#1  0x00000000008aef42 in fireRIRrules
#2  0x00000000008b23ec in QueryRewrite
#3  0x0000000000906390 in pg_rewrite_query
#4  0x0000000000906192 in pg_analyze_and_re
复制

在 fireRIRrules 中首先会进行 RLS 是否生效进行检查，即当前用户是否受 RLS 策略限制：

	/* Determine the state of RLS for this, pass checkAsUser explicitly */
	rls_status = check_enable_rls(rte->relid, rte->checkAsUser, false);

	/* If there is no RLS on this table at all, nothing to do */
	if (rls_status == RLS_NONE)
		return;
复制

然后，会依次多表中的策略进行检测，主要初始化两个链表：

	*securityQuals = NIL;     /* 控制当前用户是否能看到旧数据 */
	*withCheckOptions = NIL;  /* 控制新插入数据是否符合数据限制 */
复制

这两个链表主要作用为将 relation->rd_rsdesc 中的适用于当前 DML 语句的策略取出，形式与 where 语句部分类似。

整体结构如上图，RLS 策略会根据不同的 statement type 应用到 RangeTableEntry 的 SecurityQuals 字段或 parsetree 的 WithCheckOptions。其中，SELECT 和 DELETE 类型的 RLS 策略会应用到 RangeTableEntry 的 SecurityQuals 字段，UPDATE 和 INSERT 的策略会应用到 parsetree 的 WithCheckOptions 字段。此外，这里还有一些额外的限制，包括：

  • DELETE 类型的语句会默认应用所有的 SELECT 策略，即用户只能删除自己能够“看到”的数据；

  • UPDATE 类型的语句会默认应用所有的 SELECT 策略，即用户只能更新自己能够查询到的数据。

			/*
			 * Add the new security barrier quals to the start of the RTE's
			 * list so that they get applied before any existing barrier quals
			 * (which would have come from a security-barrier view, and should
			 * get lower priority than RLS conditions on the table itself).
			 */
			rte->securityQuals = list_concat(securityQuals,
											 rte->securityQuals);

			parsetree->withCheckOptions = list_concat(withCheckOptions,
													  parsetree->withCheckOptions);
复制

3.2.1 SELECT 与 DELETE 类型策略处理

RangeTableEntry 中的 securityQuals 字段会在优化器中的 subquery_planner 中被处理为 AND 链表形式。然后会在 grouping_planner 中利用 distribute_qual_to_rels 函数将 securityQuals 中的所有策略与 WHERE 语句一样分发到相应的 Relation 中。

		/*
		 * Process each element of the securityQuals list as if it were a
		 * separate qual expression (as indeed it is).  We need to do it this
		 * way to get proper canonicalization of AND/OR structure.  Note that
		 * this converts each element into an implicit-AND sublist.
		 */
		foreach(lcsq, rte->securityQuals)
		{
			lfirst(lcsq) = preprocess_expression(root,
												 (Node *) lfirst(lcsq),
												 EXPRKIND_QUAL);
		}
复制

在执行器 ExecScan 读取数据的阶段会对数据的过滤条件进行处理。

3.2.2 UPDATE 与 DELETE 类型策略处理

对于 withCheckOptions 的处理，在进行 insert 和 update 过程中，包括 ExecInsert 和 ExecUpdate 等函数中，会通过 ExecWithCheckOptions 函数对新增的数据是否符合 RLS 限制进行检查，从而实现 UPDATE 和 INSERT 的 RLS 限制。

/*
 * ExecWithCheckOptions -- check that tuple satisfies any WITH CHECK OPTIONs
 * of the specified kind.
 *
 * Note that this needs to be called multiple times to ensure that all kinds of
 * WITH CHECK OPTIONs are handled (both those from views which have the WITH
 * CHECK OPTION set and from row-level security policies).  See ExecInsert()
 * and ExecUpdate().
 */
void
ExecWithCheckOptions(WCOKind kind, ResultRelInfo *resultRelInfo,
					 TupleTableSlot *slot, EState *estate)
					 TupleTableSlot *slot, EState *estate)
{
	Relation	rel = resultRelInfo->ri_RelationDesc;
	TupleDesc	tupdesc = RelationGetDescr(rel);

                       ...
	
	/* Check each of the constraints */
	forboth(l1, resultRelInfo->ri_WithCheckOptions,
			l2, resultRelInfo->ri_WithCheckOptionExprs)
	{
		WithCheckOption *wco = (WithCheckOption *) lfirst(l1);
		ExprState  *wcoExpr = (ExprState *) lfirst(l2);

		/*
		 * WITH CHECK OPTION checks are intended to ensure that the new tuple
		 * is visible (in the case of a view) or that it passes the
		 * 'with-check' policy (in the case of row security). If the qual
		 * evaluates to NULL or FALSE, then the new tuple won't be included in
		 * the view or doesn't pass the 'with-check' policy for the table.
		 */
		if (!ExecQual(wcoExpr, econtext))
    { 
         /* Raise an error here */ 
    }
 }
复制

其中，一个 update 测试语句调用堆栈如下：

#0  ExecWithCheckOptions (kind=WCO_RLS_UPDATE_CHECK, resultRelInfo=0x2946d20, slot=0x2947af0, estate=0x2946868) at execMain.c:2042
#1  0x0000000000739c65 in ExecUpdateAct (context=0x7ffcc8b38ae0, resultRelInfo=0x2946d20, tupleid=0x7ffcc8b38ada, oldtuple=0x0,
    slot=0x2947af0, canSetTag=true, updateCxt=0x7ffcc8b38a50) at nodeModifyTable.c:2006
#2  0x000000000073a2c9 in ExecUpdate (context=0x7ffcc8b38ae0, resultRelInfo=0x2946d20, tupleid=0x7ffcc8b38ada, oldtuple=0x0, slot=0x2947af0,
    canSetTag=true) at nodeModifyTable.c:2308
#3  0x000000000073c87a in ExecModifyTable (pstate=0x2946b10) at nodeModifyTable.c:3857
#4  0x0000000000703f24 in ExecProcNodeFirst (node=0x2946b10) at execProcnode.c:464
#5  0x00000000006f8f91 in ExecProcNode (node=0x2946b10) at ../../../src/include/executor/executor.h:259
#6  0x00000000006fb662 in ExecutePlan (estate=0x2946868, planstate=0x2946b10, use_parallel_mode=false, operation=CMD_UPDATE,
    sendTuples=false, numberTuples=0, direction=ForwardScanDirection, dest=0x294af08, execute_once=true) at execMain.c:1636
#7  0x00000000006f94d0 in standard_ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true)
    at execMain.c:363
#8  0x00000000006f936a in ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:307
#9  0x000000000090bba6 in ProcessQuery (plan=0x294ae28, sourceText=0x2881ed8 "update t set a = 10;", params=0x0, queryEnv=0x0,
    dest=0x294af08, qc=0x7ffcc8b38f40) at pquery.c:160
#10 0x000000000090d325 in PortalRunMulti (portal=0x28f36e8, isTopLevel=true, setHoldSnapshot=false, dest=0x294af08, altdest=0x294af08,
    qc=0x7ffcc8b38f40) at pquery.c:1277
#11 0x000000000090c989 in PortalRun (portal=0x28f36e8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x294af08,
    altdest=0x294af08, qc=0x7ffcc8b38f40) at pquery.c:791
#12 0x0000000000906a31 in exec_simple_query (query_string=0x2881ed8 "update t set a = 10;") at postgres.c:1250
#13 0x000000000090ad09 in PostgresMain (dbname=0x28ad680 "postgres", username=0x28ad668 "normal") at postgres.c:4593
#14 0x000000000085cb21 in BackendRun (port=0x28a4e50) at postmaster.c:4511
#15 0x000000000085c4ba in BackendStartup (port=0x28a4e50) at postmaster.c:4239
#16 0x0000000000858e03 in ServerLoop () at postmaster.c:1806
#17 0x00000000008586df in PostmasterMain (argc=3, argv=0x287ca50) at postmaster.c:1478
#18 0x000000000076ddd6 in main (argc=3, argv=0x287ca50) at main.c:202
复制

一个 insert 测试语句调用堆栈如下：

#0  ExecWithCheckOptions (kind=WCO_RLS_INSERT_CHECK, resultRelInfo=0x2946d28, slot=0x2947ca0, estate=0x2946868) at execMain.c:2042
#1  0x0000000000738783 in ExecInsert (context=0x7ffcc8b38ae0, resultRelInfo=0x2946d28, slot=0x2947ca0, canSetTag=true, inserted_tuple=0x0,
    insert_destrel=0x0) at nodeModifyTable.c:1008
#2  0x000000000073c74f in ExecModifyTable (pstate=0x2946b18) at nodeModifyTable.c:3822
#3  0x0000000000703f24 in ExecProcNodeFirst (node=0x2946b18) at execProcnode.c:464
#4  0x00000000006f8f91 in ExecProcNode (node=0x2946b18) at ../../../src/include/executor/executor.h:259
#5  0x00000000006fb662 in ExecutePlan (estate=0x2946868, planstate=0x2946b18, use_parallel_mode=false, operation=CMD_INSERT,
    sendTuples=false, numberTuples=0, direction=ForwardScanDirection, dest=0x294afc8, execute_once=true) at execMain.c:1636
#6  0x00000000006f94d0 in standard_ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true)
    at execMain.c:363
#7  0x00000000006f936a in ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:307
#8  0x000000000090bba6 in ProcessQuery (plan=0x294aee8, sourceText=0x2881ed8 "insert into t values (101, '123'),(12312,'1231');", params=0x0,
    queryEnv=0x0, dest=0x294afc8, qc=0x7ffcc8b38f40) at pquery.c:160
#9  0x000000000090d325 in PortalRunMulti (portal=0x28f36e8, isTopLevel=true, setHoldSnapshot=false, dest=0x294afc8, altdest=0x294afc8,
    qc=0x7ffcc8b38f40) at pquery.c:1277
#10 0x000000000090c989 in PortalRun (portal=0x28f36e8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x294afc8,
    altdest=0x294afc8, qc=0x7ffcc8b38f40) at pquery.c:791
#11 0x0000000000906a31 in exec_simple_query (query_string=0x2881ed8 "insert into t values (101, '123'),(12312,'1231');") at postgres.c:1250
#12 0x000000000090ad09 in PostgresMain (dbname=0x28ad680 "postgres", username=0x28ad668 "normal") at postgres.c:4593
#13 0x000000000085cb21 in BackendRun (port=0x28a4e50) at postmaster.c:4511
#14 0x000000000085c4ba in BackendStartup (port=0x28a4e50) at postmaster.c:4239
#15 0x0000000000858e03 in ServerLoop () at postmaster.c:1806
#16 0x00000000008586df in PostmasterMain (argc=3, argv=0x287ca50) at postmaster.c:1478
#17 0x000000000076ddd6 in main (argc=3, argv=0x287ca50) at main.c:202
复制

4. 总结

本文分析了 PostgreSQL 中的 Row-Level Security 策略的使用方式和实现原理。

1. 简介

RLS（ROW Level Security）是PostgreSQL 9.5版本中新增特性，提供了基于行的安全策略，限制数据库用户的查看表数据权限。在 9.5 版本之前，数据库中对用户的权限管控在表级别，例如：限制某个用户是否允许查询某个表。采用RLS后，不同的用户可以访问到一个表中不同的数据。

默认情况下，表没有任何安全策略限制，对于 DML 来说表中所有的行都是平等的。当在一个表上启用行级别安全测试后，所有对该表的 DML 操作都必须被一条行安全性策略所允许（默认情况下，table owner 、bypassrls = true 与 superuser 不受 RLS 限制）。行级别安全策略可以指定特定的 statements (SELECT UPDATE DELETE INSERT 及其各种组合)、表名、用户列表，一个策略可应用到多个用户，并且继承规则也适用。

2. 使用方式

2.1 DDL 命令

  • CREATE POLICY：创建策略；
CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
复制
  • ALTER POLICY：修改策略；
ALTER POLICY name ON table_name RENAME TO new_name

ALTER POLICY name ON table_name
    [ TO { role_name | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
复制
  • DROP POLICY：删除策略；
DROP POLICY [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
复制
  • ALTER TABLE：用于行级安全性的启用 / 禁用；
ALTER TABLE name [ ENABLE | DISABLE ] ROW LEVEL SECURITY
复制

每个策略都有一个名字，每个表可以定义多个策略，因为策略是针对表的，所以表内的多个策略名字必须唯一，但是不同的表可以有同名的策略，当表有多个策略时，多个策略之间是 OR / AND 的关系。

2.2 效果

本文主要为介绍 RLS 实现原理，因此此处仅简单介绍下 RLS 的使用效果。

3. 原理

3.1 策略提取

DML 语句在使用时会涉及到至少一个表，以 SELECT 为例，通常会涉及到以下调用：

#0  RelationBuildRowSecurity (relation=0x7f3e22b04358) at policy.c:199
#1  0x0000000000a80488 in RelationBuildDesc (targetRelId=24582, insertIt=true) at relcache.c:1236
#2  0x0000000000a81a6a in RelationIdGetRelation (relationId=24582) at relcache.c:2107
#3  0x000000000049dafb in relation_open (relationId=24582, lockmode=0) at relation.c:59
#4  0x000000000049dd05 in relation_openrv_extended (relation=0x2882a30, lockmode=1, missing_ok=true) at relation.c:193
#5  0x0000000000535be9 in table_openrv_extended (relation=0x2882a30, lockmode=1, missing_ok=true) at table.c:137
#6  0x000000000061b173 in parserOpenTable (pstate=0x2882c80, relation=0x2882a30, lockmode=1) at parse_relation.c:1367
#7  0x000000000061b38d in addRangeTableEntry (pstate=0x2882c80, relation=0x2882a30, alias=0x0, inh=true, inFromCl=true)
    at parse_relation.c:1443
#8  0x00000000005fb2ef in transformTableEntry (pstate=0x2882c80, r=0x2882a30) at parse_clause.c:397
#9  0x00000000005fcc39 in transformFromClauseItem (pstate=0x2882c80, n=0x2882a30, top_nsitem=0x7ffcc8b38d38, namespace=0x7ffcc8b38d30)
    at parse_clause.c:1074
#10 0x00000000005fabcd in transformFromClause (pstate=0x2882c80, frmList=0x2882a80) at parse_clause.c:132
#11 0x00000000005c6358 in transformSelectStmt (pstate=0x2882c80, stmt=0x2882af0) at analyze.c:1313
#12 0x00000000005c48ef in transformStmt (pstate=0x2882c80, parseTree=0x2882af0) at analyze.c:365
#13 0x00000000005c47d9 in transformOptionalSelectInto (pstate=0x2882c80, parseTree=0x2882af0) at analyze.c:305
#14 0x00000000005c46d1 in transformTopLevelStmt (pstate=0x2882c80, parseTree=0x2882c00) at analyze.c:255
#15 0x00000000005c43d1 in parse_analyze_fixedparams (parseTree=0x2882c00, sourceText=0x2881ed8 "select * from t;", paramTypes=0x0,
    numParams=0, queryEnv=0x0) at analyze.c:123
#16 0x000000000090616d in pg_analyze_and_rewrite_fixedparams (parsetree=0x2882c00, query_string=0x2881ed8 "select * from t;", paramTypes=0x0,
    numParams=0, queryEnv=0x0) at postgres.c:657
#17 0x0000000000906897 in exec_simple_query (query_string=0x2881ed8 "select * from t;") at postgres.c:1166
#18 0x000000000090ad09 in PostgresMain (dbname=0x28ad680 "postgres", username=0x28ad668 "normal") at postgres.c:4593
#19 0x000000000085cb21 in BackendRun (port=0x28a4e50) at postmaster.c:4511
#20 0x000000000085c4ba in BackendStartup (port=0x28a4e50) at postmaster.c:4239
#21 0x0000000000858e03 in ServerLoop () at postmaster.c:1806
#22 0x00000000008586df in PostmasterMain (argc=3, argv=0x287ca50) at postmaster.c:1478
#23 0x000000000076ddd6 in main (argc=3, argv=0x287ca50) at main.c:202
复制

transformSelectStmt

-> transformFromClause

...

-> relation_open

-> RelationIdGetRelation

-> RelationBuildDesc （relation 没在 cache 中，重新 build 出来）

-> RelationBuildRowSecurity

Relation 部分结构如下，rd_rsdesc 字段会：

typedef struct RelationData
{
	RelFileNode rd_node;		/* relation physical identifier */

    	...

	/* use "struct" here to avoid needing to include rowsecurity.h: */
	struct RowSecurityDesc *rd_rsdesc;	/* row security policies, or NULL */

    	...

	/* use "struct" here to avoid needing to include pgstat.h: */
	struct PgStat_TableStatus *pgstat_info; /* statistics collection area */
} RelationData;
复制

在 relation_open 时，会通过 pg_policy 系统表进行 systable scan，将定义在该表上的所有 RLS policy 读取并使用 RowSecurityPolicy 结构体进行保存，该结构体主要包含策略名、statements 类型、用户列表以及策略内容等。

typedef struct RowSecurityPolicy
{
	char	     *policy_name;      /* Name of the policy */
	char		    polcmd;           /* Type of command policy is for */
	ArrayType  *roles;            /* Array of roles policy is for */
	bool		    permissive;       /* restrictive or permissive policy */
	Expr	     *qual;             /* Expression to filter rows */
	Expr	     *with_check_qual;	/* Expression to limit rows allowed */
	bool		    hassublinks;      /* If either expression has sublinks */
} RowSecurityPolicy;
复制

因此，在表通过 relation_open 后 RLS 相关部分结构如下图：

3.2 策略应用

在 parsetree list 解析完成后，会对语法树进行查询重写，即 pg_analyze_and_rewrite。该过程中，会对 RLS 进行应用。

/*
 * QueryRewrite -
 *	  Primary entry point to the query rewriter.
 *	  Rewrite one query via query rewrite system, possibly returning 0
 *	  or many queries.
 *
 * NOTE: the parsetree must either have come straight from the parser,
 * or have been scanned by AcquireRewriteLocks to acquire suitable locks.
 */
List *
QueryRewrite(Query *parsetree)
{
  	... 

	/*
	 * Step 2
	 *
	 * Apply all the RIR rules on each query
	 *
	 * This is also a handy place to mark each query with the original queryId
	 */
	results = NIL;
	foreach(l, querylist)
	{
		Query	   *query = (Query *) lfirst(l);

		query = fireRIRrules(query, NIL);

		query->queryId = input_query_id;

		results = lappend(results, query);
	}

   ...
}
复制

调用栈：

#0  get_row_security_policies
#1  0x00000000008aef42 in fireRIRrules
#2  0x00000000008b23ec in QueryRewrite
#3  0x0000000000906390 in pg_rewrite_query
#4  0x0000000000906192 in pg_analyze_and_re
复制

在 fireRIRrules 中首先会进行 RLS 是否生效进行检查，即当前用户是否受 RLS 策略限制：

	/* Determine the state of RLS for this, pass checkAsUser explicitly */
	rls_status = check_enable_rls(rte->relid, rte->checkAsUser, false);

	/* If there is no RLS on this table at all, nothing to do */
	if (rls_status == RLS_NONE)
		return;
复制

然后，会依次多表中的策略进行检测，主要初始化两个链表：

	*securityQuals = NIL;     /* 控制当前用户是否能看到旧数据 */
	*withCheckOptions = NIL;  /* 控制新插入数据是否符合数据限制 */
复制

这两个链表主要作用为将 relation->rd_rsdesc 中的适用于当前 DML 语句的策略取出，形式与 where 语句部分类似。

整体结构如上图，RLS 策略会根据不同的 statement type 应用到 RangeTableEntry 的 SecurityQuals 字段或 parsetree 的 WithCheckOptions。其中，SELECT 和 DELETE 类型的 RLS 策略会应用到 RangeTableEntry 的 SecurityQuals 字段，UPDATE 和 INSERT 的策略会应用到 parsetree 的 WithCheckOptions 字段。此外，这里还有一些额外的限制，包括：

  • DELETE 类型的语句会默认应用所有的 SELECT 策略，即用户只能删除自己能够“看到”的数据；
  • UPDATE 类型的语句会默认应用所有的 SELECT 策略，即用户只能更新自己能够查询到的数据。
			/*
			 * Add the new security barrier quals to the start of the RTE's
			 * list so that they get applied before any existing barrier quals
			 * (which would have come from a security-barrier view, and should
			 * get lower priority than RLS conditions on the table itself).
			 */
			rte->securityQuals = list_concat(securityQuals,
											 rte->securityQuals);

			parsetree->withCheckOptions = list_concat(withCheckOptions,
													  parsetree->withCheckOptions);
复制

3.2.1 SELECT 与 DELETE 类型策略处理

RangeTableEntry 中的 securityQuals 字段会在优化器中的 subquery_planner 中被处理为 AND 链表形式。然后会在 grouping_planner 中利用 distribute_qual_to_rels 函数将 securityQuals 中的所有策略与 WHERE 语句一样分发到相应的 Relation 中。

		/*
		 * Process each element of the securityQuals list as if it were a
		 * separate qual expression (as indeed it is).  We need to do it this
		 * way to get proper canonicalization of AND/OR structure.  Note that
		 * this converts each element into an implicit-AND sublist.
		 */
		foreach(lcsq, rte->securityQuals)
		{
			lfirst(lcsq) = preprocess_expression(root,
												 (Node *) lfirst(lcsq),
												 EXPRKIND_QUAL);
		}
复制

在执行器 ExecScan 读取数据的阶段会对数据的过滤条件进行处理。

3.2.2 UPDATE 与 DELETE 类型策略处理

对于 withCheckOptions 的处理，在进行 insert 和 update 过程中，包括 ExecInsert 和 ExecUpdate 等函数中，会通过 ExecWithCheckOptions 函数对新增的数据是否符合 RLS 限制进行检查，从而实现 UPDATE 和 INSERT 的 RLS 限制。

/*
 * ExecWithCheckOptions -- check that tuple satisfies any WITH CHECK OPTIONs
 * of the specified kind.
 *
 * Note that this needs to be called multiple times to ensure that all kinds of
 * WITH CHECK OPTIONs are handled (both those from views which have the WITH
 * CHECK OPTION set and from row-level security policies).  See ExecInsert()
 * and ExecUpdate().
 */
void
ExecWithCheckOptions(WCOKind kind, ResultRelInfo *resultRelInfo,
					 TupleTableSlot *slot, EState *estate)
					 TupleTableSlot *slot, EState *estate)
{
	Relation	rel = resultRelInfo->ri_RelationDesc;
	TupleDesc	tupdesc = RelationGetDescr(rel);

                       ...
	
	/* Check each of the constraints */
	forboth(l1, resultRelInfo->ri_WithCheckOptions,
			l2, resultRelInfo->ri_WithCheckOptionExprs)
	{
		WithCheckOption *wco = (WithCheckOption *) lfirst(l1);
		ExprState  *wcoExpr = (ExprState *) lfirst(l2);

		/*
		 * WITH CHECK OPTION checks are intended to ensure that the new tuple
		 * is visible (in the case of a view) or that it passes the
		 * 'with-check' policy (in the case of row security). If the qual
		 * evaluates to NULL or FALSE, then the new tuple won't be included in
		 * the view or doesn't pass the 'with-check' policy for the table.
		 */
		if (!ExecQual(wcoExpr, econtext))
    { 
         /* Raise an error here */ 
    }
 }
复制

其中，一个 update 测试语句调用堆栈如下：

#0  ExecWithCheckOptions (kind=WCO_RLS_UPDATE_CHECK, resultRelInfo=0x2946d20, slot=0x2947af0, estate=0x2946868) at execMain.c:2042
#1  0x0000000000739c65 in ExecUpdateAct (context=0x7ffcc8b38ae0, resultRelInfo=0x2946d20, tupleid=0x7ffcc8b38ada, oldtuple=0x0,
    slot=0x2947af0, canSetTag=true, updateCxt=0x7ffcc8b38a50) at nodeModifyTable.c:2006
#2  0x000000000073a2c9 in ExecUpdate (context=0x7ffcc8b38ae0, resultRelInfo=0x2946d20, tupleid=0x7ffcc8b38ada, oldtuple=0x0, slot=0x2947af0,
    canSetTag=true) at nodeModifyTable.c:2308
#3  0x000000000073c87a in ExecModifyTable (pstate=0x2946b10) at nodeModifyTable.c:3857
#4  0x0000000000703f24 in ExecProcNodeFirst (node=0x2946b10) at execProcnode.c:464
#5  0x00000000006f8f91 in ExecProcNode (node=0x2946b10) at ../../../src/include/executor/executor.h:259
#6  0x00000000006fb662 in ExecutePlan (estate=0x2946868, planstate=0x2946b10, use_parallel_mode=false, operation=CMD_UPDATE,
    sendTuples=false, numberTuples=0, direction=ForwardScanDirection, dest=0x294af08, execute_once=true) at execMain.c:1636
#7  0x00000000006f94d0 in standard_ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true)
    at execMain.c:363
#8  0x00000000006f936a in ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:307
#9  0x000000000090bba6 in ProcessQuery (plan=0x294ae28, sourceText=0x2881ed8 "update t set a = 10;", params=0x0, queryEnv=0x0,
    dest=0x294af08, qc=0x7ffcc8b38f40) at pquery.c:160
#10 0x000000000090d325 in PortalRunMulti (portal=0x28f36e8, isTopLevel=true, setHoldSnapshot=false, dest=0x294af08, altdest=0x294af08,
    qc=0x7ffcc8b38f40) at pquery.c:1277
#11 0x000000000090c989 in PortalRun (portal=0x28f36e8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x294af08,
    altdest=0x294af08, qc=0x7ffcc8b38f40) at pquery.c:791
#12 0x0000000000906a31 in exec_simple_query (query_string=0x2881ed8 "update t set a = 10;") at postgres.c:1250
#13 0x000000000090ad09 in PostgresMain (dbname=0x28ad680 "postgres", username=0x28ad668 "normal") at postgres.c:4593
#14 0x000000000085cb21 in BackendRun (port=0x28a4e50) at postmaster.c:4511
#15 0x000000000085c4ba in BackendStartup (port=0x28a4e50) at postmaster.c:4239
#16 0x0000000000858e03 in ServerLoop () at postmaster.c:1806
#17 0x00000000008586df in PostmasterMain (argc=3, argv=0x287ca50) at postmaster.c:1478
#18 0x000000000076ddd6 in main (argc=3, argv=0x287ca50) at main.c:202
复制

一个 insert 测试语句调用堆栈如下：

#0  ExecWithCheckOptions (kind=WCO_RLS_INSERT_CHECK, resultRelInfo=0x2946d28, slot=0x2947ca0, estate=0x2946868) at execMain.c:2042
#1  0x0000000000738783 in ExecInsert (context=0x7ffcc8b38ae0, resultRelInfo=0x2946d28, slot=0x2947ca0, canSetTag=true, inserted_tuple=0x0,
    insert_destrel=0x0) at nodeModifyTable.c:1008
#2  0x000000000073c74f in ExecModifyTable (pstate=0x2946b18) at nodeModifyTable.c:3822
#3  0x0000000000703f24 in ExecProcNodeFirst (node=0x2946b18) at execProcnode.c:464
#4  0x00000000006f8f91 in ExecProcNode (node=0x2946b18) at ../../../src/include/executor/executor.h:259
#5  0x00000000006fb662 in ExecutePlan (estate=0x2946868, planstate=0x2946b18, use_parallel_mode=false, operation=CMD_INSERT,
    sendTuples=false, numberTuples=0, direction=ForwardScanDirection, dest=0x294afc8, execute_once=true) at execMain.c:1636
#6  0x00000000006f94d0 in standard_ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true)
    at execMain.c:363
#7  0x00000000006f936a in ExecutorRun (queryDesc=0x294e898, direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:307
#8  0x000000000090bba6 in ProcessQuery (plan=0x294aee8, sourceText=0x2881ed8 "insert into t values (101, '123'),(12312,'1231');", params=0x0,
    queryEnv=0x0, dest=0x294afc8, qc=0x7ffcc8b38f40) at pquery.c:160
#9  0x000000000090d325 in PortalRunMulti (portal=0x28f36e8, isTopLevel=true, setHoldSnapshot=false, dest=0x294afc8, altdest=0x294afc8,
    qc=0x7ffcc8b38f40) at pquery.c:1277
#10 0x000000000090c989 in PortalRun (portal=0x28f36e8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x294afc8,
    altdest=0x294afc8, qc=0x7ffcc8b38f40) at pquery.c:791
#11 0x0000000000906a31 in exec_simple_query (query_string=0x2881ed8 "insert into t values (101, '123'),(12312,'1231');") at postgres.c:1250
#12 0x000000000090ad09 in PostgresMain (dbname=0x28ad680 "postgres", username=0x28ad668 "normal") at postgres.c:4593
#13 0x000000000085cb21 in BackendRun (port=0x28a4e50) at postmaster.c:4511
#14 0x000000000085c4ba in BackendStartup (port=0x28a4e50) at postmaster.c:4239
#15 0x0000000000858e03 in ServerLoop () at postmaster.c:1806
#16 0x00000000008586df in PostmasterMain (argc=3, argv=0x287ca50) at postmaster.c:1478
#17 0x000000000076ddd6 in main (argc=3, argv=0x287ca50) at main.c:202
复制

4. 总结

本文分析了 PostgreSQL 中的 Row-Level Security 策略的使用方式和实现原理。

polardb阿里云polardbpolardb-pg
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论

相关阅读

QPlus V6.3 更新，新增PostgreSQL与PolarDB PG支持，OceanBase 容灾管理重磅上线
沃趣科技
263次阅读
2025-05-13 09:39:27
国内独家｜阿里云首发MongoDB 8.0，性能提升“快”人一步
阿里云瑶池数据库
229次阅读
2025-04-24 09:53:13
SQL 优化之 OR 子句改写
xiongcc
152次阅读
2025-04-21 00:08:06
4年10亿美金，Neon用Serverless PG证明：AI需要的不是“大”，而是“隐形”
阿里云瑶池数据库
77次阅读
2025-05-08 10:03:12
秒级修改字段
胖橘
48次阅读
2025-04-23 11:33:45
PolarDB MySQL之列存索引（IMCI）性能
xiaozhuo
48次阅读
2025-04-21 16:11:53
PolarDB MySQL之列存索引测试方法
xiaozhuo
47次阅读
2025-04-21 16:06:42
PolarDB MySQL之在只读节点上创建自定义临时表
xiaozhuo
45次阅读
2025-04-21 15:49:05
PostgreSQL 虚拟文件描述符
PolarDB
44次阅读
2025-04-25 10:10:46
现已开源｜阿里云RDS发布MCP Server能力，解锁数据库“对话即运维”新体验
阿里云瑶池数据库
42次阅读
2025-04-23 11:19:36