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

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.

主站蜘蛛池模板: 崇礼县| 金湖县| 临安市| 江城| 庆城县| 故城县| 板桥市| 孟连| 西林县| 台安县| 科尔| 东平县| 台中市| 威海市| 五指山市| 墨江| 邵阳县| 江城| 张家港市| 城固县| 天柱县| 阳原县| 江陵县| 色达县| 亚东县| 阿合奇县| 喀喇沁旗| 胶州市| 麟游县| 日土县| 昌平区| 丹阳市| 建昌县| 孟津县| 灵宝市| 泰和县| 修水县| 抚远县| 锡林浩特市| 始兴县| 长垣县|