Thread: BUG #15116: pg_recvlogical always fails
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.
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
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
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
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?
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
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.
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