Re: Logical replication - empty search_path bug?

Hi,

On Fri, Oct 22, 2021 at 9:08 PM Troska, Cezary
<Cezary(dot)Troska(at)alvaria(dot)com> wrote:
>
> 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 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/