Обсуждение: Logical replication - empty search_path bug?
Hello,
My name is Cezary. I was using postgresql-11 (11.11) logical replication and I came across a strange situation. I have a table looking like that:
CREATE TABLE testrepl(
salary integer CHECK (salary > get_minimal_salary())
);
get_minimal_salary function looks like that:
CREATE OR REPLACE FUNCTION get_minimal_salary() RETURNS INTEGER AS
$$
BEGIN
RETURN (SELECT minimal_salary FROM company_standards);
END;
$$ LANGUAGE plpgsql;
And company_standards table looks like that:
CREATE TABLE company_standards(
minimal_salary integer
);
Contents of the tables on the Master:
database=# SELECT * FROM company_standards;
minimal_salary
----------------
20
(1 row)
database=# SELECT * FROM testrepl;
salary
--------
30
40
(2 rows)
I create a publication with both testrepl and company_standards tables. Replication signals an error on the Replica side when trying to replicate testrepl data. ERROR message looks like follows:
2021-10-22 12:18:33.982 GMT [19728] LOG: background worker "logical replication worker" (PID 20198) exited with exit code 1
2021-10-22 12:18:38.992 GMT [20200] LOG: logical replication table synchronization worker for subscription "replsub", table "testrepl" has started
2021-10-22 12:18:39.008 GMT [20200] ERROR: relation "company_standards" does not exist at character 36
2021-10-22 12:18:39.008 GMT [20200] QUERY: SELECT (SELECT minimal_salary FROM company_standards)
2021-10-22 12:18:39.008 GMT [20200] CONTEXT: PL/pgSQL function public.get_minimal_salary() line 3 at RETURN
COPY testrepl, line 1: "30"
However company_standards table has been replicated and contains the same values as on the Master side. get_minimal_salary works when run by hand, problems occur only when it is run as a part of the logical replication process. Global search_path is set to the default "$user", public and there is no custom search path for the replication user.
I was looking for the cause of this situation and I found that during the replication the search_path being used is empty. I did that by modifying get_minimal_salary function like that:
CREATE OR REPLACE FUNCTION get_minimal_salary() RETURNS INTEGER AS
$$
BEGIN
Copy (SELECT setting FROM pg_settings WHERE name='search_path') To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;
RETURN (SELECT minimal_salary FROM company_standards);
END;
$$ LANGUAGE plpgsql;
Which gave me /tmp/test.csv showing that the search_path is empty in this situation:
setting
""
Replication works fine when the get_minimal_salary function has specified schema for company_standars table (public.company_standards). That’s another thing pointing at the search_path problem.
I searched the documentation before writing to you and I haven’t seen anywhere that such behavior is to be expected, I didn’t find anything clarifying that logical replication alters or cleanses the search_path for its processes. Is this situation a bug, or is it just me missing something in the documentation? And is there a way of setting the search_path to be used for the replication?
The situation was observed on Centos7 (7.7.1908) architecture x86_64.
Kind regards,
Cezary Troska
Hi, On Fri, Oct 22, 2021 at 9:08 PM Troska, Cezary <Cezary.Troska@alvaria.com> wrote: > > Hello, > > > > My name is Cezary. I was using postgresql-11 (11.11) logical replication and I came across a strange situation. I havea table looking like that: > > > > > > CREATE TABLE testrepl( > > salary integer CHECK (salary > get_minimal_salary()) > > ); > > > > > > get_minimal_salary function looks like that: > > > > > > CREATE OR REPLACE FUNCTION get_minimal_salary() RETURNS INTEGER AS > > $$ > > BEGIN > > RETURN (SELECT minimal_salary FROM company_standards); > > END; > > $$ LANGUAGE plpgsql; > > > > > > And company_standards table looks like that: > > > > > > CREATE TABLE company_standards( > > minimal_salary integer > > ); > > > > > > Contents of the tables on the Master: > > > > > > database=# SELECT * FROM company_standards; > > minimal_salary > > ---------------- > > 20 > > (1 row) > > > > database=# SELECT * FROM testrepl; > > salary > > -------- > > 30 > > 40 > > (2 rows) > > > > > > I create a publication with both testrepl and company_standards tables. Replication signals an error on the Replica sidewhen trying to replicate testrepl data. ERROR message looks like follows: > > > > > > 2021-10-22 12:18:33.982 GMT [19728] LOG: background worker "logical replication worker" (PID 20198) exited with exitcode 1 > > 2021-10-22 12:18:38.992 GMT [20200] LOG: logical replication table synchronization worker for subscription "replsub",table "testrepl" has started > > 2021-10-22 12:18:39.008 GMT [20200] ERROR: relation "company_standards" does not exist at character 36 > > 2021-10-22 12:18:39.008 GMT [20200] QUERY: SELECT (SELECT minimal_salary FROM company_standards) > > 2021-10-22 12:18:39.008 GMT [20200] CONTEXT: PL/pgSQL function public.get_minimal_salary() line 3 at RETURN > > COPY testrepl, line 1: "30" > > > > > > However company_standards table has been replicated and contains the same values as on the Master side. get_minimal_salaryworks when run by hand, problems occur only when it is run as a part of the logical replication process.Global search_path is set to the default "$user", public and there is no custom search path for the replication user. I think it's not a bug. We purposely set empty to search_path as a security fix of CVE-2020-14349. Please refer to the release note[1]: --- Set a secure search_path in logical replication walsenders and apply workers (Noah Misch) A malicious user of either the publisher or subscriber database could potentially cause execution of arbitrary SQL code by the role running replication, which is often a superuser. Some of the risks here are equivalent to those described in CVE-2018-1058, and are mitigated in this patch by ensuring that the replication sender and receiver execute with empty search_path settings. (As with CVE-2018-1058, that change might cause problems for under-qualified names used in replicated tables' DDL.) Other risks are inherent in replicating objects that belong to untrusted roles; the most we can do is document that there is a hazard to consider. (CVE-2020-14349) --- Regards, [1] https://www.postgresql.org/docs/11/release-11-9.html -- Masahiko Sawada EDB: https://www.enterprisedb.com/