AnalyticDB for PostgreSQL 6.0新特性解析-授权操作增强

授权语法对比

ADB PG4.3和6.0 GRANT语法上对比：

新增功能

改动点有：

支持同一SCEMA下对象授权

ALL [TABLES|SEQUENCES|FUNCTIONS] IN SCHEMA schema_name；

4.3不支持，6.0操作示例：

# 6.0
yuanzhe_test=> \dt s1.*
          List of relations
 Schema | Name | Type  |    Owner
--------+------+-------+--------------
 s1     | t1   | table | yuanzhe_test
 s1     | t2   | table | yuanzhe_test
(2 rows)

yuanzhe_test=> grant select on all tables in schema s1 to u1;
GRANT

支持列级别授权

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ]( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO { role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  

4.3不支持，6.0 操作示例:

yuanzhe_test=> \d s1.t1
        Table "s1.t1"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 name   | text    |

yuanzhe_test=> grant select (name) on s1.t1 to u1;
GRANT
yuanzhe_test=> \c yuanzhe_test u1
Password for user u1:
You are now connected to database "yuanzhe_test" as user "u1".
yuanzhe_test=> select name from s1.t1;
 name
------
(0 rows)

yuanzhe_test=> select id from s1.t1;
ERROR:  permission denied for relation t1

支持对自定义DOMAIN授权

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

4.3也支持DOMAIN，但是不支持授权，6.0支持授权，示例：

# 4.3
yuanzhe_test=> CREATE DOMAIN us_zip_code AS TEXT CHECK
yuanzhe_test->        ( VALUE ~ '^\\d{5}$' OR VALUE ~ '^\\d{5}-\\d{4}$' );
CREATE DOMAIN
yuanzhe_test=> grant USAGE on DOMAIN us_zip_code to u1;
ERROR:  syntax error at or near "us_zip_code"
LINE 1: grant USAGE on DOMAIN us_zip_code to u1;
                              ^
# 6.0
yuanzhe_test=> CREATE DOMAIN us_zip_code AS TEXT CHECK
yuanzhe_test->        ( VALUE ~ '^\\d{5}$' OR VALUE ~ '^\\d{5}-\\d{4}$' );
CREATE DOMAIN
yuanzhe_test=> grant USAGE on DOMAIN us_zip_code to u1;
GRANT

支持对FDW权限管理

6.0支持FDW，对FDW相关的权限管理，目前只有superuser才支持创建FDW，未开放

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

支持对TYPE进行授权

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

4.3 支持创建TYPE，但不支持授权，6.0支持授权。示例：

# 4.3
yuanzhe_test=> CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
yuanzhe_test=> grant USAGE on TYPE compfoo to u1;
ERROR:  syntax error at or near "compfoo"
LINE 1: grant USAGE on TYPE compfoo to u1;
                            ^
                            
# 6.0
yuanzhe_test=> CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE TYPE
yuanzhe_test=> grant USAGE on TYPE compfoo to u1;
GRANT
yuanzhe_test=> \c yuanzhe_test u1
Password for user u1:
You are now connected to database "yuanzhe_test" as user "u1".
yuanzhe_test=> create table t3(id int, dum compfoo);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
yuanzhe_test=> \d+ t3
                          Table "public.t3"
 Column |  Type   | Modifiers | Storage  | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
 id     | integer |           | plain    |              |
 dum    | compfoo |           | extended |              |