Advanced filtering of Connections in PostGraphile
Branch: master
Clone or download

README.md

Package on npm

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

yarn add postgraphile
yarn add postgraphile-plugin-connection-filter
npx postgraphile --append-plugins postgraphile-plugin-connection-filter

Library

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

const app = express();

app.use(
  postgraphile(process.env.DATABASE_URL, "app_public", {
    appendPlugins: [ConnectionFilterPlugin],
    graphiql: true,
  })
);

app.listen(5000);

Handling null and empty objects

By default, this plugin will throw an error when null literals or empty objects ({}) are included in filter input objects. This prevents queries with ambiguous semantics such as filter: { field: null } and filter: { field: { equalTo: null } } from returning unexpected results. For background on this decision, see https://github.com/graphile-contrib/postgraphile-plugin-connection-filter/issues/58.

To allow null and {} in inputs, use the connectionFilterAllowNullInput and connectionFilterAllowEmptyObjectInput options documented under Plugin Options. Please note that even with connectionFilterAllowNullInput enabled, null is never interpreted as a SQL NULL; fields with null values are simply ignored when resolving the query.

Operators

The following operators are exposed by default, depending on the field type:

Scalars

All of the scalar types generated by PostGraphile (BigFloat, BigInt, BitString, Boolean, Date, Datetime, Float, Int, InternetAddress, Interval, JSON, KeyValueHash, String, Time, UUID) have the following operators:

PostgreSQL GraphQL Description
IS [NOT] NULL isNull: Boolean Is null (if true is specified) or is not null (if false is specified).
= equalTo: T Equal to the specified value.
<> notEqualTo: T Not equal to the specified value.
IS DISTINCT FROM distinctFrom: T Not equal to the specified value, treating null like an ordinary value.
IS NOT DISTINCT FROM notDistinctFrom: T Equal to the specified value, treating null like an ordinary value.
IN (...) in: [T] Included in the specified list.
NOT IN (...) notIn: [T] Not included in the specified list.
< lessThan: T Less than the specified value.
<= lessThanOrEqualTo: T Less than or equal to the specified value.
> greaterThan: T Greater than the specified value.
>= greaterThanOrEqualTo: T Greater than or equal to the specified value.

where T is the type of the field being filtered.

The only exception is KeyValueHash (hstore) fields, for which no sort operators (<, <=, >, >=) are available.

The following types have additional operators:

InternetAddress (inet)

PostgreSQL GraphQL Description
>> contains: InternetAddress Contains the specified internet address.
>>= containsOrEqualTo: InternetAddress Contains or equal to the specified internet address.
<< containedBy: InternetAddress Contained by the specified internet address.
<<= containedByOrEqualTo: InternetAddress Contained by or equal to the specified internet address.
&& containsOrContainedBy: InternetAddress Contains or contained by the specified internet address.

JSON (jsonb)

PostgreSQL GraphQL Description
@> contains: JSON Contains the specified JSON.
? containsKey: String Contains the specified key.
?& containsAllKeys [String] Contains all of the specified keys.
?| containsAnyKeys: [String] Contains any of the specified keys.
<@ containedBy: JSON Contained by the specified JSON.

KeyValueHash (hstore)

PostgreSQL GraphQL Description
@> contains: KeyValueHash Contains the specified KeyValueHash.
? containsKey: String Contains the specified key.
?& containsAllKeys [String] Contains all of the specified keys.
?| containsAnyKeys: [String] Contains any of the specified keys.
<@ containedBy: KeyValueHash Contained by the specified KeyValueHash.

String

PostgreSQL GraphQL Description
LIKE '%...%' includes: String Contains the specified string (case-sensitive).
NOT LIKE '%...%' notIncludes: String Does not contain the specified string (case-sensitive).
ILIKE '%...%' includesInsensitive: String Contains the specified string (case-insensitive).
NOT ILIKE '%...%' notIncludesInsensitive: String Does not contain the specified string (case-insensitive).
LIKE '...%' startsWith: String Starts with the specified string (case-sensitive).
NOT LIKE '...%' notStartsWith: String Does not start with the specified string (case-sensitive).
ILIKE '...%' startsWithInsensitive: String Starts with the specified string (case-insensitive).
NOT ILIKE '...%' notStartsWithInsensitive: String Does not start with the specified string (case-insensitive).
LIKE '%...' endsWith: String Ends with the specified string (case-sensitive).
NOT LIKE '%...' notEndsWith: String Does not end with the specified string (case-sensitive).
ILIKE '%...' endsWithInsensitive: String Ends with the specified string (case-insensitive).
NOT ILIKE '%...' notEndsWithInsensitive: String Does not end with the specified string (case-insensitive).
LIKE '...' like: String Matches the specified pattern (case-sensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters.
NOT LIKE '...' notLike: String Does not match the specified pattern (case-sensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters.
ILIKE '...' likeInsensitive: String Matches the specified pattern (case-insensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters.
NOT ILIKE '...' notLikeInsensitive: String Does not match the specified pattern (case-insensitive). An underscore (_) matches any single character; a percent sign (%) matches any sequence of zero or more characters.
SIMILAR TO '...' similarTo: String Matches the specified pattern using the SQL standard's definition of a regular expression.
NOT SIMILAR TO '...' notSimilarTo: String Does not match the specified pattern using the SQL standard's definition of a regular expression.

Enums

Enum fields have the same operators as scalar fields.

Ranges

Range fields have the same operators as scalar fields, plus the following range operators:

PostgreSQL GraphQL Description
@> contains: T Contains the specified range.
@> containsElement: E Contains the specified value.
<@ containedBy: T Contained by the specified range.
&& overlaps T Overlaps the specified range.
<< strictlyLeftOf: T Strictly left of the specified range.
>> strictlyRightOf: T Strictly right of the specified range.
&< notExtendsRightOf: T Does not extend right of the specified range.
&> notExtendsLeftOf: T Does not extend left of the specified range.
-|- adjacentTo: T Adjacent to the specified range.

where T is the type of the range field being filtered and E is the element type of the range.

Arrays

Array fields have the following operators:

PostgreSQL GraphQL Description
IS [NOT] NULL isNull: Boolean Is null (if true is specified) or is not null (if false is specified).
= equalTo: [T] Equal to the specified value.
<> notEqualTo: [T] Not equal to the specified value.
IS DISTINCT FROM distinctFrom: [T] Not equal to the specified value, treating null like an ordinary value.
IS NOT DISTINCT FROM notDistinctFrom: [T] Equal to the specified value, treating null like an ordinary value.
< lessThan: [T] Less than the specified value.
<= lessThanOrEqualTo: [T] Less than or equal to the specified value.
> greaterThan: [T] Greater than the specified value.
>= greaterThanOrEqualTo: [T] Greater than or equal to the specified value.
@> contains: [T] Contains the specified list of values.
<@ containedBy: [T] Contained by the specified list of values.
&& overlaps: [T] Overlaps the specified list of values.
= ANY(...) anyEqualTo: T Any array item is equal to the specified value.
<> ANY(...) anyNotEqualTo: T Any array item is not equal to the specified value.
> ANY(...) anyLessThan: T Any array item is less than the specified value.
>= ANY(...) anyLessThanOrEqualTo: T Any array item is less than or equal to the specified value.
< ANY(...) anyGreaterThan: T Any array item is greater than the specified value.
<= ANY(...) anyGreaterThanOrEqualTo: T Any array item is greater than or equal to the specified value.

where T is the item type of the array field being filtered.

Logic

Complex logic can be expressed using the following logical operators:

PostgreSQL GraphQL Description
AND and: [T] Checks for all expressions in this list.
OR or: [T] Checks for any expressions in this list.
NOT not: T Negates the expression.

Examples

Null values
query {
  allPosts(filter: {
    body: { isNull: true }
  }) {
    ...
  }
}
Non-null values
query {
  allPosts(filter: {
    body: { isNull: false }
  }) {
    ...
  }
}
Comparison operator with scalar input
query {
  allPosts(filter: {
    createdAt: { greaterThan: "2016-01-01" }
  }) {
    ...
  }
}
Comparison operator with array input
query {
  allPosts(filter: {
    authorId: { in: [1, 2] }
  }) {
    ...
  }
}
Multiple comparison operators

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" }
  }) {
    ...
  }
}
Logical operator
query {
  allPosts(filter: {
    or: [
      { authorId: { equalTo: 6 } },
      { createdAt: { greaterThan: "2016-01-01" } }
    ]
  }) {
    ...
  }
}
Compound logic
query {
  allPosts(filter: {
    not: {
      or: [
        { authorId: { equalTo: 6 } },
        { createdAt: { greaterThan: "2016-01-01" } }
      ]
    }
  }) {
    ...
  }
}
Relations: Nested
query {
  allPeople(filter: {
    firstName: { startsWith:"John" }
  }) {
    nodes {
      firstName
      lastName
      postsByAuthorId(filter: {
        createdAt: { greaterThan: "2016-01-01" }
      }) {
        nodes {
          ...
        }
      }
    }
  }
}
Relations: Root-level, many-to-one

Requires connectionFilterRelations: true

query {
  allPosts(filter: {
    personByAuthorId: { createdAt: { greaterThan: "2018-01-01" } }
  }) {
    ...
  }
}
Relations: Root-level, one-to-one

Requires connectionFilterRelations: true

query {
  allPeople(filter: {
    accountByPersonId: { status: { equalTo: ACTIVE } }
  }) {
    ...
  }
}
Relations: Root-level, one-to-many

Requires connectionFilterRelations: true

One-to-many relation fields require the filter criteria to be nested under every, some, or none.

query {
  allPeople(filter: {
    postsByAuthorId: {
      some: {
        status: { equalTo: PUBLISHED }
      }
    }
  }) {
    nodes {
      id
      createdAt
    }
  }
}

There is also an exist Boolean field for evaluating whether any related objects exist.

query {
  allPeople(filter: {
    postsByAuthorId: {
      exist: true
    }
  }) {
    nodes {
      id
      createdAt
    }
  }
}

For additional examples, see the tests.

Plugin Options

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

connectionFilterAllowedOperators

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.

connectionFilterAllowedFieldTypes

Restrict filtering to specific field types:

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

The available field types will depend on your database schema.

connectionFilterComputedColumns

Enable/disable filtering by computed columns:

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

Consider setting this to false and using @filterable smart comments to selectively enable filtering:

create function app_public.foo_computed(foo app_public.foo)
  returns ... as $$ ... $$ language sql stable;

comment on function app_public.foo_computed(foo app_public.foo) is E'@filterable';
connectionFilterLists

Enable/disable filtering on List fields:

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

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

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

Enable/disable filtering on related fields:

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

Enable/disable filtering on functions that return setof:

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

Consider setting this to false and using @filterable smart comments to selectively enable filtering:

create function app_public.some_foos()
  returns setof ... as $$ ... $$ language sql stable;

comment on function app_public.some_foos() is E'@filterable';
connectionFilterLogicalOperators

Enable/disable filtering with logical operators (and/or/not):

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

Allow/forbid null literals in input:

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

When false, passing null as a field value will throw an error. When true, passing null as a field value is equivalent to omitting the field.

connectionFilterAllowEmptyObjectInput

Allow/forbid empty objects ({}) in input:

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

When false, passing {} as a field value will throw an error. When true, passing {} as a field value is equivalent to omitting the field.

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