Re: SET ROLE and search_path

Rob Sargent <robjsargent@xxxxxxxxx> · Wed, 20 May 2020 10:51:36 -0600



    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.