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.