PostgreSQL: How do I set the search_path at the user level?

Question

I want to set the default search_path to something other than the "public" schema. But I only want to do this for a particular user. How can I accomplish this?

score 70 · Accepted Answer · edited Sep 27 '19 at 13:24

70

I found the answer:

ALTER ROLE username SET search_path = schema1,schema2,schema3,etc;

edited Sep 27 '19 at 13:24

Mark Rotteveel

100,966
191
140
197

answered Jun 01 '10 at 17:26

Jin Kim

16,562
18
60
86

11

ALTER USER has been deprecated in favor of ALTER ROLE – sevzas Aug 07 '15 at 18:42
2

FYI, to make that setting specific to the user + database, use `alter role username IN DATABASE DATABASENAME set search_path = whatever` – Chris Johnson Oct 04 '17 at 20:52
I'm not sure if it's something I did wrong, but the '=' sign didn't seem to work for me in PSQL. When I used the keyword 'to' instead it seemed to work. – Damien Sawyer Jun 19 '18 at 02:56
This post was useful. http://www.postgresonline.com/journal/archives/279-Schema-and-search_path-surprises.html – Damien Sawyer Jun 19 '18 at 03:01
do i need to restart postgres service for this to kick in? – lollerskates Aug 09 '19 at 21:22
log out / log in from PostgreSQL – Ludovic Kuty Aug 27 '20 at 12:27

PostgreSQL: How do I set the search_path at the user level?

1 Answers1

Linked