如何基于PostgreSQL标准模型（专家权限模型）进行授权_实时数仓 Hologres(Hologres)-阿里云帮助中心

背景信息

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_acl）ALTER 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项目中表的管理或删除权限。具体操作步骤如下：

创建用户组。

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

create role PROJ1_DEV_GROUP; //表的Owner，拥有表的所有操作权限。
create role PROJ1_WRITE_GROUP; //表的写入权限，可以写入数据至相应表。
create role PROJ1_VIEW_GROUP; //查看表数据权限，可以查看相应表的数据。

授权用户组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权限。
创建用户并管理用户组。
授权用户组Schema权限后，Superuser需要创建用户并添加用户至对应的用户组，示例语句如下。
```
create user "USER1";
grant PROJ1_DEV_GROUP to "USER1"; 

create user "USER2";
grant PROJ1_VIEW_GROUP to "USER2";
```

创建表并授权。

创建表等对象时，由表的创建者（必须为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语句简化对每张表的授权操作。

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

创建用户组。

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

create role PROJ1_DEV_GROUP; //表的Owner，拥有表的所有操作权限。
create role PROJ1_WRITE_GROUP; //表的写入权限，可以写入数据至相应表。
create role PROJ1_VIEW_GROUP; //查看表数据权限，可以查看相应表的数据。

授权用户组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权限。

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

完成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。

修改表的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执行的，您也可以不执行上述命令。

修改用户的默认项目。

调整用户的默认项目，需要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;