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.sql file?
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 transaction mode.
--- /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
