36. PostgreSQL 安全性¶
PostgreSQL 具有豐富且彈性的權限系統，能夠將特定的權限分配給特定的角色，並授予使用者一個或多個這些角色的權限。
此外，PostgreSQL 伺服器可以使用多種不同的系統來驗證使用者。這表示資料庫可以使用與其他架構元件相同的驗證基礎架構，簡化密碼管理。
36.1. 使用者與角色¶
在本章中，我們將建立兩個有用的生產使用者
一個用於發布應用程式的唯讀使用者。
一個用於開發人員在建構軟體或分析資料時使用的讀寫使用者。
我們將建立具有正確權限的兩個角色，然後建立兩個使用者並將他們加入適當的角色，而不是建立使用者並授予他們必要的權限。這樣，當我們建立其他使用者時，就可以輕鬆重複使用這些角色。
36.1.1. 建立角色¶
角色是使用者，使用者也是角色。唯一的區別在於，「使用者」可以說是具有「登入」權限的角色。
因此，從功能上來說，下面的兩個 SQL 陳述式是相同的，它們都建立一個「具有登入權限的角色」，也就是說，一個「使用者」。
CREATE ROLE mrbean LOGIN;
CREATE USER mrbean;
36.1.2. 唯讀使用者¶
我們的唯讀使用者將供網頁應用程式用於查詢 nyc_streets 資料表。
該應用程式將具有對 nyc_streets 資料表的特定存取權，但會從 postgis_reader 角色繼承 PostGIS 操作所需的系統存取權。
-- A user account for the web app
CREATE USER app1;
-- Web app needs access to specific data tables
GRANT SELECT ON nyc_streets TO app1;
-- A generic role for access to PostGIS functionality
CREATE ROLE postgis_reader INHERIT;
-- Give that role to the web app
GRANT postgis_reader TO app1;
現在，當我們以 app1 登入時，我們可以從 nyc_streets 資料表中選取列。但是，我們無法執行 ST_Transform 呼叫！為什麼不能？
-- This works!
SELECT * FROM nyc_streets LIMIT 1;
-- This doesn't work!
SELECT ST_AsText(ST_Transform(geom, 4326))
FROM nyc_streets LIMIT 1;
ERROR: permission denied for relation spatial_ref_sys
CONTEXT: SQL statement "SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1"
答案包含在錯誤陳述式中。雖然我們的 app1 使用者可以正常檢視 nyc_streets 資料表的內容，但無法檢視 spatial_ref_sys 的內容，因此呼叫 ST_Transform 失敗。
因此，我們還需要授予 postgis_reader 角色對所有 PostGIS 元數據資料表的讀取權限
GRANT SELECT ON geometry_columns TO postgis_reader;
GRANT SELECT ON geography_columns TO postgis_reader;
GRANT SELECT ON spatial_ref_sys TO postgis_reader;
現在我們有一個很好的通用 postgis_reader 角色，我們可以將其應用於任何需要從 PostGIS 資料表讀取的使用者。
-- This works now!
SELECT ST_AsText(ST_Transform(geom, 4326))
FROM nyc_streets LIMIT 1;
36.1.3. 讀寫使用者¶
我們需要考慮兩種讀寫情境
需要寫入現有資料表的網頁應用程式和其他應用程式。
需要建立新的資料表和幾何欄位作為其工作一部分的開發人員或分析師。
對於需要寫入資料表權限的網頁應用程式，我們只需要授予資料表本身額外的權限，並且我們可以繼續使用 postgis_reader 角色。
-- Add insert/update/delete abilities to our web application
GRANT INSERT,UPDATE,DELETE ON nyc_streets TO app1;
例如，讀寫 WFS 服務將需要這些權限。
對於開發人員和分析師，主要 PostGIS 元數據資料表需要更多的存取權。我們將需要一個可以編輯 PostGIS 元數據資料表的 postgis_writer 角色！
-- Make a postgis writer role
CREATE ROLE postgis_writer;
-- Start by giving it the postgis_reader powers
GRANT postgis_reader TO postgis_writer;
-- Add insert/update/delete powers for the PostGIS tables
GRANT INSERT,UPDATE,DELETE ON spatial_ref_sys TO postgis_writer;
-- Make app1 a PostGIS writer to see if it works!
GRANT postgis_writer TO app1;
現在，嘗試以 app1 使用者身分執行上述資料表建立 SQL，看看結果如何！
36.2. 加密¶
PostgreSQL 提供了許多加密工具，其中許多是可選的，有些預設為啟用。
預設情況下，所有密碼都經過 MD5 加密。用戶端/伺服器交握會雙重加密 MD5 密碼，以防止任何攔截密碼的人員重複使用雜湊。
SSL 連線在用戶端和伺服器之間可選用，以加密所有資料和登入資訊。使用 SSL 連線時，也可以使用 SSL 憑證驗證。
可以使用 pgcrypto 模組加密資料庫內的欄位，該模組包含雜湊演算法、直接密碼（blowfish、aes）以及公鑰和對稱 PGP 加密。
36.2.1. SSL 連線¶
為了使用 SSL 連線，您的用戶端和伺服器都必須支援 SSL。
首先，關閉 PostgreSQL，因為啟用 SSL 需要重新啟動。
接下來，我們取得或產生 SSL 憑證和金鑰。憑證不得有密碼，否則資料庫伺服器將無法啟動。您可以如下產生自我簽署的金鑰
# Create a new certificate, filling out the certification info as prompted openssl req -new -text -out server.req # Strip the passphrase from the certificate openssl rsa -in privkey.pem -out server.key # Convert the certificate into a self-signed cert openssl req -x509 -in server.req -text -key server.key -out server.crt # Set the permission of the key to private read/write chmod og-rwx server.key
將
server.crt和server.key複製到 PostgreSQL 資料目錄中。透過將「ssl」參數設定為「on」，在
postgresql.conf檔案中啟用 SSL 支援。現在重新啟動 PostgreSQL；伺服器已準備好進行 SSL 操作。
伺服器啟用 SSL 後，建立加密連線很容易。在 PgAdmin 中，建立新的伺服器連線（檔案 > 新增伺服器…），並將 SSL 參數設定為「require」。
使用新的連線連線後，您可以在其屬性中看到它正在使用 SSL 連線。
由於預設 SSL 連線模式為「prefer」，因此您在連線時甚至不需要指定 SSL 首選項。使用命令列 psql 終端機的連線將會自動選擇 SSL 選項並預設使用它
psql (8.4.9)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
postgres=#
請注意終端機如何報告連線的 SSL 狀態。
36.2.2. 資料加密¶
pgcrypto 模組具有大量的加密選項，因此我們僅示範最簡單的使用案例：使用對稱密碼加密資料欄。
首先，透過在 PgAdmin 或 psql 中載入 contrib SQL 檔案來啟用 pgcrypto。
pgsql/8.4/share/postgresql/contrib/pgcrypto.sql
然後，測試加密功能。
-- encrypt a string using blowfish (bf) SELECT encrypt('this is a test phrase', 'mykey', 'bf');
並確保它也是可逆的！
-- round-trip a string using blowfish (bf) SELECT decrypt(encrypt('this is a test phrase', 'mykey', 'bf'), 'mykey', 'bf');
36.3. 驗證¶
PostgreSQL 支援許多不同的驗證方法，以便輕鬆整合到現有的企業架構中。對於生產用途，通常使用以下方法
密碼是基本系統，其中密碼由資料庫儲存，並使用 MD5 加密。
Kerberos 是一種標準的企業驗證方法，PostgreSQL 中的 GSSAPI 和 SSPI 方案都使用它。使用 SSPI，PostgreSQL 可以針對 Windows 伺服器進行驗證。
LDAP 是另一種常見的企業驗證方法。與大多數 Linux 發行版本綁定的 OpenLDAP 伺服器提供了 LDAP 的開放原始碼實作。
如果您希望所有用戶端連線都透過 SSL 進行，並且能夠管理金鑰的散發，則憑證驗證是一個選項。
驗證方法由 pg_hba.conf 檔案控制。檔案名稱中的「HBA」代表「主機式存取」，因為除了允許您指定每個資料庫要使用的驗證方法外，還允許您使用網路位址限制主機存取。
以下是 pg_hba.conf 檔案的範例
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# remote connections for nyc database only
host nyc all 192.168.1.0/2 ldap
該檔案由五欄組成
TYPE 決定存取的種類，對於來自同一伺服器的連線為「local」，對於遠端連線則為「host」。
DATABASE 指定組態行所指的資料庫，或所有資料庫的「all」。
USER 指定該行所指的使用者，或所有使用者的「all」。
CIDR-ADDRESS 指定使用網路/網路遮罩語法的遠端連線的網路限制。
METHOD 指定要使用的驗證協定。「trust」完全略過驗證，並簡單地接受任何有效的的使用者名稱，而無需進行挑戰。
通常會信任本機連線，因為對伺服器本身的存取通常是特權的。安裝 PostgreSQL 時，預設會停用遠端連線：如果您想從遠端電腦連線，則必須新增一個項目。
上面範例中 nyc 的行是遠端存取項目的範例。nyc 範例只允許對本機網路上的電腦（在本例中為 192.168.1. 網路）以及只對 nyc 資料庫進行 LDAP 驗證存取。根據您的網路安全性，您將在生產設定中使用或多或少嚴格版本的這些規則。
