SQL-standard function bodies and creating SECURITY DEFINER routines securely - Mailing list pgsql-docs
Hello
PostgreSQL 14 added the feature: "Allow SQL-language functions and procedures 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.
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
pgsql-docs by date: