快速入门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")).
4、约束判定时机
https://www.postgresql.org/docs/9.6/static/sql-set-constraints.html
https://www.postgresql.org/docs/9.6/static/sql-createtable.html
约束定义层面的设置, 参考alter table, create table语法。
是否允许延迟判定约束:
DEFERRABLE
NOT DEFERRABLE
如果配置了允许延迟判定是否违反约束，那么什么时候判定？
INITIALLY IMMEDIATE -- 语句结束时判定
INITIALLY DEFERRED -- 事务结束时判定
事务中设置(覆盖约束的定义设置)
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
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)