PostgreSQL9.5新特性之行級安全性及其應用級解決方案

PostgreSQL9.5新特性之行級安全性及其應用級解決方案

PostgreSQL在9.5版本中，新增了行級安全性策略特性(RLS)，該特性在資料安全體系提供了在傳統的授權安全體系之外更細粒度的控制。對應的，Oracle在很久之前提供了類似的VPD（Virtual Private Database）技術，該技術在Oracle10g時代就已經成熟。在SQL Server 2016中，也提供了類似的行級安全特性。如今PostgreSQL在本次發布的大版本中也提供了該新特性，本文將對該技術做詳細地介紹，然後提出對應的應用級解決方案。

PostgreSQL 9.5 發布下載，帶來 UPSERT 等新特性

1.行級安全性策略1.1.行級安全性原則概述

在之前版本的資料安全技術中，是通過GRANT/REVOKE指令實現的，這兩個指令提供了對象級的安全限制，針對錶，還提供了列級的安全限制。但是很多情境中，往往希望不同的使用者訪問同一個表能看到不同的資料，也就是行級安全的需求，這個特性在9.5中提供了支援。該版本中，在正常的SQL查詢和資料更新之外，可以附加額外的行級安全性原則，可以限制查詢返回以及資料操作的結果。預設的話，表沒有任何安全性原則限制。

所有對資料的操作，包括資料查詢和資料更新，都受到策略的限制，如果沒有配置安全性原則，所有的資料查詢和更新都會禁止，但是對全表進行操作的命令，比如TRUNCATE和REFERENCES不受影響。

行級安全性原則可以加在命令上，也可以加在角色上，也可以兩者都加。命令可以是ALL, SELECT, INSERT, UPDATE和DELETE,同一個策略也可以賦予多個角色。但是表的所有者，超級使用者(postgres)以及加上了BYPASSRLS屬性的角色不受安全性的限制。如果應用想忽略行級安全性機制的限制，也可以將row_security設定為off。

啟用行級安全的表如何對資料的查詢和更新進行控制呢？這是通過一個返回布爾值的運算式實現的。這個運算式優先於查詢條件和查詢內的函數，這個規則唯一的例外是leakproof函數。這裡需要兩個運算式，分別對資料的查詢和更新進行獨立地控制。

只有所有者才具有啟用/禁用行級安全性，給表添加策略的許可權。

CREATE POLICY，ALTER POLICY，DROP POLICY命令分別用於策略的建立、修改和刪除，ALTER TABLE可以用於行級安全性的啟用/禁用。

每個策略都有一個名字，每個表可以定義多個策略，因為策略是針對錶的，所以表內的多個策略名字必須唯一，但是不同的表可以有同名的策略，當表有多個策略時，多個策略之間是OR的關係。

1.2.相關樣本啟用行級安全性

要開啟表的行級安全性，需要使用ALTER TABLE命令，如下：

CREATE TABLE user (id text, name text, email text,manager text);ALTER TABLE user ENABLE ROW LEVEL SECURITY;

ALTER TABLE也可以用于禁用行級安全性，但是禁用之後並不刪除相應的策略。

建立策略

如果希望只有管理者才能看到對應的使用者：

CREATE POLICY user_manager ON user TO managers USING (manager = current_user);

如果未指定角色，那麼預設為PUBLIC，即針對系統內的所有角色，如果希望系統內每個使用者只能看到自己的資料，只需要建立一個下面的簡單策略即可：

CREATE POLICY user_policy ON users USING (id = current_user);

如果要限制對資料的更新操作，可以添加WITH CHECK語句，下面的策略允許所有人看到所有的資料，但是只能修改自己的資料：

CREATE POLICY user_policy ON user USING (true) WITH CHECK (id = current_user);

關於建立策略的更詳細的說明，可以參照CREATE POLICY命令的手冊。

2.應用級解決方案

從上述可以看出，PostgreSQL的行級安全性是針對登入到資料庫的各個具有不同許可權的使用者的，這對於資料庫的設計者來說沒有問題，因為他們工作於資料庫層。而在實際情境中我們是工作於應用程式層的，我們希望對應用中的同一條SQL語句，能進行不同的許可權控制，這裡面就涉及三個問題：

1. 應用是通過一個共用的賬戶登入資料庫的。
2. 應用的規則可能很複雜。
3. 還有一個就是可能需要很多的動態參數。

下面就針對上述三個問題，拿出應用級的解決方案。

2.1.三級賬戶體系

在現實中，應用的開發為了方便，通常的做法是，先用超級使用者(postgres)建立一個登入使用者角色，然後用新建立的登入使用者角色登入，再建立和登入使用者名稱同名的資料庫，然後應用也會用這個登入使用者串連資料庫。

因為行級安全性對於表的所有者以及超級使用者等無效，因此原來的開發模型就不再適用，應用就需要通過單獨的賬戶進行登入，這樣就形成了三級賬戶體系：

1. 超級使用者(postgres)：作為資料庫系統的管理者，擁有整個資料庫系統的所有許可權；
2. 資料庫擁有者：該賬戶作為資料庫的管理者，擁有整個資料庫的所有許可權；
3. 應用所有者：該賬戶預設只具有登入資料庫的許可權，其他的操作都需要相關授權；

應用通過應用所有者賬戶串連資料庫，比如要對某個資料庫的public模式內的所有表進行增刪改查操作，則需要進行如下的授權：

假定登入使用者為u1_public：

GRANT ALL ON ALL TABLES IN SCHEMA public TO u1_public;

其他對象的許可權授權也同理。

2.2.策略函數

不管是USING運算式還是WITH CHECK運算式，都要求運算式的傳回值是布爾值，但是對於運算式本身沒有限制，因此對於一些複雜的情境，是可以寫策略函數的，比如：

ALTER POLICY user_policy ON user USING(p());

這個是合法的，只要p函數的傳回值是布爾就可以。

這個p函數內部顯然可以寫複雜的邏輯，但是這個p函數暫時看不能傳遞參數，而且該函數只能返回布爾值也對該函數的發揮空間有了限制，不如Oracle的策略函數傳回值是一個字串的WHERE子句靈活，因為無法用於一些動態情境中。

2.3.動態參數

最後一個問題，就是動態參數，就是具體的策略運算式或者策略函數依賴於應用操作者本身的一些具體的、事務級的參數，比如使用者的id，使用者所屬的組織機構id等，甚至一些使用者在介面上進行選擇或者輸入的資料。這個問題在Oracle中是通過資料庫的內容物件實現的，而在PostgreSQL中沒有這樣的對象。那麼怎麼辦呢？

PostgreSQL的強大之處就在這裡！這裡我們要引入兩個概念，一個是定製選項，一個是系統管理函數中的配置設定函數。

1.定製選項：

任何資料庫，也包括其他的很多複雜軟體，都有很多的配置參數，PostgreSQL也一樣。在PostgreSQL中，有很多的內建參數，定義在postgresql.conf 中。

我們知道，PostgreSQL支援擴充，這些擴充可能也需要一些參數，那麼在PostgreSQL中如何定義這些參數呢？他是通過定製選項提供這個功能。

定製選項由兩部分組成，首先是副檔名，然後是一個.，然後是屬性名稱，比如rls.userid。因為定製選項可能在擴充還沒有載入之前就需要進行設定，因此PostgreSQL允許這些變數以預留位置的形式存在直到擴充模組載入之前都不起任何作用，當擴充模組載入後才會賦予這些變數實際的含義。

瞭解了這一點，我們發現可以利用這個特性來進行動態參數的傳遞。

另外要提示一點，在PostgreSQL9.2版本之前，這個定製選項中的副檔名需要在postgres.conf檔案中進行定義，比如custom_variable_classes=rls，而在9.2版本中取消了這一限制，這就給我們提供了更大的方便。

2.配置設定函數：

知道了PostgreSQL支援動態參數而且知道了動態參數的定義規則之後，下一步就需要知道如何對這些參數進行事務級的賦值/取值了，這時我們就需要利用配置設定函數了。

PostgreSQL中對於參數的設定，提供了三種方式，一個是SET命令，一個是對於內建參數的ALTER SYSTEM命令，再一個就是配置設定函數current_setting和set_config，而這兩個函數正是我們需要的，我們看下這兩個函數的定義： 

名稱	傳回值	描述
current_setting(setting_name)	text	擷取設定的當前值
set_config(setting_name,new_value,is_local)	text	設定參數然後返回新值

這裡需要特別關注的就是set_config函數的第三個參數is_local，如果該參數為true，那麼該參數只在當前事務有效，如果為false，則對當前會話有效。在SET命令中，也有和這個相對應的LOCAL/SESSION參數。

瞭解了這兩個特性之後，我們就有了對應的應用程式層解決方案，需要兩個步驟：

1. 定義並傳遞參數：

   可以在事務開啟之後，進行相應的SQL操作之前進行，比如調用如下的SQL：

   SELECT set_config('rls.userid', 'xiaoming', true); 

2. 策略運算式或者策略函數中擷取參數：

   假定對於前述的user表，我們希望應用中登入的使用者只能查詢/更新自己的資料，那麼對應的策略如下：

   CREATE POLICY user_policy ON user USING (id =current_setting('rls.userid')) WITH CHECK (id=current_setting('rls.userid'));

------------------------------------華麗麗的分割線------------------------------------

Ubuntu Server 14.04 下安裝 PostgreSQL 9.3.5 資料庫 

CentOS 6.3環境下yum安裝PostgreSQL 9.3

PostgreSQL緩衝詳述

Windows平台編譯 PostgreSQL

Ubuntu下LAPP（Linux+Apache+PostgreSQL+PHP)環境的配置與安裝

Ubuntu上的phppgAdmin安裝及配置

CentOS平台下安裝PostgreSQL9.3

PostgreSQL配置Streaming Replication叢集

如何在CentOS 7/6.5/6.4 下安裝PostgreSQL 9.3 與 phpPgAdmin 

------------------------------------華麗麗的分割線------------------------------------

PostgreSQL 的詳細介紹：請點這裡
PostgreSQL 的：請點這裡

本文永久更新連結地址：