postgresql acl权限检查
·
acl 权限检查
简述
当一个对象被创建时，它会被分配一个所有者。所有者通常是执行创建语句的角色。对于大多数类型的对象，初始状态是只有所有者（或超级用户）才能对该对象进行任何操作。要允许其他角色使用它，必须授予权限。
有不同种类的权限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE、SET、ALTER SYSTEM 和 MAINTAIN(pg18)。适用于特定对象的权限取决于对象的类型（表、函数等）。
修改或销毁对象的权利是对象所有者固有的，不能单独授予或撤销
授予或取消权限
-- 可以改变对象的owner，使另一个用户拥有该对象的所有权限
ALTER TABLE table_name OWNER TO new_owner;
-- grant 授权
GRANT UPDATE ON accounts TO joe;
-- revoke 撤销权限
-- 特殊的“角色”名称 PUBLIC 可用于将权限授予系统上的每个角色， 另外还可以创建用户组来使组内用户都有对应的权限
REVOKE ALL ON accounts FROM PUBLIC;
对象可授予的权限
不同的对象，可以被分配的权限不同，比如对象位数据库的话就没有select权限，select权限应该分配给表类的对象
| 权限 | 缩写 | 适用的对象类型 |
|---|---|---|
SELECT | r （“读取”） | LARGE OBJECT、SEQUENCE、TABLE（及类表对象）、表列 |
INSERT | a （“追加”） | TABLE、表列 |
UPDATE | w （“写入”） | LARGE OBJECT、SEQUENCE、TABLE、表列 |
DELETE | d | TABLE |
TRUNCATE | D | TABLE |
REFERENCES | x | TABLE、表列 |
TRIGGER | t | TABLE |
CREATE | C | DATABASE、SCHEMA、TABLESPACE |
CONNECT | c | DATABASE |
TEMPORARY | T | DATABASE |
EXECUTE | X | FUNCTION、PROCEDURE |
USAGE | U | DOMAIN、FOREIGN DATA WRAPPER、FOREIGN SERVER、LANGUAGE、SCHEMA、SEQUENCE、TYPE |
SET | s | PARAMETER |
ALTER SYSTEM | 一个 | PARAMETER |
MAINTAIN | m | TABLE |
| 对象类型 | 所有权限 | 默认 PUBLIC 权限 | psql 命令 |
|---|---|---|---|
DATABASE | CTc | Tc | \l |
DOMAIN | U | U | \dD+ |
FUNCTION 或 PROCEDURE | X | X | \df+ |
FOREIGN DATA WRAPPER | U | none | \dew+ |
FOREIGN SERVER | U | none | \des+ |
LANGUAGE | U | U | \dL+ |
LARGE OBJECT | rw | none | \dl+ |
PARAMETER | sA | none | \dconfig+ |
SCHEMA | UC | none | \dn+ |
SEQUENCE | rwU | none | \dp |
TABLE（及类表对象） | arwdDxtm | none | \dp |
| Table column | arwx | none | \dp |
TABLESPACE | C | none | \db+ |
TYPE | U | U | \dT+ |
// 对象所有的权限
#define ACL_ALL_RIGHTS_COLUMN (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES)
#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER)
#define ACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE|ACL_SELECT|ACL_UPDATE)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT)
#define ACL_ALL_RIGHTS_FDW (ACL_USAGE)
#define ACL_ALL_RIGHTS_FOREIGN_SERVER (ACL_USAGE)
#define ACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE)
#define ACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE)
#define ACL_ALL_RIGHTS_LARGEOBJECT (ACL_SELECT|ACL_UPDATE)
#define ACL_ALL_RIGHTS_PARAMETER_ACL (ACL_SET|ACL_ALTER_SYSTEM)
#define ACL_ALL_RIGHTS_SCHEMA (ACL_USAGE|ACL_CREATE)
#define ACL_ALL_RIGHTS_TABLESPACE (ACL_CREATE)
#define ACL_ALL_RIGHTS_TYPE (ACL_USAGE)
代码实现
每个对象的元数据系统表中存放了AclItem的数组, 用来存放grant的权限，AclItem是一个带有grantee id，grantor id，以及AclMode（uint64） 类型的的ai_privs，ai_privs存放grantor 授权给grantee 的权限
用户执行sql，在对对象访问时（一般在实际执行操作前，初始化阶段即判断是否有权限），构建需要的权限，组成同样的AclMode 类型的权限，然后查系统表，找到对应的对象，遍历AclItem数组，找到grantee为自身的item，然后按位与，即可知道当前是否满足执行sql需要的权限
grant和revoke是获取对应的对象元组，然后修改元组对应grantee的aclmode值
CATALOG(pg_class,1259,RelationRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83,RelationRelation_Rowtype_Id) BKI_SCHEMA_MACRO
{
//....
#ifdef CATALOG_VARLEN /* variable-length fields start here */
// 每个对象的元数据系统表中存放了AclItem的数组, 用来存放grant的权限
aclitem relacl[1] BKI_DEFAULT(_null_);
// ....
#endif
} FormData_pg_class;
// 实际在磁盘中存放的结构体ArrayType
// acl是一个一维的存放AclItem结构体的数组
typedef struct ArrayType Acl;
typedef struct AclItem
{
Oid ai_grantee; //权限被授予者
Oid ai_grantor; //权限授予者
AclMode ai_privs; /* privilege bits */
} AclItem;
typedef uint64 AclMode; /* a bitmask of privilege bits */
// 权限对应的bit位
#define ACL_INSERT (1<<0) /* for relations */
#define ACL_SELECT (1<<1)
#define ACL_UPDATE (1<<2)
#define ACL_DELETE (1<<3)
#define ACL_TRUNCATE (1<<4)
#define ACL_REFERENCES (1<<5)
#define ACL_TRIGGER (1<<6)
#define ACL_EXECUTE (1<<7) /* for functions */
#define ACL_USAGE (1<<8) /* for various object types */
#define ACL_CREATE (1<<9) /* for namespaces and databases */
#define ACL_CREATE_TEMP (1<<10) /* for databases */
#define ACL_CONNECT (1<<11) /* for databases */
#define ACL_SET (1<<12) /* for configuration parameters */
#define ACL_ALTER_SYSTEM (1<<13) /* for configuration parameters */
#define N_ACL_RIGHTS 14 /* 1 plus the last 1<<x */
#define ACL_NO_RIGHTS 0
/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
// AclMode 是一个64位的无符号整数，前32位用来表示当前可以授予给别人的权限， 后32位表示当前可用的权限
#define ACLITEM_GET_PRIVS(item) ((item).ai_privs & 0xFFFFFFFF)
#define ACLITEM_GET_GOPTIONS(item) (((item).ai_privs >> 32) & 0xFFFFFFFF)
#define ACLITEM_GET_RIGHTS(item) ((item).ai_privs)
// 如 ACL_INSERT & ACLITEM_GET_PRIVS(item) ！=0 表示当前可以执行insert权限
// 如 ACL_INSERT & ACLITEM_GET_GOPTIONS(item) ！=0 表示当前可以授予别人insert权限
权限检查函数
// 主要在grant/revoke时调用
static AclMode
pg_aclmask(ObjectType objtype, Oid object_oid, AttrNumber attnum, Oid roleid,
AclMode mask, AclMaskHow how)
{
switch (objtype)
{
case OBJECT_COLUMN:
return
pg_class_aclmask(object_oid, roleid, mask, how) |
pg_attribute_aclmask(object_oid, attnum, roleid, mask, how);
case OBJECT_TABLE:
case OBJECT_SEQUENCE:
return pg_class_aclmask(object_oid, roleid, mask, how);
case OBJECT_LARGEOBJECT:
return pg_largeobject_aclmask_snapshot(object_oid, roleid,
mask, how, NULL);
case OBJECT_PARAMETER_ACL:
return pg_parameter_acl_aclmask(object_oid, roleid, mask, how);
case OBJECT_STATISTIC_EXT:
elog(ERROR, "grantable rights not supported for statistics objects");
/* not reached, but keep compiler quiet */
return ACL_NO_RIGHTS;
case OBJECT_EVENT_TRIGGER:
elog(ERROR, "grantable rights not supported for event triggers");
/* not reached, but keep compiler quiet */
return ACL_NO_RIGHTS;
// 。。。 其他都调用object_aclmask
case OBJECT_TYPE:
return object_aclmask(TypeRelationId, object_oid, roleid, mask, how);
default:
elog(ERROR, "unrecognized object type: %d",
(int) objtype);
/* not reached, but keep compiler quiet */
return ACL_NO_RIGHTS;
}
}
// 权限检查比较通用的函数
AclResult
object_aclcheck(Oid classid, Oid objectid, Oid roleid, AclMode mode)
{
if (object_aclmask(classid, objectid, roleid, mode, ACLMASK_ANY) != 0)
return ACLCHECK_OK;
else
return ACLCHECK_NO_PRIV;
}
static AclMode
object_aclmask(Oid classid, Oid objectid, Oid roleid,
AclMode mask, AclMaskHow how)
{
int cacheid;
AclMode result;
HeapTuple tuple;
Datum aclDatum;
bool isNull;
Acl *acl;
Oid ownerId;
/* Special cases */
switch (classid)
{
case NamespaceRelationId:
return pg_namespace_aclmask(objectid, roleid, mask, how);
case TypeRelationId:
return pg_type_aclmask(objectid, roleid, mask, how);
}
/* Even more special cases */
// RelationRelationId 需要判断对应的objectid是否为系统表、是否为视图等
Assert(classid != RelationRelationId); /* should use pg_class_acl* */
Assert(classid != LargeObjectMetadataRelationId); /* should use
* pg_largeobject_acl* */
// 超级用户跳过检查
if (superuser_arg(roleid))
return mask;
// 获取cacheid， 在syscache中查询对应objectid的tuple，获取ownerid和aclDatum
cacheid = get_object_catcache_oid(classid);
tuple = SearchSysCache1(cacheid, ObjectIdGetDatum(objectid));
if (!HeapTupleIsValid(tuple))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_DATABASE),
errmsg("%s with OID %u does not exist", get_object_class_descr(classid), objectid)));
ownerId = DatumGetObjectId(SysCacheGetAttrNotNull(cacheid,
tuple,
get_object_attnum_owner(classid)));
aclDatum = SysCacheGetAttr(cacheid, tuple, get_object_attnum_acl(classid),
&isNull);
// aclDatum为NULL，创建默认acl，否则获取acl数组
if (isNull)
{
/* No ACL, so build default ACL */
acl = acldefault(get_object_type(classid, objectid), ownerId);
aclDatum = (Datum) 0;
}
else
{
/* detoast ACL if necessary */
acl = DatumGetAclP(aclDatum);
}
// mask为需要的权限，acl为当前tuple上分配的权限，& 后获取结果
result = aclmask(acl, roleid, ownerId, mask, how);
// 释放资源返回
if (acl && (Pointer) acl != DatumGetPointer(aclDatum))
pfree(acl);
ReleaseSysCache(tuple);
return result;
}
typedef enum
{
ACLMASK_ALL, //mask全匹配时即返回
ACLMASK_ANY //存在任意非0权限即返回
} AclMaskHow;
AclMode
aclmask(const Acl *acl, Oid roleid, Oid ownerId,
AclMode mask, AclMaskHow how)
{
AclMode result;
AclMode remaining;
AclItem *aidat;
int i,
num;
if (acl == NULL)
elog(ERROR, "null ACL");
// 检查是否为1维数组，数组元素是否为aclitem
check_acl(acl);
/* Quick exit for mask == 0 */
if (mask == 0)
return 0;
result = 0;
// grantbit不为空 如果role有owner的权限（为owner或者继承自owner）
if ((mask & ACLITEM_ALL_GOPTION_BITS) &&
has_privs_of_role(roleid, ownerId))
{
result = mask & ACLITEM_ALL_GOPTION_BITS;
if ((how == ACLMASK_ALL) ? (result == mask) : (result != 0))
return result;
}
num = ACL_NUM(acl);
aidat = ACL_DAT(acl);
// 从数组中读aclitem， 检查grantee是否为 public或者自己， 然后通过ai_privs & mask ，得出结果
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;
// all mask全匹配时即返回， any result！=0即返回
if ((how == ACLMASK_ALL) ? (result == mask) : (result != 0))
return result;
}
}
// 检查被roleid继承的其他用户是否有对应的权限
remaining = mask & ~result;
for (i = 0; i < num; i++)
{
AclItem *aidata = &aidat[i];
if (aidata->ai_grantee == ACL_ID_PUBLIC ||
aidata->ai_grantee == roleid)
continue; /* already checked it */
if ((aidata->ai_privs & remaining) &&
has_privs_of_role(roleid, aidata->ai_grantee))
{
result |= aidata->ai_privs & mask;
if ((how == ACLMASK_ALL) ? (result == mask) : (result != 0))
return result;
remaining = mask & ~result;
}
}
return result;
}
// 大部分对象调用object_aclcheck即可， 部分对象依然需要调用对应的检查函数
// 检查列对象是否有权限
pg_attribute_aclcheck
// 检查表对象是否有权限
pg_class_aclcheck
// 检查GUC对象是否有权限
pg_parameter_aclcheck
// 检查largeobject对象是否有权限
pg_largeobject_aclcheck_snapshot
grant revoke时的权限检查
grant
grant和revoke需要修改tuple上的acl数组，找到对应的grantee和grantor，修改权限aclmode
void
select_best_grantor(Oid roleId, AclMode privileges,
const Acl *acl, Oid ownerId,
Oid *grantorId, AclMode *grantOptions)
{
AclMode needed_goptions = ACL_GRANT_OPTION_FOR(privileges);
List *roles_list;
int nrights;
ListCell *l;
// owner或superuser 即为最优 grantor
if (roleId == ownerId || has_superuser_privilege_arg(roleId))
{
*grantorId = ownerId;
*grantOptions = needed_goptions;
return;
}
// 获取roleId的被继承role
roles_list = roles_is_member_of(roleId的被继承role, ROLERECURSE_PRIVS,
InvalidOid, NULL);
/* initialize candidate result as default */
*grantorId = roleId;
*grantOptions = ACL_NO_RIGHTS;
nrights = 0;
// 选择有直接权限满足或者有最多的role作为最佳grantor
foreach(l, roles_list)
{
Oid otherrole = lfirst_oid(l);
AclMode otherprivs;
otherprivs = aclmask_direct(acl, otherrole, ownerId,
needed_goptions, ACLMASK_ALL);
if (otherprivs == needed_goptions)
{
/* Found a suitable grantor */
*grantorId = otherrole;
*grantOptions = otherprivs;
return;
}
/*
* If it has just some of the needed privileges, remember best
* candidate.
*/
if (otherprivs != ACL_NO_RIGHTS)
{
int nnewrights = count_one_bits(otherprivs);
if (nnewrights > nrights)
{
*grantorId = otherrole;
*grantOptions = otherprivs;
nrights = nnewrights;
}
}
}
}
// revoke和grant逻辑类似，都调用同一个接口ExecuteGrantStmt
ExecuteGrantStmt
// ... name转oid、获取grant的对象所有可授予的所有权限等
ExecGrantStmt_oids
// sequence或relation
ExecGrant_Relation
// 1 .先做检查，判断表/sequence是否存在，是否授予的权限属于表/sequence的权限
// 2. 获取旧的acl，没有的话调用acldefault 获取默认权限
select_best_grantor // 选择best_grantor 以及可授予的权限
restrict_and_check_grant // 权限检查
merge_acl_with_grant
aclupdate //更新数组中的值
heap_modify_tuple // 修改元组
CatalogTupleUpdate
updateAclDependencies // 更新依赖
expand_col_privileges // 如果要对列授权，修改对应属性权限
ExecGrant_Attribute
ExecGrant_common
//和上面类似，没有对列授权的步骤
ExecGrant_Largeobject
ExecGrant_Parameter
sql语句执行时的权限检查
ddl检查
standard_ProcessUtility内对应操作执行前做检查
// 创建表
DefineRelation
RangeVarGetAndCheckCreationNamespace
object_aclcheck //需要对应ns下的create权限
object_aclcheck //指定tablespace时，需要tablespace的create权限
object_aclcheck //ofType 需要usage权限
// set guc
ExecSetVariableStmt
set_config_option
set_config_option_ext
pg_parameter_aclcheck
。。。。
非utility语句检查
- 执行器初始化时对目标表做权限检查、对要调用的表达式做权限检查等
initplan
ExecCheckPermissions
ExecCheckOneRelPerms
pg_class_aclmask
// 所需权限都满足的话，即满足权限执行完毕返回，否则先判断是只含ACL_SELECT/ACL_INSERT/ACL_UPDATE的权限，然后进行列级检查
// ACL_SELECT时判断所读的列是否满足条件
pg_attribute_aclcheck_all // 从pg_attribute表中读出所有列，判断是否满足acl权限
// 或者pg_attribute_aclcheck对对应列做检查
// insert或update， 判断插入或删除的列是否满足条件
ExecCheckPermissionsModified
pg_attribute_aclcheck_all // 或者pg_attribute_aclcheck
-
优化器统计信息场景使用
examine_variable all_rows_selectable -
表达式初始化时，判断对应表达式函数是否有执行权限
ExecInitExprRec
ExecInitFunc
object_aclcheck
。。。。
权限检查应尽量在初始化阶段做，主要调用函数object_aclcheck、pg_class_aclmask、pg_attribute_aclcheck_all、pg_attribute_aclcheck等
参考
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
9
0- 0
已为社区贡献4条内容
所有评论(0)