<p>I'm thinking on having a Shared Database with an individual schema per customer(1 customer many users). This way I still deal with only one connection pool, but I need to deal with the schema "switching" and I dunno how.</p>
<hr/>**评论：**<br/><br/>barsonme: <pre><p>Yeah. We use Postgres. We set up separate schemas for each customer. Each customer has their own unique global "domain" identifier that doubles as their schema name.</p>
<p>All the SQL queries are fully prefixed with the schema.</p></pre>mcouturier: <pre><p>I guess the question is, once you specified a schema name when you initialized your connection pool, how do you change it for a specific connection of that pool when a customer request something? </p>
<p>Edit: schema, not database </p></pre>barsonme: <pre><p>we don't. in pg (and other databases) you can just write:</p>
<p><code>select * from schema.table where ...</code></p>
<p><code>update schema.table set foo = bar ...</code></p>
<p>for tenant-specific information.</p>
<p>If we need to do something fancy or weird we can always create a new connection, but we haven't needed that so far.</p></pre>residentbio: <pre><p>I was thinking on keeping a Global Schema, where I would have table to which I would add an entry per schema. Maybe the auth could be in the global state, or maybe a per domain log in. </p>
<p>The point is, say I get a new customer, with a CLI I'm just starting I would add an entry to my customer table in the global schema, and also I would create a new schema. What do you think?</p></pre>barsonme: <pre><p>Yeah, we have a master table with all the tenant "domains" (i.e., the schema names) and create a new schema with a dedicated DB connection that has the necessary permissions to create a schema.</p>
<p>Then whenever we construct SQL queries that need tenant-specific information, we just qualify all the table names. So, <code>select * from schema.table where ...</code></p>
<p>We store sessions on the backend, so when somebody signs in their "domain" is stored in the session and we just extract it for each SQL query we construct.</p></pre>residentbio: <pre><p>Thanks, then I'm on the right track. Not using sessions, I have an JWT implementation, but I rather not put the schemas name on the payload, just to keep my mind at ease. Maybe I could put it at context level. We'll see.</p></pre>barsonme: <pre><p>don't even get me started about JWT. :-)</p>
<p><a href="https://twitter.com/CiPHPerCoder/status/841539451545407488" rel="nofollow">https://twitter.com/CiPHPerCoder/status/841539451545407488</a></p>
<p><a href="https://twitter.com/tqbf/status/841407679016927233" rel="nofollow">https://twitter.com/tqbf/status/841407679016927233</a></p>
<p><a href="http://cryto.net/%7Ejoepie91/blog/2016/06/13/stop-using-jwt-for-sessions/" rel="nofollow">http://cryto.net/~joepie91/blog/2016/06/13/stop-using-jwt-for-sessions/</a></p></pre>residentbio: <pre><p>Oh I know haha, I mean I was sold on, look it is easier to deal with on mobile devices than sessions. But the are some red flags here and there that by the time I'm ready to ship, I may switch back to sessions. </p></pre>CaptaincCodeman: <pre><p>You should checkout AppEngine and its NoSQL datastore - the namespace features makes it super-easy to do a multi-tenanted system with data divided up, but all kept in one service. That way your app scaling is for the service as a whole, not per-client. Switch tenant based on on domain name, auth id or whatever.</p>
<p>This was more to test-out the front-end (Polymer) generation but might give you some ideas:</p>
<p><a href="https://github.com/CaptainCodeman/go-poly-tenant" rel="nofollow">https://github.com/CaptainCodeman/go-poly-tenant</a></p></pre>SpNg: <pre><p>Currently building one on top of Postgres. Check out Row Security which was release in 9.5. </p></pre>barsonme: <pre><p>I asked about that in the psql irc channel and the reaction I got was "don't"</p></pre>SpNg: <pre><p>So there is a lot to think about when trying to make an app multi-tenant. Do you have separate databases, separate schemas, or add a new column with the "tenant_id"? I read a lot of articles when trying to figure out what approach to go with, and ultimately there is no perfect answer just a bunch of tradeoffs. I found <a href="https://msdn.microsoft.com/en-us/library/aa479086.aspx" rel="nofollow">this article</a> from Microsoft to have a great overview of the various tradeoffs between the different approaches.</p>
<p>When building our app, we worked hard to avoid the requirement of WHERE tenant_id='foo' for all our SQL queries since this is error prone and pushes the responsibility to the application when it would be ideal to have it at the data layer. We also wanted to leverage a connection pool which is more difficult to do if you have to switch between schema prefixes, databases or database roles. We ultimately settled on using PG's Row Security and a strategy outlined in <a href="https://blog.2ndquadrant.com/application-users-vs-row-level-security/" rel="nofollow">this blog</a> from 2ndquadrant which establishes a session variable for the tenant_id. This allows us to segregate data by tenant on a per row basis so we can avoid using WHERE tenant_id='foo' and use our connection pool. Overall it's working great though there are some interesting problems to still solve (i.e. can your users be associated with more than one tenant?).</p></pre>barsonme: <pre><p>how do you manage your connection pool?</p></pre>SpNg: <pre><p>We use the connection pool from <a href="https://godoc.org/github.com/jackc/pgx#ConnPool" rel="nofollow">pgx</a>.</p></pre>luckyleprechaun98: <pre><p>Why do you want to do it that way?</p></pre>benbjohnson: <pre><p>Probably for security reasons. If you have separate schemas then you largely remove the chance of accidentally forgetting a <code>WHERE</code> clause that limits the data to one customer.</p>
<p>I have a multi-tenant app which uses BoltDB and each account is separated into a different top-level bucket. The bucket is looked up based on subdomain so there's really no way I can think of that I'll accidentally release data to the wrong account.</p></pre>luckyleprechaun98: <pre><p>I guess that makes some sense. I always thought it would be difficult to build a parameterized query with a dynamic schema name vs just using <code>WHERE account=$1</code>. </p></pre>residentbio: <pre><p>Yes, since my app is designed for local enterprises, I would need a good level of insolation of the data per customer. </p></pre>maus80: <pre><p>In my experience you want to have a schema per customer and a mapping of the schemas on the database servers (which schema lives where?) and store that mapping (and the connection strings) in the shared data. Then you want to either replicate the shared data (in its own schema) to all database servers or make a separate connection for your shared data. That is what I would do if I need to scale and secure the system.</p></pre>
