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

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