File: README.md
PostgREST serves a fully RESTful API from any existing PostgreSQL
database. It provides a cleaner, more standards-compliant, faster
API than you are likely to write from scratch.
Sponsors
Big thanks to our sponsors! You can join them by supporting PostgREST on Patreon.
Usage
See the docs for how to install PostgREST on your platform. You can also use Docker.
Invoke for help:
bashpostgrest --help
[Documentation](http://postgrest.org)
Latest documentation is at postgrest.org. You can contribute to the docs in PostgREST/postgrest/docs.
Performance
TLDR; subsecond response times for up to 2000 requests/sec on Heroku
free tier. If you're used to servers written in interpreted languages,
prepare to be pleasantly surprised by PostgREST performance.
Three factors contribute to the speed. First the server is written
in Haskell using the
Warp
HTTP server (aka a compiled language with lightweight threads).
Next it delegates as much calculation as possible to the database
including
- Serializing JSON responses directly in SQL
- Data validation
- Authorization
- Combined row counting and retrieval
- Data post in single command (
returning *)
Finally it uses the database efficiently with the
Hasql library
by
- Keeping a pool of db connections
- Using the PostgreSQL binary protocol
- Being stateless to allow horizontal scaling
Security
PostgREST handles
authentication (via JSON Web
Tokens) and delegates authorization to the role information defined in
the database. This ensures there is a single declarative source of truth
for security. When dealing with the database the server assumes the
identity of the currently authenticated user, and for the duration of
the connection cannot do anything the user themselves couldn't. Other
forms of authentication can be built on top of the JWT primitive. See
the docs for more information.
Versioning
A robust long-lived API needs the freedom to exist in multiple
versions. PostgREST does versioning through database schemas. This
allows you to expose tables and views without making the app brittle.
Underlying tables can be superseded and hidden behind public facing
views.
Self-documentation
PostgREST uses the OpenAPI standard to
generate up-to-date documentation for APIs. You can use a tool like
Swagger-UI to render
interactive documentation for demo requests against the live API server.
This project uses HTTP to communicate other metadata as well. For
instance the number of rows returned by an endpoint is reported by -
and limited with - range headers. More about
that.
Data Integrity
Rather than relying on an Object Relational Mapper and custom
imperative coding, this system requires you to put declarative constraints
directly into your database. Hence no application can corrupt your
data (including your API server).
The PostgREST exposes HTTP interface with safeguards to prevent
surprises, such as enforcing idempotent PUT requests.
See examples of PostgreSQL
constraints
and the API guide.
Supporting development
You can help PostgREST ongoing maintenance and development by making a regular donation through Patreon https://www.patreon.com/postgrest
Every donation will be spent on making PostgREST better for the whole community.
Contributing
Contributions are always welcome and appreciated. Please see the Contributing guidelines.
Thanks
The PostgREST organization is grateful to:
- The project sponsors and backers who support PostgREST's development.
- The project contributors who have improved PostgREST immensely with their code
and good judgement. See more details in the changelog.
The cool logo came from Mikey Casalaina.
File: docs/_diagrams/README.md
ERD
The ER diagrams were created with https://github.com/BurntSushi/erd/.
You can go download erd from https://github.com/BurntSushi/erd/releases and then do:
./erd_static-x86-64 -i ./er/film.er -o ../_static/film.pngThe fonts used belong to the GNU FreeFont family. You can download them here: http://ftp.gnu.org/gnu/freefont/
UML
The UML diagrams are created with https://plantuml.com/.
PlantUML only creates one diagram per file.
That's why we need to create another one for dark mode.
For example, for the file uml/arch.uml there's uml/dark/arch-dark.uml which includes the first one:
plantuml -tsvg uml/arch.uml -o ../../_static
plantuml -tsvg -darkmode uml/dark/arch-dark.uml -o ../../../_staticFile: docs/explanations/architecture.rst
Architecture
############
This page describes the architecture of PostgREST.
Bird's Eye View
You can click on the components to navigate to their respective documentation.
.. container:: img-dark
.. See https://github.com/sphinx-doc/sphinx/issues/2240#issuecomment-187366626
.. raw:: html
<object width="100%" data="../_static/arch-dark.svg" type="image/svg+xml"></object>.. container:: img-light
.. raw:: html
<object width="100%" data="../_static/arch.svg" type="image/svg+xml"></object>Code Map
This section talks briefly about various important modules.
Main
The starting point of the program is Main.hs <https://github.com/PostgREST/postgrest/blob/main/src/executable/Main.hs>_.
CLI
Main then calls CLI.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/CLI.hs>_, which is in charge of :ref:cli.
App
App.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/App.hs>_ is then in charge of composing the different modules.
Auth
Auth.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/Auth.hs>_ is in charge of :ref:authn.
Api Request
ApiRequest.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/ApiRequest.hs>_ is in charge of parsing the URL query string (following PostgREST syntax), the request headers, and the request body.
A request might be rejected at this level if it's invalid. For example when providing an unknown media type to PostgREST or using an unknown HTTP method.
Plan
Using the Schema Cache, Plan.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/Plan.hs>_ generates an internal AST, filling out-of-band SQL details (like an ON CONFLICT (pk) clause) required to complete the user request.
A request might be rejected at this level if it's invalid. For example when doing resource embedding on a nonexistent resource.
Query
Query.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/Query.hs>_ generates the SQL queries (parametrized and prepared) required to satisfy the user request.
Only at this stage a connection from the pool might be used.
Schema Cache
SchemaCache.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/SchemaCache.hs>_ is in charge of :ref:schema_cache.
Config
Config.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/Config.hs>_ is in charge of :ref:configuration.
Admin
Admin.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/Admin.hs>_ is in charge of the :ref:admin_server.
Listener
Reload.hs <https://github.com/PostgREST/postgrest/blob/main/src/library/PostgREST/AppState/Reload.hs>_ is in charge of the :ref:listener.
File: docs/explanations/db_authz.rst
.. _db_authz:
Database Authorization
######################
Database authorization is the process of granting and verifying database access permissions. PostgreSQL manages permissions using the concept of roles.
Users and Groups
A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.
Roles for Each Web User
PostgREST can accommodate either viewpoint. If you treat a role as a single user then :ref:user_impersonation does most of what you need. When an authenticated user makes a request PostgREST will switch into the database role for that user, which in addition to restricting queries, is available to SQL through the :code:current_user variable.
You can use row-level security to flexibly restrict visibility and access for the current user. Here is an example <https://www.enterprisedb.com:443/blog/application-users-vs-row-level-security>_ from Tomas Vondra, a chat table storing messages sent between users. Users can insert rows into it to send messages to other users, and query it to see messages sent to them by other users.
.. code-block:: postgres
CREATE TABLE chat (
message_uuid UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
message_time TIMESTAMP NOT NULL DEFAULT now(),
message_from NAME NOT NULL DEFAULT current_user,
message_to NAME NOT NULL,
message_subject VARCHAR(64) NOT NULL,
message_body TEXT
);
ALTER TABLE chat ENABLE ROW LEVEL SECURITY;
We want to enforce a policy that ensures a user can see only those messages sent by them or intended for them. Also we want to prevent a user from forging the message_from column with another person's name.
PostgreSQL allows us to set this policy with row-level security:
.. code-block:: postgres
CREATE POLICY chat_policy ON chat
USING ((message_to = current_user) OR (message_from = current_user))
WITH CHECK (message_from = current_user)
Anyone accessing the generated API endpoint for the chat table will see exactly the rows they should, without our needing custom imperative server-side coding.
.. warning::
Roles are namespaced per-cluster rather than per-database so they may be prone to collision.
Web Users Sharing Role
Alternately database roles can represent groups instead of (or in addition to) individual users. You may choose that all signed-in users for a web app share the role webuser. You can distinguish individual users by including extra claims in the JWT such as email.
.. code:: json
{
"role": "webuser",
"email": "[email protected]"
}
SQL code can access claims through PostgREST :ref:tx_settings. For instance to get the email claim, call this function:
.. code:: sql
current_setting('request.jwt.claims', true)::json->>'email';
.. note::
For PostgreSQL < 14
.. code:: sql
current_setting('request.jwt.claim.email', true);This allows JWT generation services to include extra information and your database code to react to it. For instance the RLS example could be modified to use this current_setting rather than current_user. The second 'true' argument tells current_setting to return NULL if the setting is missing from the current configuration.
Hybrid User-Group Roles
You can mix the group and individual role policies. For instance we could still have a webuser role and individual users which inherit from it:
.. code-block:: postgres
CREATE ROLE webuser NOLOGIN;
-- grant this role access to certain tables etc
CREATE ROLE user000 NOLOGIN;
GRANT webuser TO user000;
-- now user000 can do whatever webuser can
GRANT user000 TO authenticator;
-- allow authenticator to switch into user000 role
-- (the role itself has nologin)
Schemas
You must explicitly allow roles to access the exposed schemas in :ref:db-schemas.
.. code-block:: postgres
GRANT USAGE ON SCHEMA api TO webuser;
Tables
To let web users access tables you must grant them privileges for the operations you want them to do.
.. code-block:: postgres
GRANT
SELECT
, INSERT
, UPDATE(message_body)
, DELETE
ON chat TO webuser;
You can also choose on which table columns the operation is valid. In the above example, the web user can only update the message_body column.
.. _func_privs:
Functions
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:
.. code-block:: postgres
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:
.. code-block:: postgres
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
This will work because the :code:`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:
.. code-block:: postgres
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:
.. code-block:: postgres
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA api TO web_user;Security definer
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 function performs.
If the function accesses private database objects, your :ref:API roles <roles> won't be able to successfully execute the function.
Another option is to define the function with the :code: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.
.. code-block:: postgres
-- login as a user which has privileges on the private schemas
-- create a sample function
create or replace function login(email text, pass text, out token text) 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
Views are invoked with the privileges of the view owner, much like functions with the SECURITY DEFINER option. When created by a SUPERUSER role, all row-level security <https://www.postgresql.org/docs/current/ddl-rowsecurity.html>_ policies will be bypassed.
If you're on PostgreSQL >= 15, this behavior can be changed by specifying the security_invoker option.
.. code-block:: postgres
CREATE VIEW sample_view WITH (security_invoker = true) AS
SELECT * FROM sample_table;
On PostgreSQL < 15, you can create a non-SUPERUSER role and make this role the view's owner.
.. code-block:: postgres
CREATE ROLE api_views_owner NOSUPERUSER NOBYPASSRLS;
ALTER VIEW sample_view OWNER TO api_views_owner;
File: docs/explanations/install.rst
.. _install:
Installation
############
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.
.. include:: ../shared/installation.rst
.. _pg-dependency:
Supported PostgreSQL versions
=============== =================================
Supported PostgreSQL >= 14
=============== =================================
PostgREST works with all PostgreSQL versions still officially supported <https://www.postgresql.org/support/versioning/>_.
Running PostgREST
If you downloaded PostgREST from the release page, first extract the compressed file to obtain the executable.
.. code-block:: bash
For UNIX platforms
tar Jxf postgrest-[version]-[platform].tar.xz
On Windows you should unzip the file
Now you can run PostgREST with the :code:--help flag to see usage instructions:
.. code-block:: bash
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:
.. code:: bash
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 :ref:configuration.
.. note::
If you see a dialog box like this on Windows, it may be that the :code:pg_config program is not in your system path.
.. image:: ../_static/win-err-dialog.png
It usually lives in :code:C:\Program Files\PostgreSQL\<version>\bin. See this article <https://www.howtogeek.com/118594/how-to-edit-your-system-path-for-easy-command-line-access/>_ about how to modify the system path.
To test that the system path is set correctly, run pg_config from the command line. You should see it output a list of paths.
Docker
You can get the official PostgREST Docker image <https://hub.docker.com/r/postgrest/postgrest>_ with:
.. code-block:: bash
pull the latest version
docker pull postgrest/postgrest
to pull a particular version, use one of the versions on https://hub.docker.com/r/postgrest/postgrest/tags
docker pull postgrest/postgrest:
To configure the container image, use :ref:env_variables_config.
There are two ways to run the PostgREST container: with an existing external database, or through docker-compose.
Containerized PostgREST with native PostgreSQL
The first way to run PostgREST in Docker is to connect it to an existing native database on the host.
.. code-block:: bash
Run the server
docker run --rm --net=host
-e PGRST_DB_URI="postgres://app_user:password@localhost/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 :code:pg_hba.conf to grant the user local login access.
.. note::
Docker on Mac does not support the :code:--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.
.. code-block:: bash
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 :code:listen_address within postgresql.conf. For instance:
.. code-block:: bash
listen_addresses = 'localhost,10.0.0.10'You might also need to add a new IPv4 local connection within pg_hba.conf. For instance:
.. code-block:: bash
host all all 10.0.0.10/32 trustThe docker command will then look like this:
.. code-block:: bash
# Run the server
docker run --rm -p 3000:3000 \
-e PGRST_DB_URI="postgres://app_user:[email protected]/postgres" \
postgrest/postgrest.. _pg-in-docker:
Containerized PostgREST *and* db with docker-compose
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:
.. code-block:: yaml
docker-compose.yml
version: '3'
services:
server:
image: postgrest/postgrest
ports:
- "3000:3000"
environment:
PGRST_SERVER_HOST: 0.0.0.0 # necessary for postgrest --ready flag to work
PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
depends_on:
- db
db:
image: postgres
ports:
- "5432:5432"
environment:
POSTGRES_DB: app_db
POSTGRES_USER: app_user
POSTGRES_PASSWORD: password
# Uncomment this if you want to persist the data.
# volumes:
# - "./pgdata:/var/lib/postgresql/data"
Go into the directory where you saved this file and run :code:docker-compose up. You will see the logs of both the database and PostgREST, and be able to access the latter on port 3000.
If you want to have a visual overview of your API in your browser you can add swagger-ui to your :code:docker-compose.yml:
.. code-block:: yaml
in services:
swagger:
image: swaggerapi/swagger-ui
ports:
- "8080:8080"
expose:
- "8080"
environment:
API_URL: http://localhost:3000/With this you can see the swagger-ui in your browser on port 8080.
.. _docker_cpu_contraint:
Docker Resource Constraints
PostgREST does not support --cpus constraint option <https://docs.docker.com/engine/containers/resource_constraints/#configure-the-default-cfs-scheduler>_.
As a workaround, you may use the GHC RTS <https://ghc.gitlab.haskell.org/ghc/doc/users_guide/runtime_control.html#runtime-system-rts-options>_ -N option. For instance, to limit it to 2 CPU cores, do:
.. code::
Set environment variable GHCRTS set to "-N2"
docker run --rm -p 3000:3000
-e PGRST_DB_URI="postgres://app_user:[email protected]/postgres"
-e GHCRTS="-N2"
postgrest/postgrest
.. _build_source:
Building from Source
When a pre-built binary does not exist for your system you can build the project from source.
You can build PostgREST from source with Stack <https://github.com/commercialhaskell/stack>_. It will install any necessary Haskell dependencies on your system.
Install Stack <https://docs.haskellstack.org/en/stable/#how-to-install-stack>_ for your platformInstall Library Dependencies
===================== =======================================
Operating System Dependencies
===================== =======================================
Ubuntu/Debian libpq-dev, libgmp-dev, zlib1g-dev
CentOS/Fedora/Red Hat postgresql-devel, zlib-devel, gmp-devel
BSD postgresql12-client
macOS libpq, gmp
===================== =======================================Build and install binary
.. code-block:: bash
git clone https://github.com/PostgREST/postgrest.git
cd postgrestadjust 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.
--install-ghcflag is only needed for the first build and can be omitted in the subsequent builds.
- Check that the server is installed: :code:
postgrest --help.
File: docs/explanations/nginx.rst
.. _nginx:
Nginx
PostgREST is a fast way to construct a RESTful API. Its default behavior is great for scaffolding in development. When it's time to go to production it works great too, as long as you take precautions.
PostgREST is a small sharp tool that focuses on performing the API-to-database mapping. We rely on a reverse proxy like Nginx for additional safeguards.
The first step is to create an Nginx configuration file that proxies requests to an underlying PostgREST server.
.. code-block:: nginx
http {
# ...
# upstream configuration
upstream postgrest {
server localhost:3000;
}
# ...
server {
# ...
# expose to the outside world
location /api/ {
default_type application/json;
proxy_hide_header Content-Location;
add_header Content-Location /api/$upstream_http_content_location;
proxy_set_header Connection "";
proxy_http_version 1.1;
proxy_pass http://postgrest/;
}
# ...
}
}
.. note::
For ubuntu, if you already installed nginx through :code:apt you can add this to the config file in
:code:/etc/nginx/sites-enabled/default.
.. _https:
HTTPS
PostgREST aims to do one thing well: add an HTTP interface to a PostgreSQL database. To keep the code small and focused we do not implement HTTPS. Use a reverse proxy such as NGINX to add this, here's how <https://nginx.org/en/docs/http/configuring_https_servers.html>_.
Rate Limiting
Nginx supports "leaky bucket" rate limiting (see official docs <https://nginx.org/en/docs/http/ngx_http_limit_req_module.html>_). Using standard Nginx configuration, routes can be grouped into request zones for rate limiting. For instance we can define a zone for login attempts:
.. code-block:: nginx
limit_req_zone $binary_remote_addr zone=login:10m rate=1r/s;
This creates a shared memory zone called "login" to store a log of IP addresses that access the rate limited urls. The space reserved, 10 MB (:code:10m) will give us enough space to store a history of 160k requests. We have chosen to allow only allow one request per second (:code:1r/s).
Next we apply the zone to certain routes, like a hypothetical function called :code:login.
.. code-block:: nginx
location /rpc/login/ {
# apply rate limiting
limit_req zone=login burst=5;
}
The burst argument tells Nginx to start dropping requests if more than five queue up from a specific IP.
Nginx rate limiting is general and indiscriminate. To rate limit each authenticated request individually you will need to add logic in a :ref:Custom Validation <custom_validation> function.
Alternate URL Structure
As discussed in :ref:singular_plural, there are no special URL forms for singular resources in PostgREST, only operators for filtering. Thus there are no URLs like :code:/people/1. It would be specified instead as
.. code-block:: bash
curl "http://localhost:3000/people?id=eq.1"
-H "Accept: application/vnd.pgrst.object+json"
This allows compound primary keys and makes the intent for singular response independent of a URL convention.
Nginx rewrite rules allow you to simulate the familiar URL convention. The following example adds a rewrite rule for all table endpoints, but you'll want to restrict it to those tables that have a numeric simple primary key named "id."
.. code-block:: nginx
support /endpoint/:id url style
location ~ ^/([a-z_]+)/([0-9]+) {
# make the response singular
proxy_set_header Accept 'application/vnd.pgrst.object+json';
# assuming an upstream named "postgrest"
proxy_pass http://postgrest/$1?id=eq.$2;}
.. TODO
.. Administration
.. API Versioning
.. HTTP Caching
.. Upgrading
File: docs/explanations/schema_isolation.rst
.. _schema_isolation:
Schema Isolation
A PostgREST instance exposes all the tables, views, and functions 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 functions 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.
.. container:: svg-container-md
.. container:: img-dark
.. See https://github.com/sphinx-doc/sphinx/issues/2240#issuecomment-187366626
.. raw:: html
<object width="100%" data="../_static/sch-iso-dark.svg" type="image/svg+xml"></object>.. container:: img-light
.. raw:: html
<object width="100%" data="../_static/sch-iso.svg" type="image/svg+xml"></object>File: docs/how-tos/create-soap-endpoint.rst
.. _create_soap_endpoint:
Create a SOAP endpoint
:author: fjf2002 <https://github.com/fjf2002>_
PostgREST supports :ref:custom_media. With a bit of work, SOAP endpoints become possible.
Minimal Example
This example will simply return the request body, inside a tag therequestbodywas.
Add the following function to your PostgreSQL database:
.. code-block:: postgres
create domain "text/xml" as pg_catalog.xml;
CREATE OR REPLACE FUNCTION my_soap_endpoint(xml) RETURNS "text/xml" AS $$
DECLARE
nsarray CONSTANT text[][] := ARRAY[
ARRAY['soapenv', 'http://schemas.xmlsoap.org/soap/envelope/']
];
BEGIN
RETURN xmlelement(
NAME "soapenv:Envelope",
XMLATTRIBUTES('http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soapenv"),
xmlelement(NAME "soapenv:Header"),
xmlelement(
NAME "soapenv:Body",
xmlelement(
NAME theRequestBodyWas,
(xpath('/soapenv:Envelope/soapenv:Body', $1, nsarray))[1]
)
)
);
END;
$$ LANGUAGE plpgsql;
Do not forget to refresh the :ref:PostgREST schema cache <schema_reloading>.
Use curl for a first test:
.. code-block:: bash
curl http://localhost:3000/rpc/my_soap_endpoint \
--header 'Content-Type: text/xml' \
--header 'Accept: text/xml' \
--data-binary @- <<XML
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
<mySOAPContent>
My SOAP Content
</mySOAPContent>
</soapenv:Body>
</soapenv:Envelope>
XMLThe output should contain the original request body within the therequestbodywas entity,
and should roughly look like:
.. code-block:: xml
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
<soapenv:Body xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<mySOAPContent>
My SOAP Content
</mySOAPContent>
</soapenv:Body>
</soapenv:Body>
</soapenv:Envelope>A more elaborate example
Here we have a SOAP service that converts a fraction to a decimal value,
with pass-through of PostgreSQL errors to the SOAP response.
Please note that in production you probably should not pass through plain database errors
potentially disclosing internals to the client, but instead handle the errors directly.
.. code-block:: postgres
-- helper function
CREATE OR REPLACE FUNCTION _soap_envelope(body xml)
RETURNS xml
LANGUAGE sql
AS $function$
SELECT xmlelement(
NAME "soapenv:Envelope",
XMLATTRIBUTES('http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:soapenv"),
xmlelement(NAME "soapenv:Header"),
xmlelement(NAME "soapenv:Body", body)
);
$function$;
-- helper function
CREATE OR REPLACE FUNCTION _soap_exception(
faultcode text,
faultstring text
)
RETURNS xml
LANGUAGE sql
AS $function$
SELECT _soap_envelope(
xmlelement(NAME "soapenv:Fault",
xmlelement(NAME "faultcode", faultcode),
xmlelement(NAME "faultstring", faultstring)
)
);
$function$;
CREATE OR REPLACE FUNCTION fraction_to_decimal(xml)
RETURNS "text/xml"
LANGUAGE plpgsql
AS $function$
DECLARE
nsarray CONSTANT text[][] := ARRAY[
ARRAY['soapenv', 'http://schemas.xmlsoap.org/soap/envelope/']
];
exc_msg text;
exc_detail text;
exc_hint text;
exc_sqlstate text;
BEGIN
-- simulating a statement that results in an exception:
RETURN _soap_envelope(xmlelement(
NAME "decimalValue",
(
(xpath('/soapenv:Envelope/soapenv:Body/fraction/numerator/text()', $1, nsarray))[1]::text::int
/
(xpath('/soapenv:Envelope/soapenv:Body/fraction/denominator/text()', $1, nsarray))[1]::text::int
)::text::xml
));
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS
exc_msg := MESSAGE_TEXT,
exc_detail := PG_EXCEPTION_DETAIL,
exc_hint := PG_EXCEPTION_HINT,
exc_sqlstate := RETURNED_SQLSTATE;
RAISE WARNING USING
MESSAGE = exc_msg,
DETAIL = exc_detail,
HINT = exc_hint;
RETURN _soap_exception(faultcode => exc_sqlstate, faultstring => concat(exc_msg, ', DETAIL: ', exc_detail, ', HINT: ', exc_hint));
END
$function$;
Let's test the fraction_to_decimal service with illegal values:
.. code-block:: bash
curl http://localhost:3000/rpc/fraction_to_decimal \
--header 'Content-Type: text/xml' \
--header 'Accept: text/xml' \
--data-binary @- <<XML
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
<fraction>
<numerator>42</numerator>
<denominator>0</denominator>
</fraction>
</soapenv:Body>
</soapenv:Envelope>
XMLThe output should roughly look like:
.. code-block:: xml
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
soapenv:Header/
soapenv:Body
soapenv:Fault
References
For more information concerning PostgREST, cf.
- :ref:
function_single_unnamed - :ref:
custom_media. See :ref:any_handler, if you need to support anapplication/soap+xmlmedia type or if you want to respond with XML without sending a media type. - :ref:
Nginx reverse proxy <nginx>
For SOAP reference, visit
- the specification at https://www.w3.org/TR/soap/
- shorter more practical advice is available at https://www.w3schools.com/xml/xml_soap.asp
File: docs/how-tos/providing-images-for-img.rst
.. _providing_img:
Providing images for ````
:author: pkel <https://github.com/pkel>_
In this how-to, you will learn how to create an endpoint for providing images to HTML :code:`` tags without client side JavaScript. In fact, the presented technique is suitable for providing not only images, but arbitrary files.
We will start with a minimal example that highlights the general concept.
Afterwards we present a more detailed solution that fixes a few shortcomings of the first approach.
.. warning::
Be careful when saving binaries in the database, having a separate storage service for these is preferable in most cases. See Storing Binary files in the Database <https://wiki.postgresql.org/wiki/BinaryFilesInDB>_.
Minimal Example
First, we need a public table for storing the files.
.. code-block:: postgres
create table files(
id int primary key
, blob bytea
);
Let's assume this table contains an image of two cute kittens with id 42. We can retrieve this image in binary format from our PostgREST API by using :ref:custom_media:
.. code-block:: postgres
create domain "application/octet-stream" as bytea;
create or replace function file(id int) returns "application/octet-stream" as $$
select blob from files where id = file.id;
$$ language sql;
Now we can request the RPC endpoint :code:/rpc/file?id=42 with the :code:Accept: application/octet-stream header.
.. code-block:: bash
curl "localhost:3000/rpc/file?id=42" -H "Accept: application/octet-stream"
Unfortunately, putting the URL into the :code:src of an :code:`` tag will not work. That's because browsers do not send the required :code:Accept: application/octet-stream header.
Instead, the :code:Accept: image/webp header is sent by many web browsers by default.
Luckily we can change the accepted media type in the function like so:
.. code-block:: postgres
create domain "image/webp" as bytea;
create or replace function file(id int) returns "image/webp" as $$
select blob from files where id = file.id;
$$ language sql;
Now, the image will be displayed in the HTML page:
.. code-block:: html
Improved Version
The basic solution has some shortcomings:
- The response :code:
Content-Typeheader is set to :code:image/webp.
This might be a problem if you want to specify a different format for the file. - Download requests (e.g. Right Click -> Save Image As) to :code:
/files?select=blob&id=eq.42will propose :code:filesas filename.
This might confuse users. - Requests to the binary endpoint are not cached.
This will cause unnecessary load on the database.
The following improved version addresses these problems.
First, in addition to the minimal example, we need to store the media types and names of our files in the database.
.. code-block:: postgres
alter table files
add column type text generated always as (byteamagic_mime(substr(blob, 0, 4100))) stored,
add column name text;
This uses the :code:byteamagic_mime() function from the pg_byteamagic extension <https://github.com/nmandery/pg_byteamagic>_ to automatically generate the type in the :code:files table. To guess the type of a file, it's generally enough to look at the beginning of the file, which is more efficient.
Next, we set modify the function to set the content type and filename.
We use this opportunity to configure some basic, client-side caching.
For production, you probably want to configure additional caches, e.g. on the :ref:reverse proxy <nginx>.
.. code-block:: postgres
create domain "/" as bytea;
create function file(id int) returns "/" as
$$
declare headers text;
declare blob bytea;
begin
select format(
'[{"Content-Type": "%s"},'
'{"Content-Disposition": "inline; filename="%s""},'
'{"Cache-Control": "max-age=259200"}]'
, files.type, files.name)
from files where files.id = file.id into headers;
perform set_config('response.headers', headers, true);
select files.blob from files where files.id = file.id into blob;
if FOUND -- special var, see https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
then return(blob);
else raise sqlstate 'PT404' using
message = 'NOT FOUND',
detail = 'File not found',
hint = format('%s seems to be an invalid file id', file.id);
end if;
end
$$ language plpgsql;
With this, we can obtain the cat image from :code:/rpc/file?id=42. Thus, the resulting HTML will be:
.. code-block:: html