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

授权语法比照

ADB PG4.3和6.0 GRANT语法上比照:
AnalyticDB for PostgreSQL 6.0新特性解析-授权操作增强

新增功用

改动点有:

支撑同一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 |              |