## File: README.md [](https://www.patreon.com/postgrest) [](http://postgrest.org) [](https://hub.docker.com/r/postgrest/postgrest/) [](https://github.com/PostgREST/postgrest/actions?query=branch%3Amain) [](https://app.codecov.io/gh/PostgREST/postgrest) [](http://hackage.haskell.org/package/postgrest) 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](https://www.patreon.com/postgrest). ## Usage 1. See the docs for [how to install PostgREST on your platform](https://docs.postgrest.org/en/stable/explanations/install.html). You can also [use Docker](https://docs.postgrest.org/en/stable/explanations/install.html#docker). 2. Invoke for help: ```bash postgrest --help ``` ## [Documentation](http://postgrest.org) Latest documentation is at [postgrest.org](http://postgrest.org). You can contribute to the docs in [PostgREST/postgrest/docs](https://github.com/PostgREST/postgrest/tree/main/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](https://www.haskell.org/) using the [Warp](http://www.yesodweb.com/blog/2011/03/preliminary-warp-cross-language-benchmarks) 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](https://nikita-volkov.github.io/hasql-benchmarks/) library by * Keeping a pool of db connections * Using the PostgreSQL binary protocol * Being stateless to allow horizontal scaling ## Security PostgREST [handles authentication](http://postgrest.org/en/stable/auth.html) (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](https://openapis.org/) standard to generate up-to-date documentation for APIs. You can use a tool like [Swagger-UI](https://github.com/swagger-api/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](http://begriffs.com/posts/2014-03-06-beyond-http-header-links.html). ## 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](http://www.tutorialspoint.com/postgresql/postgresql_constraints.htm) and the [API guide](http://postgrest.org/en/stable/api.html). ## 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](https://github.com/PostgREST/postgrest/blob/main/CONTRIBUTING.md). ## Thanks The PostgREST organization is grateful to: - The project [sponsors and backers](https://github.com/PostgREST/postgrest/blob/main/BACKERS.md) who support PostgREST's development. - The project [contributors](https://github.com/PostgREST/postgrest/graphs/contributors) who have improved PostgREST immensely with their code and good judgement. See more details in the [changelog](https://github.com/PostgREST/postgrest/blob/main/CHANGELOG.md). The cool logo came from [Mikey Casalaina](https://github.com/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: ```bash ./erd_static-x86-64 -i ./er/film.er -o ../_static/film.png ``` The 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](uml/arch.uml) there's [uml/dark/arch-dark.uml](uml/dark/arch-dark.uml) which includes the first one: ```bash plantuml -tsvg uml/arch.uml -o ../../_static plantuml -tsvg -darkmode uml/dark/arch-dark.uml -o ../../../_static ``` --- ## File: 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 .. container:: img-light .. raw:: html Code Map ======== This section talks briefly about various important modules. Main ---- The starting point of the program is `Main.hs `_. CLI --- Main then calls `CLI.hs `_, which is in charge of :ref:`cli`. App --- `App.hs `_ is then in charge of composing the different modules. Auth ---- `Auth.hs `_ is in charge of :ref:`authn`. Api Request ----------- `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 `_ 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 `_ 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 `_ is in charge of :ref:`schema_cache`. Config ------ `Config.hs `_ is in charge of :ref:`configuration`. Admin ----- `Admin.hs `_ is in charge of the :ref:`admin_server`. Listener -------- `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 `_ 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": "john@doe.com" } 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 `_). 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 `_ 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 ` 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 `_ 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 `_ 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 `_. 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 `_. 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\\bin`. See this `article `_ 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 `_ 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 trust The 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:password@10.0.0.10/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 `_. As a workaround, you may use the `GHC RTS `_ ``-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:password@10.0.0.10/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 `_. 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, 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 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. - `--install-ghc` flag 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 `_. Rate Limiting ------------- Nginx supports "leaky bucket" rate limiting (see `official docs `_). 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 ` 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 `_ (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 .. container:: img-light .. raw:: html --- ## File: docs/how-tos/create-soap-endpoint.rst .. _create_soap_endpoint: Create a SOAP endpoint ====================== :author: `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 `. 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 @- < My SOAP Content XML The output should contain the original request body within the ``therequestbodywas`` entity, and should roughly look like: .. code-block:: xml My SOAP Content 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 @- < 42 0 XML The output should roughly look like: .. code-block:: xml 22012 division by zero, DETAIL: , HINT: References ---------- For more information concerning PostgREST, cf. - :ref:`function_single_unnamed` - :ref:`custom_media`. See :ref:`any_handler`, if you need to support an ``application/soap+xml`` media type or if you want to respond with XML without sending a media type. - :ref:`Nginx reverse proxy ` 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 `_ 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 `_. 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: 1. The response :code:`Content-Type` header is set to :code:`image/webp`. This might be a problem if you want to specify a different format for the file. 2. Download requests (e.g. Right Click -> Save Image As) to :code:`/files?select=blob&id=eq.42` will propose :code:`files` as filename. This might confuse users. 3. 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 `_ 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 `. .. 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