一天学会PostgreSQL应用开发与管理 - 5 数据定义-阿里云开发者社区

背景

本章大纲

1. 数据类型

2. 数据操作

3. 表管理

4. 视图

5. 约束

6. RLS（行安全策略）

第三章：数据定义

1. 数据类型

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

1、数值

Name	Storage Size	Description	Range
smallint	2 bytes	small-range integer	-32768 to +32767
integer	4 bytes	typical choice for integer	-2147483648 to +2147483647
bigint	8 bytes	large-range integer	-9223372036854775808 to +9223372036854775807
decimal	variable	user-specified precision, exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric	variable	user-specified precision, exact	up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real	4 bytes	variable-precision, inexact	6 decimal digits precision
double precision	8 bytes	variable-precision, inexact	15 decimal digits precision
smallserial	2 bytes	small autoincrementing integer	1 to 32767
serial	4 bytes	autoincrementing integer	1 to 2147483647
bigserial	8 bytes	large autoincrementing integer	1 to 9223372036854775807

NUMERIC(precision, scale)

精度够用时，建议float8，性能比numeric更好。

扩展浮点精度

postgres=# set extra_float_digits=3;  
SET

2、货币（float8剪切的domain）

Name	Storage Size	Description	Range
money	8 bytes	currency amount	-92233720368547758.08 to +92233720368547758.07

3、字符串

Name	Description
character varying(n), varchar(n)	variable-length with limit
character(n), char(n)	fixed-length, blank padded
text	variable unlimited length

长度定义为字符长度，并非字节长度。

4、字节流

Name	Storage Size	Description
bytea	1 or 4 bytes plus the actual binary string	variable-length binary string

输入格式

Decimal Octet Value	Description	Escaped Input Representation	Example	Output Representation
0	zero octet	`E'\\000'`	`SELECT E'\\000'::bytea;`	`\000`
39	single quote	`'''' or E'\\047'`	`SELECT E'\''::bytea;`	`'`
92	backslash	`E'\\\\' or E'\\134'`	`SELECT E'\\\\'::bytea;`	`\\`
0 to 31 and 127 to 255	"non-printable" octets	`E'\\xxx' (octal value)`	`SELECT E'\\001'::bytea;`	`\001`

输出格式

Decimal	Octet Value	Description	Escaped Output Representation	Example Output Result
92	backslash	`\\`	`SELECT E'\\134'::bytea;`	`\\`
0 to 31 and 127 to 255	"non-printable" octets	`\xxx (octal value)`	`SELECT E'\\001'::bytea;`	`\001`
32 to 126	"printable" octets	`client character set representation`	`SELECT E'\\176'::bytea;`	`~`

5、日期、时间

Name	Storage Size	Description	Low Value	High Value	Resolution
timestamp [ (p) ] [ without time zone ]	8 bytes	both date and time (no time zone)	4713 BC	294276 AD	1 microsecond / 14 digits
timestamp [ (p) ] with time zone	8 bytes	both date and time, with time zone	4713 BC	294276 AD	1 microsecond / 14 digits
date	4 bytes	date (no time of day)	4713 BC	5874897 AD	1 day
time [ (p) ] [ without time zone ]	8 bytes	time of day (no date)	`00:00:00`	`24:00:00`	1 microsecond / 14 digits
time [ (p) ] with time zone	12 bytes	times of day only, with time zone	`00:00:00+1459`	`24:00:00-1459`	1 microsecond / 14 digits
interval [ fields ] [ (p) ]	16 bytes	time interval	-178000000 years	178000000 years	1 microsecond / 14 digits

6、布尔

Name	Storage Size	Description
boolean	1 byte	state of true or false

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、几何

Name	Storage Size	Description	Representation
point	16 bytes	Point on a plane	(x,y)
line	32 bytes	Infinite line	{A,B,C}
lseg	32 bytes	Finite line segment	((x1,y1),(x2,y2))
box	32 bytes	Rectangular box	((x1,y1),(x2,y2))
path	16+16n bytes	Closed path (similar to polygon)	((x1,y1),...)
path	16+16n bytes	Open path	[(x1,y1),...]
polygon	40+16n bytes	Polygon (similar to closed path)	((x1,y1),...)
circle	24 bytes	Circle	<(x,y),r> (center point and radius)

9、网络

Name	Storage Size	Description
cidr	7 or 19 bytes	IPv4 and IPv6 networks
inet	7 or 19 bytes	IPv4 and IPv6 hosts and networks
macaddr	6 bytes	MAC addresses

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

Function	Description
uuid_generate_v1()	This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications.
uuid_generate_v1mc()	This function generates a version 1 UUID but uses a random multicast MAC address instead of the real MAC address of the computer.
uuid_generate_v3(namespace uuid, name text)	This function generates a version 3 UUID in the given namespace using the specified input name. The namespace should be one of the special constants produced by the uuid_ns_*() functions shown in Table F-34. (It could be any UUID in theory.) The name is an identifier in the selected namespace. For example: SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org'); The name parameter will be MD5-hashed, so the cleartext cannot be derived from the generated UUID. The generation of UUIDs by this method has no random or environment-dependent element and is therefore reproducible.
uuid_generate_v4()	This function generates a version 4 UUID, which is derived entirely from random numbers.
uuid_generate_v5(namespace uuid, name text)	This function generates a version 5 UUID, which works like a version 3 UUID except that SHA-1 is used as a hashing method. Version 5 should be preferred over version 3 because SHA-1 is thought to be more secure than MD5.

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 primitive type	PostgreSQL type	Notes
string	text	\u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8
number	numeric	NaN and infinity values are disallowed
boolean	boolean	Only lowercase true and false spellings are accepted
null	(none)	SQL NULL is a different concept

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

Name	References	Description	Value Example
oid	any	numeric object identifier	564182
regproc	pg_proc	function name	sum
regprocedure	pg_proc	function with argument types	sum(int4)
regoper	pg_operator	operator name	+
regoperator	pg_operator	operator with argument types	*(integer,integer) or -(NONE,integer)
regclass	pg_class	relation name	pg_type
regtype	pg_type	data type name	integer
regrole	pg_authid	role name	smithee
regnamespace	pg_namespace	namespace name	pg_catalog
regconfig	pg_ts_config	text search configuration	english
regdictionary	pg_ts_dict	text search dictionary	simple

例子

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

Name	Description
any	Indicates that a function accepts any input data type.
anyelement	Indicates that a function accepts any data type (see Section 36.2.5).
anyarray	Indicates that a function accepts any array data type (see Section 36.2.5).
anynonarray	Indicates that a function accepts any non-array data type (see Section 36.2.5).
anyenum	Indicates that a function accepts any enum data type (see Section 36.2.5 and Section 8.7).
anyrange	Indicates that a function accepts any range data type (see Section 36.2.5 and Section 8.17).
cstring	Indicates that a function accepts or returns a null-terminated C string.
internal	Indicates that a function accepts or returns a server-internal data type.
language_handler	A procedural language call handler is declared to return language_handler.
fdw_handler	A foreign-data wrapper handler is declared to return fdw_handler.
index_am_handler	An index access method handler is declared to return index_am_handler.
tsm_handler	A tablesample method handler is declared to return tsm_handler.
record	Identifies a function taking or returning an unspecified row type.
trigger	A trigger function is declared to return trigger.
event_trigger	An event trigger function is declared to return event_trigger.
pg_ddl_command	Identifies a representation of DDL commands that is available to event triggers.
void	Indicates that a function returns no value.
opaque	An obsolete type name that formerly served all the above purposes.

例子

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)

一天学会PostgreSQL应用开发与管理 - 5 数据定义

背景

本章大纲

1. 数据类型

2. 数据操作

3. 表管理

4. 视图

5. 约束

6. RLS（行安全策略）

第三章：数据定义

1. 数据类型

2. 数据操作

3. 表管理

4. 视图

5. 约束

6. RLS（行安全策略）

关系型数据库

热门文章

最新文章

相关产品

相关课程

相关电子书

相关实验场景

推荐镜像