Hi, I'm trying to implement a PostgreSQL multi-tenant database that will be accessed by a Web Application. The users that will login will belong to different companies and a schema was created in the database for each company. However, I would like the Web Application to connect with a single Postgres login. Let's say that I have 2 companies : comp1 and comp2 with their respective schema (comp1 / comp2). Then, the web application connects with web_app login which has been granted comp1 and comp2 roles.... Depending on the user connecting to the application, I would like to use SET ROLE comp1 / SET ROLE comp2 in order to get access to the relevant data only. However, it seems that SET ROLE does not change the search_path (which is different for comp1 and comp2).
Is there any way to change the search_path in an easy way (in a procedure) after SET ROLE has been executed. Am I missing anything with SET ROLE. When search_path contains "$user", does it refer to session_user or current_user ? Thanks for any advice Patrick
