- PostgreSQL 11 Server Side Programming Quick Start Guide
- Luca Ferrari
- 348字
- 2021-06-10 19:23:07
Security
The f_files_from_directory() function shown in Listing 15 must be executed as a superuser in order to work. This is due to the fact that the function body exploits other internal functions to read the local filesystem, which is considered dangerous and therefore restricted to database administrators.
While we have the possibility of explicitly granting permission to other users, what is required in this case is to be able to execute the function as a superuser. Functions can achieve this by means of the SECURITY option. This can be one of the following:
- INVOKER: This is the default value. It means that the function will run with the privileges of the user that invoked it
- DEFINER: This means that the function will always run with the privileges of the user that has defined it
In other words, using a Unix analogy, the SECURITY DEFINER option is similar to the setuid(2) option for Unix executables.
Given this, we can declare the function of Listing 15 with the SECURITY DEFINER option from a database administrator, as shown in Listing 17. The function will always be executed as a superuser from any other user:
testdb=# CREATE OR REPLACE FUNCTION
f_files_from_directory( dir text DEFAULT '.' )
RETURNS SETOF files AS $code$
...
$code$ LANGUAGE plpgsql SECURITY DEFINER;
testdb=> SELECT current_user;
current_user
--------------
luca
testdb=> SELECT * FROM
f_files_from_directory( '/home/luca/git/fluca1978-pg-utils/examples/cte' );
pk | f_name | f_size | f_hash | f_type | ts
-----+-------------------------------+---------+----------------------------------+--------+---------------------
136 | family_tree.sql | 1879.00 | cc948a6e78a1581e350958c71093927d | sql | 2018-05-31 16:17:19
137 | family_tree_recursive_cte.sql | 400.00 | 42a149f41d3c78241160ea473154e4b5 | sql | 2018-05-31 16:17:19
138 | file_system_cte.sql | 1424.00 | acc41b140745747e7647de742868d768 | sql | 2018-05-31 16:17:19
139 | star_wars_family_tree_cte.sql | 2937.00 | 3e2bf991e553ae86e6f1ca2aa525b597 | sql | 2018-05-31 16:17:19
It is interesting to note that the privilege escalation propagates to all the function execution flow, so there is no additional need to grant any other permission to functions such as pg_ls_dir(), which would not normally work as expected for a non-administrator user.
- PowerShell 3.0 Advanced Administration Handbook
- 教父母學(xué)會(huì)上網(wǎng)
- 精通Excel VBA
- 大數(shù)據(jù)挑戰(zhàn)與NoSQL數(shù)據(jù)庫(kù)技術(shù)
- Python Data Science Essentials
- Windows環(huán)境下32位匯編語(yǔ)言程序設(shè)計(jì)
- 數(shù)據(jù)庫(kù)系統(tǒng)原理及應(yīng)用教程(第5版)
- 筆記本電腦維修90個(gè)精選實(shí)例
- Excel 2010函數(shù)與公式速查手冊(cè)
- C++程序設(shè)計(jì)基礎(chǔ)(上)
- 和機(jī)器人一起進(jìn)化
- 智能制造系統(tǒng)及關(guān)鍵使能技術(shù)
- 手把手教你學(xué)Flash CS3
- 設(shè)計(jì)模式
- Deep Learning Essentials