PostgreSQL11 新特性解讀 : 新增三個預設角色Release說明pg_read_server_filespg_write_server_filespg_execute_server_program

PostgreSQL 11 新增三個預設系統角色，如下：

• pg_read_server_files
• pg_write_server_files
• pg_execute_server_program

這三個角色主要涉及資料庫服務端檔案的讀寫權限，例如使用copy指令或file_fdw子產品讀寫資料庫端檔案的權限。

這些權限之前版本隻有超級使用者才具備，這三個預設角色的出現，使得可以将資料庫服務端的檔案的通路權限（目前僅包含copy指令或file_fdw子產品）下放給普通使用者。

Release說明

Add default roles which control file system access (Stephen Frost)

Specifically, the new roles are: pg_read_server_files, pg_write_server_files, pg_execute_server_program. These roles now also control who can use COPY and extension file_fdw. Previously only superusers could use these functions, and that is still the default behavior.

手冊中說明很清楚，下面示範這三種角色的權限。

pg_read_server_files 角色具有資料庫服務端檔案的讀權限，例如使用copy指令或file_fdw子產品讀資料庫端檔案的權限。

在資料庫主機 pghost2 家目錄建立 t_copy.txt 檔案并寫入兩行資料，如下：

1       a
2       b            

以 francs 使用者登入資料庫 francs 建立測試表 t_copy 如下:

[pg11@pghost2 ~]$ psql francs francs
psql (11beta3)
Type "help" for help.

francs=> CREATE TABLE t_copy(id int4, name text);
CREATE TABLE           

建立 role11 使用者，如下

postgres=# CREATE ROLE role11 NOSUPERUSER PASSWORD 'role11' LOGIN;
CREATE ROLE           

以 role11 使用者登入到 francs 資料庫，執行 copy 指令，嘗試将資料庫服務端檔案 t_copy.txt 檔案的資料加載到表 t_copy 中，如下:

[pg11@pghost2 ~]$ psql francs role11
psql (11beta3)
Type "help" for help.

francs=> COPY t_copy FROM '/home/pg11/t_copy.txt';
ERROR:  must be superuser or a member of the pg_read_server_files role to COPY from a file
HINT:  Anyone can COPY to stdout or from stdin. psql s \copy command also works for anyone.           

以上報錯，提示需要超級使用者或具有pg_read_server_files權限才能使用 COPY 指令讀取資料庫服務端檔案。

給 role11 使用者賦 pg_read_server_files 角色權限，如下:

francs=> \c francs postgres
You are now connected to database "francs" as user "postgres".

francs=# GRANT pg_read_server_files TO role11;
GRANT ROLE

francs=# GRANT USAGE ON SCHEMA francs TO role11;
GRANT

francs=# GRANT INSERT ON francs.t_copy TO role11;
GRANT           

francs庫中建立了模式 francs ，是以也需要将模式的使用權限賦給 role11，否則通路表時會報沒有使用模式權限的錯誤；之後再賦予表的寫權限。

再次測試成功，如下。

[pg11@pghost2 ~]$ psql francs role11
psql (11beta3)
Type "help" for help.

francs=> COPY francs.t_copy FROM '/home/pg11/t_copy.txt';
COPY 2           

pg_write_server_files 角色具有資料庫服務端檔案的寫權限，例如使用copy指令或file_fdw子產品寫資料庫端檔案的權限，接着示範。

以 role11 使用者登入資料庫 francs ，嘗試導出表資料到資料庫服務端。

[pg11@pghost2 ~]$ psql francs role11;
psql (11beta3)
Type "help" for help.

francs=> COPY francs.t_copy TO '/home/pg11/t_copy2.txt';
ERROR:  must be superuser or a member of the pg_write_server_files role to COPY to a file
HINT:  Anyone can COPY to stdout or from stdin. psql s \copy command also works for anyone.           

賦權如下：

[pg11@pghost2 ~]$ psql francs postgres
psql (11beta3)
Type "help" for help.

francs=# GRANT pg_write_server_files TO role11;
GRANT ROLE

francs=# GRANT SELECT ON francs.t_copy TO role11;
GRANT           

再次測試成功，如下

[pg11@pghost2 ~]$ psql francs role11;
psql (11beta3)
Type "help" for help.

francs=> COPY francs.t_copy TO '/home/pg11/t_copy2.txt';
COPY 2

francs=> \! cat '/home/pg11/t_copy2.txt'
1       a
2       b           

可見，已将資料導出到資料庫服務端上的檔案。

pg_execute_server_program 角色具有執行資料庫服務端的程式權限，以file_fdw外部表舉例如下。

首先準備資料檔案，将 t_copy 檔案進行壓縮，如下:

[pg11@pghost2 ~]$ cat t_copy.txt 
1       a
2       b

[pg11@pghost2 ~]$ gzip t_copy.txt            

建立 file_fdw 外部擴充和外部表，以超級使用者postgres登入francs庫，如下:

[pg11@pghost2 ~]$ psql francs postgres
psql (11beta3)
Type "help" for help.

francs=# CREATE EXTENSION file_fdw;
CREATE EXTENSION

francs=# CREATE SERVER srv_file FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER

francs=# GRANT USAGE ON FOREIGN SERVER srv_file TO role11;
GRANT           

以普通使用者role11登入francs庫，建立帶OPTIONS(program)選項的外部表，如下:

[pg11@pghost2 ~]$ psql francs role11
psql (11beta3)
Type "help" for help.

francs=> CREATE FOREIGN TABLE ft_t_copy(id int4,name text) SERVER srv_file OPTIONS(program 'gunzip < /home/pg11/t_copy.txt.gz');
ERROR:  only superuser or a member of the pg_execute_server_program role may specify the program option of a file_fdw foreign table           

以上報錯，提示需要 superuser 或 pg_execute_server_program 權限才有權限指定 file_fdw 外部表的 program 選項。

将 pg_execute_server_program 角色賦予 role11使用者，注意以下以postgres超級使用者執行。

francs=# GRANT pg_execute_server_program TO role11;
GRANT ROLE           

再次以role11使用者登入francs庫測試，如下:

francs=> CREATE FOREIGN TABLE ft_t_copy(id int4,name text) SERVER srv_file OPTIONS(program 'gunzip < /home/pg11/t_copy.txt.gz');
CREATE FOREIGN TABLE

francs=> SELECT * FROM ft_t_copy ;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)           

建立帶帶OPTIONS(program)選項的外部表成功。

總結

pg_read_server_files、pg_write_server_files、pg_execute_server_program 角色涉及到讀寫資料庫服務端檔案，權限較大，配置設定此角色權限給資料庫使用者時需謹慎考慮。

參考

• Default Roles
• Postgres 11 highlight - New System Roles
• PostgreSQL9.1新特性之三：基于檔案通路的 SQL/MED

新書推薦

最後推薦和張文升共同編寫的《PostgreSQL實戰》，本書基于PostgreSQL 10 編寫，共18章，重點介紹SQL進階特性、并行查詢、分區表、實體複制、邏輯複制、備份恢複、高可用、性能優化、PostGIS等，涵蓋大量實戰用例!

連結：

https://item.jd.com/12405774.html