Greenplum权限管理

对于BI用户的授权规则：

1、分配usage权限

GRANT USAGE ON SCHEMA [schema_name] to [user/role];

2、分配SELECT权限

GRANT SELECT ON ALL TABLES IN SCHEMA [schema_name] TO [user/role];

GRANT INSERT,UPDATE,DELETE,TRUNCATE ON ALL TABLES IN SCHEMA [schema_name] TO [user/role];

3、分配default privileges

ALTER DEFAULT PRIVILEGES in schema [schema_name] grant SELECT on tables to [user/role];

ALTER DEFAULT PRIVILEGES in schema [schema_name] grant INSERT,UPDATE,DELETE,TRUNCATE on tables to [user/role];

在其他场景下，可能需要将mydb用户未来在bi_user schema下创建的所有表的select,insert,update,delete,truncate权限赋予new_user;
alter default privileges for role mydb in schema bi_user grant select,insert,update,delete,truncate on tables to new_user;

相关的视图：

1、查询用户对数据库的权限

postgres=# select datname,datacl from pg_database ;

datname | datacl
----------------+----------------------------------
template1 | {=c/gpadmin,gpadmin=CTc/gpadmin}
template0 | {=c/gpadmin,gpadmin=CTc/gpadmin}
这里的CTc的理解参考文档：PostgreSQL: Documentation: 14: 5.7. Privileges

2、查询用户对schema的权限

postgres=# select * from pg_namespace ;

3、查询用户对表的权限

postgres=# select * from information_schema.table_privileges ;

本文来源：https://blog.csdn.net/helontian/article/details/127247000