Re: ALTER ROLE SET search_path produced by pg_dumpall gives : ERROR:syntax error at or near "$" .

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