---
title: What is new in PostgREST v11.1?
description: >-
  Impersonated Role Settings, Configurable Isolation Level, improved Bulk
  Insert, and more
author: steve_chavez
date: '2023-07-12'
tags:
  - postgres
categories:
  - postgres
---
PostgREST 11.1 is now available on the Supabase platform. Besides the [pre-release](https://supabase.com/blog/postgrest-11-prerelease) features, we’ve added configuration and querying improvements. Here is what's new:

## Impersonated Role Settings

Every role that passes [PostgREST JWT Authentication](https://postgrest.org/en/stable/references/auth.html#jwt-based-user-impersonation) is an *impersonated role*. On the Supabase platform, these are the `anon`, `authenticated` and `service_role` roles.

These roles can now have settings applied with a regular `ALTER ROLE .. SET`. This is useful, for example, to prevent web users from running expensive queries.

Let’s try it by setting a statement timeout and cost limit.

### Statement timeout

`statement_timeout` aborts any statement that takes more than the specified amount of time. Let’s set it for the `anon`, `authenticated` and `service_role` roles:

```sql
-- anonymous users can run queries that take 100 milliseconds max
alter
  role anon
set
  statement_timeout = '100ms';

-- authenticated users can run queries that take 5 seconds max
alter
  role authenticated
set
  statement_timeout = '5s';

-- backend-only users can run queries that take 15 seconds max
alter
  role service_role
set
  statement_timeout = '15s';
```

You need to reload PostgREST config cache to apply these changes.

```sql
NOTIFY pgrst,
'reload config';
```

Now, suppose you do an expensive query with the `anon` role. Like filtering on a big table's unindexed column (this will cause a full table scan):

```jsx
const { data, error } = await supabase.from('big_table').select().eq('unindexed_column', 'value')
```

Then, after 5 seconds, the request will be aborted with the response:

```json
{
  "hint": null,
  "details": null,
  "code": "57014",
  "message": "canceling statement due to statement timeout"
}
```

Which is what we wanted. Note that there's already a global `statement_timeout` set but you can be more fine-grained with this feature. See [timeouts](https://supabase.com/docs/guides/database/timeouts) for more details.

### Statement Cost Limit

With a statement timeout, expensive queries will still get executed for a length of time. They'll consume resources until they’re terminated.

The [pg\_plan\_filter](https://github.com/pgexperts/pg_plan_filter) extension (available on the Supabase platform), brings a statement cost limit. This abort queries at the planning phase, before they get executed.

You can use it like:

```sql
-- anonymous users can only run cheap queries
ALTER
  USER anon
SET
  plan_filter.statement_cost_limit = 10000;

-- authenticated users can run more expensive queries
ALTER
  USER authenticated
SET
  plan_filter.statement_cost_limit = 1e6;

-- backend-only users can run any query
ALTER
  USER service_role
SET
  plan_filter.statement_cost_limit = 0;

NOTIFY pgrst,
'reload config';

-- reload postgREST config cache to apply changes
```

Let’s repeat the previous expensive query with the `anon` role.

```jsx
const { data, error } = await supabase.from('big_table').select().eq('unindexed_column', 'value')
```

Then, immediately, the request will be aborted and the response will be:

```jsx
{
  "hint": null,
  "details": null,
  "code": "54001",
  "message": "plan cost limit exceeded"
}
```

Note that tuning is required to get the cost limit right. You should use the `plan_filter.statement_cost_limit` with care as it can invalidate legitimate queries.

## Configurable Transaction Isolation Level

By default, all queries run in a transaction with the default *read committed* isolation level.

You can now modify this with the `default_transaction_isolation` setting.

If you want a function to run with *repeatable read* isolation level:

```sql
create function hello()
returns text as $$
  select 'hello';
$$ language sql
set default_transaction_isolation = 'repeatable read';
```

Or if you want an impersonated role to run its queries with a *serializable* isolation level:

```sql
alter
  role service_role
set
  default_transaction_isolation = 'serializable';

NOTIFY pgrst,
'reload config';

-- reload postgREST config cache
```

Note that the default *read committed* is good enough for almost all use cases. Higher isolation levels incur in overhead as they use more sophisticated locking. They're only needed in special cases.

## Bulk insert JSON with default values

A long wanted feature was bulk inserting JSON while considering columns' default values.

Having the following sample table.

```sql
create table
  foo (
    id bigint generated by default as identity primary key,
    bar text,
    baz int default 100
  );
```

You can now do it like this:

```jsx
const { error } = await supabase
  .from('foo')
  .insert([
	  { "bar": "val1"
	  }
	, { "bar": "val2"
	  , "baz": 15
	  }
	], defaultToNull: false)
  .select()
```

And the response will be:

```json
[
  { "id": 1, "bar": "val1", "baz": 100 },
  { "id": 2, "bar": "val2", "baz": 15 }
]
```

As you can see, `id` and `baz` took their default values.

## ANY/ALL filter modifiers

As a shortcut to `OR` filters, you can now use `any` modifiers on various filters. Take the `like` filter as an example:

```jsx
const res = await postgrest
  .from('users')
  .select()
  .likeAnyOf('username', ['%supa%', '%kiwi%'])
```

This is equivalent to the following in SQL.

```sql
select *
from users
where username like ANY('{%supa%,%kiwi%}');
```

`any` modifiers are available for the `eq,like,ilike,gt,gte,lt,lte,match,imatch` filters.

For completeness, the `all` modifier is also included.

## Minimal Breaking Changes from v10

If you only use PostgREST through Supabase client libraries (like [`supabase-js`](https://supabase.com/docs/reference/javascript/introduction)) then it's safe to upgrade to v11. If you use PostgREST with other HTTP clients (like `curl`), consider the breaking changes for this version:

- The `Range` header is now only considered on GET requests and is ignored for any other method. Previously PostgREST responded with an error but [RFC 9110](https://www.rfc-editor.org/rfc/rfc9110.html#name-range) dictates that we should ignore the `Range` header instead.
- RPC requests no longer consider the `Prefer: params=multiple-objects` header. This header was already deprecated on [v10.1.0](https://github.com/PostgREST/postgrest/releases/tag/v10.1.0).

By making use of [Logflare](https://logflare.app/), we detected that out of 20 thousands of projects:

- Only 7 projects used `Range` for HTTP methods other than GET. In these cases all responses were errors so in fact this breaking change is a fix for those requests.
- None were using `Prefer: params=multiple-objects`.

So overall the breaking changes are minimal.

## Closing up

There you have it, now you can make your API more secure with role settings and use higher isolation levels without resorting to direct PostgreSQL connections.

PostgREST v11.1 is available for all Supabase projects created after 5 July 2023. Existing projects can upgrade by doing a pause/unpause.

## More Postgres resources

- [Storing OpenAI embeddings in Postgres with pgvector](https://supabase.com/blog/openai-embeddings-postgres-vector)
- [Choosing a Postgres Primary Key](https://supabase.com/blog/choosing-a-postgres-primary-key)
- [SQL or NoSQL? Why not use both (with PostgreSQL)?](https://supabase.com/blog/sql-or-nosql-both-with-postgresql)
- [pg\_jsonschema: JSON Schema support for Postgres](https://supabase.com/blog/pg-jsonschema-a-postgres-extension-for-json-validation)
- [Implementing "seen by" functionality with Postgres](https://supabase.com/blog/seen-by-in-postgresql)