那如果要用 SQL + Procedural Language 程式的話呢？
PostgreSQL 有提供 COPY 指令，但是 COPY 只能給 Superuser 執行：COPY 指令涉及資料庫「外部」的操作（寫檔案），Postgres 基於安全考量，只允許資料庫的 Superuser，以資料庫程序的執行者對資料庫外面操作（OS 中的相關檔案操作權限也以這個執行帳戶的能耐來操作）。
要是把 COPY 寫在函數裡面「也只能」用 Superuser 執行這個函數嗎？預設行為的確會禁止一般帳戶使用這樣的函數，執行到這裡就出錯了：
CREATE OR REPLACE FUNCTION enterprisedb.w2file (query text, filepath text)
RETURNS void
AS $$
BEGIN
EXECUTE format('COPY ( %s ) TO %L CSV;'
, query, filepath);
END;
$$ LANGUAGE plpgsql;
GRANT USAGE ON SCHEMA enterprisedb TO some_user;
GRANT EXECUTE ON FUNCTION enterprisedb.w2file(text, text) TO some_user;
edb=> \c
You are now connected to database "edb" as user "some_user".
edb=> select enterprisedb.w2file('select * from dept','/tmp/file1.csv');
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
CONTEXT: SQL statement "COPY ( select * from dept ) TO '/tmp/file1.csv' CSV;"
PL/pgSQL function enterprisedb.w2file(text,text) line 3 at EXECUTE
(注意)
- 函數所在的 Schema，要能夠被使用才行
- 上面的 format() 的控制字元（%s，%I，%L，%%）的用法，可以參考這裡。
- 這裡的提示 \copy 是 psql 的 Meta-Command，無法用在 PL/pgSQL 裡面
但是！在 Postgres 的函數，有執行權限修飾字，叫做 Security Label，可以處理這個問題。Security Label 分為 Security definer / Security Invoker 兩種：
- SECURITY DEFINER 指的是，在這個函數被呼叫之時，該函數執行期間的相關權限，等同於這個函數的擁有者在執行一樣
- SECURITY INVOKER 則是，在這個函數被呼叫之時，執行期間的相關權限完全以目前使用該函數的帳戶。預設是這個（很重要所以標紅字！）
注意這不要和函數物件本身的 Onwership / Execute 權限搞混：
Ownership / Execute 權限指的是一個函數物件本身，能不能夠被某個帳戶所調整 / 呼叫；沒權限的帳戶呼叫就會發生 Permission Denied，碰都碰不得。
而 Security Definer/Invoker 則是函數在執行期間的事情（這時已經被 Grant 相關使用權限了）。例如這篇的範例，被 Superuser 建立、內含 Copy 指令的自訂函數；就算某個普通帳戶有權限呼叫這個自訂函數，執行到 Copy 指令時，也沒辦法繼續下去，就會中途出錯。
因此，確切的用法是，先使用 superuser 建立包含 copy 的函數，並增加 SECURITY DEFINER 修飾字。然後再 grant execute 給要執行的帳戶
CREATE OR REPLACE FUNCTION enterprisedb.w2file (query text, filepath text)
RETURNS void
AS $$
BEGIN
EXECUTE format('COPY ( %s ) TO %L CSV;'
, query, filepath);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;
GRANT USAGE ON SCHEMA enterprisedb TO some_user;
GRANT EXECUTE ON FUNCTION enterprisedb.w2file(text, text) TO some_user;
edb=> \c
You are now connected to database "edb" as user "some_user".
edb=> select enterprisedb.w2file('select * from dept', '/tmp/file2.csv');
w2file_sql
------------
(1 row)
edb=> \q
[enterprisedb@edbvm ~]$ cat /tmp/file2.csv
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
這樣就成功了。
另外，其實還可以用 PL/Python(U) 來處理：如果可以賦予建立 PL/Python 函數的權限的話，就可以產生這種危險的（?）函數。實際賦予會出錯（這裡我用 EnterpriseDB 的 PostgreSQL 發行版，安裝 LanguagePack 套件，裡面附上的是 Python3）
edb=# CREATE EXTENSION plpython3u; edb=# CREATE LANGUAGE plpython3u; edb=# grant USAGE on LANGUAGE plpython3u TO user1; ERROR: language "plpython3u" is not trusted DETAIL: GRANT and REVOKE are not allowed on untrusted languages, because only superusers can use untrusted languages.
所以 Untrusted PL/Python 的 Stored Procedure 還是需要 Superuser 把程式佈上去才行～
在處理之前要注意一下：PL/Python 執行完的 SQL 回傳值會放在 List of Dictionaries 裡面，每一筆資料列都以 Python Dictionary 的 Key-Value 方式呈現，並使用 List 把多筆資料裝起來，因此匯出資料需要用迴圈寫入檔案。
-- 使用 Superuser 佈上去
CREATE OR REPLACE FUNCTION w2file_py (query text, filepath text)
RETURNS TEXT
AS $$
import os
if not os.path.exists(filepath):
outputfile = open(filepath, 'w')
else:
return 'FIle exist! No Output Result.' # End
rv = plpy.execute(query)
for item in rv:
outputfile.write("%s\n" % item)
return 'Query Result Exported.' # End
$$ LANGUAGE plpython3u;
GRANT USAGE ON SCHEMA enterprisedb TO some_user;
GRANT EXECUTE ON FUNCTION enterprisedb.w2file_py(text, text) TO some_user;
edb=> \c
You are now connected to database "edb" as user "some_user".
edb=> select w2file_py ('select * from emp limit 2', '/tmp/output.txt');
w2file_py
------------------------
Query Result Exported.
(1 row)
edb=> \! cat /tmp/output.txt
{'deptno': Decimal('20'), 'sal': Decimal('1250.00'), 'empno': Decimal('7369'), 'mgr': Decimal('7902'), 'comm': None, 'job': 'CLERK', 'ename': 'SMITH', 'hiredate': '17-DEC-80 00:00:00'}
{'deptno': Decimal('30'), 'sal': Decimal('2500.00'), 'empno': Decimal('7499'), 'mgr': Decimal('7698'), 'comm': Decimal('300.00'), 'job': 'SALESMAN', 'ename': 'ALLEN', 'hiredate': '20-FEB-81 00:00:00'}
要處理成 CSV 的話要調一下程式：使用 csv 模組的 writerows() 一次處理 List of dictionaries，記得要提供欄位名稱資訊（每個 Dict 的 Key 都長的一樣，只要用第零組的 key 作代表就好，也就是 rv[0].keys()），如果不要欄位名稱，就把 writeheader() 拿掉就好
CREATE OR REPLACE FUNCTION w2file_py (query text, filepath text)
RETURNS TEXT
AS $$
import os
import csv
if not os.path.exists(filepath):
rv = plpy.execute(query)
with open(filepath, 'w') as outputfile:
csvw = csv.DictWriter(outputfile, rv[0].keys() )
csvw.writeheader()
csvw.writerows(rv)
return 'Query Result Exported.' # End
else:
return 'FIle exist! No Output Result.' # End
$$ LANGUAGE plpython3u;
GRANT USAGE ON SCHEMA enterprisedb TO some_user;
GRANT EXECUTE ON FUNCTION enterprisedb.w2file_py(text, text) TO some_user;
edb=> \c
You are now connected to database "edb" as user "user1".
edb=> select enterprisedb.w2file_py ('select * from emp limit 2', '/tmp/outcsvpy.txt');
w2file_py
------------------------
Query Result Exported.
(1 row)
edb=> \! cat /tmp/outcsvpy.txt
empno,mgr,deptno,sal,job,comm,ename,hiredate
7369,7902,20,1250.00,CLERK,,SMITH,17-DEC-80 00:00:00
7499,7698,30,2500.00,SALESMAN,300.00,ALLEN,20-FEB-81 00:00:00
注意看一下，使用 PL/Python3U 竟然不用設定 Security Definer！我們可以看到，由於 PL/Python 在 Postgres 裡面其實是在資料庫之外分支一個 Python 程序來執行 Script，因此 Postgres 無法檢查 Python 裡面做了什麼，不會有執行期間執行權限的問題。但這也表示 PL/Python 的使用要小心處理安全性的問題才行～
要使用 Security Label 控制函數的安全性，還是使用 PL/Python 這類程式語言外掛的函數，端看撰寫方便與否來決定。最根本的還是避免一般帳戶執行一些行為不夠安全的函數。
函數行為的安全性，便取決於個別函數的實際撰寫內容決定；只能建議盡量不要用太過萬能的函數比較好，最好可以限制匯出格式或是寫死能處理的表格。像這裡的範例在使用安全性上都是不太好的示範～
參考資料
使用 COPY 指令的部份
PL/Python 部份
沒有留言:
張貼留言