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.
would like the Web Application to connect with a single
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….
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.
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.
search_path contains “$user”, does it refer to session_user
or current_user ?
Thanks for any
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.
* Tom Lane ([hidden email]) wrote:
> Rob Sargent <[hidden email]> 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.