基于PostgreSQL标准权限模型授权

本文为您介绍在Hologres中,如何基于PostgreSQL标准模型(专家权限模型)进行授权的最佳实践。帮助您简化授权操作并使用更细粒度的权限管理。

背景信息

Hologres兼容PostgreSQL生态,支持PostgreSQL的标准权限模型(简称专家权限模型)。同时,Hologres提供了一套简单权限模型的授权模式,详情请参见简单权限模型概述

简单权限模型的权限划分粒度比较粗,不太适用于细粒度的权限管理场景。PostgreSQL的标准授权对于权限的划分非常细致,如果您需要使用更细粒度的权限管理,请参照本文基于PostgreSQL授权的最佳实践进行操作。

PostgreSQL权限模型简介

标准PostgreSQL授权拥有详细的权限管理体系,详情请参见Postgresql授权

PostgreSQL授权的限制如下:

  • PostgreSQL授权仅支持对现有对象授权,对未来的对象不生效。示例如下。

    1. User1执行了GRANT SELECT ON ALL TABLES IN SCHEMA public TO User2;语句对User2授予了public Schema中所有表的SELECT权限。

    2. User1在public Schema中创建了一张新表table_new

    3. User2执行SELECT * FROM table_new语句时显示报错Permission denied

      由于User1对User2授予SELECT权限时,仅包含授权时刻public Schema中的所有表,而不包含未来在public Schema中创建的表,因此会产生上述报错。

  • 您可以使用ALTER DEFAULT PRIVILEGES语句,对未来对象设置默认权限,详情请参见ALTER DEFAULT PRIVILEGES。该权限仅对未来对象生效。示例语句如下。

    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC; --当前授权的人对在public Schema中新创建的表具有读权限。

    您也可以使用ALTER DEFAULT PRIVILEGES FOR ROLE xxx语句,为其他角色创建默认权限。当前用户和xxx满足以下关系时才能成功设置默认权限:

    • 当前用户是xxx组的成员。

    • 当前用户是超级用户(Superuser)时,xxx可以为用户或组。

    您可以使用psql命令\ddp查看(系统表为pg_catalog.pg_default_aclALTER DEFAULT PRIVILEGES是否设置成功。

    ALTER DEFAULT PRIVILEGES类似于一个触发器。创建新表时,Hologres会使用当前用户和Schema去检查pg_catalog.pg_default_acl系统表中是否有匹配项。如果存在匹配项,则系统自动添加匹配项规则。

    说明
    • 您只能使用当前用户与匹配项规则进行匹配,不能使用当前用户的用户组去匹配。

    • alter default privileges规则匹配只能在创建表时执行,在创建表之后修改表Owner( alter table tablename owner to)不会触发alter default privileges

    例如,用户User1属于Group1,如果要给Group1匹配规则,授予未来表全权限。情况如下:

    • 如果当前用户是User1,则创建表时匹配不到规则。

    • 如果创建表之前先执行set session role group1; ,改变当前用户为Group1,则创建表时就可以匹配到规则,系统自动为新创建的表授权。

  • 只有表Owner才可以删除表。

    在标准的PostgreSQL授权语句中,您需要根据Ownership来判断是否可以删除表。可以删除表的角色如下:

    • 表的Owner(即建表者)。

    • 表所在Schema的Owner。

    • Superuser

  • 在PostgreSQL中,系统会默认创建表的用户为表的Owner,拥有该表的所有权限,包括删除表的权限。

    修改表Owner,示例如下语句。

    ALTER TABLE <tablename> OWNER TO user2; //将表Owner由User1修改为User2。
    ALTER TABLE <tablename> OWNER TO group1;//将表Owner修改为Group1。

    修改表Owner的操作限制如下:

    • User1为表的Owner。

    • User1必须是Group1的直接或者间接成员。

      例如,User1是Group1的成员,或User1是Group1中某个组的成员。

    • Group1必须在表所在的Schema中有创建表的权限。

    • Superuser可以修改任意表的表Owner。

PostgreSQL权限模型规划

标准的PostgreSQL(专家权限模型)权限粒度划分比较细致,在使用之前需要对现有实例对象做如下权限规划:

  • 总共有多少个权限组。

  • 每个组的作用是什么。

  • 每个组包含哪些用户。

  • 哪些角色在什么时候可以删除表。

  • 每个组在哪些Schema中工作。

建议您执行如下操作,规划实例对象:

  • 确定权限组以及组的作用。

    权限组分为以下类型:

    • XX_DEV_GROUP:表的Owner,拥有表的所有操作权限。

    • XX_WRITE_GROUP:表的写入权限,可以写入数据至相应表。

    • XX_VIEW_GROUP:查看表数据权限,可以查看相应表的数据。

    XX表示一个项目。例如PROJ1项目的权限组包括PROJ1_DEV_GROUP、 PROJ1_WRITE_GROUP及PROJ1_VIEW_GROUP。

    说明

    组名的命名规范仅为建议参考,不做强制要求。

  • 确定权限组所在的Schema。

    推荐每个项目的权限组使用一个Schema。

    一个DEV_GROUP可以拥有多张表,但每张表只能属于一个DEV_GROUP。例如,表TABLE1属于PROJ1_DEV_GROUP,则该表就不属于PROJ2_DEV_GROUP。

    一个用户可以属于多个DEV_GROUP。例如,User1可以既是PROJ1_DEV_GROUP的成员,也可以是PROJ2_DEV_GROUP的成员。

Hologres专家权限模型最佳实践一

本次实践以表对象为例,您也可以选用其他对象进行实验。

表的Owner是对应的XXX_DEV_GROUP权限组,因此,DEV_GROUP组中的任意成员都可以管理或删除该表。

例如,用户被添加到PROJ1_DEV_GROUP用户组之后,就拥有PROJ1项目中表的管理或删除权限。具体操作步骤如下:

  1. 创建用户组。

    您可以根据业务需求划分权限模型,由Superuser创建相应的用户组,以PROJ1项目为例,示例语句如下。

    CREATE ROLE PROJ1_DEV_GROUP; //表的Owner,拥有表的所有操作权限。
    CREATE ROLE PROJ1_WRITE_GROUP; //表的写入权限,可以写入数据至相应表。
    CREATE ROLE PROJ1_VIEW_GROUP; //查看表数据权限,可以查看相应表的数据。
  2. 授权用户组Schema的权限。

    您需要授予创建完成的用户组Schema的权限,示例项目PROJ1可以在Schema1中工作,语句如下。

    授权PROJ1拥有SCHEMA1中的相关权限。
    GRANT CREATE,usage ON schema SCHEMA1 TO PROJ1_DEV_GROUP;
    GRANT usage ON schema SCHEMA1 TO PROJ1_WRITE_GROUP;
    GRANT usage ON schema SCHEMA1 TO PROJ1_VIEW_GROUP;
    说明
    • 一个项目可以对应多个Schema,一个Schema也可以对应多个项目。

    • 默认public Schema中所有用户都有CREATE和USAGE权限。

  3. 创建用户并管理用户组。

    授权用户组Schema权限后,Superuser需要创建用户并添加用户至对应的用户组,示例语句如下。

    CREATE USER "USER1";
    GRANT PROJ1_DEV_GROUP TO "USER1"; 
    
    CREATE USER "USER2";
    GRANT PROJ1_VIEW_GROUP TO "USER2";
  4. 创建表并授权。

    创建表等对象时,由表的创建者(必须为PROJ1_DEVE_GROUP的成员)或Superuser执行相应的授权语句(假设新创建的表为TABLE1)。 示例如下。

    GRANT ALL ON TABLE SCHEMA1.TABLE1 TO PROJ1_WRITE_GROUP; //授予PROJ1_WRITE_GROUP写入数据至TABLE1的权限。
    GRANT SELECT ON TABLE SCHEMA1.TABLE1 TO PROJ1_VIEW_GROUP; //授予PROJ1_VIEW_GROUP TABLE1的SELECT权限。
    ALTER TABLE SCHEMA1.TABLE1 owner TO PROJ1_DEV_GROUP; //修改TABLE1的Owner为PROJ1_DEV_GROUP。

Hologres专家权限模型最佳实践二

本次实践,使用ALTER DEFAULT PRIVILEGES语句简化对每张表的授权操作。

您需要提前确定创建的表默认属于哪个项目。具体操作步骤如下:

  1. 创建用户组。

    您可以根据业务需求划分权限模型,由Superuser创建相应的用户组,以PROJ1项目为例,示例语句如下。

    CREATE ROLE PROJ1_DEV_GROUP; //表的Owner,拥有表的所有操作权限。
    CREATE ROLE PROJ1_WRITE_GROUP; //表的写入权限,可以写入数据至相应表。
    CREATE ROLE PROJ1_VIEW_GROUP; //查看表数据权限,可以查看相应表的数据。
  2. 授权用户组Schema的权限。

    您需要授予创建完成的用户组Schema的权限,示例项目PROJ1可以在Schema1中工作,语句如下。

    --授权PROJ1拥有SCHEMA1中的相关权限。
    GRANT CREATE,USAGE ON SCHEMA SCHEMA1 TO PROJ1_DEV_GROUP;
    GRANT USAGE ON SCHEMA SCHEMA1 TO PROJ1_WRITE_GROUP;
    GRANT USAGE ON SCHEMA SCHEMA1 TO PROJ1_VIEW_GROUP;
    说明
    • 一个项目可以对应多个Schema,一个Schema也可以对应多个项目。

    • 默认public Schema中所有用户都有CREATE和USAGE权限。

  3. 创建用户并设置默认授权。

    完成Schema授权后,需要Superuser创建用户并添加用户至相应的组中,同时设置该用户创建表时拥有的默认权限。

    USER1创建的表默认属于PROJ1_DEV_GROUP,并且USER1为合法的阿里云账号。示例设置授权语句如下。

    CREATE USER "USER1";
    
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON  TABLES TO PROJ1_DEV_GROUP; //设置USER1创建的表,PROJ1_DEV_GROUP默认都有读写权限。
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ1_WRITE_GROUP; //设置USER1创建的表,PROJ1_WRITE_GROUP默认都有读写权限。
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT SELECT ON TABLES TO PROJ1_VIEW_GROUP; //设置USER1创建的表,PROJ1_VIEW_GROUP默认都有读权限。
    
    GRANT PROJ1_DEV_GROUP TO "USER1"; //添加USER1至PROJ1_DEV_GROUP。
  4. 修改表的Owner。

    如果您希望DEV_GROUP中的其他用户也可以管理或删除创建的表,则可以修改表的Owner为对应项目的DEV_GROUP,例如PROJ1_DEV_GROUP。

    修改表Owner的语句必须由表的创建者或Superuser执行。例如,示例中表的创建者必须是PROJ1_DEV_GROUP的成员。假设新创建的表为TABLE1。示例修改表Owner的语句如下。

    ALTER TABLE SCHEMA1.TABLE1 OWNER TO PROJ1_DEV_GROUP; //修改TABLE1的Owner为PROJ1_DEV_GROUP。

    如下情况,您可以修改表的Owner:

    • 新创建的表,由Superuser定期修改表Owner。

    • 在需要管理或删除表之前修改表Owner。

    说明

    如果您可以确认表的管理或删除操作是由表的创建者或Superuser执行的,您也可以不执行上述命令。

  5. 修改用户的默认项目。

    调整用户的默认项目,需要Superuser或用户本人执行alter default privileges命令,撤销已设置的缺省权限后,使用新的alter default privileges创建默认授权。

    将USER1的默认项目由PROJ1改为PROJ2,该操作不影响现有的表。示例语句如下。

    取消原有默认授权。
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE ALL ON TABLES FROM PROJ1_DEV_GROUP;
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE ALL ON TABLES FROM PROJ1_WRITE_GROUP;
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" REVOKE SELECT ON TABLES FROM PROJ1_VIEW_GROUP;
    
    创建新的默认授权。
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ2_DEV_GROUP;
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT ALL ON TABLES TO PROJ2_WRITE_GROUP;
    ALTER DEFAULT PRIVILEGES FOR ROLE "USER1" GRANT SELECT ON TABLES TO PROJ2_VIEW_GROUP;