Обсуждение: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR:syntax error at or near "$" .

This is with PostgreSQL 10.4.
How to reproduce :
postgres@smadev:~% psql
psql (10.4)
Type "help" for help.

Alter the role for search path :
dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
ALTER ROLE
dynacom=#

Verify :
postgres@smadev:~% psql -U amura3
Password for user amura3:
psql (10.4)
Type "help" for help.

dynacom=> show search_path ;
         search_path
---------------------------
  $user, amuragents, public
(1 row)

dynacom=>

pg_dumpall's output :
ALTER ROLE amura3 SET search_path TO $user, amuragents, public;

psql -f pg_dumpall_out.sql :
dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public;
ERROR:  syntax error at or near "$"

Is this a bug or am I missing something ?

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On 07/30/2018 05:57 AM, Achilleas Mantzios wrote:
> This is with PostgreSQL 10.4.
> How to reproduce :
> postgres@smadev:~% psql
> psql (10.4)
> Type "help" for help.
> 
> Alter the role for search path :
> dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
> ALTER ROLE
> dynacom=#
> 
> Verify :
> postgres@smadev:~% psql -U amura3
> Password for user amura3:
> psql (10.4)
> Type "help" for help.
> 
> dynacom=> show search_path ;
>          search_path
> ---------------------------
>   $user, amuragents, public
> (1 row)
> 
> dynacom=>
> 
> pg_dumpall's output :
> ALTER ROLE amura3 SET search_path TO $user, amuragents, public;
> 
> psql -f pg_dumpall_out.sql :
> dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public;
> ERROR:  syntax error at or near "$"

Well the above does not have $user double quoted. Was that hand entered 
or was that like that in the pg_dumpall_out.sql file?

> 
> Is this a bug or am I missing something ?

I could not replicate:

create role sp_test;

alter role sp_test SET search_path TO "$user", public;

pg_dumpall -g -U postgres > sp_test.sql

In sp_test.sql

CREATE ROLE sp_test;

ALTER ROLE sp_test SET search_path TO "$user", public;

drop role sp_test ;

psql -d test -U postgres  -f sp_test.sql


\du

sp_test        | Cannot login 
    | {}


\drds
                  List of settings
   Role   | Database |          Settings
---------+----------+-----------------------------
  sp_test |          | search_path="$user", public


> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 30/07/2018 16:23, Adrian Klaver wrote:
> On 07/30/2018 05:57 AM, Achilleas Mantzios wrote:
>> This is with PostgreSQL 10.4.
>> How to reproduce :
>> postgres@smadev:~% psql
>> psql (10.4)
>> Type "help" for help.
>>
>> Alter the role for search path :
>> dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
>> ALTER ROLE
>> dynacom=#
>>
>> Verify :
>> postgres@smadev:~% psql -U amura3
>> Password for user amura3:
>> psql (10.4)
>> Type "help" for help.
>>
>> dynacom=> show search_path ;
>>          search_path
>> ---------------------------
>>   $user, amuragents, public
>> (1 row)
>>
>> dynacom=>
>>
>> pg_dumpall's output :
>> ALTER ROLE amura3 SET search_path TO $user, amuragents, public;
>>
>> psql -f pg_dumpall_out.sql :
>> dynacom=# ALTER ROLE amura3 SET search_path TO $user, amuragents, public;
>> ERROR:  syntax error at or near "$"
>
> Well the above does not have $user double quoted. Was that hand entered or was that like that in the
pg_dumpall_out.sqlfile?
 
>
>>
>> Is this a bug or am I missing something ?
>
> I could not replicate:

You are absolutely right, I apologize for the noise :(
I had forgotten that we had to tweak src/backend/utils/misc/guc.c in order for search_path to work with pgbouncer in
transactionmode.
 

--- /usr/local/src/postgresql-10.4/src/backend/utils/misc/guc.c.orig 2018-05-07 23:51:40.000000000 +0300
+++ /usr/local/src/postgresql-10.4/src/backend/utils/misc/guc.c 2018-06-14 16:07:29.832476000 +0300
@@ -3266,7 +3266,7 @@
                 {"search_path", PGC_USERSET, CLIENT_CONN_STATEMENT,
                         gettext_noop("Sets the schema search order for names that are not schema-qualified."),
                         NULL,
-                       GUC_LIST_INPUT | GUC_LIST_QUOTE
+                       GUC_LIST_INPUT | GUC_REPORT
                 },
                 &namespace_search_path,
                 "\"$user\", public",

With GUC_LIST_QUOTE it kept re-quoting and it broke the app. Without GUC_REPORT it was losing the search_path.

>
> create role sp_test;
>
> alter role sp_test SET search_path TO "$user", public;
>
> pg_dumpall -g -U postgres > sp_test.sql
>
> In sp_test.sql
>
> CREATE ROLE sp_test;
>
> ALTER ROLE sp_test SET search_path TO "$user", public;
>
> drop role sp_test ;
>
> psql -d test -U postgres  -f sp_test.sql
>
>
> \du
>
> sp_test        | Cannot login    | {}
>
>
> \drds
>                  List of settings
>   Role   | Database |          Settings
> ---------+----------+-----------------------------
>  sp_test |          | search_path="$user", public
>
>
>>
>
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> This is with PostgreSQL 10.4.
> How to reproduce :

> dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
> ALTER ROLE
> ...
> dynacom=> show search_path ;
>          search_path
> ---------------------------
>   $user, amuragents, public
> (1 row)

Hm, that's not happening for me:

regression=# alter user joe set search_path to "$user", public;
...
regression=> show search_path ;
   search_path   
-----------------
 "$user", public
(1 row)

and then pg_dumpall produces

ALTER ROLE joe SET search_path TO "$user", public;

There was a relevant bug fix in March (commit 742869946) ...
are you certain this is a 10.4 server, and not 10.3 or older?

            regards, tom lane

On 30/07/2018 16:51, Tom Lane wrote:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
>> This is with PostgreSQL 10.4.
>> How to reproduce :
>> dynacom=# ALTER ROLE amura3 SET search_path TO "$user", amuragents, public;
>> ALTER ROLE
>> ...
>> dynacom=> show search_path ;
>>           search_path
>> ---------------------------
>>    $user, amuragents, public
>> (1 row)
> Hm, that's not happening for me:
You (and Adrian) are right. This is due to our own tweaking (which I had forgotten). Sorry for the false alarm.
>
> regression=# alter user joe set search_path to "$user", public;
> ...
> regression=> show search_path ;
>     search_path
> -----------------
>   "$user", public
> (1 row)
>
> and then pg_dumpall produces
>
> ALTER ROLE joe SET search_path TO "$user", public;
>
> There was a relevant bug fix in March (commit 742869946) ...
> are you certain this is a 10.4 server, and not 10.3 or older?
>
>             regards, tom lane
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> On 30/07/2018 16:51, Tom Lane wrote:
>> Hm, that's not happening for me:

> You (and Adrian) are right. This is due to our own tweaking (which I had forgotten). Sorry for the false alarm.

It looks like your hack was to work around the bug that was fixed
properly in 742869946.  You should be able to undo that now ...

            regards, tom lane

On 30/07/2018 17:00, Tom Lane wrote:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
>> On 30/07/2018 16:51, Tom Lane wrote:
>>> Hm, that's not happening for me:
>> You (and Adrian) are right. This is due to our own tweaking (which I had forgotten). Sorry for the false alarm.
> It looks like your hack was to work around the bug that was fixed
> properly in 742869946.  You should be able to undo that now ...
Hello Tom,
On pgsql 10.4 , I reverted (undid) this patch/hack :

root@smadev:/usr/local/src/postgresql-10.4# diff -u ./src/backend/utils/misc/guc.c ./src/backend/utils/misc/guc.c.hack
--- ./src/backend/utils/misc/guc.c      2018-08-01 16:22:30.901629000 +0300
+++ ./src/backend/utils/misc/guc.c.hack 2018-08-01 15:45:15.893348000 +0300
@@ -3266,7 +3266,7 @@
                 {"search_path", PGC_USERSET, CLIENT_CONN_STATEMENT,
                         gettext_noop("Sets the schema search order for names that are not schema-qualified."),
                         NULL,
-                       GUC_LIST_INPUT | GUC_LIST_QUOTE
+                       GUC_LIST_INPUT | GUC_REPORT
                 },
                 &namespace_search_path,
                 "\"$user\", public",

And (i.e. with stock 10.4) I tested with pgbouncer again in transaction mode, and still, (i.e. without GUC_REPORT) ,
pgbouncerclient does not get from server and does not store the search path after 
 
the first server connection, and therefore, after server disconnects , the second time the client tries to connect it
doesnot set the search_path .
 
I tested with :
GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_REPORT
as well, but then I get the re-quote problem I was telling about :
At first connection the search path is set correctly : bdynacom,epaybdynacom,epay,"$user", public
but after server disconnection and re-connection the search path becomes : "bdynacom,epaybdynacom,epay,""$user"",
public"
which is wrong.

So in order to retain all the benefits of transaction mode in pgbouncer I had to re-apply the first patch/hack, at the
expenseof the nuisance at pg_dumpall and search paths.
 


>
>             regards, tom lane
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> On 30/07/2018 17:00, Tom Lane wrote:
>> It looks like your hack was to work around the bug that was fixed
>> properly in 742869946.  You should be able to undo that now ...

> On pgsql 10.4 , I reverted (undid) this patch/hack :

> -                       GUC_LIST_INPUT | GUC_LIST_QUOTE
> +                       GUC_LIST_INPUT | GUC_REPORT

What I meant was you should be able to undo the removal of GUC_LIST_QUOTE.
The other change is independent, and it seems to be about working around
a bug in pgbouncer.  You ought to discuss that with the pgbouncer folk.

> I tested with :
> GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_REPORT
> as well, but then I get the re-quote problem I was telling about :
> At first connection the search path is set correctly : bdynacom,epaybdynacom,epay,"$user", public
> but after server disconnection and re-connection the search path becomes : "bdynacom,epaybdynacom,epay,""$user"",
public"
> which is wrong.

This seems like it might be a bug in pgbouncer as well; or at least
an oversight, in that it's not expecting any GUC_REPORT parameters
to need GUC_LIST_QUOTE treatment.

            regards, tom lane