Thread: BUG #15116: pg_recvlogical always fails

BUG #15116: pg_recvlogical always fails

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      15116
Logged by:          Krzysztof Kotlarski
Email address:      kotlarski.krzysztof@gmail.com
PostgreSQL version: 9.6.8
Operating system:   Linux
Description:

When run with correct arguments (working in previous versions)
pg_recvlogical always fails with: 

pg_recvlogical: could not clear search_path: ERROR:  syntax error

I suspect it's trying to execute ALWAYS_SECURE_SEARCH_PATH_SQL on
replication connection which is nonSQL.


Re: BUG #15116: pg_recvlogical always fails

From
Peter Eisentraut
Date:
On 3/16/18 11:34, PG Bug reporting form wrote:
> When run with correct arguments (working in previous versions)
> pg_recvlogical always fails with: 
> 
> pg_recvlogical: could not clear search_path: ERROR:  syntax error
> 
> I suspect it's trying to execute ALWAYS_SECURE_SEARCH_PATH_SQL on
> replication connection which is nonSQL.

I can confirm this problem.

I have copied the 030_pg_recvlogical.pl test from 10 into 9.6 to produce
a test failure with that same error message.

Haven't looked further yet.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: BUG #15116: pg_recvlogical always fails

From
Michael Paquier
Date:
On Fri, Mar 16, 2018 at 06:37:04PM -0400, Peter Eisentraut wrote:
> I can confirm this problem.

At least slot creation fails.  One just needs to start up an instance
and run that for example:
$ pg_recvlogical --create-slot -S popo -d "dbname=postgres"
pg_recvlogical: could not clear search_path: ERROR:  syntax erro
--
Michael

Attachment

Re: BUG #15116: pg_recvlogical always fails

From
Michael Paquier
Date:
On Sat, Mar 17, 2018 at 09:20:26AM +0900, Michael Paquier wrote:
> At least slot creation fails.  One just needs to start up an instance
> and run that for example:
> $ pg_recvlogical --create-slot -S popo -d "dbname=postgres"
> pg_recvlogical: could not clear search_path: ERROR:  syntax erro

The issue is that support for plain SQL queries has been added in
PostgreSQL 10 for logical replication connections, however e170b8c8 has
missed the fact that this is not supported for older versions.  Hence,
two things need to happen as pg_recvlogical needs to be kept compatible
with past server versions:
1) Make the check deciding if search_path should be enforced smarter by
checking if the server version is newer than 10.  This needs to be
patched on HEAD and REL_10_STABLE.  recvlogical-search-path-fix.patch
does so.
2) Simply remove the check on past branches, as I recall that we
maintain downstream compatibility, but do not for example guarantee that
a Postgres 10 server would work with a 9.6's pg_recvlogical. This is
what recvlogical-search-path-fix-96.patch attached does.

Thanks,
--
Michael

Attachment

Re: BUG #15116: pg_recvlogical always fails

From
Magnus Hagander
Date:


On Sat, Mar 17, 2018 at 1:32 AM, Michael Paquier <michael@paquier.xyz> wrote:
On Sat, Mar 17, 2018 at 09:20:26AM +0900, Michael Paquier wrote:
> At least slot creation fails.  One just needs to start up an instance
> and run that for example:
> $ pg_recvlogical --create-slot -S popo -d "dbname=postgres"
> pg_recvlogical: could not clear search_path: ERROR:  syntax erro

The issue is that support for plain SQL queries has been added in
PostgreSQL 10 for logical replication connections, however e170b8c8 has
missed the fact that this is not supported for older versions.  Hence,
two things need to happen as pg_recvlogical needs to be kept compatible
with past server versions:
1) Make the check deciding if search_path should be enforced smarter by
checking if the server version is newer than 10.  This needs to be
patched on HEAD and REL_10_STABLE.  recvlogical-search-path-fix.patch
does so.

This looks like the correct fix.

 
2) Simply remove the check on past branches, as I recall that we
maintain downstream compatibility, but do not for example guarantee that
a Postgres 10 server would work with a 9.6's pg_recvlogical. This is
what recvlogical-search-path-fix-96.patch attached does.

In this scenario, since we don't actively *enforce* this version difference, I think we should apply the fix from #1 in this scenario as well. Otherwise you might use an old pg_recvlogical to connect to a newer server, and open up a vulnerability somehow. I assume pg_recvlogical of that age doesn't actually try to do something with it, but it still feels safer for the future.

Is there any particular reason why doing #1 would not work on the older branches?

--

Re: BUG #15116: pg_recvlogical always fails

From
Michael Paquier
Date:
On Sat, Mar 17, 2018 at 06:33:48PM +0100, Magnus Hagander wrote:
> In this scenario, since we don't actively *enforce* this version
> difference, I think we should apply the fix from #1 in this scenario as
> well. Otherwise you might use an old pg_recvlogical to connect to a newer
> server, and open up a vulnerability somehow. I assume pg_recvlogical of
> that age doesn't actually try to do something with it, but it still feels
> safer for the future.

Yeah, there is no actual reason to not apply #1 everywhere as well.  The
back-patch is a no-brainer.
--
Michael

Attachment

Re: BUG #15116: pg_recvlogical always fails

From
Magnus Hagander
Date:


On Sun, Mar 18, 2018 at 12:02 AM, Michael Paquier <michael@paquier.xyz> wrote:
On Sat, Mar 17, 2018 at 06:33:48PM +0100, Magnus Hagander wrote:
> In this scenario, since we don't actively *enforce* this version
> difference, I think we should apply the fix from #1 in this scenario as
> well. Otherwise you might use an old pg_recvlogical to connect to a newer
> server, and open up a vulnerability somehow. I assume pg_recvlogical of
> that age doesn't actually try to do something with it, but it still feels
> safer for the future.

Yeah, there is no actual reason to not apply #1 everywhere as well.  The
back-patch is a no-brainer.

Applied that way with minor adjustments to the comment. 


--

Re: BUG #15116: pg_recvlogical always fails

From
Michael Paquier
Date:
On Sun, Mar 18, 2018 at 01:13:48PM +0100, Magnus Hagander wrote:
> Applied that way with minor adjustments to the comment.

Thanks, Magnus.
--
Michael

Attachment