Configuration

Config parameters can be provided via Config File, Environment Variables or In-Database Configuration. Using Configuration Reloading you can modify the parameters without restarting the server.

Without configuration, PostgREST won’t be able to serve requests. At the minimum it needs either a role to serve anonymous requests with - or a secret to use for JWT authentication.

To connect to a database it uses a libpq connection string. The connection string can be set in the configuration file or via environment variable or can be read from an external file. See db-uri for details. Any parameter that is not set in the connection string is read from libpq environment variables. The default connection string is postgresql://, which reads all parameters from the environment.

Config parameters are read in the following order:

From the config file.
From environment variables, overriding values from the config file.
From the database, overriding values from both the config file and environment variables.

Config File

PostgREST can read a config file. There is no predefined location for this file, you must specify the file path as the one and only argument to the server:

./postgrest /path/to/postgrest.conf

The configuration file must contain a set of key value pairs:

# postgrest.conf

# The standard connection URI format, documented at
# https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
db-uri       = "postgres://user:pass@host:5432/dbname"

# The database role to use when no client authentication is provided.
# Should differ from authenticator
db-anon-role = "anon"

# The secret to verify the JWT for authenticated requests with.
# Needs to be 32 characters minimum.
jwt-secret           = "reallyreallyreallyreallyverysafe"
jwt-secret-is-base64 = false

# Port the postgrest process is listening on for http requests
server-port = 80

You can run postgrest --example to display all possible configuration parameters and how to use them in a configuration file.

Environment Variables

You can also set these configuration parameters using environment variables. They are capitalized, have a PGRST_ prefix, and use underscores. For example: PGRST_DB_URI corresponds to db-uri and PGRST_APP_SETTINGS_* to app.settings.*.

See the full list of environment variable names on List of parameters.

In-Database Configuration

Using a pre-config function, you can configure the server with database settings. For example, you can configure db-schemas and jwt-secret like this:

# postgrest.conf

db-pre-config  = "postgrest.pre_config"

# or env vars

PGRST_DB_PRE_CONFIG = "postgrest.pre_config"

-- create a dedicated schema, hidden from the API
create schema postgrest;
-- grant usage on this schema to the authenticator
grant usage on schema postgrest to authenticator;

-- the function can configure postgREST by using set_config
create or replace function postgrest.pre_config()
returns void as $$
  select
      set_config('pgrst.db_schemas', 'schema1, schema2', true)
    , set_config('pgrst.jwt_secret', 'REALLYREALLYREALLYREALLYVERYSAFE', true);
$$ language sql;

Note that underscores(_) need to be used instead of dashes(-) for the in-database config parameters. See the full list of in-database names on List of parameters.

You can disable the in-database configuration by setting db-config to false.

Note

For backwards compatibility, you can do in-db config by modifying the authenticator role. This is no longer recommended as it requires SUPERUSER.

ALTER ROLE authenticator SET pgrst.db_schemas = "tenant1, tenant2, tenant3"
ALTER ROLE authenticator IN DATABASE <your_database_name> SET pgrst.db_schemas = "tenant4, tenant5" -- database-specific setting, overrides the previous setting

Configuration Reloading

It’s possible to reload PostgREST’s configuration without restarting the server. You can do this via signal or via notification.

Any modification to the Config File will be applied during reload.
Any modification to the In-Database Configuration will be applied during reload.
Not all settings are reloadable, see the reloadable list on List of parameters.
It’s not possible to change Environment Variables for a running process, hence reloading a Docker container configuration will not work. In these cases, you can restart the process or use In-Database Configuration.

Reload with signal

To reload the configuration via signal, send a SIGUSR2 signal to the server process.

killall -SIGUSR2 postgrest

Reload with NOTIFY

To reload the configuration from within the database, you can use a NOTIFY command.

NOTIFY pgrst, 'reload config'

The "pgrst" notification channel is enabled by default. You can name the channel with db-channel and enable or disable it with db-channel-enabled.

List of parameters

Name	Type	Default	Reloadable	Environment variable	In-database name
admin-server-port	Int			PGRST_ADMIN_SERVER_PORT
app.settings.*	String		Y	PGRST_APP_SETTINGS_*
db-anon-role	String		Y	PGRST_DB_ANON_ROLE	pgrst.db_anon_role
db-channel	String	pgrst	Y	PGRST_DB_CHANNEL
db-channel-enabled	Boolean	True	Y	PGRST_DB_CHANNEL_ENABLED
db-config	Boolean	True	Y	PGRST_DB_CONFIG
db-pre-config	String		Y	PGRST_DB_PRE_CONFIG	pgrst.db_pre_config
db-extra-search-path	String	public	Y	PGRST_DB_EXTRA_SEARCH_PATH	pgrst.db_extra_search_path
db-max-rows	Int	∞	Y	PGRST_DB_MAX_ROWS	pgrst.db_max_rows
db-plan-enabled	Boolean	False	Y	PGRST_DB_PLAN_ENABLED	pgrst.db_plan_enabled
db-pool	Int	10		PGRST_DB_POOL
db-pool-acquisition-timeout	Int	10		PGRST_DB_POOL_ACQUISITION_TIMEOUT
db-pool-max-idletime	Int	30		PGRST_DB_POOL_MAX_IDLETIME
db-pool-max-lifetime	Int	1800		PGRST_DB_POOL_MAX_LIFETIME
db-pre-request	String		Y	PGRST_DB_PRE_REQUEST	pgrst.db_pre_request
db-prepared-statements	Boolean	True	Y	PGRST_DB_PREPARED_STATEMENTS	pgrst.db_prepared_statements
db-root-spec	String		Y	PGRST_DB_ROOT_SPEC	pgrst.db_root_spec
db-schemas	String	public	Y	PGRST_DB_SCHEMAS	pgrst.db_schemas
db-tx-end	String	commit		PGRST_DB_TX_END
db-uri	String	postgresql://		PGRST_DB_URI
db-use-legacy-gucs	Boolean	True	Y	PGRST_DB_USE_LEGACY_GUCS	pgrst.db_use_legacy_gucs
jwt-aud	String		Y	PGRST_JWT_AUD	pgrst.jwt_aud
jwt-role-claim-key	String	.role	Y	PGRST_JWT_ROLE_CLAIM_KEY	pgrst.jwt_role_claim_key
jwt-secret	String		Y	PGRST_JWT_SECRET	pgrst.jwt_secret
jwt-secret-is-base64	Boolean	False	Y	PGRST_JWT_SECRET_IS_BASE64	pgrst.jwt_secret_is_base64
log-level	String	error		PGRST_LOG_LEVEL
openapi-mode	String	follow-privileges	Y	PGRST_OPENAPI_MODE	pgrst.openapi_mode
openapi-security-active	Boolean	False	Y	PGRST_OPENAPI_SECURITY_ACTIVE	pgrst.openapi_security_active
openapi-server-proxy-uri	String		Y	PGRST_OPENAPI_SERVER_PROXY_URI	pgrst.openapi_server_proxy_uri
raw-media-types	String		Y	PGRST_RAW_MEDIA_TYPES	pgrst.raw_media_types
server-host	String	!4		PGRST_SERVER_HOST
server-port	Int	3000		PGRST_SERVER_PORT
server-trace-header	String		Y	PGRST_SERVER_TRACE_HEADER	pgrst.server_trace_header
server-unix-socket	String			PGRST_SERVER_UNIX_SOCKET
server-unix-socket-mode	String	660		PGRST_SERVER_UNIX_SOCKET_MODE

admin-server-port

Specifies the port for the Health Check endpoints.

app.settings.*

Arbitrary settings that can be used to pass in secret keys directly as strings, or via OS environment variables. For instance: app.settings.jwt_secret = "$(MYAPP_JWT_SECRET)" will take MYAPP_JWT_SECRET from the environment and make it available to postgresql functions as current_setting('app.settings.jwt_secret').

db-anon-role

The database role to use when executing commands on behalf of unauthenticated clients. For more information, see Overview of role system.

When unset anonymous access will be blocked.

db-channel

The name of the notification channel that PostgREST uses for Schema Cache Reloading and configuration reloading.

db-channel-enabled

When this is set to true, the notification channel specified in db-channel is enabled.

You should set this to false when using PostgresSQL behind an external connection pooler such as PgBouncer working in transaction pooling mode. See this section for more information.

db-config

Enables the in-database configuration.

db-pre-config

Name of the function that does in-database configuration.

db-extra-search-path

Extra schemas to add to the search_path of every request. These schemas tables, views and stored procedures don’t get API endpoints, they can only be referred from the database objects inside your db-schemas.

This parameter was meant to make it easier to use PostgreSQL extensions (like PostGIS) that are outside of the db-schemas.

Multiple schemas can be added in a comma-separated string, e.g. public, extensions.

db-max-rows

For backwards compatibility, this config parameter is also available without prefix as “max-rows”.

A hard limit to the number of rows PostgREST will fetch from a view, table, or stored procedure. Limits payload size for accidental or malicious requests.

db-plan-enabled

When this is set to true, the execution plan of a request can be retrieved by using the Accept: application/vnd.pgrst.plan header. See Execution plan.

It’s recommended to use this in testing environments only since it reveals internal database details. However, if you choose to use it in production you can add a db-pre-request to filter the requests that can use this feature.

For example, to only allow requests from an IP address to get the execution plans:
-- Assuming a proxy(Nginx, Cloudflare, etc) passes an "X-Forwarded-For" header(https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/X-Forwarded-For)
create or replace function filter_plan_requests()
returns void as $$
declare
  headers   json := current_setting('request.headers', true)::json;
  client_ip text := coalesce(headers->>'x-forwarded-for', '');
  accept    text := coalesce(headers->>'accept', '');
begin
  if accept like 'application/vnd.pgrst.plan%' and client_ip != '144.96.121.73' then
    raise insufficient_privilege using
      message = 'Not allowed to use application/vnd.pgrst.plan';
  end if;
end; $$ language plpgsql;

-- set this function on your postgrest.conf
-- db-pre-request = filter_plan_requests

db-pool

Number of maximum connections to keep open in PostgREST’s database pool.

db-pool-acquisition-timeout

Specifies the maximum time in seconds that the request will wait for the pool to free up a connection slot to the database.

db-pool-max-idletime

For backwards compatibility, this config parameter is also available as “db-pool-timeout”.

Time in seconds to close idle pool connections.

db-pool-max-lifetime

Specifies the maximum time in seconds of an existing connection in the pool.

db-pre-request

For backwards compatibility, this config parameter is also available without prefix as “pre-request”.

A schema-qualified stored procedure name to call right after the Transaction-Scoped Settings are set. See Pre-Request.

db-prepared-statements

Enables or disables prepared statements.

When disabled, the generated queries will be parameterized (invulnerable to SQL injection) but they will not be prepared (cached in the database session). Not using prepared statements will noticeably decrease performance, so it’s recommended to always have this setting enabled.

You should only set this to false when using PostgresSQL behind an external connection pooler such as PgBouncer working in transaction pooling mode. See this section for more information.

db-root-spec

Function to override the OpenAPI response. See Overriding Full OpenAPI Response.

db-schemas

For backwards compatibility, this config parameter is also available in singular as “db-schema”.

The list of database schemas to expose to clients. See Schemas.

db-tx-end

Specifies how to terminate the database transactions.

# The transaction is always committed
db-tx-end = "commit"

# The transaction is committed unless a "Prefer: tx=rollback" header is sent
db-tx-end = "commit-allow-override"

# The transaction is always rolled back
db-tx-end = "rollback"

# The transaction is rolled back unless a "Prefer: tx=commit" header is sent
db-tx-end = "rollback-allow-override"

db-uri

The standard connection PostgreSQL URI format. Symbols and unusual characters in the password or other fields should be percent encoded to avoid a parse error. If enforcing an SSL connection to the database is required you can use sslmode in the URI, for example postgres://user:pass@host:5432/dbname?sslmode=require.

The user with whom PostgREST connects to the database is also known as the authenticator role. For more information see Overview of role system.

When running PostgREST on the same machine as PostgreSQL, it is also possible to connect to the database using a Unix socket and the Peer Authentication method as an alternative to TCP/IP communication and authentication with a password, this also grants higher performance. To do this you can omit the host and the password, e.g. postgres://user@/dbname, see the libpq connection string documentation for more details.

Choosing a value for this parameter beginning with the at sign such as @filename (e.g. @./configs/my-config) loads the connection string out of an external file.

db-use-legacy-gucs

Determine if GUC request settings for headers, cookies and jwt claims use the legacy names (string with dashes, invalid starting from PostgreSQL v14) with text values instead of the new names (string without dashes, valid on all PostgreSQL versions) with json values.

On PostgreSQL versions 14 and above, this parameter is ignored.

jwt-aud

Specifies the JWT audience claim. If this claim is present in the client provided JWT then you must set this to the same value as in the JWT, otherwise verifying the JWT will fail.

jwt-role-claim-key

For backwards compatibility, this config parameter is also available without prefix as “role-claim-key”.

A JSPath DSL that specifies the location of the role key in the JWT claims. This can be used to consume a JWT provided by a third party service like Auth0, Okta or Keycloak. Usage examples:
# {"postgrest":{"roles": ["other", "author"]}}
# the DSL accepts characters that are alphanumerical or one of "_$@" as keys
jwt-role-claim-key = ".postgrest.roles[1]"

# {"https://www.example.com/role": { "key": "author }}
# non-alphanumerical characters can go inside quotes(escaped in the config value)
jwt-role-claim-key = ".\"https://www.example.com/role\".key"

jwt-secret

The secret or JSON Web Key (JWK) (or set) used to decode JWT tokens clients provide for authentication. For security the key must be at least 32 characters long. If this parameter is not specified then PostgREST refuses authentication requests. Choosing a value for this parameter beginning with the at sign such as @filename loads the secret out of an external file. This is useful for automating deployments. Note that any binary secrets must be base64 encoded. Both symmetric and asymmetric cryptography are supported. For more info see Asymmetric Keys.

Choosing a value for this parameter beginning with the at sign such as @filename (e.g. @./configs/my-config) loads the secret out of an external file.

Warning

Only when using the Config File, if the jwt-secret contains a $ character by itself it will give errors. In this case, use $$ and PostgREST will interpret it as a single $ character.

jwt-secret-is-base64

When this is set to true, the value derived from jwt-secret will be treated as a base64 encoded secret.

log-level

Specifies the level of information to be logged while running PostgREST.
# Only startup and db connection recovery messages are logged
log-level = "crit"

# All the "crit" level events plus server errors (status 5xx) are logged
log-level = "error"

# All the "error" level events plus request errors (status 4xx) are logged
log-level = "warn"

# All the "warn" level events plus all requests (every status code) are logged
log-level = "info"
Because currently there’s no buffering for logging, the levels with minimal logging(crit/error) will increase throughput.

openapi-mode

Specifies how the OpenAPI output should be displayed.

# Follows the privileges of the JWT role claim (or from db-anon-role if the JWT is not sent)
# Shows information depending on the permissions that the role making the request has
openapi-mode = "follow-privileges"

# Ignores the privileges of the JWT role claim (or from db-anon-role if the JWT is not sent)
# Shows all the exposed information, regardless of the permissions that the role making the request has
openapi-mode = "ignore-privileges"

# Disables the OpenApi output altogether.
# Throws a `404 Not Found` error when accessing the API root path
openapi-mode = "disabled"

openapi-security-active

When this is set to true, security options are included in the OpenAPI output.

openapi-server-proxy-uri

Overrides the base URL used within the OpenAPI self-documentation hosted at the API root path. Use a complete URI syntax scheme:[//[user:password@]host[:port]][/]path[?query][#fragment]. Ex. https://postgrest.com
{
  "swagger": "2.0",
  "info": {
    "version": "0.4.3.0",
    "title": "PostgREST API",
    "description": "This is a dynamic API generated by PostgREST"
  },
  "host": "postgrest.com:443",
  "basePath": "/",
  "schemes": [
    "https"
  ]
}

raw-media-types

This serves to extend the Media Types that PostgREST currently accepts through an Accept header.

These media types can be requested by following the same rules as the ones defined in Scalar Function Response Format.

As an example, the below config would allow you to request an image and a XML file by doing a request with Accept: image/png or Accept: font/woff2, respectively.
raw-media-types="image/png, font/woff2"

server-host

Where to bind the PostgREST web server. In addition to the usual address options, PostgREST interprets these reserved addresses with special meanings:

* - any IPv4 or IPv6 hostname

*4 - any IPv4 or IPv6 hostname, IPv4 preferred

!4 - any IPv4 hostname

*6 - any IPv4 or IPv6 hostname, IPv6 preferred

!6 - any IPv6 hostname

server-port

The TCP port to bind the web server.

server-trace-header

The header name used to trace HTTP requests. See Trace Header.

server-unix-socket

Unix domain socket where to bind the PostgREST web server. If specified, this takes precedence over server-port. Example:
server-unix-socket = "/tmp/pgrst.sock"

server-unix-socket-mode

Unix file mode to be set for the socket specified in server-unix-socket Needs to be a valid octal between 600 and 777.
server-unix-socket-mode = "660"