PostgreSQL 9.5新特性

PostgreSQL 9.5新特性

 

一、RLS行级安全策略详解

"Row-Level Security (RLS) support" 是9.5版本的主要特性之一，提供了基于行的安全策略，限制数据库用户的查看表数据权限。

eg.

1.创建测试表，插入测试数据

fdb=> create table test_row(id serial primary key, username text, log_event text, create_time timestamp(0) without time zone default clock_timestamp());

CREATE TABLE

 

fdb=> insert into test_row(username,log_event) values('user1','user1:aaa');

INSERT 0 1

fdb=> insert into test_row(username,log_event) values('user1','user1:aadsfdfa');

INSERT 0 1

fdb=> insert into test_row(username,log_event) values('user2','user2:aadsfdfa');

INSERT 0 1

fdb=> insert into test_row(username,log_event) values('user2','user2:test');

INSERT 0 1

fdb=> insert into test_row(username,log_event) values('user3','user3:test3');

INSERT 0 1

fdb=> insert into test_row(username,log_event) values('user3','user3:test3333');

INSERT 0 1

fdb=> insert into test_row(username,log_event) values('user4','user4:test3333');

INSERT 0 1

 

2.创建user1,user2,user3测试用户

[pg95@db1 ~]$ psql fdb

psql (9.5alpha1)

Type "help" for help.

 

fdb=# create role user1 with login;

CREATE ROLE

fdb=# create role user2 with login;

CREATE ROLE

fdb=# create role user3 with login;

CREATE ROLE

 

fdb=> grant select on test_row to user1,user2,user3;

GRANT

 

fdb=> grant usage on schema fdb to user1,user2,user3;

GRANT

 

3.以user登录可以查询到全部数据

fdb=> \c fdb user1

You are now connected to database "fdb" as user "user1".

 

fdb=> select * from fdb.test_row;

id | username | log_event | create_time

----+----------+----------------+---------------------

1 | user1 | user1:aaa | 2015-07-30 14:48:49

2 | user1 | user1:aadsfdfa | 2015-07-30 14:48:54

3 | user2 | user2:aadsfdfa | 2015-07-30 14:48:59

4 | user2 | user2:test | 2015-07-30 14:49:06

5 | user3 | user3:test3 | 2015-07-30 14:49:15

6 | user3 | user3:test3333 | 2015-07-30 14:49:24

7 | user4 | user4:test3333 | 2015-07-30 14:49:29

(7 rows)

 

4.给表添加policy

[pg95@db1 ~]$ psql fdb fdb

psql (9.5alpha1)

Type "help" for help.

 

fdb=> CREATE POLICY policy_test_row ON test_row

fdb-> FOR SELECT

fdb-> TO PUBLIC

fdb-> USING (username = current_user);

CREATE POLICY

 

fdb=> select relname,relrowsecurity from pg_class where relname='test_row';

relname | relrowsecurity

----------+----------------

test_row | f

(1 row)

 

fdb=> ALTER TABLE test_row ENABLE ROW LEVEL SECURITY;

ALTER TABLE

 

fdb=> select relname,relrowsecurity from pg_class where relname='test_row';

relname | relrowsecurity

----------+----------------

test_row | t

(1 row)

备注：给表 test_row 添加 policy ，限制数据库登陆用户仅允许查看当前用户的日志记录。

 

5.测试

user1用户登录

fdb=> \c fdb user1

You are now connected to database "fdb" as user "user1".

 

fdb=> select * from fdb.test_row;

id | username | log_event | create_time

----+----------+----------------+---------------------

1 | user1 | user1:aaa | 2015-07-30 14:48:49

2 | user1 | user1:aadsfdfa | 2015-07-30 14:48:54

(2 rows)

user2用户登录

fdb=> \c fdb user2

You are now connected to database "fdb" as user "user2".

 

fdb=> select * from fdb.test_row;

id | username | log_event | create_time

----+----------+----------------+---------------------

3 | user2 | user2:aadsfdfa | 2015-07-30 14:48:59

4 | user2 | user2:test | 2015-07-30 14:49:06

(2 rows)

备注：user1 用户仅能查看 username 值为'user1' 的记录，user2用户仅能查看 username值为 'user2'的记录。

实验结果：对应的用户只能查看对应的行记录，限制数据库用户的查看表数据权限。

 

二、UPSERT

INSERT ... ON CONFLICT, also known as “UPSERT”

eg1.

[postgres@cqs ~]$ psql

psql (9.5alpha2)

Type "help" for help.

postgres=# \c mydb

mydb=# create table ins_update_test(id integer primary key,name text);

CREATE TABLE

mydb=#

mydb=# insert into ins_update_test values(100,'bingbang');

INSERT 0 1

mydb=# select * from ins_update_test;

 id  |   name   

-----+----------

 100 | bingbang

(1 row)

mydb=# insert into ins_update_test values(100,'xiaobang');

ERROR:  duplicate key value violates unique constraint "ins_update_test_pkey"

DETAIL:  Key (id)=(100) already exists.

这时候如果想插入更新ins_update_test表的数据时，可以采用以下方法

mydb=# insert into ins_update_test values(100,'xiaobang') on conflict(id) do update set name=excluded.name;

INSERT 0 1

mydb=#

mydb=#select * from ins_update_test;

 id  |   name   

-----+----------

 100 | xiaobang

(1 row)

mydb=#

解决方案：结尾要加上 on on conflict(id) do update set name=excluded.name;

原因是id是主键，具有唯一性，所以也就是更新数据库表的其他字段name。

eg2.

Block-Range Indexes (BRIN)

BRIN(Block Range Index)：保存数据块的值的摘要信息，如存储某一组块里面所有记录中的最大最小值，与Exadata 的Storage Index 相似。 通过BRIN 我们可以让Index 大小指数级缩小，当然不恰当的使用也会影响性能。

mydb=# create table ins_update_test2(x integer primary key,status text);

CREATE TABLE

mydb=# insert into ins_update_test2 values(1,'old'),(3,'old'),(5,'old');

INSERT 0 3

mydb=#

mydb=# select * from ins_update_test2;

 x | status

---+--------

 1 | old

 3 | old

 5 | old

(3 rows)

mydb=# insert into ins_update_test2 select  *,'new' from generate_series(2,5) on conflict(x) do update set status='conflict';

INSERT 0 4

mydb=#

mydb=# select * from ins_update_test2;

 x |  status  

---+----------

 1 | old

 2 | new

 3 | conflict

 4 | new

 5 | conflict

(5 rows)

 

三、jsonb的相关操作

 

jsonb || jsonb (concatenate / overwrite)

The || operator allows us to combine 2 jsonb objects. If there's overlap, values are replaced on the highest level.

译：||运算符允许我们将两个jsonb对象连接起来，如果有重叠的键，值会被后者替换。

eg1.没有重复键的

mydb=# select '{"name":"cqs","age":"24"}'::jsonb || '{"town":"China"}'::jsonb;

                   ?column?                    

-----------------------------------------------

 {"age": "24", "name": "cqs", "town": "China"}

(1 row)

eg2.有重复键的

mydb=# select '{"name":"cqs","age":"24","town":"China"}'::jsonb || '{"town":"English"}'::jsonb;

                    ?column?                     

-------------------------------------------------

 {"age": "24", "name": "cqs", "town": "English"}

(1 row)

eg3.有重复键

mydb=# select '{"name":"cqs","age":"24","town":"China","friends":{"name":"ysg","age":"24"}}'::jsonb || '{"friends":{"name":"xrl"}}'::jsonb;

 

                                 ?column?                                  

---------------------------------------------------------------------------

 {"age": "24", "name": "cqs", "town": "China", "friends": {"name": "xrl"}}

(1 row)

 

jsonb - text / int (remove key / array element)

译：删除jsonb的键或数组的值

eg1.删除jsonb的键

mydb=# select '{"name":"cqs","age":"24"}'::jsonb - 'age';

    ?column?     

-----------------

 {"name": "cqs"}

(1 row)

eg2.删除数组的键值

mydb=# select '["red","green","blue"]'::jsonb - 1;

    ?column?     

-----------------

 ["red", "blue"]

(1 row)

 

jsonb #- text[] / int (remove key / array element in path)

译：删除指定路径的键值

eg1.jsonb

mydb=# select '{"name":"cqs","age":"24","town":"China","friends":{"name":"ysg","age":"24"}}'::jsonb #- '{friends,age}'::text[];              

                                 ?column?                                  

---------------------------------------------------------------------------

 {"age": "24", "name": "cqs", "town": "China", "friends": {"name": "ysg"}}

(1 row)

eg2.array

mydb=# select '{"name":"cqs","age":"24","town":"China","friends":["ysg","wjr","czm","wy"]}'::jsonb #- '{friends,1}'::text[];         

                                    ?column?                                    

--------------------------------------------------------------------------------

 {"age": "24", "name": "cqs", "town": "China", "friends": ["ysg", "czm", "wy"]}

(1 row)

 

jsonb_set() --function

构造：jsonb_set(target jsonb,path text[],new_value jsonb,create_missing boolean)

语法：

jsonb_set(

  target jsonb,   # The jsonb value you're amending.

  path text[],   # The path to the value you wish to add to or change, represented as a text array.   

  new_value jsonb,   # The new object, key : value pair or array value(s) to add to or change.  

  create_missing boolean   # An optional field that, if true (default), creates the value if the key doesn't already exist.

         # If false, the path must exist for the update to happen, or the value won't be updated.

  )

eg1.修改对应的键值（create_missing默为true）

mydb=# select jsonb_set('{"name":"cqs","contact":{"phone":"xxxxxxxxxxxx","qq":"xxxxxxxxxxxx"}}'::jsonb,'{contact,qq}','"1254325782"'::jsonb);

                                jsonb_set                                 

--------------------------------------------------------------------------

 {"name": "cqs", "contact": {"qq": "xxxxxxxxxxxx", "phone": "xxxxxxxxxxxx"}}

(1 row)

eg2.新增键值

mydb=# select jsonb_set('{"name":"cqs","contact":{"phone":"xxxxxxxxxxxx ","qq":"xxxxxxxxxxxx"}}'::jsonb,'{contact,email}','"xxxxxxxxxxxx@qq.com"'::jsonb);

                                               jsonb_set                                       

-----------------------------------------------------------------------------------------------

 {"name": "cqs", "contact": {"qq": "xxxxxxxxxxxx", "email": "xxxxxxxxxxxx@qq.com", "phone": "xxxxxxxxxxxx"}}

(1 row)

eg.新增键值时设置create_missing参数为false

mydb=# select jsonb_set('{"name":"cqs","contact":{"phone":"xxxxxxxxxxxx","qq":"xxxxxxxxxxxx"}}'::jsonb,'{contact,email}','"xxxxxxxxxxxx@qq.com"'::jsonb,false);

                                jsonb_set                                

-------------------------------------------------------------------------

 {"name": "cqs", "contact": {"qq": "xxxxxxxxxxxx", "phone": "xxxxxxxxxxxx"}}

(1 row)

eg3.数组中的处理（修改contact数组中第2个键的值）

mydb=# select jsonb_set('{"name":"cqs","contact":["xxxxxxxxxxxx","8061492521","xxxxxxxxxxxx"]}'::jsonb,'{contact,1}','"xxxxxxxxxxxx"'::jsonb);           

                                jsonb_set                                 

--------------------------------------------------------------------------

 {"name": "cqs", "contact": ["xxxxxxxxxxxx", "8061492521", "xxxxxxxxxxxx"]}

(1 row)

 

jsonb_pretty() --function

作用：格式化

eg.

mydb=# select jsonb_pretty(jsonb_set('{"name":"cqs","contact":{"phone":"xxxxxxxxxxxx","qq":"xxxxxxxxxxxx"}}'::jsonb,'{contact,email}','"xxxxxxxxxxxx@qq.com"'::jsonb,true));

             jsonb_pretty              

---------------------------------------

 {                                    +

     "name": "cqs",                   +

     "contact": {                     +

         "qq": "xxxxxxxxxxxx",           +

         "email": "xxxxxxxxxxxx@qq.com",+

         "phone": "xxxxxxxxxxxx"       +

     }                                +

 }

(1 row)