On 5/20/20 10:36 AM, Patrick FICHE wrote:
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
Does your role definition assign a search_path?
create role comp1;
alter role comp1 set search_path=comp1,base,public;
Every re-use of the postgres connection must start by resetting the
search_path. I find it easier to log in as comp1. Some jiggery-pokery
involved in passwords but no one in company #1 needs to know the user
name let alone password.
