Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'd recommend searching for info on the PostgreSQL mailing lists about multi-tenanted design. There's been lots of discussion there, and the answer boils down to "it depends". There are trade-offs every way between guaranteed isolation, performance, and maintainability.</p> <p>A common approach is to use a single database, but one <a href="http://www.postgresql.org/docs/current/static/ddl-schemas.html" rel="noreferrer">schema</a> (namespace) per customer with the same table structure in each schema, plus a shared or common schema for data that's the same across all of them. A PostgreSQL schema is like a MySQL "database" in that you can query across different schema but they're isolated by default. With customer data in separate schema you can use the <a href="http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH" rel="noreferrer"><code>search_path</code></a> setting, usually via <a href="http://www.postgresql.org/docs/current/static/sql-alteruser.html" rel="noreferrer"><code>ALTER USER</code></a> <code>customername SET search_path = 'customerschema, sharedschema'</code> to ensure each customer sees their data and only their data. </p> <p>For additional protection, you should <a href="http://www.postgresql.org/docs/current/static/sql-revoke.html" rel="noreferrer"><code>REVOKE</code></a> <code>ALL FROM SCHEMA customerschema FROM public</code> then <a href="http://www.postgresql.org/docs/current/static/sql-grant.html" rel="noreferrer"><code>GRANT</code></a><code>ALL ON SCHEMA customerschema TO thecustomer</code> so they're the only one with any access to it, doing the same to each of their tables. Your connection pool then can log in with a fixed user account that has <em>no</em> <code>GRANT</code>ed access to any customer schema but has the right to <a href="http://www.postgresql.org/docs/current/static/sql-set-role.html" rel="noreferrer"><code>SET ROLE</code></a> to become any customer. (Do that by giving them membership of each customer role with NOINHERIT set so rights have to be explicitly claimed via <code>SET ROLE</code>). The connection should immediately <code>SET ROLE</code> to the customer it's currently operating as. That'll allow you to avoid the overhead of making new connections for each customer while maintaining strong protection against programmer error leading to access to the wrong customer's data. So long as the pool does a <a href="http://www.postgresql.org/docs/current/interactive/sql-discard.html" rel="noreferrer"><code>DISCARD ALL</code></a> and/or a <a href="http://www.postgresql.org/docs/8.4/interactive/sql-set-role.html" rel="noreferrer"><code>RESET ROLE</code></a> before handing connections out to the next client, that's going to give you very strong isolation without the frustration of individual connections per-user.</p> <p>If your web app environment doesn't have a decent connection pool built-in (say, you're using PHP with persistent connections) then you <em>really</em> need to put a <a href="http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling" rel="noreferrer">good connection pool</a> in place between Pg and the web server anyway, because too many connections to the backend will hurt your performance. <a href="http://pgfoundry.org/projects/pgbouncer/" rel="noreferrer">PgBouncer</a> and <a href="http://www.pgpool.net/" rel="noreferrer">PgPool-II</a> are the best options, and handily can take care of doing the <code>DISCARD ALL</code> and <code>RESET ROLE</code> for you during connection hand-off.</p> <p>The main downside of this approach is the overhead with maintaining that many tables, since your base set of non-shared tables is cloned for each customer. It'll add up as customer numbers grow, to the point where the sheer number of tables to examine during autovacuum runs starts to get expensive and where any operation that scales based on the total number of tables in the DB slows down. This is more of an issue if you're thinking of having many thousands or tens of thousands of customers in the same DB, but I <em>strongly</em> recommend you do some scaling tests with this design using dummy data before committing to it.</p> <p>The ideal approach is likely to be single tables with automatic row-level security controlling tuple visibility, but unfortunately that's something PostgreSQL doesn't have yet. It looks like it's on the way thanks to the SEPostgreSQL work adding suitable infrastructure and APIs, but it's not in 9.1.</p>
    singulars
    1. This table or related slice is empty.
    1. POin postgresql, are partitions or multiple databases more efficient?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCraig Ringer
    1. USCraig Ringer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POin postgresql, are partitions or multiple databases more efficient?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthanks much!! (sorry, have been working with MySQL lately and it made me brain dead.) schema should be an option over multiple databases - in fact, have been using this for other projects. great idea of setting the role after connect. have been using set path, but combination of two is best.
      singulars
      1. PO
      1. UScc young
    2. COYep, setting the role lets you use database-level security without so much pain. It's great.
      singulars
      1. PO
      1. USCraig Ringer
    3. CO... and remember to use a decent connection pool like PgPool-II or PgBouncer if you're using something primitive on the web server side like PHP with persistent connections. There's no need if you're using something like a Java app server that does its own connection pooling in-server.
      singulars
      1. PO
      1. USCraig Ringer
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload