Postgresql源码（114）视图权限授予逻辑

0 速查

被授权的对象在系统表中记录授权信息，例如pg_namespace中的nspacl列：

{mingjie=UC/mingjie,=UC/mingjie,pusr1=UC/mingjie}
1

pusr1=UC/mingjie的含义：

• mingjie是赋予者
• pusr1是被赋予者
• UC是权限，表示USAGE和CREATE

1 视图权限案例

有时会遇到下面场景，访问一个视图没有权限：

drop schema sch1 cascade;
drop user pusr1;
drop user pusr2;

create user pusr1;
create user pusr2;

create schema sch1;

create procedure sch1.func1(i int) as $$
begin
  raise notice 'func1';
end;
$$ language plpgsql;

\c - pusr1
call sch1.func1(1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

结果

查看namespace视图：

这种情况下，添加两种权限都可以访问函数：

grant all on schema sch1 to public;
grant all on schema sch1 to pusr1;
1
2

这里我们看到namespace元数据增加了两条规则，对应两条grant：

grant all on schema sch1 to public;   →          =UC/mingjie           
grant all on schema sch1 to pusr1;    →     pusr1=UC/mingjie     
1
2

显然这两条规则就是权限判断的依据，下面分析这两条规则的使用流程。

2 权限判定流程分析

部分代码

static AclMode
pg_namespace_aclmask(Oid nsp_oid, Oid roleid,
					 AclMode mask, AclMaskHow how)
{
	...
	...
1
2
3
4
5
6

查pg_namespace表：

	tuple = SearchSysCache1(NAMESPACEOID, ObjectIdGetDatum(nsp_oid));
1

检查owner是谁？

	ownerId = ((Form_pg_namespace) GETSTRUCT(tuple))->nspowner;
1

拿到规则aclDatum：{mingjie=UC/mingjie,=UC/mingjie,pusr1=UC/mingjie}

	aclDatum = SysCacheGetAttr(NAMESPACEOID, tuple, Anum_pg_namespace_nspacl,
							   &isNull);
1
2

默认没grant的时候aclDatum字段是isNull，这时候owner有权限访问，其他没权限。

	if (isNull)
	{
		/* No ACL, so build default ACL */
		acl = acldefault(OBJECT_SCHEMA, ownerId);
		aclDatum = (Datum) 0;
	}
1
2
3
4
5
6

有grant后，aclDatum字段有值了，把{mingjie=UC/mingjie,=UC/mingjie,pusr1=UC/mingjie}传入，detoast后变成valena变长类型。

	else
	{
		/* detoast ACL if necessary */
		acl = DatumGetAclP(aclDatum);
	}

1
2
3
4
5
6

拿着acl进入aclmask处理，判断roleid是否有访问权限。

	result = aclmask(acl, roleid, ownerId, mask, how);

	...
	...

	return result;
}
1
2
3
4
5
6
7

aclmask函数

AclMode
aclmask(const Acl *acl, Oid roleid, Oid ownerId,
		AclMode mask, AclMaskHow how)
{
1
2
3
4

入参：

• valena变量含义{mingjie=UC/mingjie,=UC/mingjie,pusr1=UC/mingjie}
• roleid：24601表示pusr1。
• ownerId：10表示建库的超级用户。
• mask：256表示#define ACL_USAGE (1<<8) /* for various object types */
• how：ACLMASK_ANY

	num = ACL_NUM(acl);
	aidat = ACL_DAT(acl);
1
2
3

num = 3
(gdb) p aidat[0]
$6 = {ai_grantee = 10, ai_grantor = 10, ai_privs = 768}
(gdb) p aidat[1
$7 = {ai_grantee = 0, ai_grantor = 10, ai_privs = 768}
(gdb) p aidat[2
$8 = {ai_grantee = 24601, ai_grantor = 10, ai_privs = 768}

这里解释下这三个数据的含义
$8 = {ai_grantee = 24601, ai_grantor = 10, ai_privs = 768}

10表示建库的超级用户，赋予，24601表示pusr1，768的权限
768 = 1100000000 = ACL_USAGE | ACL_CREATE
#define ACL_USAGE (1<<8) /* for various object types */
#define ACL_CREATE (1<<9) /* for namespaces and databases */

注意这里有一个特殊的ai_grantee：ACL_ID_PUBLIC=0，表示被授权者是任意用户。

下面循环就是对上述逻辑进行判断：

	/*
	 * Check privileges granted directly to roleid or to public
	 */
	for (i = 0; i < num; i++)
	{
		AclItem    *aidata = &aidat[i];

		if (aidata->ai_grantee == ACL_ID_PUBLIC ||
			aidata->ai_grantee == roleid)
		{
			result |= aidata->ai_privs & mask;
			if ((how == ACLMASK_ALL) ? (result == mask) : (result != 0))
1
2
3
4
5
6
7
8
9
10
11
12

返回256：ACL_USAGE

				return result;
		}
	}
	...
	...
}
1
2
3
4
5
6

3 系统schema的grant … to public是哪里赋值的？

注意到pg_catalog、public、information_schema三者都是有初始授权的，记录下赋值方法和位置。

postgres=> select * from pg_namespace ;
  oid  |      nspname       | nspowner |                            nspacl
-------+--------------------+----------+---------------------------------------------------------------
    99 | pg_toast           |       10 |
    11 | pg_catalog         |       10 | {mingjie=UC/mingjie,=U/mingjie}
  2200 | public             |     6171 | {pg_database_owner=UC/pg_database_owner,=U/pg_database_owner}
 13918 | information_schema |       10 | {mingjie=UC/mingjie,=U/mingjie}
1
2
3
4
5
6
7

information_schema

在information_schema.sql中赋权：

CREATE SCHEMA information_schema;
GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
SET search_path TO information_schema;
1
2
3

pg_catalog / public

pg_namespace.dat里面插入pg_catalog的tuple，但没有权限信息：

[

{ oid => '11', oid_symbol => 'PG_CATALOG_NAMESPACE',
  descr => 'system catalog schema',
  nspname => 'pg_catalog', nspacl => '_null_' },
{ oid => '99', oid_symbol => 'PG_TOAST_NAMESPACE',
  descr => 'reserved schema for TOAST tables',
  nspname => 'pg_toast', nspacl => '_null_' },
# update dumpNamespace() if changing this descr
{ oid => '2200', oid_symbol => 'PG_PUBLIC_NAMESPACE',
  descr => 'standard public schema',
  nspname => 'public', nspowner => 'pg_database_owner', nspacl => '_null_' },

]
1
2
3
4
5
6
7
8
9
10
11
12
13
14

initdb初始化时在这里给pg_catalog授权：

(gdb) bt
#0  ExecGrant_common (istmt=0x7ffcc262d0c0, classid=2615, default_privs=768, object_check=0x0) at aclchk.c:2170
#1  0x000000000059d7d1 in ExecGrantStmt_oids (istmt=0x7ffcc262d0c0) at aclchk.c:625
#2  0x000000000059d6a6 in ExecuteGrantStmt (stmt=0x27458c8) at aclchk.c:583
#3  0x00000000009c0d56 in ProcessUtilitySlow (pstate=0x283fd28, pstmt=0x2745998, queryString=0x2744c88 "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0xd64360 , qc=0x7ffcc262d890) at utility.c:1813
#4  0x00000000009bf16e in standard_ProcessUtility (pstmt=0x2745998, queryString=0x2744c88 "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0xd64360 , qc=0x7ffcc262d890) at utility.c:977
#5  0x00000000009be69a in ProcessUtility (pstmt=0x2745998, queryString=0x2744c88 "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0xd64360 , qc=0x7ffcc262d890) at utility.c:530
#6  0x00000000009bd2db in PortalRunUtility (portal=0x26f2bd8, pstmt=0x2745998, isTopLevel=true, setHoldSnapshot=false, dest=0xd64360 , qc=0x7ffcc262d890) at pquery.c:1158
#7  0x00000000009bd535 in PortalRunMulti (portal=0x26f2bd8, isTopLevel=true, setHoldSnapshot=false, dest=0xd64360 , altdest=0xd64360 , qc=0x7ffcc262d890) at pquery.c:1315
#8  0x00000000009bca6d in PortalRun (portal=0x26f2bd8, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0xd64360 , altdest=0xd64360 , qc=0x7ffcc262d890) at pquery.c:791
#9  0x00000000009b6533 in exec_simple_query (query_string=0x2744c88 "GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n") at postgres.c:1274
#10 0x00000000009babc3 in PostgresMain (dbname=0x2699be0 "template1", username=0x2699350 "mingjie") at postgres.c:4637
#11 0x00000000009ba472 in PostgresSingleUserMain (argc=12, argv=0x2693a50, username=0x2699350 "mingjie") at postgres.c:4096
#12 0x00000000007b821e in main (argc=12, argv=0x2693a50) at main.c:195
1
2
3
4
5
6
7
8
9
10
11
12
13
14