Postgresql安全
分類：連接安全、賬戶安全（個人理解）
一、 連接安全：
1、 客戶端接入認證的主要方式是通過pg_hba.conf檔案來進行配置，
2、 
3、 具體引數說明：
3.1、TYPE定義了多種連接PostgreSQL的方式，分別是：
“local”使用本地unix套接字，
“host”使用TCP/IP連接（包括SSL和非SSL），“host”結合“IPv4地址”使用IPv4方式，結合“IPv6地址”則使用IPv6方式，
“hostssl”只能使用SSL TCP/IP連接，
“hostnossl”不能使用SSL TCP/IP連接，
3.2、DATABASE指定哪個資料庫，多個資料庫，庫名間以逗號分隔，“all”只有在沒有其他的符合條目時才代表“所有”，如果有其他的符合條目則代表“除了該條之外的”，因為“all”的優先級最低，
這兩條都是指定local訪問方式，因為前一條指定了特定的資料庫db1，所以后一條的all代表的是除了db1之外的資料庫，同理用戶的all也是這個道理，
注意：all` ,`sameuser`,`samerole`,`replication`,`資料庫名稱` ,或者多個
資料庫名稱用 `逗號`，值 all 表明該記錄匹配所有資料庫; 不匹配 replication，
值 sameuser表示如果被請求的資料庫和請求的用戶同名，則匹配;
值samerole指定所請求的用戶必須是與所請求的資料庫同名的角色成員，
值 replication 表示匹配一條replication連接，它不指定一個特定的資料庫，一般在流復制中使用;
3.3、USER指定哪個資料庫用戶（PostgreSQL正規的叫法是角色，role），多個用戶以逗號分隔，
3.4、ADDRESS項可以是IPv4地址或IPv6地址，可以定義某臺主機或某個網段，舉例： IPv4 地址范圍的典型示例是對于單個主機為172.20.143.89/32；
對于小型網路為172.20.143.0/24對于較大網路為10.6.0.0/16；0.0.0.0/0代表所有IPv4地址，::0/0代表所有IPv6地址，當然你可以使用 all 選項來匹配所有的IP地址
3.5、METHOD指定如何處理客戶端的認證，常用的有ident，md5，password，trust，reject，cert，scram-sha-256
ident是Linux下PostgreSQL默認的local認證方式，凡是能正確登錄服務器的作業系統用戶（注：不是資料庫用戶）就能使用本用戶映射的資料庫用戶不需密碼登錄資料庫，用戶映射檔案為pg_ident.conf，這個檔案記錄著與作業系統用戶匹配的資料庫用戶，如果某作業系統用戶在本檔案中沒有映射用戶，則默認的映射資料庫用戶與作業系統用戶同名，比如，服務器上有名為user1的作業系統用戶，同時資料庫上也有同名的資料庫用戶，user1登錄作業系統后可以直接輸入psql，以user1資料庫用戶身份登錄資料庫且不需密碼，
md5是常用的密碼認證方式，如果你不使用ident，最好使用md5，密碼是以md5形式傳送給資料庫，較安全，且不需建立同名的作業系統用戶，
password是以明文密碼傳送給資料庫，建議不要在生產環境中使用，
trust是只要知道資料庫用戶名就不需要密碼或ident就能登錄，建議不要在生產環境中使用，
reject是拒絕認證，
cert使用SSL服務進行驗證
案例：md5升級到scram-sha-256， 在postgresql.conf中設定 password_encryption = 'scram-sha-256'， 讓所有用戶設定新密碼，并將pg_hba.conf 中的認證方法宣告更改為scram-sha-256
Ssl案例：https://www.cnblogs.com/sandata/p/12659913.html
1.3 使用SSL進行安全的TCP/IP連接
從CA認證中心申請到正式的服務器、客戶端的證書和密鑰，（假設服務器的私鑰為server.key，證書為server.crt，客戶端的私鑰為client.key，證書為client.crt，CA根證書名稱為cacert.pem，）此處以OPENSSL生成的認證為基礎:
1）部署CA環境
登陸postgres用戶執行
|
1 |
[postgres@PGServer2 ~]$ mkdir -p security |
拷貝openssl.cnf到security目錄下
|
1 |
[postgres@PGServer2 ~]$ cp /etc/pki/tls/openssl.cnf ~/security/ |
創建CA環境并授權private為777權限
|
1 2 |
[postgres@PGServer2 ~]$ mkdir -p security/CA/{certs,private} [postgres@PGServer2 ~]$ chmod 777 security/CA/private/ |
驗證CA環境目錄
|
1 2 3 4 5 6 |
[postgres@PGServer2 ~]$ tree security/ security/ ├── CA │ ├── certs │ └── private └── openssl.cnf |
創建serial檔案，并寫入01
|
1 |
[postgres@PGServer2 ~]$ echo '01' > security/CA/serial |
創建index.txt索引檔案
|
1 |
[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
|
1 2
3 4 5 |
[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
|
1 2 3 4 5 6 7 |
[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
其中有些可以省略不填
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[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的口令，
檢查輸出的請求與簽名是否匹配
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[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）生成服務器證書私鑰
|
1 2 3 4 5 6 7 |
[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時的內容一致
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[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
|
1 2 3 |
[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中的口令
檢查請求與簽名是否匹配
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
[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認證連接
將ssl認證功能啟用
postgres=# show ssl;
ssl
-----
off
(1 row)
postgres=# alter system set ssl = on;
ALTER SYSTEM
2）配置server.key和server.crt檔案的位置
|
1
2 3 |
[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認證檔案配置的密碼后，資料庫即能啟動
|
1 2 3 4 |
[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將物件的權限授予其他用戶，
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名稱，
postgres=# CREATE SCHEMA s1;
CREATE SCHEMA
postgres=# CREATE SCHEMA s2;
CREATE SCHEMA
修改schema s1為schema1，s2為schema2
postgres=# ALTER SCHEMA s1 RENAME TO schema1;
ALTER SCHEMA
postgres=# ALTER SCHEMA s2 RENAME TO schema2;
ALTER SCHEMA
創建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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/1176.html
標籤：PostgreSQL