Production considerations

When it comes time to deploy your PostGraphile application to production, there's a few things you'll want to think about including topics such as logging, security and stability. This article outlines some of the issues you might face, and how to solve them.

Database Access Considerations

PostGraphile is just a node app / middleware, so you can deploy it to any number of places: Heroku, Now.sh, a VM, a container such as Docker, or of course onto bare metal. Typically you won't run PostGraphile on the same hardware/container/VM as the database, so PostGraphile needs to be able to connect to your database without you putting your DB at risk.

A standard way of doing this is to put the DB behind a firewall. However, if you're using a system like Heroku or Now.sh you probably can't do that, so instead you must make your DB accessible to the internet. When doing so here are a few things we recommend:

1. Only allow connections over SSL (force_ssl setting)
2. Use a secure username (not root, admin, postgres, etc which are all fairly commonly used)
3. Use a super secure password; you can use a command like this to generate one: openssl rand -base64 30 | tr '+/' '-_'
4. Use a non-standard port for your PostgreSQL server if you can (pick a random port number)
5. Use a hard-to-guess hostname, and never reveal the hostname to anyone who doesn't need to know it
6. If possible, limit the IP addresses that can connect to your DB to be just those of your hosting provider.

Heroku have some instructions on making RDS available for use under Heroku which should also work for Now.sh or any other service: https://devcenter.heroku.com/articles/amazon-rds

By default PgRBACPlugin is enabled which inspects the RBAC (GRANT / REVOKE) privileges in the database and reflects these in your GraphQL schema. As is GraphQL best practices, this still only results in one GraphQL schema (not one per user), so it takes the user account you connect to PostgreSQL with (from your connection string) and walks all the roles that this user can become within the database, and uses the union of all these permissions. Using this plugin is recommended, as it results in a much leaner schema that doesn't contain functionality that you can't actually use. You can, however, disable it via disablePlugins: ['PgRBACPlugin'].

Database Latency

PostGraphile needs to issue queries to your database. For a transaction this might be multiple statements (begin, set local ..., select ..., commit) and each of these requires a roundtrip to the database. Thus the latency between your database and your PostGraphile server can easily be multiplied. If your database is in London but your PostGraphile server is in Tokyo then the ~300ms roundtrip time times 4 is a base latency that users will see of 1.2 seconds, no matter how fast your queries actually are.

Run PostGraphile in the same city as your database, preferably in the same data centre.

Grafast Considerations

Since PostGraphile uses Grafast under the hood, you should also familiarize yourself with Grafast's production considerations.

Common Middleware Considerations

In a production app, you typically want to add a few common enhancements, e.g.

• Logging
• Gzip or similar compression
• Security protections
• Rate limiting

Since there's already a lot of options and opinions in this space, and they're not directly related to the problem of serving GraphQL from your PostgreSQL database, PostGraphile does not include these things by default. We recommend that you use something like Express middlewares to implement these common requirements. This is why we recommend using PostGraphile as a library for production usage.

Picking the Express (or similar) middlewares that work for you is beyond the scope of this article; but you should ensure that they're installed before you add your PostGraphile server to the middleware stack.

Denial of Service Considerations

When you run PostGraphile in production you'll want to ensure that people cannot easily trigger denial of service (DOS) attacks against you. Due to the nature of GraphQL it's easy to construct a small query that could be very expensive for the server to run, for example:

allUsers {
  nodes {
    postsByAuthorId {
      nodes {
        commentsByPostId {
          userByAuthorId {
            postsByAuthorId {
              nodes {
                commentsByPostId {
                  userByAuthorId {
                    postsByAuthorId {
                      nodes {
                        commentsByPostId {
                          userByAuthorId {
                            id
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

There's lots of techniques for protecting your server from these kinds of queries; a great introduction to this subject is this blog post from Apollo.

These techniques should be used in conjunction with common HTTP protection methods such as rate limiting which are typically better implemented at a separate layer; for example you could use Cloudflare rate limiting for this, or an Express.js middleware.

Statement Timeout

One simple solution to this issue is to place a timeout on the database operations via the statement_timeout PostgreSQL setting. This will halt any query that takes longer than the specified number of milliseconds to execute. This can still enable nefarious actors to have your database work hard for that duration, but it does prevent these malicious queries from running for an extended period, reducing the ease of a DoS (Denial of Service) attack. This solution is a good way to catch anything that may have slipped through the cracks of your other defences, or just to get you up and running while you work on more robust/lower level solutions, but when you expose your GraphQL endpoint to the world it's better to cut things off at the source before a query is ever sent to the database using one or more of the techniques detailed below.

Currently you can set this on a per-transaction basis using the pgSettings functionality in PostGraphile library mode, e.g.:

graphile.config.mjs

export default {
  // ...
  grafast: {
    context(requestContext, args) {
      return {
        statement_timeout: "3000",
        // ...
      };
    },
  },
};

To be more efficient (only setting this once per database connection rather than once per GraphQL request) you can also set this up on a per connection basis if you pass a correctly configured pg.Pool instance to PostGraphile directly, e.g.:

graphile.config.mjs

import { Pool } from "pg";
import { makePgService } from "postgraphile/adaptors/pg";

/** does nothing */
function noop() {}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
pool.on("error", noop);
pool.on("connect", (client) => {
  client.on("error", noop);
  client.query("SET statement_timeout TO 3000");
});

export default {
  // ...
  pgServices: [
    makePgService({
      pool,
      schemas: ["app_public"],
    }),
  ],
};

Simple: Query Allowlist ("persisted queries" / "persisted operations")

If you do not intend to allow third parties to run arbitrary operations against your API then using persisted operations as a query allowlist is a highly recommended solution to protect your GraphQL endpoint. This technique ensures that only the operations you use in your own applications can be executed on the server, preventing malicious (or merely curious) actors from executing operations which may be more expensive than those you have written.

This technique is suitable for the vast majority of use cases and supports many GraphQL clients, but it does have a few caveats:

• Your API will only accept operations that you've approved, so it's not suitable if you want third parties to run arbitrary custom operations.
• You must be able to generate a unique ID (e.g. a hash) from each operation at build time of your application/web page - your GraphQL operations must be "static". It's important to note this only applies to the operation document itself, the variables can of course change at runtime.
• You must have a way of sharing these static operations from the application build process to the server so that the server will know what operation the ID represents.
• You must be careful not to use variables in dangerous places within your operation; for example if you were to use allUsers(first: $myVar) a malicious attacker could set $myVar to 2147483647 to cause your server to process as much data as possible. Use fixed limits, conditions and orders where possible, even if it means having additional static operations.
• It does not protect you from writing expensive queries yourself; it may be wise to combine this technique with a cost estimation technique to help guide your developers and avoid accidentally writing expensive queries.

PostGraphile has first-party support for persisted operations via the open source @grafserv/persisted plugin; we recommend its use to the vast majority of our users.