Re: Logical replication - empty search_path bug?

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/