天天看點
傳回

PostgreSQL管理資料庫安全

随着網絡技術的進步和網絡安全事件的頻現，資料安全始終是個人和企業需要防護的重要方面。該章節介紹一下在PostgreSQL中如何對資料庫進行安全層面的加強。

前言

随着技術的進步和網絡技術的發展，安全是任何行業都需要面對的挑戰，如前兩年爆發的WnCry電腦病毒，資料資訊被加密。是以，資料安全始終需要預防。正因為資料庫作為存儲資料最重要的載體，是以除了硬體實體層面的資料安全防護之外，也需要在軟體層面加強資料安全防護。接下來介紹一下PostgreSQL中在資料安全層面對資料庫做防護。

目錄

一、用戶端接入認證

二、管理使用者及安全

三、設定密碼安全政策

四、行級安全(rls)政策

PostgreSQL用戶端接入認證的主要方式是通過pg_hba.conf檔案來進行配置。

1.1 配置用戶端接入認證

用戶端接入認證背景

如果主機需要遠端連接配接資料庫，必須在資料庫系統的配置檔案中增加此主機的資訊，并且進行用戶端接入認證。配置檔案（預設名稱為pg_hba.conf）存放在資料庫的資料目錄裡。hba（host-based authentication）表示是基于主機的認證。

  • PostgreSQL支援如下三種認證方式，這三種方式都需要配置pg_hba.conf檔案。
  1. 基于主機的認證：伺服器端根據用戶端的IP位址、使用者名及要通路的資料庫來檢視配置檔案進而判斷使用者是否通過認證。
  2. 密碼認證：包括遠端連接配接的加密密碼認證和本地連接配接的非加密密碼認證。
  3. SSL加密：使用openssl（通用協定平台）提供伺服器端和用戶端安全連接配接的環境。
  • pg_hba.conf檔案的格式是一行寫一條資訊，表示一個認證規則，空白和注釋（以#開頭）被忽略。
  • 每個認證規則是由若幹空格和/，空格和制表符分隔的字段組成。如果字段用引号包圍，則它可以包含空白。一條記錄不能跨行存在。

pg_hba.conf檔案中的每條記錄可以是下面四種格式之一

# local      DATABASE  USER  METHOD  [OPTIONS]
# host      DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

因為認證時系統是為每個連接配接請求順序檢查pg_hba.conf裡的記錄的，是以這些記錄的順序非常關鍵。

  • 在配置 pg_hba.conf 檔案時，請依據通訊需求按照格式内容從上至下配置記錄，優先級高的需求需要配置在前面。
  • 若服務端pg_hba.conf檔案配置了hostssl，postgresql.conf中必須開啟SSL認證模式。

是以對于認證規則的配置建議如下：

  • 靠前的記錄有比較嚴格的連接配接參數和比較弱的認證方法。
  • 靠後的記錄有比較寬松的連接配接參數和比較強的認證方法。

1.2 pg_hba.conf 配置檔案參數說明及認證方式

參數說明

參數名稱 描述 取值範圍
local

表示這條記錄隻接受通過Unix域套接字進行的連接配接。沒有這種類型的記錄，就不允許Unix域套接字的連接配接。

隻有在從伺服器本機使用gsql連接配接且在不指定-U參數的情況下，才是通過Unix域套接字連接配接。

 -
host 表示這條記錄既接受一個普通的TCP/IP套接字連接配接，也接受一個經過SSL加密的TCP/IP套接字連接配接。
hostssl 表示這條記錄隻接受一個經過SSL加密的TCP/IP套接字連接配接。 用SSL進行安全的連接配接，需要配置申請數字證書并配置相關參數。
hostnossl 表示這條記錄隻接受一個普通的TCP/IP套接字連接配接。
DATABASE 聲明記錄所比對且允許通路的資料庫。
  • all：表示該記錄比對所有資料庫。
  • sameuser：表示如果請求通路的資料庫和請求的使用者同名，則比對。
  • samerole：表示請求的使用者必須是與資料庫同名角色中的成員。
  • samegroup：與samerole作用完全一緻，表示請求的使用者必須是與資料庫同名角色中的成員。
  • 一個包含資料庫名的檔案或者檔案中的資料庫清單：檔案可以通過在檔案名前面加字首@來聲明。檔案中的資料庫清單以逗号或者換行符分隔。
  • 特定的資料庫名稱或者用逗号分隔的資料庫清單。

說明：

值replication表示如果請求一個複制連結，則比對，但複制連結不表示任何特定的資料庫。如需使用名為replication的資料庫，需在database列使用記錄 replication  作為資料庫名。
USER 聲明記錄所比對且允許通路的資料庫使用者。
  • all：表明該記錄比對所有使用者。
  • +使用者角色：表示比對任何直接或者間接屬于這個角色的成員。
+表示字首符号。
  • 一個包含使用者名的檔案或者檔案中的使用者清單：檔案可以通過在檔案名前面加字首@來聲明。檔案中的使用者清單以逗号或者換行符分隔。
特定的資料庫使用者名或者用逗号分隔的使用者清單。
ADDRESS 指定與記錄比對且允許通路的IP位址範圍。 支援IPv4和IPv6，可以使用如下兩種形式來表示：
  • IP位址/掩碼長度。例如，10.10.20.0/24
  • IP位址子網路遮罩。例如，10.10.20.0 255.255.255.0
以IPv4格式給出的IP位址會比對那些擁有對應位址的IPv6連接配接，比如127.0.0.1将比對IPv6位址 ::ffff:127.0.0.1
METHOD 聲明連接配接時使用的認證方法。 支援如下幾種認證方式
  • trust
  • reject
  • md5
  • scram-sha-256
  • cert
  • peer | ident
  • ldap
  • password
  • gss
  • gssapi
  • radius

認證方式

說明

采用這種認證模式時，使用psql且不指定-U參數的連接配接，此時不需要密碼。

trust認證對于單使用者工作站的本地連接配接是非常合适和友善的，通常不适用于多使用者環境。如果想使用這種認證方法，可利用檔案系統權限限制對伺服器的Unix域套接字檔案的通路。要使用這種限制有兩個方法：

  • 設定參數unix_socket_permissions和unix_socket_group。
  • 設定參數unix_socket_directory，将Unix域套接字檔案放在一個經過恰當限制的目錄裡。

須知：

設定檔案系統權限隻能Unix域套接字連接配接，它不會限制本地TCP/IP連接配接。
無條件地拒絕連接配接。常用于過濾某些主機。

要求用戶端提供一個md5加密的密碼進行認證。

不推薦使用md5認證，因為md5為不安全的加密算法，存在網絡安全風險。
md5的更新版本，要求用戶端提供一個sha256算法加密的密碼進行認證，該密碼在傳送過程中結合salt（伺服器發送給用戶端的随機數）的單向sha256加密，增強了安全性。(推薦)

用戶端證書認證模式，此模式需進行SSL連接配接配置且需要用戶端提供有效的SSL證書，不需要提供使用者密碼。

該認證方式隻支援hostssl類型的規則。
使用明文密碼

1.3 使用SSL進行安全的TCP/IP連接配接

從CA認證中心申請到正式的伺服器、用戶端的證書和密鑰。（假設伺服器的私鑰為server.key，證書為server.crt，用戶端的私鑰為client.key，證書為client.crt，CA根證書名稱為cacert.pem。）此處以OPENSSL生成的認證為基礎:

1）部署CA環境

登陸postgres使用者執行

[postgres@PGServer2 ~]$ mkdir -p security

拷貝openssl.cnf到security目錄下

[postgres@PGServer2 ~]$ cp /etc/pki/tls/openssl.cnf ~/security/

建立CA環境并授權private為777權限

[postgres@PGServer2 ~]$ mkdir -p security/CA/{certs,private}
[postgres@PGServer2 ~]$ chmod 777 security/CA/private/

驗證CA環境目錄

[postgres@PGServer2 ~]$ tree security/
security/
├── CA
│   ├── certs
│   └── private
└── openssl.cnf

建立serial檔案，并寫入01

[postgres@PGServer2 ~]$ echo '01' > security/CA/serial

建立index.txt索引檔案

[postgres@PGServer2 ~]$ touch security/CA/index.txt

修改openssl.cnf檔案中的參數

[postgres@PGServer2 ~]$ vi security/openssl.cnf
[postgres@PGServer2 ~]$ cat security/openssl.cnf  | egrep "security|default_md|new_certs_dir" | awk '{print $1,$2,$3}'
dir = /home/postgres/security/CA
new_certs_dir = $dir/certs
default_md = sha256

2）生成根私鑰

生成2048位的CA私鑰：輸入密碼test

[postgres@PGServer2 ~]$ openssl  genrsa -aes256 -out security/CA/private/cakey.pem 2048
Generating RSA private key, 2048 bit long modulus
.+++
....+++
e is 65537 (0x10001)
Enter pass phrase for security/CA/private/cakey.pem:
Verifying - Enter pass phrase for security/CA/private/cakey.pem:

3）生成根證書請求檔案

根證書檔案名稱為server.req

輸入cakey.pem的密碼test。

輸入國家名稱:CN

輸入省份:Jiangsu

輸入城市:NanJing

輸入組織名稱:gs

其中有些可以省略不填

[postgres@PGServer2 ~]$ openssl req -config security/openssl.cnf -new -key security/CA/private/cakey.pem -out security/CA/careq.pem
Enter pass phrase for security/CA/private/cakey.pem:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Jiangsu
Locality Name (eg, city) [Default City]:Nanjing
Organization Name (eg, company) [Default Company Ltd]:gs
Organizational Unit Name (eg, section) []:gs
Common Name (eg, your name or your server's hostname) []:shaohua
Email Address []: 

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:Postgres@DB
An optional company name []:
[postgres@PGServer2 ~]$

4）生成自簽名根證書

使用openssl.cnf中的配置

輸入cakey.pem的密碼，

檢查輸出的請求與簽名是否比對

[postgres@PGServer2 ~]$ openssl ca -config security/openssl.cnf -out security/CA/cacert.pem -keyfile security/CA/private/cakey.pem  -selfsign -infiles security/CA/careq.pem
Using configuration from security/openssl.cnf
Enter pass phrase for security/CA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: Mar 30 07:16:21 2020 GMT
            Not After : Mar 30 07:16:21 2021 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Jiangsu
            organizationName          = gs
            organizationalUnitName    = gs
            commonName                = shaohua
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                B2:5E:02:8B:7E:8C:19:56:D3:00:17:71:9C:BF:B5:DA:33:C3:21:4F
            X509v3 Authority Key Identifier: 
                keyid:B2:5E:02:8B:7E:8C:19:56:D3:00:17:71:9C:BF:B5:DA:33:C3:21:4F

Certificate is to be certified until Mar 30 07:16:21 2021 GMT (365 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

已下發名為cacert.pem的CA根證書

5）生成伺服器證書私鑰

[postgres@PGServer2 security]$ openssl genrsa -aes256 -out server.key 2048
Generating RSA private key, 2048 bit long modulus
..................................................................................................................................+++
.......................................................+++
e is 65537 (0x10001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:

6）生成伺服器證書請求檔案

輸入server.key的密碼

輸入系統要求的一些資訊

其中一些字段可以不填，確定和建立CA時的内容一緻

[postgres@PGServer2 security]$ openssl req -config openssl.cnf -new -key server.key -out server.req
Enter pass phrase for server.key:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:Jiangsu
Locality Name (eg, city) [Default City]:Nanjing
Organization Name (eg, company) [Default Company Ltd]:gs
Organizational Unit Name (eg, section) []:gs
Common Name (eg, your name or your server's hostname) []:shaohua
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:Postgres@DB
An optional company name []:

7）生成伺服器證書

修改CA/index.txt.attr中的屬性為no

[postgres@PGServer2 security]$ vi CA/index.txt.attr 
[postgres@PGServer2 security]$ cat CA/index.txt.attr 
unique_subject = no

下發生成的伺服器證書請求檔案，下發成功後，會生成一個正式的伺服器證書server.crt

使用openssl.cnf中的配置，輸入cakey.pem中的密碼

檢查請求與簽名是否比對

[postgres@PGServer2 security]$ openssl ca -config openssl.cnf -in server.req -out server.crt -days 3650 -md sha256
Using configuration from openssl.cnf
Enter pass phrase for /home/postgres/security/CA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 2 (0x2)
        Validity
            Not Before: Mar 30 07:26:57 2020 GMT
            Not After : Mar 28 07:26:57 2030 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = Jiangsu
            organizationName          = gs
            organizationalUnitName    = gs
            commonName                = shaohua
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                64:96:2B:B7:1E:CC:DD:22:D9:0D:07:79:A7:22:FC:23:FB:66:86:FC
            X509v3 Authority Key Identifier: 
                keyid:B2:5E:02:8B:7E:8C:19:56:D3:00:17:71:9C:BF:B5:DA:33:C3:21:4F

Certificate is to be certified until Mar 28 07:26:57 2030 GMT (3650 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

1.4     PostgreSQL伺服器使用ssl認證連接配接 

1）啟動PostgreSQL伺服器

将ssl認證功能啟用

[postgres@PGServer2 ~]$ pg_ctl  start -D $PGDATA -l /tmp/logfile
waiting for server to start.... done
server started
[postgres@PGServer2 security]$ psql
psql (12.2)
Type "help" for help.

postgres=# show ssl;
 ssl 
-----
 off
(1 row)

postgres=# alter system set ssl = on;
ALTER SYSTEM

2）配置server.key和server.crt檔案的位置

[postgres@PGServer2 ~]$ cat $PGDATA/postgresql.conf  | egrep -v "^#" | egrep "ssl_key_file|ssl_cert_file"
ssl_cert_file = '/home/postgres/security/server.crt'
ssl_key_file = '/home/postgres/security/server.key'

3）重新啟動PostgreSQL伺服器

輸入之前ssl認證檔案配置的密碼後，資料庫即能啟動

[postgres@PGServer2 ~]$ pg_ctl  start -D $PGDATA -l /tmp/logfile
waiting for server to start....Enter PEM pass phrase:.
 done
server started

2.1 預設權限機制

資料庫對象建立後，進行對象建立的使用者就是該對象的所有者。叢集安裝後的預設情況下，未開啟三權分立，資料庫系統管理者具有與對象所有者相同的權限。也就是說對象建立後，預設隻有對象所有者或者系統管理者可以查詢、修改和銷毀對象，以及通過GRANT将對象的權限授予其他使用者。

為使其他使用者能夠使用對象，必須向使用者或包含該使用者授予必要的權限。

不同的權限與不同的對象類型關聯。要撤消已經授予的權限，可以使用REVOKE。對象所有者的權限（例如ALTER、 DROP、GRANT和REVOKE）是隐式的，無法授予或撤消。即隻要擁有對象就可以執行對象所有者的這些隐式權限。對象所有者可以撤消自己的普通權限，例如，使表對自己以及其他人隻讀。

系統表和系統視圖要麼隻對系統管理者可見，要麼對所有使用者可見。辨別了需要系統管理者權限的系統表和視圖隻有系統管理者可以查詢。

資料庫提供對象隔離的特性，對象隔離特性開啟時，使用者隻能檢視有權限通路的對象(表、視圖、字段、函數)，系統管理者不受影響。

2.2 使用者

使用CREATE USER 和 ALTER USER 可以建立和管理資料庫使用者。資料庫包含一個或者多個已命名的資料庫。使用者和角色在整個資料庫中是共享的，但是資料并不共享。即使用者可以連接配接到任何資料庫，但當連接配接成功後，任何使用者都隻能通路連接配接請求裡聲明的那個資料庫。

2.2.1 管理者使用者

管理者使用者可以管理資料庫中的對象，可以通過WITH SUPERUSER關鍵詞建立管理者使用者

示例:建立具有管理者角色的使用者admin1和admin2

postgres=# CREATE USER admin1 WITH SUPERUSER ENCRYPTED PASSWORD 'admin1';
CREATE ROLE
postgres=# CREATE USER admin2 WITH SUPERUSER ENCRYPTED PASSWORD 'admin2';
CREATE ROLE

2.2.2 業務使用者

對于有多個業務部門，各部門間使用不同的資料庫使用者進行業務操作，同時有一個同級的資料庫維護部門使用資料庫管理者進行維護操作的場景下，業務部門可能希望在未經授權的情況下，管理者使用者隻能對各部門的資料進行控制操作（DROP、ALTER、TRUNCATE），但是不能進行通路操作（INSERT、DELETE、UPDATE、SELECT、COPY）。即針對管理者使用者，表對象的控制權和通路權要能夠分離，提高普通使用者資料安全性。

示例:建立普通使用者user1和user2

postgres=# CREATE USER user1 WITH ENCRYPTED PASSWORD 'user1';
CREATE ROLE
postgres=# CREATE USER user2 WITH ENCRYPTED PASSWORD 'user2';
CREATE ROLE

2.3 模式

Schema又稱作模式。通過管理Schema，允許多個使用者使用同一資料庫而不互相幹擾，可以将資料庫對象組織成易于管理的邏輯組，同時便于将第三方應用添加到相應的Schema下而不引起沖突。

每個資料庫包含一個或多個Schema。資料庫中的每個Schema包含表和其他類型的對象。資料庫建立初始，預設具有一個名為public的Schema，且所有使用者都擁有此Schema的權限。可以通過Schema分組資料庫對象。Schema類似于作業系統目錄，但Schema不能嵌套。

相同的資料庫對象名稱可以應用在同一資料庫的不同Schema中，而沒有沖突。例如，a_schema和b_schema都可以包含名為mytable的表。具有所需權限的使用者可以通路資料庫的多個Schema中的對象。

在初始資料庫postgres中建立使用者時，系統會自動幫助使用者建立一個同名Schema。在其他資料庫中，若需要同名Schema，則需要使用者手動建立。

資料庫對象是建立在資料庫搜尋路徑中的第一個Schema内的。

建立SCHEMA可以使用CREATE SCHEMA語句建立。

更改SCHEMA名稱或者所有者，可以使用ALTER SCHEMA語句進行修改。

要删除SCHEMA及其對象，使用DROP SCHEMA 文法可以進行删除。

要在SCHEMA内建立表，以schema.tablename格式建立表。不指定schemaname時，對象預設建立到search_path中的第一個schema名稱。

示例1:建立schema對象s1和s2

postgres=# CREATE SCHEMA s1;
CREATE SCHEMA
postgres=# CREATE SCHEMA s2;
CREATE SCHEMA

示例2:修改schema s1為schema1，s2為schema2

postgres=# ALTER SCHEMA  s1 RENAME TO schema1;
ALTER SCHEMA
postgres=# ALTER SCHEMA  s2 RENAME TO schema2;
ALTER SCHEMA

示例3: 建立schema1下的表 st1 ,schema t2下的表為st2

postgres=# CREATE TABLE schema1.st1(id int,name varchar(20));
CREATE TABLE
postgres=# CREATE TABLE schema2.st2(id int,name varchar(20));
CREATE TABLE

示例4：檢視建立的表 st1 和 st2

--需要先設定schema的搜尋路徑，否則找不到該schema1和schema2下的對象
postgres=# set search_path = public,schema1,schema2;
SET
--需要注意的是，設定search_path為多個時，不要加引号
postgres=# \d
         List of relations
 Schema  | Name | Type  |  Owner   
---------+------+-------+----------
 schema1 | st1  | table | postgres
 schema2 | st2  | table | postgres

示例5:為schema1和schema2下的兩張表st1和st2插入資料

postgres=# INSERT INTO schema1.st1 VALUES(1,'PostgreSQL');
INSERT 0 1
postgres=# INSERT INTO schema2.st2 VALUES(1,'MySQL');
INSERT 0 1

示例6：使用之前建立的普通使用者user1和user2通路schema1和schema2下的表

postgres=# \c postgres user1;
You are now connected to database "postgres" as user "user1".
postgres=> SELECT * FROM schema1.st1 ;
ERROR:  permission denied for schema schema1
LINE 1: SELECT * FROM schema1.st1 ;
--此時，user1使用者沒有足夠的權限可以使用schema1，是以需要對使用者user1授權對schema1的使用權限
postgres=# \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT USAGE ON SCHEMA schema1 TO user1;
GRANT
--再次使用user1檢視schema1下的對象st1
postgres=# \c postgres user1;
You are now connected to database "postgres" as user "user1".
postgres=> SELECT * FROM schema1.st1 ;
ERROR:  permission denied for relation st1
--此時雖然user1對schema雖然具有使用權限，但是沒有權限通路schema1下的對象st1，
--是以還需要對schema1.st1授權user1可以通路的權限，即select權限
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# GRANT SELECT ON TABLE schema1.st1 TO user1;
GRANT
--再次使用user1對schema1下的對象st1進行查詢
postgres=# \c postgres user1;
You are now connected to database "postgres" as user "user1".
postgres=> SELECT * FROM schema1.st1 ;
 id |    name    
----+------------
  1 | PostgreSQL
(1 row)

對于使用者user2，如果想要對schema2下的對象st2進行檢視，需要做上述同樣的操作。

綜上案例，可以發現，如果一個普通使用者對于schema下的對象進行通路，修改等操作，首先要讓普通使用者具有對schema的使用權限，其次對schema下的對象要授權普通使用者具有通路修改的權限。

設定賬戶的密碼安全政策

使用者密碼存儲在系統表pg_authid中，為防止使用者密碼洩露，PostgreSQL對使用者密碼進行加密存儲，所采用的加密算法由配置參數password_encryption_type決定。

  • 當參數password_encryption設定為 scram-sha-256 時，表示采用 scram-sha-25 6方式對密碼加密。
  • 當參數password_encryption設定為 md5 時，表示采用md5方式對密碼加密。md5為不安全的加密算法，不建議使用。

注意:PostgreSQL資料庫預設采用MD5加密。該參數設定後需要重新啟動資料庫伺服器，并且需要更改原有密碼，使修改後使用者使用的密碼加密算法生效。

示例1:檢視資料庫預設加密算法

postgres=# SHOW password_encryption ;
 password_encryption 
---------------------
 md5

示例2:修改預設加密算法為 scram-sha-256

postgres=# ALTER SYSTEM SET password_encryption = 'scram-sha-256';
ALTER SYSTEM
--重新啟動資料庫伺服器
[postgres@PGServer2 ~]$ pg_ctl  stop -D $PGDATA -l /tmp/logfile && pg_ctl start -D $PGDATA -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start....Enter PEM pass phrase:.
 done
server started

示例3:檢視之前建立的使用者user1使用的密碼加密政策

postgres=# SELECT rolname,rolpassword FROM pg_authid  WHERE rolname = 'user1';
 rolname |             rolpassword             
---------+-------------------------------------
 user1   | md57d1b5a4329b6478e976508ab9a49ee3d

示例4:配置pg_hba.conf中的METHOD為 scram-sha-256

[postgres@PGServer2 ~]$ cat $PGDATA/pg_hba.conf  | grep 10
host    all             all             10.10.20.0/24           scram-sha-256

示例5:使用user1連接配接到postgres資料庫

--user1的密碼為 test
[postgres@PGServer2 ~]$ psql -h 10.10.20.101 -p 5432 -U user1 -d postgres
Password for user user1: 
psql: FATAL:  password authentication failed for user "user1"
FATAL:  password authentication failed for user "user1"
--此時輸入test的密碼，但是連接配接失敗，原因是由于在更改password_encryption為scram-sha-256之前，user1使用者的密碼存儲使用的加密政策為 md5 加密，示例3可以檢視到user1采用md5加密
--解決方法:重新修改user1的密碼
postgres=# ALTER USER user1 WITH ENCRYPTED PASSWORD 'user1';
ALTER ROLE
--檢視修改後的user1的密碼加密政策為scram-sha-256
postgres=# SELECT rolname,rolpassword FROM pg_authid  WHERE rolname = 'user1';
 rolname |                                                              rolpassword                                                              
---------+---------------------------------------------------------------------------------------------------------------------------------------
 user1   | SCRAM-SHA-256$4096:oAK38X5JC7XJLanDu+WBBQ==$ErZytCUN6ot/qfXnnxNnTneM0MmQVLvepghMP0bAJlg=:uUy8CiCgCU6E4+fbZdUPhofvFSLb53zHJDJUGQTdG+w=
--重新使用密碼user1進行連接配接
[postgres@PGServer2 ~]$ psql -U user1 -d postgres -h 10.10.20.101
Password for user user1: 
psql (10.11)
Type "help" for help.

postgres=>

密碼政策設定為scram-sha-256對資料隔離通路具有安全性，是以生産環境中，建議使用scram-sha-256進行加密。 

行級通路控制特性将資料庫通路控制精确到資料表行級别，使資料庫達到行級通路控制的能力。不同使用者執行相同的SQL查詢操作，讀取到的結果是不同的。

使用者可以在資料表建立行通路控制(Row Level Security)政策，該政策是指針對特定資料庫使用者、特定SQL操作生效的表達式。當資料庫使用者對資料表通路時，若SQL滿足資料表特定的Row Level Security政策，在查詢優化階段将滿足條件的表達式，按照屬性(PERMISSIVE | RESTRICTIVE)類型，通過AND或OR方式拼接，應用到執行計劃上。

行級通路控制的目的是控制表中行級資料可見性，通過在資料表上預定義Filter，在查詢優化階段将滿足條件的表達式應用到執行計劃上，影響最終的執行結果。目前受影響的SQL語句包括SELECT，UPDATE，DELETE。

示例:假設一張表中存儲了不同使用者的資料，但是不同的使用者隻能看到自身相關的資訊，不能通路其它使用者的資料資訊。

--建立使用者 lily,lucy,tom
postgres=# CREATE USER lily WITH ENCRYPTED PASSWORD 'lily';
CREATE ROLE
postgres=# CREATE USER lucy WITH ENCRYPTED PASSWORD 'lucy';
CREATE ROLE
postgres=# CREATE USER tom WITH ENCRYPTED PASSWORD 'tom';
CREATE ROLE
--建立表 user_data，并插入不同使用者的不同資料
postgres=# postgres=# CREATE TABLE user_data(id int primary key,name varchar(20),gender varchar(6),nickname varchar(20),age int2,carrer varchar(20));
CREATE TABLE
postgres=#  INSERT INTO user_data  VALUES (1,'lily','女','昵稱:麗麗',20,'銷售');
INSERT 0 1
postgres=#  INSERT INTO user_data  VALUES (2,'lucy','女','昵稱:路西',23,'經理');
INSERT 0 1
postgres=#  INSERT INTO user_data  VALUES (3,'tom','男','昵稱:湯姆貓',22,'程式員');
INSERT 0 1

--将資料表user_data中讀取權限授權給lily,lucy和tom
postgres=# GRANT SELECT ON user_data TO lily,lucy,tom;
GRANT
--啟用行通路控制政策
postgres=# ALTER TABLE user_data ENABLE ROW LEVEL SECURITY ;
ALTER TABLE
--建立行通路控制政策，目前使用者隻能檢視使用者自身資料
postgres=# CREATE POLICY user_data_rls ON user_data FOR SELECT to PUBLIC USING (name = CURRENT_USER);
CREATE POLICY
--使用lily登入檢視user_data資料
 [postgres@PGServer2 ~]$ psql -U lily -d postgres -h 10.10.20.101
Password for user lily: 
psql (10.11)
Type "help" for help.

postgres=> select * from user_data ;
 id | name | gender | nickname  | age | carrer 
----+------+--------+-----------+-----+--------
  1 | lily | 女     | 昵稱:麗麗 |  20 | 銷售
(1 row)

使用lucy登入檢視user_data資料
postgres=> \c postgres lucy 10.10.20.101
Password for user lucy: 
You are now connected to database "postgres" as user "lucy".
postgres=> select * from user_data ;
 id | name | gender | nickname  | age | carrer 
----+------+--------+-----------+-----+--------
  2 | lucy | 女     | 昵稱:路西 |  23 | 經理

--使用tom使用者檢視user_data資料
postgres=> \c postgres tom 10.10.20.101
Password for user tom: 
You are now connected to database "postgres" as user "tom".
postgres=> select * from user_data ;
 id | name | gender |  nickname   | age | carrer 
----+------+--------+-------------+-----+--------
  3 | tom  | 男     | 昵稱:湯姆貓 |  22 | 程式員

小結:

以上就是PostgreSQL資料庫中關于用戶端接入認證，使用者級别安全和基于行安全(RLS)的内容。

在PostgreSQL中，除了這些來控制資料庫的安全以外，還有pg_hba中的其它基于主機的通路控制政策，資料庫通路審計政策及對資料的加密政策。此章節中不再對涉及的其它安全通路控制進行一一探讨。

資料庫安全 晟數學苑 PostgreSQL資料庫
上一篇: linux常用面試題
下一篇: 怎樣把centos 6.2 系統裡的網卡em1還原為eth0 .

繼續閱讀