Setting the transaction isolation level

[steve-chavez]

Previously discussed on https://github.com/PostgREST/postgrest/issues/1069, this is useful for concurrent updates and also concurrent reads.

It could be done with a header:

Prefer: tx=commit;isolation=read-commited

Prefer: tx=commit;isolation=repeatable-read

Prefer: tx=commit;isolation=serializable

read-commited being the default and the only one supported currently.

Since the other two isolation levels are costlier, we'd need to whitelist them per resource

-- Using the idea on https://github.com/PostgREST/postgrest/issues/2442#issuecomment-1228812319
SECURITY LABEL FOR pgrest ON TABLE foo.bar IS $$
  GRANT ISOLATION SERIALIZABLE FOR GET
  GRANT ISOLATION REPEATABLE READ FOR GET
$$;

-- we can provide a fallback for cloud hosting as

ALTER ROLE authenticator SET pgrest.sec.table."foo.bar" IS $$
  GRANT ISOLATION SERIALIZABLE FOR GET
  GRANT ISOLATION REPEATABLE READ FOR GET
$$;

---

Another option might be specifying the isolation per resource and not allow the client to switch them

SECURITY LABEL FOR pgrest ON TABLE foo.bar IS $$
  USE ISOLATION SERIALIZABLE
$$;

---

Or simply a config option like

db-isolation-repeatable-read = "schema1.table, schema2.table, schema1.function()"

db-isolation-serializable = "schema1.table, schema2.table, schema1.function()"