PostgreSQL DBA(102) - pgAdmin(Row Level Security)

husthxd 發表於2019-10-09

PostgreSQL提供了Row Level Security(RLS)來控制哪些行可以訪問或修改。

一、簡介
其標準的語法是:

[[email protected] ~]$ psql -d testdb
Timing is on.
Expanded display is used automatically.
psql (12.0)
Type "help" for help.
[local]:5432 [email protected]=# \help create policy
Command:     CREATE POLICY
Description: define a new row level security policy for a table
Syntax:
CREATE POLICY name ON table_name
    [ AS { PERMISSIVE | RESTRICTIVE } ]
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]
URL: https://www.postgresql.org/docs/12/sql-createpolicy.html
[local]:5432 [email protected]=#

其中,USING表示滿足using_expression表示式為T的行才能看到或操作,否則這些行將被隱藏(hidden);
WITH CHECK表示在執行INSERT/UPDATE操作時,如新資料不能通過check_expression表示式的校驗則被視為非法資料。

二、注意事項
1.RLS必須通過ALTER TABLE … ENABLE ROW LEVEL SECURITY顯式啟用;
2.如啟用RLS,那麼至少存在一個policy
建立資料表,插入資料,啟用RLS

[local]:5432 [email protected]=# drop table if exists t_rls1;
NOTICE:  table "t_rls1" does not exist, skipping
DROP TABLE
Time: 37.363 ms
[local]:5432 [email protected]=# create table t_rls1(id int,c1 int);
CREATE TABLE
Time: 137.931 ms
[local]:5432 [email protected]=# 
[local]:5432 [email protected]=# insert into t_rls1 values(1,1);
INSERT 0 1
Time: 1.820 ms
[local]:5432 [email protected]=# insert into t_rls1 values(2,2);
INSERT 0 1
Time: 0.582 ms
[local]:5432 [email protected]=# 
[local]:5432 [email protected]=# ALTER TABLE t_rls1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE
Time: 1.714 ms
[local]:5432 [email protected]=# select * from t_rls1;
 id | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

建立新的user,查詢資料表,因為不存在policy,因此沒有資料返回(但為什麼pg12這個使用者可以?後續有解釋)

Time: 23.349 ms
[local]:5432 [email protected]=# create user testuser with passwod 'test';
ERROR:  unrecognized role option "passwod"
LINE 1: create user testuser with passwod 'test';
                                  ^
Time: 1.165 ms
[local]:5432 [email protected]=# create user testuser with password 'test';
CREATE ROLE
Time: 15.721 ms
[local]:5432 [email protected]=# grant all on t_rls1 to testuser;
GRANT
Time: 8.125 ms
[local]:5432 [email protected]=#

3.同一個命令,同一種型別,多個policys之間是OR的關係,即某行滿足其中一個policy就可以返回(如select命令);同一個命令不同的命令型別,則多個policys之間的關係是AND,必須所有policys都滿足才能返回(如update中存在where子句時)。
4.只有表的owner可以建立policys
5.超級使用者或者具有BYPASSRLS許可權的使用者會跳過RLS檢查。這解釋先前為什麼policy沒有,但pg12查詢有資料返回但testuser使用者沒有。

[local]:5432 [email protected]=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 pg12      | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |

6.資料表的owner使用者預設會跳過RLS檢查

[local]:5432 [email protected]=> create table t_rls2(id int,c1 int);
CREATE TABLE
Time: 10.256 ms
[local]:5432 [email protected]=> 
[local]:5432 [email protected]=> insert into t_rls2 values(1,1);
INSERT 0 1
Time: 3.422 ms
[local]:5432 [email protected]=> insert into t_rls2 values(2,2);
INSERT 0 1
Time: 2.580 ms
[local]:5432 [email protected]=> 
[local]:5432 [email protected]=> ALTER TABLE t_rls2 ENABLE ROW LEVEL SECURITY;
ALTER TABLE
Time: 2.124 ms
[local]:5432 [email protected]=> select * from t_rls2;
 id | c1 
----+----
  1 |  1
  2 |  2
(2 rows)
Time: 1.127 ms
[local]:5432 [email protected]=> \d t_rls2
               Table "public.t_rls2"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           |          | 
 c1     | integer |           |          | 
Policies (row security enabled): (none)

testuser1&testuser2均為普通使用者,但testuser1查詢資料有返回,但testuser2沒有

[[email protected] ~]$ psql -d testdb -U testuser2
Timing is on.
Expanded display is used automatically.
psql (12.0)
Type "help" for help.
[local]:5432 [email protected]=> select * from t_rls2;
 id | c1 
----+----
(0 rows)
Time: 3.808 ms
[local]:5432 [email protected]=>

可通過ALTER TABLE … FORCE ROW LEVEL SECURITY命令強制啟用

[local]:5432 [email protected]=> ALTER TABLE t_rls2 FORCE ROW LEVEL SECURITY;
ALTER TABLE
Time: 1.967 ms
[local]:5432 [email protected]=> select * from t_rls2;
 id | c1 
----+----
(0 rows)
Time: 2.369 ms

具體的應用案例可詳見參考資料中的連結。

三、參考資料
PG Conf of US 2019 - Row Level Security
Using “Row Level Security” to make large companies more secure

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6906/viewspace-2659195/,如需轉載,請註明出處,否則將追究法律責任。


相關文章

PostgreSQL

PostgreSQL DBA(96) - System Conf(client_min_messages)

PostgreSQL提供了引數client_min_messages用以控制返回給客戶的訊息級別,不同的級別返回的訊息大小不同,預設為NOTICE。在通常情況下,如果客戶端執行的SQL語句不頻繁的話,
Linux|PostgreSQL

PostgreSQL DBA(100) - Linux(Huge Pages)

PostgreSQL通過引數huge_pages設定是否啟用大頁,利用常駐記憶體用以提升效能,本節簡單介紹了Linux中Huge Pages的相關設定。Check if CPU supports Hu
PostgreSQL

PostgreSQL DBA(99) - Develop(generate random string)

為了驗證TOAST的相關功能,通過lpad、rpad等方式由於重複率太高看不出效果,因此需要生成隨機字串來填充資料。生成隨機字串的樣例函式CREATE OR REPLACE FUNCTION sf_g
PostgreSQL

PostgreSQL DBA(94) - PG 12 Improving Partition(Select)

PG 10在分割槽表上執行查詢時,會逐個檢查每個分割槽的約束來看是否需要,如果分割槽很多在計劃階段會有較大的效能損失。PG 11通過”partition pruning“演算法來快速的標識匹配的分割槽
Oracle|PostgreSQL

Oracle vs PostgreSQL Develop(18) - Boolean

在Oracle和PG中都可以使用int型別模擬布林型別,但通過JDBC介面(JDBC驅動,Oracle為11.2.0.4,PG為9.3)獲取出來的值卻不一致,這一點需要注意。測試指令碼drop tab
PostgreSQL

PostgreSQL DBA(97) - System Conf(huge_pages)

PostgreSQL提供了引數huge_pages用以使用OS的huge pages,預設為try,即可用就用,用不了就不用。使用HUGE PAGE其中一個原因是因為HUGE PAGE是常駐記憶體的,
PostgreSQL

PostgreSQL掃描方法綜述

PostgreSQL掃描方法綜述 關係型資料庫都需要產生一個最佳的執行計劃從而在查詢時耗費的時間和資源最少。通常情況下,所有的資料庫都會產生一個以樹形式的執行計劃:計劃樹的葉子節點被稱為表掃描節點。查
PostgreSQL

PostgreSQL DBA(98) - PG 12 Faster float conversion to text

PostgreSQL 12之前的版本浮點數是通過四捨五入到特定的數字來輸出的,由引數extra_float_digits控制。這種方法對於查詢有實數列或雙精度列的大表時的輸出速度較慢。在PG12中,十
Linux|PostgreSQL

PostgreSQL 原始碼解讀(229)- Linux Kernel(程式虛擬記憶體#3)

PostgreSQL使用程式架構,每個連線對應一個後臺程式,為了更好的理解這種架構,有必要深入理解程式的相關知識.本節主要介紹了Linux下的程式虛擬記憶體結構,並以使用C語言樣例程式進行說明.一、m
PostgreSQL

PostgreSQL DBA(95) - PG 12 Partition(out of shared memory)

PostgreSQL 12 Beta3,建立包含8192個子分割槽的分割槽表,執行查詢語句,在分割槽鍵上排序,出錯。資料庫版本:[local]:5432 [email protected]=# select ve