[ Download from release page ]
The release page has pre-compiled binaries for Mac OS X, Windows, and several Linux distributions. Extract the tarball and run the binary inside with the --help
flag to see usage instructions:
# Untar the release (available at https://github.com/begriffs/postgrest/releases/latest)
$ tar Jxf postgrest-[version]-[platform].tar.xz
# Try running it
$ ./postgrest --help
# You should see a usage help message
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 Files\PostgreSQL\<version>\bin
. See this article about how to modify the system path.
To use PostgREST you will need an underlying database (PostgreSQL version 9.5 or greater is required). You can use something like Amazon RDS but installing your own locally is cheaper and more convenient for development.
On Windows, PostgREST will fail to run unless the PostgreSQL binaries are on the system path. To test whether this is the case, run pg_config
from the command line. You should see it output a list of paths.
The PostgREST server 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
The 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/static/libpq-connect.html#AEN45347
db-uri = "postgres://user:pass@host:5432/dbname"
# The name of which database schema to expose to REST clients
db-schema = "api"
# The database role to use when no client authentication is provided.
# Can (and probably 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 roles
.
Here is the full list of configuration parameters.
Name | Type | Default | Required |
---|---|---|---|
db-uri db-schema db-anon-role db-pool server-host server-port server-proxy-uri jwt-secret secret-is-base64 max-rows pre-request |
String String String Int String Int String String Bool Int String |
10 *4 3000 False ∞ |
Y Y Y |
- 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. 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"
. Also allows connections over Unix sockets for higher performance.- db-schema
The database schema to expose to REST clients. Tables, views and stored procedures in this schema will get API endpoints.
- db-anon-role
The database role to use when executing commands on behalf of unauthenticated clients.
- db-pool
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.- 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 port to bind the web server.
- 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"
]
}
- jwt-secret
The secret or JSON Web Key (JWK) used to decode JWT tokens clients provide for authentication. For security the key must be at least thirty-two 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 seeasym_keys
.- secret-is-base64
When this is set to
true
, the value derived fromjwt-secret
will be treated as a base64 encoded secret.- 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.
- 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.
PostgREST outputs basic request logging to stdout. When running it in an SSH session you must detach it from stdout or it will be terminated when the session closes. The easiest technique is redirecting the output to a log file or to the syslog:
ssh foo@example.com \
'postgrest foo.conf </dev/null >/var/log/postgrest.log 2>&1 &'
# another option is to pipe the output into "logger -t postgrest"
(Avoid nohup postgrest
because the HUP signal is used for manual schema_reloading
.)
The official PostgREST Docker image consults an internal /etc/postgrest.conf
file. To customize this file you can either mount a replacement configuration file into the container, or use environment variables. The environment variables will be interpolated into the default config file.
These variables match the options shown in our configuration
section, except they are capitalized, have a prefix, and use underscores. To get a list of the available environment variables, run this:
docker inspect -f "{{.Config.Env}}" postgrest/postgrest
There are two ways to run the PostgREST container: with an existing external database, or through docker-compose.
The first way to run PostgREST in Docker is to connect it to an existing native database on the host.
# Pull the official image
docker pull postgrest/postgrest
# Run the server
docker run --rm --net=host -p 3000:3000 \
-e PGRST_DB_URI="postgres://postgres@localhost/postgres" \
-e PGRST_DB_ANON_ROLE="postgres" \
postgrest/postgrest
The database connection string above is just an example. Adjust the role and password as necessary. You may need to edit PostgreSQL's pg_hba.conf
to grant the user local login access.
Note
Docker on Mac does not support the --net=host
flag. Instead you'll need to create an IP address alias to the host. Requests for the IP address from inside the container are unable to resolve and fall back to resolution by the host.
sudo ifconfig lo0 10.0.0.10 alias
You should then use 10.0.0.10 as the host in your database connection string. Also remember to include the IP address in the listen_address
within postgresql.conf. For instance:
listen_addresses = 'localhost,10.0.0.10'
To avoid having to install the database at all, you can run both it and the server in containers and link them together with docker-compose. Use this configuration:
# docker-compose.yml
server:
image: postgrest/postgrest
ports:
- "3000:3000"
links:
- db:db
environment:
PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
PGRST_DB_SCHEMA: public
PGRST_DB_ANON_ROLE: app_user
db:
image: postgres
ports:
- "5432:5432"
environment:
POSTGRES_DB: app_db
POSTGRES_USER: app_user
POSTGRES_PASSWORD: password
Go into the directory where you saved this file and run docker-compose up
. You will see the logs of both the database and PostgREST, and be able to access the latter on port 3000.
Note
We discourage building and using PostgREST on Alpine Linux because of a reported GHC memory leak on that platform.
When a pre-built binary does not exist for your system you can build the project from source. You'll also need to do this if you want to help with development. Stack makes it easy. It will install any necessary Haskell dependencies on your system.
- Install Stack for your platform
Install Library Dependencies
Operating System Dependencies Ubuntu/Debian libpq-dev, libgmp-dev CentOS/Fedora/Red Hat postgresql-devel, zlib-devel, gmp-devel BSD postgresql95-server OS X postgresql, gmp Build and install binary
git clone https://github.com/begriffs/postgrest.git cd postgrest # adjust local-bin-path to taste stack build --install-ghc --copy-bins --local-bin-path /usr/local/bin
Note
If building fails and your system has less than 1GB of memory, try adding a swap file.
- Check that the server is installed:
postgrest --help
.
To properly run postgrest tests one needs to create a database. To do so, use the test creation script create_test_database
in the test/
folder.
The script expects the following parameters:
test/create_test_db connection_uri database_name [test_db_user] [test_db_user_password]
Use the connection URI to specify the user, password, host, and port. Do not provide the database in the connection URI. The PostgreSQL role you are using to connect must be capable of creating new databases.
The database_name
is the name of the database that stack test
will connect to. If the database of the same name already exists on the server, the script will first drop it and then re-create it.
Optionally, specify the database user stack test
will use. The user will be given necessary permissions to reset the database after every test run.
If the user is not specified, the script will generate the role name postgrest_test_
suffixed by the chosen database name, and will generate a random password for it.
Optionally, if specifying an existing user to be used for the test connection, one can specify the password the user has.
The script will return the db uri to use in the tests--this uri corresponds to the db-uri
parameter in the configuration file that one would use in production.
Generating the user and the password allows one to create the database and run the tests against any PostgreSQL server without any modifications to the server. (Such as allowing accounts without a password or setting up trust authentication, or requiring the server to be on the same localhost the tests are run from).
To run the tests, one must supply the database uri in the environment variable POSTGREST_TEST_CONNECTION
.
Typically, one would create the database and run the test in the same command line, using the postgres superuser:
POSTGREST_TEST_CONNECTION=$(test/create_test_db "postgres://postgres:pwd@database-host" test_db) stack test
For repeated runs on the same database, one should export the connection variable:
export POSTGREST_TEST_CONNECTION=$(test/create_test_db "postgres://postgres:pwd@database-host" test_db)
stack test
stack test
...
If the environment variable is empty or not specified, then the test runner will default to connection uri
postgres://postgrest_test@localhost/postgrest_test
This connection assumes the test server on the localhost:code:
with the user postgrest_test without the password and the database of the same name.
The test database will remain after the test, together with four new roles created on the PostgreSQL server. To permanently erase the created database and the roles, run the script test/delete_test_database
, using the same superuser role used for creating the database:
test/destroy_test_db connection_uri database_name
The ability to connect to non-local PostgreSQL simplifies the test setup. One elegant way of testing is to use a disposable PostgreSQL in docker.
For example, if local development is on a mac with Docker for Mac installed:
$ docker run --name db-scripting-test -e POSTGRES_PASSWORD=pwd -p 5434:5432 -d postgres
$ POSTGREST_TEST_CONNECTION=$(test/create_test_db "postgres://postgres:pwd@localhost:5434" test_db) stack test
Additionally, if one creates a docker container to run stack test (this is necessary on Mac OS Sierra with GHC below 8.0.1, where stack test
fails), one can run PostgreSQL in a separate linked container, or use the locally installed PostgreSQL app.
Build the test container with test/Dockerfile.test
:
$ docker build -t pgst-test - < test/Dockerfile.test
$ mkdir .stack-work-docker ~/.stack-linux
The first run of the test container will take a long time while the dependencies get cached. Creating the ~/.stack-linux
folder and mapping it as a volume into the container ensures that we can run the container in disposable mode and not worry about subsequent runs being slow. .stack-work-docker
is also mapped into the container and must be specified when using stack from Linux, not to interfere with the .stack-work
for local development. (On Sierra, stack build
works, while stack test
fails with GHC 8.0.1).
Linked containers:
$ docker run --name pg -e POSTGRES_PASSWORD=pwd -d postgres
$ docker run --rm -it -v `pwd`:`pwd` -v ~/.stack-linux:/root/.stack --link pg:pg -w="`pwd`" -v `pwd`/.stack-work-docker:`pwd`/.stack-work pgst-test bash -c "POSTGREST_TEST_CONNECTION=$(test/create_test_db "postgres://postgres:pwd@pg" test_db) stack test"
Stack test in Docker for Mac, PostgreSQL app on mac:
$ host_ip=$(ifconfig en0 | grep 'inet ' | cut -f 2 -d' ')
$ export POSTGREST_TEST_CONNECTION=$(test/create_test_db "postgres://postgres@$HOST" test_db)
$ docker run --rm -it -v `pwd`:`pwd` -v ~/.stack-linux:/root/.stack -v `pwd`/.stack-work-docker:`pwd`/.stack-work -e "HOST=$host_ip" -e "POSTGREST_TEST_CONNECTION=$POSTGREST_TEST_CONNECTION" -w="`pwd`" pgst-test bash -c "stack test"
$ test/destroy_test_db "postgres://postgres@localhost" test_db