Search Postgresql Archives

Re: SET ROLE and search_path

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux