Hello PostgreSQL 14 added the feature: "Allow SQL-language functions<https://www.postgresql.org/docs/14/sql-createfunction.html> and procedures<https://www.postgresql.org/docs/14/sql-createprocedure.html> to use SQL-standard function bodies."
If I understand correctly, then in this case the system will track dependencies between tables and routines that use the tables. Thus, the SECURITY DEFINER routines that use the new approach do not require the following mitigation, i.e., SET search_path= is not needed. The following part of documentation does not mention this. https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY [https://www.postgresql.org/media/img/about/press/elephant.png]<https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY> PostgreSQL: Documentation: 14: CREATE FUNCTION<https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY> Overloading. PostgreSQL allows function overloading; that is, the same name can be used for several different functions so long as they have distinct input argument types.Whether or not you use it, this capability entails security precautions when calling functions in databases where some users mistrust other users; see Section 10.3.. Two functions are considered the same if they have the same ... www.postgresql.org Here is a small demonstration. DROP TABLE IF EXISTS T; CREATE TABLE T(t_id INTEGER, CONSTRAINT pk_t PRIMARY KEY (t_id)); INSERT INTO T(t_id) VALUES (1), (2); CREATE OR REPLACE FUNCTION f_find_t_count_with_path_newer() RETURNS bigint LANGUAGE sql SECURITY DEFINER SET search_path = public, pg_temp BEGIN ATOMIC SELECT Count(*) AS cnt FROM T; END; CREATE OR REPLACE FUNCTION f_find_t_count_without_path_newer() RETURNS bigint LANGUAGE sql SECURITY DEFINER BEGIN ATOMIC SELECT Count(*) AS cnt FROM T; END; /*I create a fake table in the temporary schema.*/ CREATE TABLE pg_temp.T(t_id INTEGER, CONSTRAINT pk_t PRIMARY KEY (t_id)); SELECT f_find_t_count_with_path_newer(); Result: 2 SELECT f_find_t_count_without_path_newer(); Result: 2 /*In both cases table T in the schema public was used to return the result.*/ Best regards Erki Eessaar
