From: | Rob Sargent <robjsargent(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: SET ROLE and search_path |
Date: | 2020-05-20 16:51:36 |
Message-ID: | 47761b8b-d892-943b-1bc9-29bde61a2e62@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | postgann2020 s | 2020-05-20 18:20:53 | Suggestion to improve query performance. |
Previous Message | Ron | 2020-05-20 16:37:23 | Re: Huge tables, trying to delete OID's taking 6+hours per table |