Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZhhXM-0008Dy-LC for pgsql-admin@arkaria.postgresql.org; Thu, 01 Oct 2015 17:20:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZhhXM-0001Ri-0f for pgsql-admin@arkaria.postgresql.org; Thu, 01 Oct 2015 17:20:56 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZhhVh-0007rm-R7 for pgsql-admin@postgresql.org; Thu, 01 Oct 2015 17:19:13 +0000 Received: from diminuendo-3.evaristesys.com ([208.52.173.21]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZhhVe-00066G-TO for pgsql-admin@postgresql.org; Thu, 01 Oct 2015 17:19:12 +0000 Received: from [63.135.160.86] (helo=presto-1-2.evaristesys.com) by diminuendo-3.evaristesys.com with esmtp (Exim 4.72) (envelope-from ) id 1ZhhVd-0002XJ-HW for pgsql-admin@postgresql.org; Thu, 01 Oct 2015 13:19:09 -0400 Received: from [172.30.106.254] by presto-1-2.evaristesys.com with esmtp (Exim 4.80) (envelope-from ) id 1ZhhVd-0003NE-G4 for pgsql-admin@postgresql.org; Thu, 01 Oct 2015 13:19:09 -0400 Subject: Re: "Dynamic routing" to different databases To: pgsql-admin@postgresql.org References: <560BFE58.9030201@evaristesys.com> <560C6F2D.1090107@evaristesys.com> <560D4772.4020002@evaristesys.com> From: Alex Balashov Message-ID: <560D6B0D.1010806@evaristesys.com> Date: Thu, 1 Oct 2015 13:19:09 -0400 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:38.0) Gecko/20100101 Thunderbird/38.2.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Brad, On 10/01/2015 12:48 PM, Nicholson, Brad (Toronto, ON, CA) wrote: > You can set the default search path on a per-role/user basis. What > you could do is map a DB user (or set of users) to a specific schema, > then set that users search path for the appropriate schema and the > queries will automatically get routed to the appropriate schema. > Segmenting your different applications user also allows you to use > the role system to restrict access to the data between applications. I'd love to, but unfortunately the service consuming the database is very, very strictly limited to one connection string, which entails one set of credentials, one server and one database, and more connections cannot be created as-needed at runtime without restarting the service, which in our industry is not acceptable. So, unfortunately I cannot avail myself of such an elegant solution; instead, I must access all the schemas via one role. -- Alex -- Alex Balashov | Principal | Evariste Systems LLC 303 Perimeter Center North, Suite 300 Atlanta, GA 30346 United States Tel: +1-800-250-5920 (toll-free) / +1-678-954-0671 (direct) Web: http://www.evaristesys.com/, http://www.csrpswitch.com/ -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin