postgraphile-plugin-connection-filter

This plugin adds a filter argument for advanced filtering of list types.

Warning: Use of this plugin (particularly with the default options) may make it astoundingly trivial for a malicious actor (or a well-intentioned application that generates complex GraphQL queries) to overwhelm your database with expensive queries. See the Performance and Security section below for details.

Performance and Security

By default, this plugin:

• Exposes a large number of filter operators, including some that can perform expensive pattern matching.
• Allows filtering on computed columns, which can result in expensive operations.
• Allows filtering on functions that return setof, which can result in expensive operations.
• Allows filtering on List fields (Postgres arrays), which can result in expensive operations.

To protect your server, you can:

• Use the connectionFilterAllowedFieldTypes and connectionFilterAllowedOperators options to limit the filterable fields and operators exposed through GraphQL.
• Set connectionFilterComputedColumns: false to prevent filtering on computed columns.
• Set connectionFilterSetofFunctions: false to prevent filtering on functions that return setof.
• Set connectionFilterLists: false to prevent filtering on List fields (Postgres arrays).

Also see the Production Considerations page of the official PostGraphile docs, which discusses query whitelisting.

Getting Started

CLI

postgraphile --append-plugins `pwd`/path/to/this/plugin/index.js

Library

const express = require("express");
const { postgraphile } = require("postgraphile");
const PostGraphileConnectionFilterPlugin = require("postgraphile-plugin-connection-filter");

const app = express();

app.use(
  postgraphile(pgConfig, schema, {
    graphiql: true,
    appendPlugins: [PostGraphileConnectionFilterPlugin],
  })
);

app.listen(5000);

Operators

The following filter operators are exposed by default:

Logical Operators

| Postgres operator | GraphQL field | GraphQL field type | --- | --- | --- | | AND | and | List | | OR | or | List | | NOT | not | Object |

Comparison Operators

| Postgres expression | GraphQL field | GraphQL field type | | --- | --- | --- | | IS [NOT] NULL | isNull | Boolean | | = | equalTo | Scalar/Enum | | <> | notEqualTo | Scalar/Enum | | IS DISTINCT FROM | distinctFrom | Scalar/Enum | | IS NOT DISTINCT FROM | notDistinctFrom | Scalar/Enum | | < | lessThan | Scalar/Enum | | <= | lessThanOrEqualTo | Scalar/Enum | | > | greaterThan | Scalar/Enum | | >= | greaterThanOrEqualTo | Scalar/Enum | | IN | in | List | | NOT IN | notIn | List | | LIKE '%...%' | includes | String | | NOT LIKE '%...%' | notIncludes | String | | ILIKE '%...%' | includesInsensitive | String | | NOT ILIKE '%...%' | notIncludesInsensitive | String | | LIKE '...%' | startsWith | String | | NOT LIKE '...%' | notStartsWith | String | | ILIKE '...%' | startsWithInsensitive | String | | NOT ILIKE '...%' | notStartsWithInsensitive | String | | LIKE '%...' | endsWith | String | | NOT LIKE '%...' | notEndsWith | String | | ILIKE '%...' | endsWithInsensitive | String | | NOT ILIKE '%...' | notEndsWithInsensitive | String | | LIKE '...' | like | String | | NOT LIKE '...' | notLike | String | | ILIKE '...' | likeInsensitive | String | | NOT ILIKE '...' | notLikeInsensitive | String | | SIMILAR TO '...' | similarTo | String | | NOT SIMILAR TO '...' | notSimilarTo | String | | @> | contains | JSON | | <@ | containedBy | JSON | | << | inetContainedBy | InternetAddress | | <<= | inetContainedByOrEquals | InternetAddress | | >> | inetContains | InternetAddress | | >>= | inetContainsOrEquals | InternetAddress | | && | inetContainsOrIsContainedBy | InternetAddress |

List Comparison Operators

| Postgres expression | GraphQL field | GraphQL field type | | --- | --- | --- | | IS [NOT] NULL | isNull | Boolean | | = | equalTo | List | | <> | notEqualTo | List | | IS DISTINCT FROM | distinctFrom | List | | IS NOT DISTINCT FROM | notDistinctFrom | List | | < | lessThan | List | | <= | lessThanOrEqualTo | List | | > | greaterThan | List | | >= | greaterThanOrEqualTo | List | | = ANY() | anyEqualTo | Scalar/Enum | | <> ANY() | anyNotEqualTo | Scalar/Enum | | > ANY() | anyLessThan | Scalar/Enum | | >= ANY() | anyLessThanOrEqualTo | Scalar/Enum | | < ANY() | anyGreaterThan | Scalar/Enum | | <= ANY() | anyGreaterThanOrEqualTo | Scalar/Enum |

Examples

query {
  allPosts(filter: {
    body: { isNull: true }
  }) {
    ...
  }
}

query {
  allPosts(filter: {
    body: { isNull: false }
  }) {
    ...
  }
}

query {
  allPosts(filter: {
    createdAt: { greaterThan: "2016-01-01" }
  }) {
    ...
  }
}

query {
  allPosts(filter: {
    authorId: { in: [1, 2] }
  }) {
    ...
  }
}

Note: Objects with multiple keys are interpreted with an implicit AND between the conditions.

query {
  allPosts(filter: {
    body: { isNull: false },
    createdAt: { greaterThan: "2016-01-01" }
  }) {
    ...
  }
}

query {
  allPosts(filter: {
    or: [
      { authorId: { equalTo: 6 } },
      { createdAt: { greaterThan: "2016-01-01" } }
    ]
  }) {
    ...
  }
}

query {
  allPosts(filter: {
    not: {
      or: [
        { authorId: { equalTo: 6 } },
        { createdAt: { greaterThan: "2016-01-01" } }
      ]
    }
  }) {
    ...
  }
}

query {
  allPeople(filter: {
    firstName: { startsWith:"John" }
  }) {
    nodes {
      firstName
      lastName
      postsByAuthorId(filter: {
        createdAt: { greaterThan: "2016-01-01" }
      }) {
        nodes {
          ...
        }
      }
    }
  }
}

Requires connectionFilterRelations: true

query {
  allPosts(filter: {
    personByAuthorId: { createdAt: { greaterThan: "2018-01-01" } }
  }) {
    ...
  }
}

Requires connectionFilterRelations: true

query {
  allPeople(filter: {
    accountByPersonId: { status: { equalTo: ACTIVE } }
  }) {
    ...
  }
}

Not supported yet. #26

For additional examples, see the tests.

Plugin Options

When using PostGraphile as a library, the following plugin options can be passed via graphileBuildOptions:

Restrict filtering to specific operators:

postgraphile(pgConfig, schema, {
  graphileBuildOptions: {
    connectionFilterAllowedOperators: [
      "isNull",
      "equalTo",
      "notEqualTo",
      "distinctFrom",
      "notDistinctFrom",
      "lessThan",
      "lessThanOrEqualTo",
      "greaterThan",
      "greaterThanOrEqualTo",
      "in",
      "notIn",
    ],
  },
})

For a full list of the available operators, see the Comparison Operators table above.

Restrict filtering to specific field types:

postgraphile(pgConfig, schema, {
  graphileBuildOptions: {
    connectionFilterAllowedFieldTypes: ["String", "Int"],
  },
})

The available field types will depend on your database schema.

Enable/disable filtering by computed columns:

postgraphile(pgConfig, schema, {
  graphileBuildOptions: {
    connectionFilterComputedColumns: false, // default: true
  },
})

Enable/disable filtering on List fields:

postgraphile(pgConfig, schema, {
  graphileBuildOptions: {
    connectionFilterLists: false, // default: true
  },
})

Use alternative names (e.g. eq, ne) for operators:

postgraphile(pgConfig, schema, {
  graphileBuildOptions: {
    connectionFilterOperatorNames: {
      equalTo: "eq",
      notEqualTo: "ne",
    },
  },
})

Enable/disable filtering on related fields:

postgraphile(pgConfig, schema, {
  graphileBuildOptions: {
    connectionFilterRelations: true, // default: false
  },
})

Enable/disable filtering on functions that return setof:

postgraphile(pgConfig, schema, {
  graphileBuildOptions: {
    connectionFilterSetofFunctions: false, // default: true
  },
})

Development

To establish a test environment, create an empty Postgres database (e.g. graphile_build_test) and set a TEST_DATABASE_URL environment variable with your connection string (e.g. postgres://localhost:5432/graphile_build_test). Ensure that psql is installed locally and then run:

yarn
npm run test