SET ROLE and search_path

From: Patrick FICHE <Patrick(dot)Fiche(at)aqsacom(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: SET ROLE and search_path
Date: 2020-05-20 16:36:30
Message-ID: AM6PR05MB5287F42DF5C317F2BDA42E43EFB60@AM6PR05MB5287.eurprd05.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2020-05-20 16:37:23 Re: Huge tables, trying to delete OID's taking 6+hours per table
Previous Message Tory M Blue 2020-05-20 16:22:01 Re: Huge tables, trying to delete OID's taking 6+hours per table