postgresql - pg_restore : can't import data if table has a constraint with a function calling another function

Question

在仅使用 public 模式的 PostgreSQL 中，在调用其他函数的检查约束函数中使用的表中的数据在使用 转储后不会使用 pg_restore 导入>pg_dump。但是，如果检查约束中的函数不调用其他函数，则可以正确导入数据。

我在从 9.3.22 升级到 9.3.23 以及从 9.4.16 升级到 9.4.17 后注意到了这种行为；在 9.3.22 和 9.4.17 中运行良好。

我的理解是由于 changes in search_path :

Avoid use of insecure search_path settings in pg_dump and other client programs (Noah Misch, Tom Lane)

pg_dump, pg_upgrade, vacuumdb and other PostgreSQL-provided applications were themselves vulnerable to the type of hijacking described in the previous changelog entry; since these applications are commonly run by superusers, they present particularly attractive targets. To make them secure whether or not the installation as a whole has been secured, modify them to include only the pg_catalog schema in their search_path settings. Autovacuum worker processes now do the same, as well.

In cases where user-provided functions are indirectly executed by these programs — for example, user-provided functions in index expressions — the tighter search_path may result in errors, which will need to be corrected by adjusting those user-provided functions to not assume anything about what search path they are invoked under. That has always been good practice, but now it will be necessary for correct behavior. (CVE-2018-1058 or CVE-2018-1058)

我仍然不清楚为什么允许具有一级公共(public)函数，但调用其他函数不是那些函数。

例如，具有这样的结构和数据:

CREATE OR REPLACE FUNCTION is_even(n integer) RETURNS boolean AS $BODY$ 
BEGIN
  return n%2 = 0;  
END ; $BODY$  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION is_even_positive(n integer) RETURNS boolean AS $BODY$ 
BEGIN
  return is_even(n) and n > 0;  
END ; $BODY$  LANGUAGE plpgsql;


CREATE TABLE test_check (
  n integer
  CONSTRAINT even_chk CHECK (is_even(n)));

CREATE TABLE test_check2(
  n integer
  CONSTRAINT even_positive_chk CHECK (is_even_positive(n)));

insert into test_check values (2);
insert into test_check values (-2);
insert into test_check2 values (2);

导出:

pg_dump -h localhost -p 5432  -F c -b -v -f test.dmp test -U test

并将其导入到新数据库中:

$ pg_restore -d test2 -U test -v test.dmp -h localhost
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA public"
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: creating FUNCTION "public.is_even(integer)"
pg_restore: creating FUNCTION "public.is_even_positive(integer)"
pg_restore: creating TABLE "public.test_check"
pg_restore: creating TABLE "public.test_check2"
pg_restore: processing data for table "public.test_check"
pg_restore: processing data for table "public.test_check2"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2035; 0 7784774 TABLE DATA test_check2 tad
pg_restore: [archiver (db)] COPY failed for table "test_check2": ERROR:  function is_even(integer) does not exist
LINE 1: SELECT is_even(n) and n > 0
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT is_even(n) and n > 0
CONTEXT:  PL/pgSQL function public.is_even_positive(integer) line 3 at RETURN
COPY test_check2, line 1: "2"
pg_restore: creating ACL "SCHEMA public"
WARNING: errors ignored on restore: 1

请注意 test_check 表已正确导入数据，而 test_check2 失败。

Answer 1

解决方案是为函数显式设置一个search_path。

那么 pg_restore 设置 search_path = pg_catalog 就没有关系了，因为它将被函数的设置覆盖。

这还将防止函数无意中从恰好在调用 session 中设置的不同架构中获取函数和运算符(这是更改试图修复的安全问题)。

ALTER FUNCTION is_even_(integer) SET search_path=public;
ALTER FUNCTION is_even_positive(integer) SET search_path=public;