官术网_书友最值得收藏!

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
Listing 17:  Transforming the function as a "setuid" one

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.

主站蜘蛛池模板: 丰镇市| 怀远县| 奉新县| 遵义县| 曲松县| 尼勒克县| 八宿县| 普安县| 永丰县| 曲沃县| 嘉义县| 会东县| 舟山市| 平江县| 奉新县| 金昌市| 平邑县| 德令哈市| 沙洋县| 康平县| 惠东县| 秦安县| 广昌县| 建始县| 科技| 亚东县| 汤原县| 马山县| 张家港市| 左贡县| 玉屏| 曲阳县| 南陵县| 建湖县| 尤溪县| 金乡县| 红原县| 平罗县| 芒康县| 二手房| 武强县|