else 401 | insufficient privileges | | other | 400 | | Errors from PostgREST[](https://docs.postgrest.org/en/v14/references/errors.html#errors-from-postgrest "Link to this heading") -------------------------------------------------------------------------------------------------------------------------------- Errors that come from PostgREST itself maintain the same structure but differ in the `PGRST` prefix in the `code` field. For instance, when querying a function that does not exist in the [schema cache](https://docs.postgrest.org/en/v14/references/schema_cache.html) : POST /rpc/nonexistent\_function HTTP/1.1 HTTP/1.1 404 Not Found Content-Type: application/json; charset=utf-8 { "hint": "...", "details": null "code": "PGRST202", "message": "Could not find the api.nonexistent\_function() function in the schema cache" } ### PostgREST Error Codes[](https://docs.postgrest.org/en/v14/references/errors.html#postgrest-error-codes "Link to this heading") PostgREST error codes have the form `PGRSTgxx`. * `PGRST` is the prefix that differentiates the error from a PostgreSQL error. * `g` is the error group * `xx` is the error identifier in the group. #### Group 0 - Connection[](https://docs.postgrest.org/en/v14/references/errors.html#group-0-connection "Link to this heading") Related to the connection with the database. | Code | HTTP status | Description | | --- | --- | --- | | PGRST000 | 503 | Could not connect with the database due to an incorrect [db-uri](https://docs.postgrest.org/en/v14/references/configuration.html#db-uri)
or due to the PostgreSQL service not running. | | PGRST001 | 503 | Could not connect with the database due to an internal error. | | PGRST002 | 503 | Could not connect with the database when building the [Schema Cache](https://docs.postgrest.org/en/v14/references/schema_cache.html)
due to the PostgreSQL service not running. | | PGRST003 | 504 | The request timed out waiting for a pool connection to be available. See [db-pool-acquisition-timeout](https://docs.postgrest.org/en/v14/references/configuration.html#db-pool-acquisition-timeout)
. | #### Group 1 - Api Request[](https://docs.postgrest.org/en/v14/references/errors.html#group-1-api-request "Link to this heading") Related to the HTTP request elements. | Code | HTTP status | Description | | --- | --- | --- | | PGRST100 | 400 | Parsing error in the query string parameter. See [Horizontal Filtering](https://docs.postgrest.org/en/v14/references/api/tables_views.html#h-filter)
, [Operators](https://docs.postgrest.org/en/v14/references/api/tables_views.html#operators)
and [Ordering](https://docs.postgrest.org/en/v14/references/api/tables_views.html#ordering)
. | | PGRST101 | 405 | For [functions](https://docs.postgrest.org/en/v14/references/api/functions.html#functions)
, only `GET` and `POST` verbs are allowed. Any other verb will throw this error. | | PGRST102 | 400 | An invalid request body was sent(e.g. an empty body or malformed JSON). | | PGRST103 | 416 | An invalid range was specified for [Limits and Pagination](https://docs.postgrest.org/en/v14/references/api/pagination_count.html#limits)
. | | PGRST105 | 405 | An invalid [PUT](https://docs.postgrest.org/en/v14/references/api/tables_views.html#upsert-put)
request was done | | PGRST106 | 406 | The schema specified when [switching schemas](https://docs.postgrest.org/en/v14/references/api/schemas.html#multiple-schemas)
is not present in the [db-schemas](https://docs.postgrest.org/en/v14/references/configuration.html#db-schemas)
configuration variable. | | PGRST107 | 415 | The `Content-Type` sent in the request is invalid. | | PGRST108 | 400 | The filter is applied to a embedded resource that is not specified in the `select` part of the query string. See [Embedded Filters](https://docs.postgrest.org/en/v14/references/api/resource_embedding.html#embed-filters)
. | | PGRST111 | 500 | An invalid `response.headers` was set. See [Response Headers](https://docs.postgrest.org/en/v14/references/transactions.html#guc-resp-hdrs)
. | | PGRST112 | 500 | The status code must be a positive integer. See [Response Status Code](https://docs.postgrest.org/en/v14/references/transactions.html#guc-resp-status)
. | | PGRST114 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/v14/references/api/tables_views.html#upsert-put)
, when [limits and offsets](https://docs.postgrest.org/en/v14/references/api/pagination_count.html#limits)
are used. | | PGRST115 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/v14/references/api/tables_views.html#upsert-put)
, when the primary key in the query string and the body are different. | | PGRST116 | 406 | More than 1 or no items where returned when requesting a singular response. See [Singular or Plural](https://docs.postgrest.org/en/v14/references/api/resource_representation.html#singular-plural)
. | | PGRST117 | 405 | The HTTP verb used in the request in not supported. | | PGRST118 | 400 | Could not order the result using the related table because there is no many-to-one or one-to-one relationship between them. | | PGRST120 | 400 | An embedded resource can only be filtered using the `is.null` or `not.is.null` [operators](https://docs.postgrest.org/en/v14/references/api/tables_views.html#operators)
. | | PGRST121 | 500 | PostgREST can’t parse the JSON objects in RAISE `PGRST` error. See [raise headers](https://docs.postgrest.org/en/v14/references/errors.html#raise-headers)
. | | PGRST122 | 400 | Invalid preferences found in `Prefer` header with `Prefer: handling=strict`. See [Strict or Lenient Handling](https://docs.postgrest.org/en/v14/references/api/preferences.html#prefer-handling)
. | | PGRST123 | 400 | Aggregate functions are disabled. See [db-aggregates-enabled](https://docs.postgrest.org/en/v14/references/configuration.html#db-aggregates-enabled)
. | | PGRST124 | 400 | `max-affected` preference is violated. See [Max Affected](https://docs.postgrest.org/en/v14/references/api/preferences.html#prefer-max-affected)
. | | PGRST125 | 404 | Invalid path is specified in request URL. | | PGRST126 | 404 | Open API config is disabled but API root path is accessed. See [openapi-mode](https://docs.postgrest.org/en/v14/references/configuration.html#openapi-mode)
. | | PGRST127 | 400 | The feature specified in the `details` field is not implemented. | | PGRST128 | 400 | `max-affected` preference is violated with `RPC` call. See [Max Affected](https://docs.postgrest.org/en/v14/references/api/preferences.html#prefer-max-affected)
. | #### Group 2 - Schema Cache[](https://docs.postgrest.org/en/v14/references/errors.html#group-2-schema-cache "Link to this heading") Related to a [Schema Cache](https://docs.postgrest.org/en/v14/references/schema_cache.html#schema-cache) . Most of the time, these errors are solved by [Schema Cache Reloading](https://docs.postgrest.org/en/v14/references/schema_cache.html#schema-reloading) . | Code | HTTP status | Description | | --- | --- | --- | | PGRST200 | 400 | Caused by stale foreign key relationships, otherwise any of the embedding resources or the relationship itself may not exist in the database. | | PGRST201 | 300 | An ambiguous embedding request was made. See [Foreign Key Joins on Multiple Foreign Key Relationships](https://docs.postgrest.org/en/v14/references/api/resource_embedding.html#complex-rels)
. | | PGRST202 | 404 | Caused by a stale function signature, otherwise the function may not exist in the database. | | PGRST203 | 300 | Caused by requesting overloaded functions with the same argument names but different types, or by using a `POST` verb to request overloaded functions with a `JSON` or `JSONB` type unnamed parameter. The solution is to rename the function or add/modify the names of the arguments. | | PGRST204 | 400 | Caused when the [column specified](https://docs.postgrest.org/en/v14/references/api/tables_views.html#specify-columns)
in the `columns` query parameter is not found. | | PGRST205 | 404 | Caused when the [table specified](https://docs.postgrest.org/en/v14/references/api/tables_views.html#tables-views)
in the URI is not found. | #### Group 3 - JWT[](https://docs.postgrest.org/en/v14/references/errors.html#group-3-jwt "Link to this heading") Related to the authentication process using JWT. You can follow the [Tutorial 1 - The Golden Key](https://docs.postgrest.org/en/v14/tutorials/tut1.html#tut1) for an example on how to implement authentication and the [Authentication page](https://docs.postgrest.org/en/v14/references/auth.html) for more information on this process. | Code | HTTP status | Description | | --- | --- | --- | | PGRST300 | 500 | A [JWT secret](https://docs.postgrest.org/en/v14/references/configuration.html#jwt-secret)
is missing from the configuration. | | PGRST301 | 401 | Provided JWT couldn’t be decoded or it is invalid. | | PGRST302 | 401 | Attempted to do a request without [Bearer Authentication](https://docs.postgrest.org/en/v14/references/auth.html#bearer-auth)
when the anonymous role is disabled by not setting it in [db-anon-role](https://docs.postgrest.org/en/v14/references/configuration.html#db-anon-role)
. | | PGRST303 | 401 | [JWT claims validation](https://docs.postgrest.org/en/v14/references/auth.html#jwt-claims-validation)
or parsing failed. | #### Group X - Internal[](https://docs.postgrest.org/en/v14/references/errors.html#group-x-internal "Link to this heading") Internal errors. If you encounter any of these, you may have stumbled on a PostgREST bug, please [open an issue](https://github.com/PostgREST/postgrest/issues) and we’ll be glad to fix it. | Code | HTTP status | Description | | --- | --- | --- | | PGRSTX00 | 500 | Internal errors related to the library used for connecting to the database. | Custom Errors[](https://docs.postgrest.org/en/v14/references/errors.html#custom-errors "Link to this heading") ---------------------------------------------------------------------------------------------------------------- You can customize the errors by using the [RAISE statement](https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE) on functions. ### RAISE errors with HTTP Status Codes[](https://docs.postgrest.org/en/v14/references/errors.html#raise-errors-with-http-status-codes "Link to this heading") Custom status codes can be done by raising SQL exceptions inside [functions](https://docs.postgrest.org/en/v14/references/api/functions.html#functions) . For instance, here’s a saucy function that always responds with an error: CREATE OR REPLACE FUNCTION just\_fail() RETURNS void LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'I refuse!' USING DETAIL \= 'Pretty simple', HINT \= 'There is nothing you can do.'; END $$; Calling the function returns HTTP 400 with the body { "message":"I refuse!", "details":"Pretty simple", "hint":"There is nothing you can do.", "code":"P0001" } One way to customize the HTTP status code is by raising particular exceptions according to the PostgREST [error to status code mapping](https://docs.postgrest.org/en/v14/references/errors.html#status-codes) . For example, `RAISE insufficient_privilege` will respond with HTTP 401/403 as appropriate. For even greater control of the HTTP status code, raise an exception of the `PTxyz` type. For instance to respond with HTTP 402, raise `PT402`: RAISE sqlstate 'PT402' using message \= 'Payment Required', detail \= 'Quota exceeded', hint \= 'Upgrade your plan'; Returns: HTTP/1.1 402 Payment Required Content-Type: application/json; charset=utf-8 { "message": "Payment Required", "details": "Quota exceeded", "hint": "Upgrade your plan", "code": "PT402" } ### Add HTTP Headers with RAISE[](https://docs.postgrest.org/en/v14/references/errors.html#add-http-headers-with-raise "Link to this heading") For full control over headers and status you can raise a `PGRST` SQLSTATE error. You can achieve this by adding the `code`, `message`, `detail` and `hint` in the PostgreSQL error message field as a JSON object. Here, the `details` and `hint` are optional. Similarly, the `status` and `headers` must be added to the SQL error detail field as a JSON object. For instance: RAISE sqlstate 'PGRST' USING message \= '{"code":"123","message":"Payment Required","details":"Quota exceeded","hint":"Upgrade your plan"}', detail \= '{"status":402,"headers":{"X-Powered-By":"Nerd Rage"}}'; Returns: HTTP/1.1 402 Payment Required Content-Type: application/json; charset=utf-8 X-Powered-By: Nerd Rage { "message": "Payment Required", "details": "Quota exceeded", "hint": "Upgrade your plan", "code": "123" } For non standard HTTP status, you can optionally add `status_text` to describe the status code. For status code `419` the detail field may look like this: detail \= '{"status":419,"status\_text":"Page Expired","headers":{"X-Powered-By":"Nerd Rage"}}'; If PostgREST can’t parse the JSON objects `message` and `detail`, it will throw a `PGRST121` error. See [Errors from PostgREST](https://docs.postgrest.org/en/v14/references/errors.html#pgrst1) . Proxy-Status Header[](https://docs.postgrest.org/en/v14/references/errors.html#proxy-status-header "Link to this heading") ---------------------------------------------------------------------------------------------------------------------------- For error cases, the standard [Proxy-Status](https://www.rfc-editor.org/rfc/rfc9209.html#name-the-proxy-status-http-field) header is returned with the error code. The error code comes from either [PostgREST](https://docs.postgrest.org/en/v14/references/errors.html#pgrst-errors) , [PostgreSQL](https://docs.postgrest.org/en/v14/references/errors.html#postgresql-errors) or [Custom](https://docs.postgrest.org/en/v14/references/errors.html#custom-errors) errors. This is useful when doing `HEAD` requests where the HTTP status is not descriptive enough. For example, doing a request on a table with high count (say 30\_000\_000), we get: HEAD /table HTTP/1.1 Prefer: count=exact HTTP/1.1 500 Internal Server Error Proxy-Status: PostgREST; error=57014 The PostgreSQL error code `57014` ([ref](https://www.postgresql.org/docs/current/errcodes-appendix.html) ) reveals that the error is due to a short `statement_timeout` value. --- # Schema Cache — PostgREST 14 documentation * [](https://docs.postgrest.org/en/v14/index.html) * Schema Cache * [View page source](https://docs.postgrest.org/en/v14/_sources/references/schema_cache.rst.txt) * * * Schema Cache[](https://docs.postgrest.org/en/v14/references/schema_cache.html#schema-cache "Link to this heading") ==================================================================================================================== PostgREST requires metadata from the database schema to provide a REST API that abstracts SQL details. One example of this is the interface for [Resource Embedding](https://docs.postgrest.org/en/v14/references/api/resource_embedding.html#resource-embedding) . Getting this metadata requires expensive queries. To avoid repeating this work, PostgREST uses a schema cache. Schema Cache Reloading[](https://docs.postgrest.org/en/v14/references/schema_cache.html#schema-cache-reloading "Link to this heading") ---------------------------------------------------------------------------------------------------------------------------------------- To not let the schema cache go stale (happens when you make changes to the database), you need to reload it. You can do this with UNIX signals or with PostgreSQL notifications. It’s also possible to do this automatically using [event triggers](https://www.postgresql.org/docs/current/event-trigger-definition.html) . Note * Requests will wait until the schema cache reload is done. This to prevent client errors due to an stale schema cache. * If you are using the [In-Database Configuration](https://docs.postgrest.org/en/v14/references/configuration.html#in-db-config) , a schema cache reload will [reload the configuration](https://docs.postgrest.org/en/v14/references/configuration.html#config-reloading) as well. ### Schema Cache Reloading with Unix Signals[](https://docs.postgrest.org/en/v14/references/schema_cache.html#schema-cache-reloading-with-unix-signals "Link to this heading") To manually reload the cache without restarting the PostgREST server, send a SIGUSR1 signal to the server process. killall \-SIGUSR1 postgrest For docker you can do: docker kill \-s SIGUSR1
else 401 | insufficient privileges | | other | 400 | | Errors from PostgREST[](https://docs.postgrest.org/en/latest/references/errors.html#errors-from-postgrest "Link to this heading") ----------------------------------------------------------------------------------------------------------------------------------- Errors that come from PostgREST itself maintain the same structure but differ in the `PGRST` prefix in the `code` field. For instance, when querying a function that does not exist in the [schema cache](https://docs.postgrest.org/en/latest/references/schema_cache.html) : POST /rpc/nonexistent\_function HTTP/1.1 HTTP/1.1 404 Not Found Content-Type: application/json; charset=utf-8 { "hint": "...", "details": null "code": "PGRST202", "message": "Could not find the api.nonexistent\_function() function in the schema cache" } ### PostgREST Error Codes[](https://docs.postgrest.org/en/latest/references/errors.html#postgrest-error-codes "Link to this heading") PostgREST error codes have the form `PGRSTgxx`. * `PGRST` is the prefix that differentiates the error from a PostgreSQL error. * `g` is the error group * `xx` is the error identifier in the group. #### Group 0 - Connection[](https://docs.postgrest.org/en/latest/references/errors.html#group-0-connection "Link to this heading") Related to the connection with the database. | Code | HTTP status | Description | | --- | --- | --- | | PGRST000 | 503 | Could not connect with the database due to an incorrect [db-uri](https://docs.postgrest.org/en/latest/references/configuration.html#db-uri)
or due to the PostgreSQL service not running. | | PGRST001 | 503 | Could not connect with the database due to an internal error. | | PGRST002 | 503 | Could not connect with the database when building the [Schema Cache](https://docs.postgrest.org/en/latest/references/schema_cache.html)
due to the PostgreSQL service not running. | | PGRST003 | 504 | The request timed out waiting for a pool connection to be available. See [db-pool-acquisition-timeout](https://docs.postgrest.org/en/latest/references/configuration.html#db-pool-acquisition-timeout)
. | #### Group 1 - Api Request[](https://docs.postgrest.org/en/latest/references/errors.html#group-1-api-request "Link to this heading") Related to the HTTP request elements. | Code | HTTP status | Description | | --- | --- | --- | | PGRST100 | 400 | Parsing error in the query string parameter. See [Horizontal Filtering](https://docs.postgrest.org/en/latest/references/api/tables_views.html#h-filter)
, [Operators](https://docs.postgrest.org/en/latest/references/api/tables_views.html#operators)
and [Ordering](https://docs.postgrest.org/en/latest/references/api/tables_views.html#ordering)
. | | PGRST101 | 405 | For [functions](https://docs.postgrest.org/en/latest/references/api/functions.html#functions)
, only `GET` and `POST` verbs are allowed. Any other verb will throw this error. | | PGRST102 | 400 | An invalid request body was sent(e.g. an empty body or malformed JSON). | | PGRST103 | 416 | An invalid range was specified for [Limits and Pagination](https://docs.postgrest.org/en/latest/references/api/pagination_count.html#limits)
. | | PGRST105 | 405 | An invalid [PUT](https://docs.postgrest.org/en/latest/references/api/tables_views.html#upsert-put)
request was done | | PGRST106 | 406 | The schema specified when [switching schemas](https://docs.postgrest.org/en/latest/references/api/schemas.html#multiple-schemas)
is not present in the [db-schemas](https://docs.postgrest.org/en/latest/references/configuration.html#db-schemas)
configuration variable. | | PGRST107 | 415 | The `Content-Type` sent in the request is invalid. | | PGRST108 | 400 | The filter is applied to a embedded resource that is not specified in the `select` part of the query string. See [Embedded Filters](https://docs.postgrest.org/en/latest/references/api/resource_embedding.html#embed-filters)
. | | PGRST111 | 500 | An invalid `response.headers` was set. See [Response Headers](https://docs.postgrest.org/en/latest/references/transactions.html#guc-resp-hdrs)
. | | PGRST112 | 500 | The status code must be a positive integer. See [Response Status Code](https://docs.postgrest.org/en/latest/references/transactions.html#guc-resp-status)
. | | PGRST114 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/latest/references/api/tables_views.html#upsert-put)
, when [limits and offsets](https://docs.postgrest.org/en/latest/references/api/pagination_count.html#limits)
are used. | | PGRST115 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/latest/references/api/tables_views.html#upsert-put)
, when the primary key in the query string and the body are different. | | PGRST116 | 406 | More than 1 or no items where returned when requesting a singular response. See [Singular or Plural](https://docs.postgrest.org/en/latest/references/api/resource_representation.html#singular-plural)
. | | PGRST117 | 405 | The HTTP verb used in the request in not supported. | | PGRST118 | 400 | Could not order the result using the related table because there is no many-to-one or one-to-one relationship between them. | | PGRST120 | 400 | An embedded resource can only be filtered using the `is.null` or `not.is.null` [operators](https://docs.postgrest.org/en/latest/references/api/tables_views.html#operators)
. | | PGRST121 | 500 | PostgREST can’t parse the JSON objects in RAISE `PGRST` error. See [raise headers](https://docs.postgrest.org/en/latest/references/errors.html#raise-headers)
. | | PGRST122 | 400 | Invalid preferences found in `Prefer` header with `Prefer: handling=strict`. See [Strict or Lenient Handling](https://docs.postgrest.org/en/latest/references/api/preferences.html#prefer-handling)
. | | PGRST123 | 400 | Aggregate functions are disabled. See [db-aggregates-enabled](https://docs.postgrest.org/en/latest/references/configuration.html#db-aggregates-enabled)
. | | PGRST124 | 400 | `max-affected` preference is violated. See [Max Affected](https://docs.postgrest.org/en/latest/references/api/preferences.html#prefer-max-affected)
. | | PGRST125 | 404 | Invalid path is specified in request URL. | | PGRST126 | 404 | Open API config is disabled but API root path is accessed. See [openapi-mode](https://docs.postgrest.org/en/latest/references/configuration.html#openapi-mode)
. | | PGRST127 | 400 | The feature specified in the `details` field is not implemented. | | PGRST128 | 400 | `max-affected` preference is violated with `RPC` call. See [Max Affected](https://docs.postgrest.org/en/latest/references/api/preferences.html#prefer-max-affected)
. | #### Group 2 - Schema Cache[](https://docs.postgrest.org/en/latest/references/errors.html#group-2-schema-cache "Link to this heading") Related to a [Schema Cache](https://docs.postgrest.org/en/latest/references/schema_cache.html#schema-cache) . Most of the time, these errors are solved by [Schema Cache Reloading](https://docs.postgrest.org/en/latest/references/schema_cache.html#schema-reloading) . | Code | HTTP status | Description | | --- | --- | --- | | PGRST200 | 400 | Caused by stale foreign key relationships, otherwise any of the embedding resources or the relationship itself may not exist in the database. | | PGRST201 | 300 | An ambiguous embedding request was made. See [Foreign Key Joins on Multiple Foreign Key Relationships](https://docs.postgrest.org/en/latest/references/api/resource_embedding.html#complex-rels)
. | | PGRST202 | 404 | Caused by a stale function signature, otherwise the function may not exist in the database. | | PGRST203 | 300 | Caused by requesting overloaded functions with the same argument names but different types, or by using a `POST` verb to request overloaded functions with a `JSON` or `JSONB` type unnamed parameter. The solution is to rename the function or add/modify the names of the arguments. | | PGRST204 | 400 | Caused when the [column specified](https://docs.postgrest.org/en/latest/references/api/tables_views.html#specify-columns)
in the `columns` query parameter is not found. | | PGRST205 | 404 | Caused when the [table specified](https://docs.postgrest.org/en/latest/references/api/tables_views.html#tables-views)
in the URI is not found. | #### Group 3 - JWT[](https://docs.postgrest.org/en/latest/references/errors.html#group-3-jwt "Link to this heading") Related to the authentication process using JWT. You can follow the [Tutorial 1 - The Golden Key](https://docs.postgrest.org/en/latest/tutorials/tut1.html#tut1) for an example on how to implement authentication and the [Authentication page](https://docs.postgrest.org/en/latest/references/auth.html) for more information on this process. | Code | HTTP status | Description | | --- | --- | --- | | PGRST300 | 500 | A [JWT secret](https://docs.postgrest.org/en/latest/references/configuration.html#jwt-secret)
is missing from the configuration. | | PGRST301 | 401 | Provided JWT couldn’t be decoded or it is invalid. | | PGRST302 | 401 | Attempted to do a request without [Bearer Authentication](https://docs.postgrest.org/en/latest/references/auth.html#bearer-auth)
when the anonymous role is disabled by not setting it in [db-anon-role](https://docs.postgrest.org/en/latest/references/configuration.html#db-anon-role)
. | | PGRST303 | 401 | [JWT claims validation](https://docs.postgrest.org/en/latest/references/auth.html#jwt-claims-validation)
or parsing failed. | #### Group X - Internal[](https://docs.postgrest.org/en/latest/references/errors.html#group-x-internal "Link to this heading") Internal errors. If you encounter any of these, you may have stumbled on a PostgREST bug, please [open an issue](https://github.com/PostgREST/postgrest/issues) and we’ll be glad to fix it. | Code | HTTP status | Description | | --- | --- | --- | | PGRSTX00 | 500 | Internal errors related to the library used for connecting to the database. | Custom Errors[](https://docs.postgrest.org/en/latest/references/errors.html#custom-errors "Link to this heading") ------------------------------------------------------------------------------------------------------------------- You can customize the errors by using the [RAISE statement](https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE) on functions. ### RAISE errors with HTTP Status Codes[](https://docs.postgrest.org/en/latest/references/errors.html#raise-errors-with-http-status-codes "Link to this heading") Custom status codes can be done by raising SQL exceptions inside [functions](https://docs.postgrest.org/en/latest/references/api/functions.html#functions) . For instance, here’s a saucy function that always responds with an error: CREATE OR REPLACE FUNCTION just\_fail() RETURNS void LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'I refuse!' USING DETAIL \= 'Pretty simple', HINT \= 'There is nothing you can do.'; END $$; Calling the function returns HTTP 400 with the body { "message":"I refuse!", "details":"Pretty simple", "hint":"There is nothing you can do.", "code":"P0001" } One way to customize the HTTP status code is by raising particular exceptions according to the PostgREST [error to status code mapping](https://docs.postgrest.org/en/latest/references/errors.html#status-codes) . For example, `RAISE insufficient_privilege` will respond with HTTP 401/403 as appropriate. For even greater control of the HTTP status code, raise an exception of the `PTxyz` type. For instance to respond with HTTP 402, raise `PT402`: RAISE sqlstate 'PT402' using message \= 'Payment Required', detail \= 'Quota exceeded', hint \= 'Upgrade your plan'; Returns: HTTP/1.1 402 Payment Required Content-Type: application/json; charset=utf-8 { "message": "Payment Required", "details": "Quota exceeded", "hint": "Upgrade your plan", "code": "PT402" } ### Add HTTP Headers with RAISE[](https://docs.postgrest.org/en/latest/references/errors.html#add-http-headers-with-raise "Link to this heading") For full control over headers and status you can raise a `PGRST` SQLSTATE error. You can achieve this by adding the `code`, `message`, `detail` and `hint` in the PostgreSQL error message field as a JSON object. Here, the `details` and `hint` are optional. Similarly, the `status` and `headers` must be added to the SQL error detail field as a JSON object. For instance: RAISE sqlstate 'PGRST' USING message \= '{"code":"123","message":"Payment Required","details":"Quota exceeded","hint":"Upgrade your plan"}', detail \= '{"status":402,"headers":{"X-Powered-By":"Nerd Rage"}}'; Returns: HTTP/1.1 402 Payment Required Content-Type: application/json; charset=utf-8 X-Powered-By: Nerd Rage { "message": "Payment Required", "details": "Quota exceeded", "hint": "Upgrade your plan", "code": "123" } For non standard HTTP status, you can optionally add `status_text` to describe the status code. For status code `419` the detail field may look like this: detail \= '{"status":419,"status\_text":"Page Expired","headers":{"X-Powered-By":"Nerd Rage"}}'; If PostgREST can’t parse the JSON objects `message` and `detail`, it will throw a `PGRST121` error. See [Errors from PostgREST](https://docs.postgrest.org/en/latest/references/errors.html#pgrst1) . Proxy-Status Header[](https://docs.postgrest.org/en/latest/references/errors.html#proxy-status-header "Link to this heading") ------------------------------------------------------------------------------------------------------------------------------- For error cases, the standard [Proxy-Status](https://www.rfc-editor.org/rfc/rfc9209.html#name-the-proxy-status-http-field) header is returned with the error code. The error code comes from either [PostgREST](https://docs.postgrest.org/en/latest/references/errors.html#pgrst-errors) , [PostgreSQL](https://docs.postgrest.org/en/latest/references/errors.html#postgresql-errors) or [Custom](https://docs.postgrest.org/en/latest/references/errors.html#custom-errors) errors. This is useful when doing `HEAD` requests where the HTTP status is not descriptive enough. For example, doing a request on a table with high count (say 30\_000\_000), we get: HEAD /table HTTP/1.1 Prefer: count=exact HTTP/1.1 500 Internal Server Error Proxy-Status: PostgREST; error=57014 The PostgreSQL error code `57014` ([ref](https://www.postgresql.org/docs/current/errcodes-appendix.html) ) reveals that the error is due to a short `statement_timeout` value. Client Error Verbosity[](https://docs.postgrest.org/en/latest/references/errors.html#client-error-verbosity "Link to this heading") ------------------------------------------------------------------------------------------------------------------------------------- For HTTP clients, the error verbosity can be set via [client-error-verbosity](https://docs.postgrest.org/en/latest/references/configuration.html#client-error-verbosity) config. With `verbose`, it returns `code`, `message`, `details` and `hint`. curl "localhost:3000/itemsxx" { "code": "PGRST205", "message": "Could not find the table 'public.itemsxx' in the schema cache", "details": "Perhaps you meant the table 'public.items'", "hint": null } With `minimal`, just `code` and `message` is returned. curl "localhost:3000/itemsxx" { "code": "PGRST205", "message": "Could not find the table 'public.itemsxx' in the schema cache" } --- # External Authentication — PostgREST devel documentation * [](https://docs.postgrest.org/en/latest/index.html) * External Authentication * [View page source](https://docs.postgrest.org/en/latest/_sources/explanations/external_auth.rst.txt) * * * External Authentication[](https://docs.postgrest.org/en/latest/explanations/external_auth.html#external-authentication "Link to this heading") ================================================================================================================================================ JWT from Auth0[](https://docs.postgrest.org/en/latest/explanations/external_auth.html#jwt-from-auth0 "Link to this heading") ------------------------------------------------------------------------------------------------------------------------------ An external service like [Auth0](https://auth0.com/) can do the hard work transforming OAuth from Github, Twitter, Google etc into a JWT suitable for PostgREST. Auth0 can also handle email signup and password reset flows. To use Auth0, create [an application](https://auth0.com/docs/get-started/applications) for your app and [an API](https://auth0.com/docs/get-started/apis) for your PostgREST server. Auth0 supports both HS256 and RS256 scheme for the issued tokens for APIs. For simplicity, you may first try HS256 scheme while creating your API on Auth0. Your application should use your PostgREST API’s [API identifier](https://auth0.com/docs/get-started/apis/api-settings) by setting it with the [audience parameter](https://auth0.com/docs/secure/tokens/access-tokens/get-access-tokens#control-access-token-audience) during the authorization request. This will ensure that Auth0 will issue an access token for your PostgREST API. For PostgREST to verify the access token, you will need to set `jwt-secret` on PostgREST config file with your API’s signing secret. --- # Authentication — PostgREST 14 documentation * [](https://docs.postgrest.org/en/stable/index.html) * Authentication * [View page source](https://docs.postgrest.org/en/stable/_sources/references/auth.rst.txt) * * * Authentication[](https://docs.postgrest.org/en/stable/references/auth.html#authentication "Link to this heading") =================================================================================================================== PostgREST is designed to keep the database at the center of API security. All [authorization happens in the database](https://docs.postgrest.org/en/stable/explanations/db_authz.html#db-authz) . It is PostgREST’s job to **authenticate** requests – i.e. verify that a client is who they say they are – and then let the database **authorize** client actions. Overview of role system[](https://docs.postgrest.org/en/stable/references/auth.html#overview-of-role-system "Link to this heading") ------------------------------------------------------------------------------------------------------------------------------------- There are three types of roles used by PostgREST, the **authenticator**, **anonymous** and **user** roles. The database administrator creates these roles and configures PostgREST to use them.  The authenticator role is used for connecting to the database and should be configured to have very limited access. It is a chameleon whose job is to “become” other users to service authenticated HTTP requests. CREATE ROLE authenticator LOGIN NOINHERIT NOCREATEDB NOCREATEROLE NOSUPERUSER; CREATE ROLE anonymous NOLOGIN; CREATE ROLE webuser NOLOGIN; Note The names “authenticator” and “anon” names are configurable and not sacred, we simply choose them for clarity. See [db-uri](https://docs.postgrest.org/en/stable/references/configuration.html#db-uri) and [db-anon-role](https://docs.postgrest.org/en/stable/references/configuration.html#db-anon-role) . ### User Impersonation[](https://docs.postgrest.org/en/stable/references/auth.html#user-impersonation "Link to this heading") The picture below shows how the server handles authentication. If auth succeeds, it switches into the user role specified by the request, otherwise it switches into the anonymous role (if it’s set in [db-anon-role](https://docs.postgrest.org/en/stable/references/configuration.html#db-anon-role) ).  This role switching mechanism is called **user impersonation**. In PostgreSQL it’s done with the `SET ROLE` statement. Note The impersonated roles will have their settings applied. See [Impersonated Role Settings](https://docs.postgrest.org/en/stable/references/transactions.html#impersonated-settings) . JWT Authentication[](https://docs.postgrest.org/en/stable/references/auth.html#jwt-authentication "Link to this heading") --------------------------------------------------------------------------------------------------------------------------- We use [JSON Web Tokens](https://datatracker.ietf.org/doc/html/rfc7519/) to authenticate API requests, this allows us to be stateless and not require database lookups for verification. As you’ll recall a JWT contains a list of cryptographically signed claims. All claims are allowed but PostgREST cares specifically about a claim called role (configurable with [JWT Role Extraction](https://docs.postgrest.org/en/stable/references/auth.html#jwt-role-extract) ). { "role": "user123" } When a request contains a valid JWT with a role claim PostgREST will switch to the database role with that name for the duration of the HTTP request. SET LOCAL ROLE user123; Note that the database administrator must allow the authenticator role to switch into this user by previously executing GRANT user123 TO authenticator; \-- similarly for the anonymous role \-- GRANT anonymous TO authenticator; If the client included no JWT (or one without a role claim) then PostgREST switches into the anonymous role. The database administrator must set the anonymous role permissions correctly to prevent anonymous users from seeing or changing things they shouldn’t. ### Bearer Authentication[](https://docs.postgrest.org/en/stable/references/auth.html#bearer-authentication "Link to this heading") To make an authenticated request the client must include an `Authorization` HTTP header with the value `Bearer
else 401 | insufficient privileges | | other | 400 | | Errors from PostgREST[](https://docs.postgrest.org/en/stable/references/errors.html#errors-from-postgrest "Link to this heading") ----------------------------------------------------------------------------------------------------------------------------------- Errors that come from PostgREST itself maintain the same structure but differ in the `PGRST` prefix in the `code` field. For instance, when querying a function that does not exist in the [schema cache](https://docs.postgrest.org/en/stable/references/schema_cache.html) : POST /rpc/nonexistent\_function HTTP/1.1 HTTP/1.1 404 Not Found Content-Type: application/json; charset=utf-8 { "hint": "...", "details": null "code": "PGRST202", "message": "Could not find the api.nonexistent\_function() function in the schema cache" } ### PostgREST Error Codes[](https://docs.postgrest.org/en/stable/references/errors.html#postgrest-error-codes "Link to this heading") PostgREST error codes have the form `PGRSTgxx`. * `PGRST` is the prefix that differentiates the error from a PostgreSQL error. * `g` is the error group * `xx` is the error identifier in the group. #### Group 0 - Connection[](https://docs.postgrest.org/en/stable/references/errors.html#group-0-connection "Link to this heading") Related to the connection with the database. | Code | HTTP status | Description | | --- | --- | --- | | PGRST000 | 503 | Could not connect with the database due to an incorrect [db-uri](https://docs.postgrest.org/en/stable/references/configuration.html#db-uri)
or due to the PostgreSQL service not running. | | PGRST001 | 503 | Could not connect with the database due to an internal error. | | PGRST002 | 503 | Could not connect with the database when building the [Schema Cache](https://docs.postgrest.org/en/stable/references/schema_cache.html)
due to the PostgreSQL service not running. | | PGRST003 | 504 | The request timed out waiting for a pool connection to be available. See [db-pool-acquisition-timeout](https://docs.postgrest.org/en/stable/references/configuration.html#db-pool-acquisition-timeout)
. | #### Group 1 - Api Request[](https://docs.postgrest.org/en/stable/references/errors.html#group-1-api-request "Link to this heading") Related to the HTTP request elements. | Code | HTTP status | Description | | --- | --- | --- | | PGRST100 | 400 | Parsing error in the query string parameter. See [Horizontal Filtering](https://docs.postgrest.org/en/stable/references/api/tables_views.html#h-filter)
, [Operators](https://docs.postgrest.org/en/stable/references/api/tables_views.html#operators)
and [Ordering](https://docs.postgrest.org/en/stable/references/api/tables_views.html#ordering)
. | | PGRST101 | 405 | For [functions](https://docs.postgrest.org/en/stable/references/api/functions.html#functions)
, only `GET` and `POST` verbs are allowed. Any other verb will throw this error. | | PGRST102 | 400 | An invalid request body was sent(e.g. an empty body or malformed JSON). | | PGRST103 | 416 | An invalid range was specified for [Limits and Pagination](https://docs.postgrest.org/en/stable/references/api/pagination_count.html#limits)
. | | PGRST105 | 405 | An invalid [PUT](https://docs.postgrest.org/en/stable/references/api/tables_views.html#upsert-put)
request was done | | PGRST106 | 406 | The schema specified when [switching schemas](https://docs.postgrest.org/en/stable/references/api/schemas.html#multiple-schemas)
is not present in the [db-schemas](https://docs.postgrest.org/en/stable/references/configuration.html#db-schemas)
configuration variable. | | PGRST107 | 415 | The `Content-Type` sent in the request is invalid. | | PGRST108 | 400 | The filter is applied to a embedded resource that is not specified in the `select` part of the query string. See [Embedded Filters](https://docs.postgrest.org/en/stable/references/api/resource_embedding.html#embed-filters)
. | | PGRST111 | 500 | An invalid `response.headers` was set. See [Response Headers](https://docs.postgrest.org/en/stable/references/transactions.html#guc-resp-hdrs)
. | | PGRST112 | 500 | The status code must be a positive integer. See [Response Status Code](https://docs.postgrest.org/en/stable/references/transactions.html#guc-resp-status)
. | | PGRST114 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/stable/references/api/tables_views.html#upsert-put)
, when [limits and offsets](https://docs.postgrest.org/en/stable/references/api/pagination_count.html#limits)
are used. | | PGRST115 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/stable/references/api/tables_views.html#upsert-put)
, when the primary key in the query string and the body are different. | | PGRST116 | 406 | More than 1 or no items where returned when requesting a singular response. See [Singular or Plural](https://docs.postgrest.org/en/stable/references/api/resource_representation.html#singular-plural)
. | | PGRST117 | 405 | The HTTP verb used in the request in not supported. | | PGRST118 | 400 | Could not order the result using the related table because there is no many-to-one or one-to-one relationship between them. | | PGRST120 | 400 | An embedded resource can only be filtered using the `is.null` or `not.is.null` [operators](https://docs.postgrest.org/en/stable/references/api/tables_views.html#operators)
. | | PGRST121 | 500 | PostgREST can’t parse the JSON objects in RAISE `PGRST` error. See [raise headers](https://docs.postgrest.org/en/stable/references/errors.html#raise-headers)
. | | PGRST122 | 400 | Invalid preferences found in `Prefer` header with `Prefer: handling=strict`. See [Strict or Lenient Handling](https://docs.postgrest.org/en/stable/references/api/preferences.html#prefer-handling)
. | | PGRST123 | 400 | Aggregate functions are disabled. See [db-aggregates-enabled](https://docs.postgrest.org/en/stable/references/configuration.html#db-aggregates-enabled)
. | | PGRST124 | 400 | `max-affected` preference is violated. See [Max Affected](https://docs.postgrest.org/en/stable/references/api/preferences.html#prefer-max-affected)
. | | PGRST125 | 404 | Invalid path is specified in request URL. | | PGRST126 | 404 | Open API config is disabled but API root path is accessed. See [openapi-mode](https://docs.postgrest.org/en/stable/references/configuration.html#openapi-mode)
. | | PGRST127 | 400 | The feature specified in the `details` field is not implemented. | | PGRST128 | 400 | `max-affected` preference is violated with `RPC` call. See [Max Affected](https://docs.postgrest.org/en/stable/references/api/preferences.html#prefer-max-affected)
. | #### Group 2 - Schema Cache[](https://docs.postgrest.org/en/stable/references/errors.html#group-2-schema-cache "Link to this heading") Related to a [Schema Cache](https://docs.postgrest.org/en/stable/references/schema_cache.html#schema-cache) . Most of the time, these errors are solved by [Schema Cache Reloading](https://docs.postgrest.org/en/stable/references/schema_cache.html#schema-reloading) . | Code | HTTP status | Description | | --- | --- | --- | | PGRST200 | 400 | Caused by stale foreign key relationships, otherwise any of the embedding resources or the relationship itself may not exist in the database. | | PGRST201 | 300 | An ambiguous embedding request was made. See [Foreign Key Joins on Multiple Foreign Key Relationships](https://docs.postgrest.org/en/stable/references/api/resource_embedding.html#complex-rels)
. | | PGRST202 | 404 | Caused by a stale function signature, otherwise the function may not exist in the database. | | PGRST203 | 300 | Caused by requesting overloaded functions with the same argument names but different types, or by using a `POST` verb to request overloaded functions with a `JSON` or `JSONB` type unnamed parameter. The solution is to rename the function or add/modify the names of the arguments. | | PGRST204 | 400 | Caused when the [column specified](https://docs.postgrest.org/en/stable/references/api/tables_views.html#specify-columns)
in the `columns` query parameter is not found. | | PGRST205 | 404 | Caused when the [table specified](https://docs.postgrest.org/en/stable/references/api/tables_views.html#tables-views)
in the URI is not found. | #### Group 3 - JWT[](https://docs.postgrest.org/en/stable/references/errors.html#group-3-jwt "Link to this heading") Related to the authentication process using JWT. You can follow the [Tutorial 1 - The Golden Key](https://docs.postgrest.org/en/stable/tutorials/tut1.html#tut1) for an example on how to implement authentication and the [Authentication page](https://docs.postgrest.org/en/stable/references/auth.html) for more information on this process. | Code | HTTP status | Description | | --- | --- | --- | | PGRST300 | 500 | A [JWT secret](https://docs.postgrest.org/en/stable/references/configuration.html#jwt-secret)
is missing from the configuration. | | PGRST301 | 401 | Provided JWT couldn’t be decoded or it is invalid. | | PGRST302 | 401 | Attempted to do a request without [Bearer Authentication](https://docs.postgrest.org/en/stable/references/auth.html#bearer-auth)
when the anonymous role is disabled by not setting it in [db-anon-role](https://docs.postgrest.org/en/stable/references/configuration.html#db-anon-role)
. | | PGRST303 | 401 | [JWT claims validation](https://docs.postgrest.org/en/stable/references/auth.html#jwt-claims-validation)
or parsing failed. | #### Group X - Internal[](https://docs.postgrest.org/en/stable/references/errors.html#group-x-internal "Link to this heading") Internal errors. If you encounter any of these, you may have stumbled on a PostgREST bug, please [open an issue](https://github.com/PostgREST/postgrest/issues) and we’ll be glad to fix it. | Code | HTTP status | Description | | --- | --- | --- | | PGRSTX00 | 500 | Internal errors related to the library used for connecting to the database. | Custom Errors[](https://docs.postgrest.org/en/stable/references/errors.html#custom-errors "Link to this heading") ------------------------------------------------------------------------------------------------------------------- You can customize the errors by using the [RAISE statement](https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE) on functions. ### RAISE errors with HTTP Status Codes[](https://docs.postgrest.org/en/stable/references/errors.html#raise-errors-with-http-status-codes "Link to this heading") Custom status codes can be done by raising SQL exceptions inside [functions](https://docs.postgrest.org/en/stable/references/api/functions.html#functions) . For instance, here’s a saucy function that always responds with an error: CREATE OR REPLACE FUNCTION just\_fail() RETURNS void LANGUAGE plpgsql AS $$ BEGIN RAISE EXCEPTION 'I refuse!' USING DETAIL \= 'Pretty simple', HINT \= 'There is nothing you can do.'; END $$; Calling the function returns HTTP 400 with the body { "message":"I refuse!", "details":"Pretty simple", "hint":"There is nothing you can do.", "code":"P0001" } One way to customize the HTTP status code is by raising particular exceptions according to the PostgREST [error to status code mapping](https://docs.postgrest.org/en/stable/references/errors.html#status-codes) . For example, `RAISE insufficient_privilege` will respond with HTTP 401/403 as appropriate. For even greater control of the HTTP status code, raise an exception of the `PTxyz` type. For instance to respond with HTTP 402, raise `PT402`: RAISE sqlstate 'PT402' using message \= 'Payment Required', detail \= 'Quota exceeded', hint \= 'Upgrade your plan'; Returns: HTTP/1.1 402 Payment Required Content-Type: application/json; charset=utf-8 { "message": "Payment Required", "details": "Quota exceeded", "hint": "Upgrade your plan", "code": "PT402" } ### Add HTTP Headers with RAISE[](https://docs.postgrest.org/en/stable/references/errors.html#add-http-headers-with-raise "Link to this heading") For full control over headers and status you can raise a `PGRST` SQLSTATE error. You can achieve this by adding the `code`, `message`, `detail` and `hint` in the PostgreSQL error message field as a JSON object. Here, the `details` and `hint` are optional. Similarly, the `status` and `headers` must be added to the SQL error detail field as a JSON object. For instance: RAISE sqlstate 'PGRST' USING message \= '{"code":"123","message":"Payment Required","details":"Quota exceeded","hint":"Upgrade your plan"}', detail \= '{"status":402,"headers":{"X-Powered-By":"Nerd Rage"}}'; Returns: HTTP/1.1 402 Payment Required Content-Type: application/json; charset=utf-8 X-Powered-By: Nerd Rage { "message": "Payment Required", "details": "Quota exceeded", "hint": "Upgrade your plan", "code": "123" } For non standard HTTP status, you can optionally add `status_text` to describe the status code. For status code `419` the detail field may look like this: detail \= '{"status":419,"status\_text":"Page Expired","headers":{"X-Powered-By":"Nerd Rage"}}'; If PostgREST can’t parse the JSON objects `message` and `detail`, it will throw a `PGRST121` error. See [Errors from PostgREST](https://docs.postgrest.org/en/stable/references/errors.html#pgrst1) . Proxy-Status Header[](https://docs.postgrest.org/en/stable/references/errors.html#proxy-status-header "Link to this heading") ------------------------------------------------------------------------------------------------------------------------------- For error cases, the standard [Proxy-Status](https://www.rfc-editor.org/rfc/rfc9209.html#name-the-proxy-status-http-field) header is returned with the error code. The error code comes from either [PostgREST](https://docs.postgrest.org/en/stable/references/errors.html#pgrst-errors) , [PostgreSQL](https://docs.postgrest.org/en/stable/references/errors.html#postgresql-errors) or [Custom](https://docs.postgrest.org/en/stable/references/errors.html#custom-errors) errors. This is useful when doing `HEAD` requests where the HTTP status is not descriptive enough. For example, doing a request on a table with high count (say 30\_000\_000), we get: HEAD /table HTTP/1.1 Prefer: count=exact HTTP/1.1 500 Internal Server Error Proxy-Status: PostgREST; error=57014 The PostgreSQL error code `57014` ([ref](https://www.postgresql.org/docs/current/errcodes-appendix.html) ) reveals that the error is due to a short `statement_timeout` value. --- # Schema Isolation — PostgREST 10.2 documentation * [](https://docs.postgrest.org/en/v10/index.html) * Schema Isolation * [View page source](https://docs.postgrest.org/en/v10/_sources/schema_structure.rst.txt) * * * Note This page is a work in progress. Schema Isolation[](https://docs.postgrest.org/en/v10/schema_structure.html#schema-isolation "Link to this heading") ===================================================================================================================== A PostgREST instance exposes all the tables, views, and stored procedures of a single [PostgreSQL schema](https://www.postgresql.org/docs/current/ddl-schemas.html) (a namespace of database objects). This means private data or implementation details can go inside different private schemas and be invisible to HTTP clients. It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which insulate the internal details from the outside world. This allows you to change the internals of your schema and maintain backwards compatibility. It also keeps your code easier to refactor, and provides a natural way to do API versioning.  Functions[](https://docs.postgrest.org/en/v10/schema_structure.html#functions "Link to this heading") ======================================================================================================= By default, when a function is created, the privilege to execute it is not restricted by role. The function access is `PUBLIC` — executable by all roles (more details at [PostgreSQL Privileges page](https://www.postgresql.org/docs/current/ddl-priv.html) ). This is not ideal for an API schema. To disable this behavior, you can run the following SQL statement: ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC; This will change the privileges for all functions created in the future in all schemas. Currently there is no way to limit it to a single schema. In our opinion it’s a good practice anyway. Note It is however possible to limit the effect of this clause only to functions you define. You can put the above statement at the beginning of the API schema definition, and then at the end reverse it with: ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO PUBLIC; This will work because the `alter default privileges` statement has effect on function created _after_ it is executed. See [PostgreSQL alter default privileges](https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html) for more details. After that, you’ll need to grant EXECUTE privileges on functions explicitly: GRANT EXECUTE ON FUNCTION login TO anonymous; GRANT EXECUTE ON FUNCTION signup TO anonymous; You can also grant execute on all functions in a schema to a higher privileged role: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api TO web\_user; Security definer[](https://docs.postgrest.org/en/v10/schema_structure.html#security-definer "Link to this heading") --------------------------------------------------------------------------------------------------------------------- A function is executed with the privileges of the user who calls it. This means that the user has to have all permissions to do the operations the procedure performs. If the function accesses private database objects, your [API roles](https://docs.postgrest.org/en/v10/auth.html#roles) won’t be able to successfully execute the function. Another option is to define the function with the `SECURITY DEFINER` option. Then only one permission check will take place, the permission to call the function, and the operations in the function will have the authority of the user who owns the function itself. \-- login as a user wich has privileges on the private schemas \-- create a sample function create or replace function login(email text, pass text) returns jwt\_token as $$ begin \-- access to a private schema called 'auth' select auth.user\_role(email, pass) into \_role; \-- other operations \-- ... end; $$ language plpgsql security definer; Note the `SECURITY DEFINER` keywords at the end of the function. See [PostgreSQL documentation](https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY) for more details. Views[](https://docs.postgrest.org/en/v10/schema_structure.html#views "Link to this heading") =============================================================================================== Views are invoked with the privileges of the view owner, much like stored procedures with the `SECURITY DEFINER` option. When created by a SUPERUSER role, all [row-level security](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) will be bypassed unless a different, non-SUPERUSER owner is specified. For changing this, we can create a non-SUPERUSER role and make this role the view’s owner. CREATE ROLE api\_views\_owner NOINHERIT; ALTER VIEW sample\_view OWNER TO api\_views\_owner; Rules[](https://docs.postgrest.org/en/v10/schema_structure.html#rules "Link to this heading") ----------------------------------------------------------------------------------------------- Insertion on views with complex [rules](https://www.postgresql.org/docs/current/sql-createrule.html) might not work out of the box with PostgREST. It’s recommended that you [use triggers instead of rules](https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_rules) . If you want to keep using rules, a workaround is to wrap the view insertion in a stored procedure and call it through the [Stored Procedures](https://docs.postgrest.org/en/v10/api.html#s-procs) interface. For more details, see this [github issue](https://github.com/PostgREST/postgrest/issues/1283) . --- # Installation — PostgREST 14 documentation * [](https://docs.postgrest.org/en/stable/index.html) * Installation * [View page source](https://docs.postgrest.org/en/stable/_sources/explanations/install.rst.txt) * * * Installation[](https://docs.postgrest.org/en/stable/explanations/install.html#installation "Link to this heading") ==================================================================================================================== The release page has [pre-compiled binaries for macOS, Windows, Linux and FreeBSD](https://github.com/PostgREST/postgrest/releases/latest) . The Linux binary is a static executable that can be run on any Linux distribution. You can also use your OS package manager. macOSFreeBSDLinuxWindows You can install PostgREST from the [Homebrew official repo](https://formulae.brew.sh/formula/postgrest) . brew install postgrest You can install PostgREST from the [official ports](https://www.freshports.org/www/hs-postgrest) . pkg install hs-postgrest Arch LinuxNix via nixpkgsNix via flake You can install PostgREST from the [community repo](https://archlinux.org/packages/extra/x86_64/postgrest/) . pacman \-S postgrest You can install PostgREST from nixpkgs. nix-env \-i postgrest You can install PostgREST via flake. { inputs.postgrest.url \= "github:postgrest/postgrest"; \# ... } You can install PostgREST using [Chocolatey](https://community.chocolatey.org/packages/postgrest) or [Scoop](https://github.com/ScoopInstaller/Scoop) . choco install postgrest scoop install postgrest Supported PostgreSQL versions[](https://docs.postgrest.org/en/stable/explanations/install.html#supported-postgresql-versions "Link to this heading") ------------------------------------------------------------------------------------------------------------------------------------------------------ | | | | --- | --- | | **Supported** | PostgreSQL >= 13 | PostgREST works with all PostgreSQL versions still [officially supported](https://www.postgresql.org/support/versioning/) . Running PostgREST[](https://docs.postgrest.org/en/stable/explanations/install.html#running-postgrest "Link to this heading") ------------------------------------------------------------------------------------------------------------------------------ If you downloaded PostgREST from the release page, first extract the compressed file to obtain the executable. \# For UNIX platforms tar Jxf postgrest-\[version\]\-\[platform\].tar.xz \# On Windows you should unzip the file Now you can run PostgREST with the `--help` flag to see usage instructions: \# Running postgrest binary ./postgrest \--help \# Running postgrest installed from a package manager postgrest \--help \# You should see a usage help message The PostgREST server reads a configuration file as its only argument: postgrest /path/to/postgrest.conf \# You can also generate a sample config file with \# postgrest -e > postgrest.conf \# You'll need to edit this file and remove the usage parts for postgrest to read it For a complete reference of the configuration file, see [Configuration](https://docs.postgrest.org/en/stable/references/configuration.html#configuration) . Note If you see a dialog box like this on Windows, it may be that the `pg_config` program is not in your system path.  It usually lives in `C:Program FilesPostgreSQL
) | | [Upserts](https://docs.postgrest.org/en/v10/api.html#upsert) | Primary keys | | [Insertions](https://docs.postgrest.org/en/v10/api.html#insert) | Primary keys (optional: only if the Location header is requested) | | [OPTIONS requests](https://docs.postgrest.org/en/v10/api.html#options-requests) | View INSTEAD OF TRIGGERS and primary keys | | [OpenAPI Support](https://docs.postgrest.org/en/v10/api.html#open-api) | Table columns, primary keys and foreign keys | | View columns and INSTEAD OF TRIGGERS | | Function signature | The Stale Schema Cache[](https://docs.postgrest.org/en/v10/schema_cache.html#the-stale-schema-cache "Link to this heading") ----------------------------------------------------------------------------------------------------------------------------- When you make changes on the metadata mentioned above, the schema cache will turn stale on a running PostgREST. Future requests that use the above features will need the [schema cache to be reloaded](https://docs.postgrest.org/en/v10/schema_cache.html#schema-reloading) ; otherwise, you’ll get an error instead of the expected result. For instance, let’s see what would happen if you have a stale schema cache for foreign key relationships and function signatures. ### Stale Foreign Key Relationships[](https://docs.postgrest.org/en/v10/schema_cache.html#stale-foreign-key-relationships "Link to this heading") Suppose you add a `cities` table to your database and define a foreign key that references an existing `countries` table. Then, you make a request to get the `cities` and their belonging `countries`. HTTPCurl GET /cities?select=name,country:countries(id,name) HTTP/1.1 curl "http://localhost:3000/cities?select=name,country:countries(id,name)" The result will be an error: { "hint": "Verify that 'cities' and 'countries' exist in the schema 'api' and that there is a foreign key relationship between them. If a new relationship was created, try reloading the schema cache.", "details": null, "code": "PGRST200", "message": "Could not find a relationship between 'cities' and 'countries' in the schema cache" } As you can see, PostgREST couldn’t find the newly created foreign key in the schema cache. See [Schema Cache Reloading](https://docs.postgrest.org/en/v10/schema_cache.html#schema-reloading) and [Automatic Schema Cache Reloading](https://docs.postgrest.org/en/v10/schema_cache.html#auto-schema-reloading) to solve this issue. ### Stale Function Signature[](https://docs.postgrest.org/en/v10/schema_cache.html#stale-function-signature "Link to this heading") The same issue will occur on newly created functions on a running PostgREST. CREATE FUNCTION plus\_one(num integer) RETURNS integer AS $$ SELECT num + 1; $$ LANGUAGE SQL IMMUTABLE; HTTPCurl GET /rpc/plus\_one?num=1 HTTP/1.1 curl "http://localhost:3000/rpc/plus\_one?num=1" { "hint": "If a new function was created in the database with this name and parameters, try reloading the schema cache.", "details": null, "code": "PGRST202", "message": "Could not find the api.plus\_one(num) function in the schema cache" } Here, PostgREST tries to find the function on the stale schema to no avail. See [Schema Cache Reloading](https://docs.postgrest.org/en/v10/schema_cache.html#schema-reloading) and [Automatic Schema Cache Reloading](https://docs.postgrest.org/en/v10/schema_cache.html#auto-schema-reloading) to solve this issue. Schema Cache Reloading[](https://docs.postgrest.org/en/v10/schema_cache.html#schema-cache-reloading "Link to this heading") ----------------------------------------------------------------------------------------------------------------------------- To reload the cache without restarting the PostgREST server, send a SIGUSR1 signal to the server process. killall \-SIGUSR1 postgrest For docker you can do: docker kill \-s SIGUSR1
else 401 | insufficient privileges | | other | 400 | | PostgREST Error Codes[](https://docs.postgrest.org/en/v10/errors.html#postgrest-error-codes "Link to this heading") ===================================================================================================================== PostgREST error codes have the form `PGRSTgxx`, where `PGRST` is the prefix that differentiates the error from a PostgreSQL error, `g` is the group where the error belongs and `xx` is the number that identifies the error in the group. Group 0 - Connection[](https://docs.postgrest.org/en/v10/errors.html#group-0-connection "Link to this heading") ----------------------------------------------------------------------------------------------------------------- Related to the connection with the database. | Code | HTTP status | Description | | --- | --- | --- | | PGRST000 | 503 | Could not connect with the database due to an incorrect [db-uri](https://docs.postgrest.org/en/v10/configuration.html#db-uri)
or due to the PostgreSQL service not running. | | PGRST001 | 503 | Could not connect with the database due to an internal error. | | PGRST002 | 503 | Could not connect with the database when building the [Schema Cache](https://docs.postgrest.org/en/v10/schema_cache.html#schema-cache)
due to the PostgreSQL service not running. | | PGRST003 | 504 | The request timed out waiting for a pool connection to be available. See [db-pool-acquisition-timeout](https://docs.postgrest.org/en/v10/configuration.html#db-pool-acquisition-timeout)
. | Group 1 - Api Request[](https://docs.postgrest.org/en/v10/errors.html#group-1-api-request "Link to this heading") ------------------------------------------------------------------------------------------------------------------- Related to the HTTP request elements. | Code | HTTP status | Description | | --- | --- | --- | | PGRST100 | 400 | Parsing error in the query string parameter. See [Horizontal Filtering (Rows)](https://docs.postgrest.org/en/v10/api.html#h-filter)
, [Operators](https://docs.postgrest.org/en/v10/api.html#operators)
and [Ordering](https://docs.postgrest.org/en/v10/api.html#ordering)
. | | PGRST101 | 405 | For [functions](https://docs.postgrest.org/en/v10/api.html#s-procs)
, only `GET` and `POST` verbs are allowed. Any other verb will throw this error. | | PGRST102 | 400 | An invalid request body was sent(e.g. an empty body or malformed JSON). | | PGRST103 | 416 | An invalid range was specified for [Limits and Pagination](https://docs.postgrest.org/en/v10/api.html#limits)
. | | PGRST105 | 405 | An invalid [PUT](https://docs.postgrest.org/en/v10/api.html#upsert-put)
request was done | | PGRST106 | 406 | The schema specified when [switching schemas](https://docs.postgrest.org/en/v10/api.html#multiple-schemas)
is not present in the [db-schemas](https://docs.postgrest.org/en/v10/configuration.html#db-schemas)
configuration variable. | | PGRST107 | 415 | The `Content-Type` sent in the request is invalid. | | PGRST108 | 400 | The filter is applied to a embedded resource that is not specified in the `select` part of the query string. See [Embedded Filters](https://docs.postgrest.org/en/v10/api.html#embed-filters)
. | | PGRST109 | 400 | Restricting a Deletion or an Update using limits must include the ordering of a unique column. See [Limited Updates/Deletions](https://docs.postgrest.org/en/v10/api.html#limited-update-delete)
. | | PGRST110 | 400 | When restricting a Deletion or an Update using limits modifies more rows than the maximum specified in the limit. See [Limited Updates/Deletions](https://docs.postgrest.org/en/v10/api.html#limited-update-delete)
. | | PGRST111 | 500 | An invalid `response.headers` was set. See [Setting Response Headers](https://docs.postgrest.org/en/v10/api.html#guc-resp-hdrs)
. | | PGRST112 | 500 | The status code must be a positive integer. See [Setting Response Status Code](https://docs.postgrest.org/en/v10/api.html#guc-resp-status)
. | | PGRST113 | 406 | More than one column was returned for a scalar result. See [Response Formats For Scalar Responses](https://docs.postgrest.org/en/v10/api.html#scalar-return-formats)
. | | PGRST114 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/v10/api.html#upsert-put)
, when [limits and offsets](https://docs.postgrest.org/en/v10/api.html#limits)
are used. | | PGRST115 | 400 | For an [UPSERT using PUT](https://docs.postgrest.org/en/v10/api.html#upsert-put)
, when the primary key in the query string and the body are different. | | PGRST116 | 406 | More than 1 or no items where returned when requesting a singular response. See [Singular or Plural](https://docs.postgrest.org/en/v10/api.html#singular-plural)
. | | PGRST117 | 405 | The HTTP verb used in the request in not supported. | Group 2 - Schema Cache[](https://docs.postgrest.org/en/v10/errors.html#group-2-schema-cache "Link to this heading") --------------------------------------------------------------------------------------------------------------------- Related to a [stale schema cache](https://docs.postgrest.org/en/v10/schema_cache.html#stale-schema) . Most of the time, these errors are solved by [reloading the schema cache](https://docs.postgrest.org/en/v10/schema_cache.html#schema-reloading) . | Code | HTTP status | Description | | --- | --- | --- | | PGRST200 | 400 | Caused by [Stale Foreign Key Relationships](https://docs.postgrest.org/en/v10/schema_cache.html#stale-fk-relationships)
, otherwise any of the embedding resources or the relationship itself may not exist in the database. | | PGRST201 | 300 | An ambiguous embedding request was made. See [Embedding Disambiguation](https://docs.postgrest.org/en/v10/api.html#embed-disamb)
. | | PGRST202 | 404 | Caused by a [Stale Function Signature](https://docs.postgrest.org/en/v10/schema_cache.html#stale-function-signature)
, otherwise the function may not exist in the database. | | PGRST203 | 300 | Caused by requesting overloaded functions with the same argument names but different types, or by using a `POST` verb to request overloaded functions with a `JSON` or `JSONB` type unnamed parameter. The solution is to rename the function or add/modify the names of the arguments. | | PGRST204 | 400 | Caused when the [column specified](https://docs.postgrest.org/en/v10/api.html#specify-columns)
in the `columns` query parameter is not found. | Group 3 - JWT[](https://docs.postgrest.org/en/v10/errors.html#group-3-jwt "Link to this heading") --------------------------------------------------------------------------------------------------- Related to the authentication process using JWT. You can follow the [Tutorial 1 - The Golden Key](https://docs.postgrest.org/en/v10/tutorials/tut1.html#tut1) for an example on how to implement authentication and the [Authentication page](https://docs.postgrest.org/en/v10/auth.html) for more information on this process. | Code | HTTP status | Description | | --- | --- | --- | | PGRST300 | 500 | A [JWT secret](https://docs.postgrest.org/en/v10/configuration.html#jwt-secret)
is missing from the configuration. | | PGRST301 | 401 | Any error related to the verification of the JWT, which means that the JWT provided is invalid in some way. | | PGRST302 | 401 | Attempted to do a request without [authentication](https://docs.postgrest.org/en/v10/auth.html#client-auth)
when the anonymous role is disabled by not setting it in [db-anon-role](https://docs.postgrest.org/en/v10/configuration.html#db-anon-role)
. | Group X - Internal[](https://docs.postgrest.org/en/v10/errors.html#group-x-internal "Link to this heading") ------------------------------------------------------------------------------------------------------------- Internal errors. If you encounter any of these, you may have stumbled on a PostgREST bug, please [open an issue](https://github.com/PostgREST/postgrest/issues) and we’ll be glad to fix it. | Code | HTTP status | Description | | --- | --- | --- | | PGRSTX00 | 500 | Internal errors related to the library used for connecting to the database. | --- # Observability — PostgREST 14 documentation * [](https://docs.postgrest.org/en/stable/index.html) * Observability * [View page source](https://docs.postgrest.org/en/stable/_sources/references/observability.rst.txt) * * * Observability[](https://docs.postgrest.org/en/stable/references/observability.html#observability "Link to this heading") ========================================================================================================================== Observability allows measuring a system’s current state based on the data it generates, such as logs, metrics, and traces. Logs[](https://docs.postgrest.org/en/stable/references/observability.html#logs "Link to this heading") -------------------------------------------------------------------------------------------------------- PostgREST logs basic request information to `stdout`, including the authenticated user if available, the requesting IP address and user agent, the URL requested, the HTTP response status and the response body size in bytes if available. With [log-level](https://docs.postgrest.org/en/stable/references/configuration.html#log-level) set to `info`, we get: 127.0.0.1 \- user \[26/Jul/2021:01:56:38 \-0500\] "GET /clients HTTP/1.1" 200 56 "" "curl/7.64.0" 127.0.0.1 \- anonymous \[26/Jul/2021:01:56:48 \-0500\] "GET /unexistent HTTP/1.1" 404 162 "" "curl/7.64.0" For diagnostic information about the server itself, PostgREST logs to `stderr`: > * The full version of the connected PostgreSQL database. > > * [Schema Cache](https://docs.postgrest.org/en/stable/references/schema_cache.html#schema-cache) > statistics. > > * The messages received by the [Listener](https://docs.postgrest.org/en/stable/references/listener.html#listener) > . > 06/May/2024:08:16:11 \-0500: Starting PostgREST 12.1... 06/May/2024:08:16:11 \-0500: Successfully connected to PostgreSQL 14.10 (Ubuntu 14.10\-0ubuntu0.22.04.1) on x86\_64\-pc\-linux\-gnu, compiled by gcc (Ubuntu 11.4.0\-1ubuntu1~22.04) 11.4.0, 64\-bit 06/May/2024:08:16:11 \-0500: Connection Pool initialized with a maximum size of 10 connections 06/May/2024:08:16:11 \-0500: API server listening on port 3000 06/May/2024:08:16:11 \-0500: Listening for database notifications on the "pgrst" channel 06/May/2024:08:16:11 \-0500: Config reloaded 06/May/2024:08:16:11 \-0500: Schema cache queried in 3.8 milliseconds 06/May/2024:08:16:11 \-0500: Schema cache loaded 15 Relations, 8 Relationships, 8 Functions, 0 Domain Representations, 4 Media Type Handlers 06/May/2024:14:11:27 \-0500: Received a config reload message on the "pgrst" channel 06/May/2024:14:11:27 \-0500: Config reloaded Note Logs are based on the `log-level` setting. See [log-level](https://docs.postgrest.org/en/stable/references/configuration.html#log-level) . ### SQL Query Logs[](https://docs.postgrest.org/en/stable/references/observability.html#sql-query-logs "Link to this heading") To log the SQL queries executed for a request, set the [log-query](https://docs.postgrest.org/en/stable/references/configuration.html#log-query) to `true`. It will be logged based on the current [log-level](https://docs.postgrest.org/en/stable/references/configuration.html#log-level) setting. log-level \= "warn" log-query \= "true" The SQL queries will only be logged on `400` HTTP errors and up. So, if the user requests a resource without sufficient privileges: curl "localhost:3000/protected\_table" This will be logged by PostgREST: 17/Feb/2025:17:28:15 \-0500: WITH pgrst\_source AS ( SELECT "public"."protected\_table".\* FROM "public"."protected\_table" ) SELECT null::bigint AS total\_result\_set, pg\_catalog.count(\_postgrest\_t) AS page\_total, coalesce(json\_agg(\_postgrest\_t), '\[\]') AS body, nullif(current\_setting('response.headers', true), '') AS response\_headers, nullif(current\_setting('response.status', true), '') AS response\_status, '' AS response\_inserted FROM ( SELECT \* FROM pgrst\_source ) \_postgrest\_t 127.0.0.1 \- web\_anon \[17/Feb/2025:17:28:15 \-0500\] "GET /protected\_table HTTP/1.1" 401 99 "" "curl/8.7.1" ### Database Logs[](https://docs.postgrest.org/en/stable/references/observability.html#database-logs "Link to this heading") Additionally, to find all the SQL operations, you can watch the database logs. By default PostgreSQL does not keep these logs, so you’ll need to make the configuration changes below. Find `postgresql.conf` inside your PostgreSQL data directory (to find that, issue the command `show data_directory;`). Either find the settings scattered throughout the file and change them to the following values, or append this block of code to the end of the configuration file. # send logs where the collector can access them log\_destination \= "stderr" # collect stderr output to log files logging\_collector \= on # save logs in pg\_log/ under the pg data directory log\_directory \= "pg\_log" # (optional) new log file per day log\_filename \= "postgresql-%Y-%m-%d.log" # log every kind of SQL statement log\_statement \= "all" Restart the database and watch the log file in real-time to understand how HTTP requests are being translated into SQL commands. Note On Docker you can enable the logs by using a custom `init.sh`: #!/bin/sh echo "log\_statement = 'all'" \>> /var/lib/postgresql/data/postgresql.conf After that you can start the container and check the logs with `docker logs`. docker run \-v "$(pwd)/init.sh":"/docker-entrypoint-initdb.d/init.sh" \-d postgres docker logs \-f
<%2$s>
%3$s
$html$,
$1.id,
case when $1.done then 's' else 'span' end,
api.sanitize\_html($1.task)
);
$$ language sql stable;
create or replace function api.html\_all\_todos() returns text as $$
select coalesce(
string\_agg(api.html\_todo(t), '' order by t.id), '
There is nothing else to do.
' ) from api.todos t; $$ language sql; These two functions are used to build the to-do list template. We won’t use them as PostgREST endpoints. * The `api.html_todo` function uses the table `api.todos` as a parameter and formats each item into a list element `- ` wrapper for all the list elements. It uses [string\_arg](https://www.postgresql.org/docs/current/functions-aggregate.html)
to concatenate all the to-dos in a single text value. It also returns an alternative message, instead of a list, when the `api.todos` table is empty.
Next, let’s add an endpoint to register a to-do in the database and modify the `/rpc/index` page accordingly.
create or replace function api.add\_todo(\_task text) returns "text/html" as $$
insert into api.todos(task) values (\_task);
select api.html\_all\_todos();
$$ language sql;
create or replace function api.index() returns "text/html" as $$
select $html$
PostgREST + HTMX To-Do List
$html$
|| api.html\_all\_todos() ||
$html$
$html$;
$$ language sql;
* The `/rpc/add_todo` endpoint allows us to add a new to-do using the `_task` parameter and returns an `html` with all the to-dos in the database.
* The `/rpc/index` now adds the `hx-headers='{"Accept": "text/html"}'` tag to the ``. This will make sure that all htmx elements inside the body send this header, otherwise PostgREST won’t recognize it as HTML.
There is also a `