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 |              |