SET ROLE and search_path

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.

I have this exact setup, and I use roles / schema names that match so the
$user var works with the search path when I set role as my application user.

When search_path contains “$user”, does it refer to session_user or

current_user ?
It uses current_user, not session_user. Works perfectly with set_role for
me.

On 5/20/20 1:28 PM, Adam Brusselback wrote:

I have this exact setup, and I use roles / schema names that match so
the $user var works with the search path when I set role as my
application user.

When search_path contains “$user”, does it refer to session_user or

current_user ?
It uses current_user, not session_user. Works perfectly with set_role
for me.

Am I (again) alone in finding this a bit hokey?  That a user name just
happens to be a schema name ...

Thanks a lot
I will try it again. I probably missed something in my configuration.

Téléchargez Outlook pour iOS<https://aka.ms/o0ukef&gt;
________________________________
De : Adam Brusselback <adambrusselback@gmail.com>
Envoyé : Wednesday, May 20, 2020 9:28:21 PM
À : Patrick FICHE <Patrick.Fiche@aqsacom.com>
Cc : pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Objet : Re: SET ROLE and search_path

I have this exact setup, and I use roles / schema names that match so the $user var works with the search path when I set role as my application user.

When search_path contains “$user”, does it refer to session_user or current_user ?

It uses current_user, not session_user. Works perfectly with set_role for me.

Rob Sargent <robjsargent@gmail.com> writes:

Am I (again) alone in finding this a bit hokey?  That a user name just
happens to be a schema name ...

That's actually strongly encouraged by the SQL spec, if memory serves.

regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Rob Sargent <robjsargent@gmail.com> writes:

Am I (again) alone in finding this a bit hokey?  That a user name just
happens to be a schema name ...

That's actually strongly encouraged by the SQL spec, if memory serves.

... and all-but-required by some other database systems. Doesn't make
it a good idea tho, imv.

Thanks,

Stephen