539      阿裏雲  技術社區[雲棲]

一天學會PostgreSQL應用開發與管理 - 5 數據定義

背景

本章大綱

1. 數據類型

2. 數據操作

3. 表管理

4. 視圖

5. 約束

6. RLS（行安全策略）

第三章：數據定義

1. 數據類型

https://www.postgresql.org/docs/9.6/static/datatype.html

1、數值

NUMERIC(precision, scale)

精度夠用時，建議float8，性能比numeric更好。

擴展浮點精度

postgres=# set extra_float_digits=3;  
SET  

2、貨幣（float8剪切的domain）

3、字符串

長度定義為字符長度，並非字節長度。

4、字節流

輸入格式

輸出格式

5、日期、時間

6、布爾

7、枚舉

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');  
CREATE TABLE person (  
    name text,  
    current_mood mood  
);  
INSERT INTO person VALUES ('Moe', 'happy');  
SELECT * FROM person WHERE current_mood = 'happy';  
 name | current_mood   
------+--------------  
 Moe  | happy  
(1 row)  

枚舉順序，與插入順序一致

8、幾何

9、網絡

10、比特流

CREATE TABLE test (a BIT(3), b BIT VARYING(5));  
INSERT INTO test VALUES (B'101', B'00');  
INSERT INTO test VALUES (B'10', B'101');  
ERROR:  bit string length 2 does not match type bit(3)  
  
INSERT INTO test VALUES (B'10'::bit(3), B'101');  
SELECT * FROM test;  
  a  |  b  
-----+-----  
 101 | 00  
 100 | 101  

11、全文檢索

tsvector

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;  
                      tsvector  
----------------------------------------------------  
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'  

tsquery

SELECT 'fat & rat'::tsquery;  
    tsquery      
---------------  
 'fat' & 'rat'  
  
SELECT 'fat & (rat | cat)'::tsquery;  
          tsquery            
---------------------------  
 'fat' & ( 'rat' | 'cat' )  
  
SELECT 'fat & rat & ! cat'::tsquery;  
        tsquery           
------------------------  
 'fat' & 'rat' & !'cat'  

全文檢索例子

SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );  
 ?column?  
----------  
 t  

中文全文檢索

https://github.com/jaiminpan/pg_jieba

postgres=# select to_tsvector('jiebacfg','中華人民共和國萬歲，如何加快PostgreSQL結巴分詞加載速度');    
                                       to_tsvector                                            
------------------------------------------------------------------------------------------    
 'postgresql':6 '萬歲':2 '中華人民共和國':1 '分詞':8 '加快':5 '加載':9 '結巴':7 '速度':10    
(1 row)    
Time: 0.522 ms    
postgres=# select 8*1000000/14.175527;    
      ?column?           
---------------------    
 564352.916120860974    
(1 row)    
Time: 0.743 ms    

12、UUID

create extension "uuid-ossp";  

13、XML

To produce a value of type xml from character data, use the function xmlparse:  
  
XMLPARSE ( { DOCUMENT | CONTENT } value)  
  
Examples:  
  
XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')  
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')  

14、JSON

json內部支持的類型

json和jsonb例子

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;  
                      json                         
-------------------------------------------------  
 {"bar": "baz", "balance": 7.77, "active":false}  
(1 row)  
  
SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;  
                      jsonb                         
--------------------------------------------------  
 {"bar": "baz", "active": false, "balance": 7.77}  
(1 row)  
  
SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;  
         json          |          jsonb            
-----------------------+-------------------------  
 {"reading": 1.230e-5} | {"reading": 0.00001230}  
(1 row)  

15、數組

postgres=# select array(select generate_series(1,10));  
         array            
------------------------  
 {1,2,3,4,5,6,7,8,9,10}  
(1 row)  
  
postgres=# select array['a','b','c'];  
  array    
---------  
 {a,b,c}  
(1 row)  
  
postgres=# select array['a','b','c'] @> array['a'];  
 ?column?   
----------  
 t  
(1 row)  

數組操作

                                                      List of functions  
   Schema   |          Name           | Result data type |                    Argument data types                    |  Type    
------------+-------------------------+------------------+-----------------------------------------------------------+--------  
 pg_catalog | array_agg               | anyarray         | anyarray                                                  | agg  
 pg_catalog | array_agg               | anyarray         | anynonarray                                               | agg  
 pg_catalog | array_agg_array_finalfn | anyarray         | internal, anyarray                                        | normal  
 pg_catalog | array_agg_array_transfn | internal         | internal, anyarray                                        | normal  
 pg_catalog | array_agg_finalfn       | anyarray         | internal, anynonarray                                     | normal  
 pg_catalog | array_agg_transfn       | internal         | internal, anynonarray                                     | normal  
 pg_catalog | array_append            | anyarray         | anyarray, anyelement                                      | normal  
 pg_catalog | array_cat               | anyarray         | anyarray, anyarray                                        | normal  
 pg_catalog | array_dims              | text             | anyarray                                                  | normal  
 pg_catalog | array_eq                | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | array_fill              | anyarray         | anyelement, integer[]                                     | normal  
 pg_catalog | array_fill              | anyarray         | anyelement, integer[], integer[]                          | normal  
 pg_catalog | array_ge                | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | array_gt                | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | array_in                | anyarray         | cstring, oid, integer                                     | normal  
 pg_catalog | array_larger            | anyarray         | anyarray, anyarray                                        | normal  
 pg_catalog | array_le                | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | array_length            | integer          | anyarray, integer                                         | normal  
 pg_catalog | array_lower             | integer          | anyarray, integer                                         | normal  
 pg_catalog | array_lt                | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | array_ndims             | integer          | anyarray                                                  | normal  
 pg_catalog | array_ne                | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | array_out               | cstring          | anyarray                                                  | normal  
 pg_catalog | array_position          | integer          | anyarray, anyelement                                      | normal  
 pg_catalog | array_position          | integer          | anyarray, anyelement, integer                             | normal  
 pg_catalog | array_positions         | integer[]        | anyarray, anyelement                                      | normal  
 pg_catalog | array_prepend           | anyarray         | anyelement, anyarray                                      | normal  
 pg_catalog | array_recv              | anyarray         | internal, oid, integer                                    | normal  
 pg_catalog | array_remove            | anyarray         | anyarray, anyelement                                      | normal  
 pg_catalog | array_replace           | anyarray         | anyarray, anyelement, anyelement                          | normal  
 pg_catalog | array_send              | bytea            | anyarray                                                  | normal  
 pg_catalog | array_smaller           | anyarray         | anyarray, anyarray                                        | normal  
 pg_catalog | array_to_json           | json             | anyarray                                                  | normal  
 pg_catalog | array_to_json           | json             | anyarray, boolean                                         | normal  
 pg_catalog | array_to_string         | text             | anyarray, text                                            | normal  
 pg_catalog | array_to_string         | text             | anyarray, text, text                                      | normal  
 pg_catalog | array_to_tsvector       | tsvector         | text[]                                                    | normal  
 pg_catalog | array_typanalyze        | boolean          | internal                                                  | normal  
 pg_catalog | array_upper             | integer          | anyarray, integer                                         | normal  
 pg_catalog | arraycontained          | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | arraycontains           | boolean          | anyarray, anyarray                                        | normal  
 pg_catalog | arraycontjoinsel        | double precision | internal, oid, internal, smallint, internal               | normal  
 pg_catalog | arraycontsel            | double precision | internal, oid, internal, integer                          | normal  
 pg_catalog | arrayoverlap            | boolean          | anyarray, anyarray                                        | normal  

16、複合類型

CREATE TYPE complex AS (  
    r       double precision,  
    i       double precision  
);  
  
CREATE TYPE inventory_item AS (  
    name            text,  
    supplier_id     integer,  
    price           numeric  
);  

構造複合類型值

'("fuzzy dice",42,1.99)'  
  
which would be a valid value of the inventory_item type defined above. To make a field be NULL, write no characters at all in its position in the list.  
'("fuzzy dice",42,)'  
  
If you want an empty string rather than NULL, write double quotes:  
'("",42,)'  

訪問複合類型內的元素

SELECT item.name FROM on_hand WHERE item.price > 9.99;  
  
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;  
  
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;  
  
SELECT (myfunc(x)).* FROM some_table;  
  
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;  

插入、修改複合類型的值

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));  
  
UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;  
  
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;  
  
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);  

17、範圍類型

目前支持的範圍類型(用戶可以自定義範圍類型)

int4range — Range of integer  
  
int8range — Range of bigint  
  
numrange — Range of numeric  
  
tsrange — Range of timestamp without time zone  
  
tstzrange — Range of timestamp with time zone  
  
daterange — Range of date  

例子

-- includes 3, does not include 7, and does include all points in between  
SELECT '[3,7)'::int4range;  
  
-- does not include either 3 or 7, but includes all points in between  
SELECT '(3,7)'::int4range;  
  
-- includes only the single point 4  
SELECT '[4,4]'::int4range;  
  
-- includes no points (and will be normalized to 'empty')  
SELECT '[4,4)'::int4range;  

範圍類型索引

CREATE INDEX reservation_idx ON reservation USING GIST (during);  

範圍類型約束1

CREATE TABLE reservation (  
    during tsrange,  
    EXCLUDE USING GIST (during WITH &&)  
);  
  
INSERT INTO reservation VALUES  
    ('[2010-01-01 11:30, 2010-01-01 15:00)');  
INSERT 0 1  
  
INSERT INTO reservation VALUES  
    ('[2010-01-01 14:45, 2010-01-01 15:45)');  
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"  
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts  
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).  

範圍類型約束2 (room相等 並且during相交時，排他)

CREATE EXTENSION btree_gist;  
CREATE TABLE room_reservation (  
    room text,  
    during tsrange,  
    EXCLUDE USING GIST (room WITH =, during WITH &&)  
);  
  
INSERT INTO room_reservation VALUES  
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');  
INSERT 0 1  
  
INSERT INTO room_reservation VALUES  
    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');  
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"  
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts  
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).  
  
INSERT INTO room_reservation VALUES  
    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');  
INSERT 0 1  

18、對象ID

數據庫係統表，大多數使用OID關聯

例子

postgres=# select oid::regclass from pg_class limit 10;  
              oid                 
--------------------------------  
 pg_type  
 pg_toast.pg_toast_187550  
 new_type  
 pg_toast.pg_toast_187550_index  
 test  
 pg_toast.pg_toast_187556  
 pg_toast.pg_toast_187556_index  
 tblaccount4  
 pg_toast.pg_toast_187783  
 pg_toast.pg_toast_187783_index  
(10 rows)  

19、PG_LSN(WAL日誌地址類型)

wal是PostgreSQL數據庫的重做日誌, pg_lsn是wal的地址編碼類型

postgres=# select pg_current_xlog_insert_location();  
 pg_current_xlog_insert_location   
---------------------------------  
 43/15D45F48  
(1 row)  

20、虛擬類型(any*)

虛擬類型，比如任意類型，任意數組，任意元素等。編寫適合任意類型的動態函數時很有用。

還有一些是用於特殊用途的虛擬類型（觸發器，handler等）

例子

postgres=# create or replace function f_test(anyarray) returns anyarray as $$  
select $1;  
$$ language sql strict;  
CREATE FUNCTION  
postgres=# select f_test(array[1,2,3]);  
 f_test    
---------  
 {1,2,3}  
(1 row)  
  
postgres=# select f_test(array['a','b']);  
 f_test   
--------  
 {a,b}  
(1 row)  

2. 數據操作

https://www.postgresql.org/docs/9.6/static/functions.html

1、函數

每一種類型，都有大量的函數，支持這種類型的計算。

在PostgreSQL中，所有的操作符，索引接口，都是基於函數的，底層都有函數的支撐。

2、操作符

每一種類型，都有大量的操作符，支持這種類型的計算。

每一個操作符，都是通過函數來實現計算的。

如何創建操作符

postgres=# \h create operator  
Command:     CREATE OPERATOR  
Description: define a new operator  
Syntax:  
CREATE OPERATOR name (  
    PROCEDURE = function_name  
    [, LEFTARG = left_type ] [, RIGHTARG = right_type ]  
    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]  
    [, RESTRICT = res_proc ] [, JOIN = join_proc ]  
    [, HASHES ] [, MERGES ]  
)  

如何查看操作符對應的函數

操作數1 , OP , 操作數2 , 結果 , 函數

postgres=# select oprleft::regtype,oprname,oprright::regtype,oprresult::regtype,oprcode::regproc from pg_operator ;  
           oprleft           | oprname |          oprright           |          oprresult          |              oprcode                 
-----------------------------+---------+-----------------------------+-----------------------------+------------------------------------  
 integer                     | =       | bigint                      | boolean                     | int48eq  
 integer                     | <>      | bigint                      | boolean                     | int48ne  
 integer                     | <       | bigint                      | boolean                     | int48lt  
 integer                     | >       | bigint                      | boolean                     | int48gt  
 integer                     | <=      | bigint                      | boolean                     | int48le  
 integer                     | >=      | bigint                      | boolean                     | int48ge  
 boolean                     | <       | boolean                     | boolean                     | boollt  
  
......  
  

一元、二元操作符，指操作數的個數

如何查找參數中包含某個特定類型的函數

select proname,proallargtypes::regtype[],proargnames from pg_proc where proallargtypes @> array['integer'::regtype::oid];  

如何查找操作數或結果中包含某個特定類型的操作符

select oprleft::regtype,oprname,oprright::regtype,oprresult::regtype,oprcode::regproc from pg_operator where oprleft='integer'::regtype or oprright='integer'::regtype or oprresult='integer'::regtype;  
  
 oprleft  | oprname | oprright | oprresult |         oprcode            
----------+---------+----------+-----------+--------------------------  
 integer  | =       | bigint   | boolean   | int48eq  
 integer  | <>      | bigint   | boolean   | int48ne  
 integer  | <       | bigint   | boolean   | int48lt  
 integer  | >       | bigint   | boolean   | int48gt  
 integer  | <=      | bigint   | boolean   | int48le  
 integer  | >=      | bigint   | boolean   | int48ge  
 integer  | =       | integer  | boolean   | int4eq  
 integer  | <       | integer  | boolean   | int4lt  
 xid      | =       | integer  | boolean   | xideqint4  
 xid      | <>      | integer  | boolean   | xidneqint4  
......  
  

3. 表管理

建表

postgres=#  \h create table  
Command:     CREATE TABLE  
Description: define a new table  
Syntax:  
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [  
  { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]  
    | table_constraint  
    | LIKE source_table [ like_option ... ] }  
    [, ... ]  
] )  
[ INHERITS ( parent_table [, ... ] ) ]  
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]  
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]  
[ TABLESPACE tablespace_name ]  

權限管理

設置某個用戶，在某個SCHEMA下的所有對象的默認權限

postgres=# \h alter defau  
Command:     ALTER DEFAULT PRIVILEGES  
Description: define default access privileges  
Syntax:  
ALTER DEFAULT PRIVILEGES  
    [ FOR { ROLE | USER } target_role [, ...] ]  
    [ IN SCHEMA schema_name [, ...] ]  
    abbreviated_grant_or_revoke  
  
where abbreviated_grant_or_revoke is one of:  
  
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON TABLES  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
將postgres在public裏麵建立的表，默認賦予select給所有人(PUBLIC)  
  
postgres=# alter default privileges for role postgres in schema public grant select on tables to public;  
ALTER DEFAULT PRIVILEGES  
  
postgres=# select * from pg_default_acl ;  
 defaclrole | defaclnamespace | defaclobjtype |   defaclacl     
------------+-----------------+---------------+---------------  
         10 |          181693 | r             | {=r/postgres}  
(1 row)  

將某個schema下的所有表的某個權限賦予給某個用戶

postgres=# \h grant  
Command:     GRANT  
Description: define access privileges  
Syntax:  
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON { [ TABLE ] table_name [, ...]  
         | ALL TABLES IN SCHEMA schema_name [, ...] }  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
將schema public裏麵的所有表的select權限賦予給所有人(PUBLIC)  
postgres=# grant select on all tables in schema public to public;  
GRANT  

4. 視圖

創建

postgres=# \h create view  
Command:     CREATE VIEW  
Description: define a new view  
Syntax:  
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]  
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]  
    AS query  
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]  

修改

postgres=# \h alter view  
Command:     ALTER VIEW  
Description: change the definition of a view  
Syntax:  
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression  
ALTER VIEW [ IF EXISTS ] name ALTER [ COLUMN ] column_name DROP DEFAULT  
ALTER VIEW [ IF EXISTS ] name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }  
ALTER VIEW [ IF EXISTS ] name RENAME TO new_name  
ALTER VIEW [ IF EXISTS ] name SET SCHEMA new_schema  
ALTER VIEW [ IF EXISTS ] name SET ( view_option_name [= view_option_value] [, ... ] )  
ALTER VIEW [ IF EXISTS ] name RESET ( view_option_name [, ... ] )  

普通視圖隻是QUERY結構，並沒有數據，查詢時會執行視圖中的QUERY。

如果使用視圖來隱藏數據，建議對視圖使用security_barrier選項，防止使用優化器攻擊視圖。

digoal=# create table userinfo(id int, groupid int, username text, age int, addr text, email text, phone text);    
CREATE TABLE    
digoal=# insert into userinfo values (1, 1, 'digoal', 1000, '杭州西湖區', 'digoal@126.com', '13999999999');    
INSERT 0 1    
digoal=# insert into userinfo values (2, 1, 'test', 1000, '火星', 'digoal@126.com', '11999999999');    
INSERT 0 1    
digoal=# insert into userinfo values (3, 1, 'test', 1000, '月球', 'digoal@126.com', '11999999999');    
INSERT 0 1    
digoal=# insert into userinfo values (4, 2, 'test', 1000, '土星', 'digoal@126.com', '11999999999');    
INSERT 0 1    
digoal=# create view v_userinfo as select * from userinfo where groupid =2;    
CREATE VIEW    
digoal=# \c digoal digoal    
You are now connected to database "digoal" as user "digoal".    
digoal=> select * from userinfo;    
ERROR:  permission denied for relation userinfo    
digoal=> select * from v_userinfo;    
ERROR:  permission denied for relation v_userinfo    
digoal=> \c digoal postgres    
You are now connected to database "digoal" as user "postgres".    
digoal=# grant select on v_userinfo to digoal;    
GRANT    
digoal=# \c digoal digoal    
You are now connected to database "digoal" as user "digoal".    
digoal=> select * from v_userinfo;    
 id | groupid | username | age  | addr |     email      |    phone        
----+---------+----------+------+------+----------------+-------------    
  4 |       2 | test     | 1000 | 土星 | digoal@126.com | 11999999999    
(1 row)    
    
digoal=> create or replace function attack(int,int,text,int,text,text,text) returns boolean as $$    
digoal$> declare    
digoal$> begin    
digoal$>   raise notice '%,%,%,%,%,%,%', $1,$2,$3,$4,$5,$6,$7;    
digoal$>   return true;    
digoal$> end;    
digoal$> $$ language plpgsql cost 0.00000000000000000000001;    
CREATE FUNCTION    
digoal=> select * from v_userinfo;    
 id | groupid | username | age  | addr |     email      |    phone        
----+---------+----------+------+------+----------------+-------------    
  4 |       2 | test     | 1000 | 土星 | digoal@126.com | 11999999999    
(1 row)    
    
digoal=> select * from v_userinfo where attack(id,groupid,username,age,addr,email,phone);    
NOTICE:  1,1,digoal,1000,杭州西湖區,digoal@126.com,13999999999    
NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999    
NOTICE:  3,1,test,1000,月球,digoal@126.com,11999999999    
NOTICE:  4,2,test,1000,土星,digoal@126.com,11999999999    
 id | groupid | username | age  | addr |     email      |    phone        
----+---------+----------+------+------+----------------+-------------    
  4 |       2 | test     | 1000 | 土星 | digoal@126.com | 11999999999    
(1 row)    
    
設置視圖的安全柵欄屬性 :     
    
使用普通的函數就不能攻擊他了.    
    
digoal=> \c digoal postgres    
You are now connected to database "digoal" as user "postgres".    
digoal=# create view v_userinfo_1 with(security_barrier) as select * from userinfo where id=2;    
CREATE VIEW    
    
digoal=# grant select on v_userinfo_1 to digoal;    
GRANT    
digoal=# \c digoal digoal    
You are now connected to database "digoal" as user "digoal".    
digoal=> select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);    
NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999    
 id | groupid | username | age  | addr |     email      |    phone        
----+---------+----------+------+------+----------------+-------------    
  2 |       1 | test     | 1000 | 火星 | digoal@126.com | 11999999999    
(1 row)    
    
如果把函數設置為leakproof, 就可以被攻擊了.(隻有超級用戶可以創建leakproof函數)    
    
digoal=> \c digoal postgres    
You are now connected to database "digoal" as user "postgres".    
digoal=# alter function digoal.attack(int,int,text,int,text,text,text) leakproof;    
ALTER FUNCTION    
digoal=# \c digoal digoal    
You are now connected to database "digoal" as user "digoal".    
digoal=> select * from v_userinfo_1 where attack(id,groupid,username,age,addr,email,phone);    
NOTICE:  1,1,digoal,1000,杭州西湖區,digoal@126.com,13999999999    
NOTICE:  2,1,test,1000,火星,digoal@126.com,11999999999    
NOTICE:  3,1,test,1000,月球,digoal@126.com,11999999999    
NOTICE:  4,2,test,1000,土星,digoal@126.com,11999999999    
 id | groupid | username | age  | addr |     email      |    phone        
----+---------+----------+------+------+----------------+-------------    
  2 |       1 | test     | 1000 | 火星 | digoal@126.com | 11999999999    
(1 row)   

物化視圖是帶數據的視圖。可以對其創建索引。

創建物化視圖

postgres=# \h create materialized view  
Command:     CREATE MATERIALIZED VIEW  
Description: define a new materialized view  
Syntax:  
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name  
    [ (column_name [, ...] ) ]  
    [ WITH ( storage_parameter [= value] [, ... ] ) ]  
    [ TABLESPACE tablespace_name ]  
    AS query  
    [ WITH [ NO ] DATA ]  
  
postgres=# create materialized view mv1 as select * from pg_class;  
<p>SELECT 456</p>  

刷新物化視圖數據

postgres=# \h refresh  
Command:     REFRESH MATERIALIZED VIEW  
Description: replace the contents of a materialized view  
Syntax:  
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name  
    [ WITH [ NO ] DATA ]  
  
必須有PK或者UK，才能增量刷新物化視圖  
postgres=# refresh materialized view concurrently mv1;  
ERROR:  cannot refresh materialized view "public.mv1" concurrently  
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.  
  
postgres=# create unique index uk_mv1 on mv1(relname);  
CREATE INDEX  
postgres=# refresh materialized view concurrently mv1;  
REFRESH MATERIALIZED VIEW  

5. 約束

1、主外鍵約束

reference key必須是唯一約束字段或PK字段。

postgres=# create table rtbl(id int primary key, info text);  
CREATE TABLE  
  
postgres=# create table ftbl(id int, c1 int references rtbl(id), info text);  
CREATE TABLE  
  
postgres=# \d+ rtbl  
                         Table "public.rtbl"  
 Column |  Type   | Modifiers | Storage  | Stats target | Description   
--------+---------+-----------+----------+--------------+-------------  
 id     | integer | not null  | plain    |              |   
 info   | text    |           | extended |              |   
Indexes:  
    "rtbl_pkey" PRIMARY KEY, btree (id)  
Referenced by:  
    TABLE "ftbl" CONSTRAINT "ftbl_c1_fkey" FOREIGN KEY (c1) REFERENCES rtbl(id)  
  
postgres=# \d+ ftbl  
                         Table "public.ftbl"  
 Column |  Type   | Modifiers | Storage  | Stats target | Description   
--------+---------+-----------+----------+--------------+-------------  
 id     | integer |           | plain    |              |   
 c1     | integer |           | plain    |              |   
 info   | text    |           | extended |              |   
Foreign-key constraints:  
    "ftbl_c1_fkey" FOREIGN KEY (c1) REFERENCES rtbl(id)  

2、check約束

postgres=# create table cktbl(id int check (id>=100), info text);  
CREATE TABLE  
postgres=# insert into cktbl values (1,'test');;  
ERROR:  new row for relation "cktbl" violates check constraint "cktbl_id_check"  
DETAIL:  Failing row contains (1, test).  

3、排他約束

CREATE TABLE reservation (  
    during tsrange,  
    EXCLUDE USING GIST (during WITH &&)  
);  
  
INSERT INTO reservation VALUES  
    ('[2010-01-01 11:30, 2010-01-01 15:00)');  
INSERT 0 1  
  
INSERT INTO reservation VALUES  
    ('[2010-01-01 14:45, 2010-01-01 15:45)');  
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"  
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts  
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).  

6. RLS（行安全策略）

行安全策略有利於隔離控製共享表在多個用戶之間的數據呈現和使用.

實現方法,

創建針對表和角色的策略, 不同的角色對表記錄的查詢, 插入, 更新, 刪除 可以有不同的控製方法.

postgres=# \h create policy  
Command:     CREATE POLICY  
Description: define a new row level security policy for a table  
Syntax:  
CREATE POLICY name ON table_name  
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]  
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]  
    [ USING ( using_expression ) ]  
    [ WITH CHECK ( check_expression ) ]  

using 針對已經存在的記錄的校驗. 可實施在select, update, delete, ALL上.

whth check 針對將要新增的記錄的校驗, 可實施在insert, update, ALL上.

需要注意的是, UPDATE因為涉及舊的記錄和新的記錄, 如果隻寫了using , 但是沒有提供with check的話, using同時會當成with check來使用進行檢查.

如果針對同樣的命令創建了多個策略, 所有策略中任意一個為TRUE都通過.

例如ALL, SELECT個創建了一個策略for role r1, 執行select時任意一個為TRUE都通過.

例如SELECT個創建了多個策略for role r1, 執行select時任意一個為TRUE都通過.

例子

創建三個角色

postgres=# create role r1 login;    
CREATE ROLE    
postgres=# create role r2 login;    
CREATE ROLE    
postgres=# create role r3 login;    
CREATE ROLE    

創建測試表

postgres=# create table test(id int, r name);    
CREATE TABLE    
postgres=# insert into test values(1, 'r1');    
INSERT 0 1    
postgres=# insert into test values(2, 'r2');    
INSERT 0 1    
postgres=# insert into test values(3, 'r3');    
INSERT 0 1    
postgres=# grant all on table test to public;    
GRANT    

創建一個新增數據的策略(使用with check)

postgres=# create policy p on test for insert to r1 with check( r = current_user);    
CREATE POLICY    

默認情況下策略是disable狀態的,

postgres=# \d+ test    
                         Table "public.test"    
 Column |  Type   | Modifiers | Storage | Stats target | Description     
--------+---------+-----------+---------+--------------+-------------    
 id     | integer |           | plain   |              |     
 r      | name    |           | plain   |              |     
Policies (Row Security Disabled):    
    POLICY "p" FOR INSERT    
      TO r1    
      WITH CHECK (r = "current_user"())    

通過pg_policies視圖可以查看已經創建的策略.

postgres=# select * from pg_policies ;    
 schemaname | tablename | policyname | roles |  cmd   | qual |       with_check           
------------+-----------+------------+-------+--------+------+------------------------    
 public     | test      | p          | {r1}  | INSERT |      | (r = "current_user"())    
(1 row)    

在策略enable前, 是無視策略的.

postgres=> insert into test values(4,'r1');    
INSERT 0 1    
postgres=> insert into test values(4,'r2');    
INSERT 0 1    

使策略生效

postgres=# alter table test enable row level security;    
ALTER TABLE    
postgres=> \d+ test    
                         Table "public.test"    
 Column |  Type   | Modifiers | Storage | Stats target | Description     
--------+---------+-----------+---------+--------------+-------------    
 id     | integer |           | plain   |              |     
 r      | name    |           | plain   |              |     
Policies:    
    POLICY "p" FOR INSERT    
      TO r1    
      WITH CHECK (r = "current_user"())    

策略生效後, 再次插入, 你會看到隻能插入和r1角色同名的r值.

postgres=# \c postgres r1    
You are now connected to database "postgres" as user "r1".    
postgres=> insert into test values(4,'r2');    
ERROR:  new row violates WITH CHECK OPTION for "test"    
postgres=> insert into test values(4,'r1');    
INSERT 0 1    

再新增一個策略, 現在r1角色插入test表時, 允許r字段的值為'r1','r2'.

postgres=# create policy p1 on test for insert to r1 with check( r = 'r2');    
CREATE POLICY    
postgres=# \c postgres r1    
You are now connected to database "postgres" as user "r1".    
postgres=> insert into test values(4,'r2');    
INSERT 0 1    
postgres=> insert into test values(4,'r1');    
INSERT 0 1    
postgres=> insert into test values(4,'r3');    
ERROR:  new row violates WITH CHECK OPTION for "test"    

創建舊值策略(using). 讓r1用戶隻能查看到r=current_user的值.

postgres=> \c postgres postgres    
You are now connected to database "postgres" as user "postgres".\    
postgres=# create policy p2 on test for select to r1 using ( r = current_user);    
CREATE POLICY    
postgres=# \c postgres r1    
You are now connected to database "postgres" as user "r1".    
postgres=> select * from test;    
 id | r      
----+----    
  1 | r1    
  4 | r1    
  4 | r1    
  4 | r1    
(4 rows)    

創建一個針對所有用戶的策略, 例如, 所有用戶隻能看到 r = current_user 的值.

postgres=> \c postgres postgres    
You are now connected to database "postgres" as user "postgres".    
postgres=# create policy p3 on test for select to public using ( r = current_user);    
CREATE POLICY    
postgres=# \c postgres r2    
You are now connected to database "postgres" as user "r2".    
postgres=> select * from test;    
 id | r      
----+----    
  2 | r2    
  4 | r2    
  4 | r2    
(3 rows)    

注意，這些策略隻針對 非超級用戶 以及 非table owner

postgres=> \c postgres postgres    
You are now connected to database "postgres" as user "postgres".    
postgres=# select * from test;    
 id | r      
----+----    
  1 | r1    
  2 | r2    
  3 | r3    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
(8 rows)    

把r1改為超級用戶, 策略失效.

postgres=# alter role r1 superuser;    
ALTER ROLE    
postgres=# \c postgres r1    
You are now connected to database "postgres" as user "r1".    
postgres=# select * from test;    
 id | r      
----+----    
  1 | r1    
  2 | r2    
  3 | r3    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
(8 rows)    

對於update操作, 因為先需要查看數據, 然後才是插入數據, 所以先會執行using檢查, 然後執行with check檢查. 如果隻有using, 那麼with check還是需要檢查的, 隻不過會使用using策略.

如果隻有with check則在查詢數據時不檢查, 但是插入時檢查.

注意，一旦對用戶創建了策略, 必須在所有命令(insert,update,delete,select)上創建, 否則默認采用拒絕方式.

例如, 現在有1個update的策略.

postgres=# \d test    
     Table "public.test"    
 Column |  Type   | Modifiers     
--------+---------+-----------    
 id     | integer |     
 r      | name    |     
Policies:    
    POLICY "p4" FOR UPDATE    
      TO r3    
      USING (r = "current_user"())    
postgres=# \c postgres r3    
You are now connected to database "postgres" as user "r3".    

因為針對r3角色創建了update策略, 但是沒有創建其他命令的策略, 所以其他命令的策略默認為FALSE

postgres=> select * from test;    
 id | r     
----+---    
(0 rows)    

更新操作應用了策略.

postgres=> update test set id=4 where r='r3';    
UPDATE 1    
postgres=> select * from test;    
 id | r     
----+---    
(0 rows)    

現在創建SELECT的策略, 可以查詢了

postgres=# create policy p1 on test for select to r3 using ( r = current_user);    
CREATE POLICY    
postgres=# \d+ test    
                         Table "public.test"    
 Column |  Type   | Modifiers | Storage | Stats target | Description     
--------+---------+-----------+---------+--------------+-------------    
 id     | integer |           | plain   |              |     
 r      | name    |           | plain   |              |     
Policies:    
    POLICY "p1" FOR SELECT    
      TO r3    
      USING (r = "current_user"())    
    POLICY "p4" FOR UPDATE    
      TO r3    
      USING (r = "current_user"())    
postgres=# \c postgres r3    
You are now connected to database "postgres" as user "r3".    
postgres=> select * from test;    
 id | r      
----+----    
  4 | r3    
(1 row)    

但是delete命令上還沒有創建策略, 所以刪除操作直接FALSE.

postgres=> delete from test ;    
DELETE 0    

在r1角色上, 沒有創建任何策略, 所以操作是允許的.

postgres=> \c postgres r1    
You are now connected to database "postgres" as user "r1".    
postgres=# select * from test;    
 id | r      
----+----    
  1 | r1    
  2 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r2    
  4 | r1    
  4 | r3    
(8 rows)    

最後更新：2017-04-12 23:25:18

  上一篇： 天泰OpenWAF開源防惡意爬蟲模塊

  下一篇： DB2數據庫運行維護