Configuration
PostgREST reads a configuration file to determine information about the database and how to serve client requests. 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
Note
Configuration can be reloaded without restarting the server. See Configuration Reloading.
The configuration file must contain a set of key value pairs. At minimum you must include these keys:
# 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 name of which database schema to expose to REST clients
db-schemas = "api"
# The database role to use when no client authentication is provided.
# Can (and should) differ from user in db-uri
db-anon-role = "anon"
The user specified in the db-uri is also known as the authenticator role. For more information about the anonymous vs authenticator roles see the Overview of Role System.
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.*
.
Configuration Reloading
To reload the configuration without restarting the PostgREST server, send a SIGUSR2 signal to the server process.
killall -SIGUSR2 postgrest
This method does not reload Environment Variables and it will not work for reloading a Docker container configuration. In these cases, you need to restart the PostgREST server or use the In-Database Configuration as an alternative.
Important
The following settings will not be reread when reloading the configuration. You will need to restart PostgREST in that case.
In-Database Configuration
By adding settings to the authenticator role (see Overview of Role System), you can make the database the single source of truth for PostgREST’s configuration. This is enabled by db-config.
For example, you can configure db-schemas and jwt-secret like this:
ALTER ROLE authenticator SET pgrst.db_schemas = "tenant1, tenant2, tenant3"
ALTER ROLE authenticator IN DATABASE <your_database_name> SET pgrst.jwt_secret = "REALLYREALLYREALLYREALLYVERYSAFE"
You can use both database-specific settings with IN DATABASE and cluster-wide settings without it. Database-specific settings will override cluster-wide settings if both are used for the same parameter.
Note that underscores(_
) need to be used instead of dashes(-
) for the in-database config parameters.
Important
For altering a role in this way, you need a SUPERUSER. You might not be able to use this configuration mode on cloud-hosted databases.
When using both the configuration file and the in-database configuration, the latter takes precedence.
Danger
If direct connections to the database are allowed, then it’s not safe to use the in-db configuration for storing the jwt-secret.
The settings of every role are PUBLIC - they can be viewed by any user that queries the pg_catalog.pg_db_role_setting
table.
In this case you should keep the jwt-secret in the configuration file or as environment variables.
In-database configuration reloading
To reload the in-database configuration from within the database, you can use a NOTIFY command.
NOTIFY pgrst, 'reload config'
The "pgrst"
notification channel is enabled by default. For configuring the channel, see db-channel and db-channel-enabled.
List of parameters
Name |
Type |
Default |
Required |
Reloadable |
---|---|---|---|---|
app.settings.* |
String |
Y |
||
db-anon-role |
String |
Y |
Y |
|
db-channel |
String |
pgrst |
Y |
|
db-channel-enabled |
Boolean |
True |
Y |
|
db-config |
Boolean |
True |
Y |
|
db-extra-search-path |
String |
public |
Y |
|
db-max-rows |
Int |
∞ |
Y |
|
db-pool |
Int |
10 |
||
db-pool-timeout |
Int |
10 |
||
db-pre-request |
String |
Y |
||
db-prepared-statements |
Boolean |
True |
Y |
|
db-schemas |
String |
Y |
Y |
|
db-tx-end |
String |
commit |
Y |
|
db-uri |
String |
Y |
||
db-use-legacy-gucs |
Boolean |
True |
Y |
|
jwt-aud |
String |
Y |
||
jwt-role-claim-key |
String |
.role |
Y |
|
jwt-secret |
String |
Y |
||
jwt-secret-is-base64 |
Boolean |
False |
Y |
|
log-level |
String |
error |
Y |
|
openapi-mode |
String |
follow-privileges |
Y |
|
openapi-server-proxy-uri |
String |
Y |
||
raw-media-types |
String |
Y |
||
server-host |
String |
!4 |
||
server-port |
Int |
3000 |
||
server-unix-socket |
String |
|||
server-unix-socket-mode |
String |
660 |
app.settings.*
Environment
PGRST_APP_SETTINGS_*
In-Database
pgrst.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 takeMYAPP_JWT_SECRET
from the environment and make it available to postgresql functions ascurrent_setting('app.settings.jwt_secret')
.
db-anon-role
Environment
PGRST_DB_ANON_ROLE
In-Database
n/a
The database role to use when executing commands on behalf of unauthenticated clients. For more information, see Overview of Role System.
db-channel
Environment
PGRST_DB_CHANNEL
In-Database
n/a
The name of the notification channel that PostgREST uses for Schema Cache Reloading and configuration reloading.
db-channel-enabled
Environment
PGRST_DB_CHANNEL_ENABLED
In-Database
n/a
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
Environment
PGRST_DB_CONFIG
In-Database
n/a
Enables the in-database configuration.
db-extra-search-path
Environment
PGRST_DB_EXTRA_SEARCH_PATH
In-Database
pgrst.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”.
Environment
PGRST_DB_MAX_ROWS
In-Database
pgrst.db_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-pool
Environment
PGRST_DB_POOL
In-Database
n/a
Number of connections to keep open in PostgREST’s database pool. Having enough here for the maximum expected simultaneous client connections can improve performance. Note it’s pointless to set this higher than the
max_connections
GUC in your database.
db-pool-timeout
Environment
PGRST_DB_POOL_TIMEOUT
In-Database
n/a
Time to live, in seconds, for an idle database pool connection. If the timeout is reached the connection will be closed. Once a new request arrives a new connection will be started.
db-pre-request
For backwards compatibility, this config parameter is also available without prefix as “pre-request”.
Environment
PGRST_DB_PRE_REQUEST
In-Database
pgrst.db_pre_request
A schema-qualified stored procedure name to call right after switching roles for a client request. This provides an opportunity to modify SQL variables or raise an exception to prevent the request from completing.
db-prepared-statements
Environment
PGRST_DB_PREPARED_STATEMENTS
In-Database
pgrst.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-schemas
For backwards compatibility, this config parameter is also available in singular as “db-schema”.
Environment
PGRST_DB_SCHEMAS
In-Database
pgrst.db_schemas
The database schema to expose to REST clients. Tables, views and stored procedures in this schema will get API endpoints.
db-schemas = "api"This schema gets added to the search_path of every request.
List of schemas
You can also specify a list of schemas that can be used for schema-based multitenancy and api versioning by Switching Schemas. Example:
db-schemas = "tenant1, tenant2"If you don’t Switch Schemas, the first schema in the list(
tenant1
in this case) is chosen as the default schema.Only the chosen schema gets added to the search_path of every request.
Warning
Never expose private schemas in this way. See Schema Isolation.
db-tx-end
Environment
PGRST_DB_TX_END
In-Database
pgrst.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
Environment
PGRST_DB_URI
In-Database
n/a
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
.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.On older systems like Centos 6, with older versions of libpq, a different db-uri syntax has to be used. In this case the URI is a string of space separated key-value pairs (key=value), so the example above would be
"host=host user=user port=5432 dbname=dbname password=pass"
.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.
db-use-legacy-gucs
Environment
PGRST_DB_USE_LEGACY_GUCS
In-Database
pgrst.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
Environment
PGRST_JWT_AUD
In-Database
pgrst.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”.
Environment
PGRST_JWT_ROLE_CLAIM_KEY
In-Database
pgrst.jwt_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
Environment
PGRST_JWT_SECRET
In-Database
pgrst.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.
jwt-secret-is-base64
Environment
PGRST_JWT_SECRET_IS_BASE64
In-Database
pgrst.jwt_secret_is_base64
When this is set to
true
, the value derived fromjwt-secret
will be treated as a base64 encoded secret.
log-level
Environment
PGRST_LOG_LEVEL
In-Database
n/a
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
Environment
PGRST_OPENAPI_MODE
In-Database
pgrst.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-server-proxy-uri
Environment
PGRST_OPENAPI_SERVER_PROXY_URI
In-Database
pgrst.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
Environment
PGRST_RAW_MEDIA_TYPES
In-Database
pgrst.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 Binary Output.
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
orAccept: text/xml
, respectively.raw-media-types="image/png, text/xml"
server-host
Environment
PGRST_SERVER_HOST
In-Database
pgrst.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
Environment
PGRST_SERVER_PORT
In-Database
pgrst.server_port
The TCP port to bind the web server.
server-unix-socket
Environment
PGRST_SERVER_UNIX_SOCKET
In-Database
pgrst.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
Environment
PGRST_SERVER_UNIX_SOCKET_MODE
In-Database
pgrst.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"