Awesome
PostgreSQL HTTP Client
Motivation
Wouldn't it be nice to be able to write a trigger that called a web service? Either to get back a result, or to poke that service into refreshing itself against the new state of the database?
This extension is for that.
Examples
URL encode a string.
SELECT urlencode('my special string''s & things?');
urlencode
-------------------------------------
my+special+string%27s+%26+things%3F
(1 row)
URL encode a JSON associative array.
SELECT urlencode(jsonb_build_object('name','Colin & James','rate','50%'));
urlencode
-------------------------------------
name=Colin+%26+James&rate=50%25
(1 row)
Run a GET request and see the content.
SELECT content
FROM http_get('http://httpbun.com/ip');
content
-----------------------------
{"origin":"24.69.186.43"}
(1 row)
Run a GET request with an Authorization header.
SELECT content::json->'headers'->>'Authorization'
FROM http((
'GET',
'http://httpbun.com/headers',
ARRAY[http_header('Authorization','Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9')],
NULL,
NULL
)::http_request);
content
----------------------------------------------
Bearer eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9
(1 row)
Read the status
and content
fields out of a http_response
object.
SELECT status, content_type
FROM http_get('http://httpbun.com/');
status | content_type
--------+--------------------------
200 | text/html; charset=utf-8
(1 row)
Show all the http_header
in an http_response
object.
SELECT (unnest(headers)).*
FROM http_get('http://httpbun.com/');
field | value
------------------+--------------------------------------------------
Server | nginx
Date | Wed, 26 Jul 2023 19:52:51 GMT
Content-Type | text/html
Content-Length | 162
Connection | close
Location | https://httpbun.org
server | nginx
date | Wed, 26 Jul 2023 19:52:51 GMT
content-type | text/html
x-powered-by | httpbun/3c0dc05883dd9212ac38b04705037d50b02f2596
content-encoding | gzip
Use the PUT command to send a simple text document to a server.
SELECT status, content_type, content::json->>'data' AS data
FROM http_put('http://httpbun.com/put', 'some text', 'text/plain');
status | content_type | data
--------+------------------+-----------
200 | application/json | some text
Use the PATCH command to send a simple JSON document to a server.
SELECT status, content_type, content::json->>'data' AS data
FROM http_patch('http://httpbun.com/patch', '{"this":"that"}', 'application/json');
status | content_type | data
--------+------------------+------------------
200 | application/json | '{"this":"that"}'
Use the DELETE command to request resource deletion.
SELECT status, content_type, content::json->>'url' AS url
FROM http_delete('http://httpbun.com/delete');
status | content_type | url
--------+------------------+---------------------------
200 | application/json | http://httpbun.com/delete
As a shortcut to send data to a GET request, pass a JSONB data argument.
SELECT status, content::json->'args' AS args
FROM http_get('http://httpbun.com/get',
jsonb_build_object('myvar','myval','foo','bar'));
To POST to a URL using a data payload instead of parameters embedded in the URL, encode the data in a JSONB as a data payload.
SELECT status, content::json->'form' AS form
FROM http_post('http://httpbun.com/post',
jsonb_build_object('myvar','myval','foo','bar'));
To access binary content, you must coerce the content from the default varchar
representation to a bytea
representation using the text_to_bytea()
function, or the textsend()
function. Using the default varchar::bytea
cast will not work, as the cast will stop the first time it hits a zero-valued byte (common in binary data).
WITH
http AS (
SELECT * FROM http_get('https://httpbingo.org/image/png')
),
headers AS (
SELECT (unnest(headers)).* FROM http
)
SELECT
http.content_type,
length(text_to_bytea(http.content)) AS length_binary
FROM http, headers
WHERE field ilike 'Content-Type';
content_type | length_binary
--------------+---------------
image/png | 8090
Similarly, when using POST to send bytea
binary content to a service, use the bytea_to_text
function to prepare the content.
To access only the headers you can do a HEAD-Request. This will not follow redirections.
SELECT
http.status,
headers.value AS location
FROM
http_head('http://google.com') AS http
LEFT OUTER JOIN LATERAL (SELECT value
FROM unnest(http.headers)
WHERE field = 'Location') AS headers
ON true;
status | location
--------+------------------------
301 | http://www.google.com/
Concepts
Every HTTP call is a made up of an http_request
and an http_response
.
Composite type "public.http_request"
Column | Type | Modifiers
--------------+-------------------+-----------
method | http_method |
uri | character varying |
headers | http_header[] |
content_type | character varying |
content | character varying |
Composite type "public.http_response"
Column | Type | Modifiers
--------------+-------------------+-----------
status | integer |
content_type | character varying |
headers | http_header[] |
content | character varying |
The utility functions, http_get()
, http_post()
, http_put()
, http_delete()
and http_head()
are just wrappers around a master function, http(http_request)
that returns http_response
.
The headers
field for requests and response is a PostgreSQL array of type http_header
which is just a simple tuple.
Composite type "public.http_header"
Column | Type | Modifiers
--------+-------------------+-----------
field | character varying |
value | character varying |
As seen in the examples, you can unspool the array of http_header
tuples into a result set using the PostgreSQL unnest()
function on the array. From there you select out the particular header you are interested in.
Functions
http_header(field VARCHAR, value VARCHAR)
returnshttp_header
http(request http_request)
returnshttp_response
http_get(uri VARCHAR)
returnshttp_response
http_get(uri VARCHAR, data JSONB)
returnshttp_response
http_post(uri VARCHAR, content VARCHAR, content_type VARCHAR)
returnshttp_response
http_post(uri VARCHAR, data JSONB)
returnshttp_response
http_put(uri VARCHAR, content VARCHAR, content_type VARCHAR)
returnshttp_response
http_patch(uri VARCHAR, content VARCHAR, content_type VARCHAR)
returnshttp_response
http_delete(uri VARCHAR, content VARCHAR, content_type VARCHAR))
returnshttp_response
http_head(uri VARCHAR)
returnshttp_response
http_set_curlopt(curlopt VARCHAR, value varchar)
returnsboolean
http_reset_curlopt()
returnsboolean
http_list_curlopt()
returnssetof(curlopt text, value text)
urlencode(string VARCHAR)
returnstext
urlencode(data JSONB)
returnstext
CURL Options
Select CURL options are available to set using the http_set_curlopt(curlopt VARCHAR, value varchar)
function.
- CURLOPT_DNS_SERVERS
- CURLOPT_PROXY
- CURLOPT_PRE_PROXY
- CURLOPT_PROXYPORT
- CURLOPT_PROXYUSERPWD
- CURLOPT_PROXYUSERNAME
- CURLOPT_PROXYPASSWORD
- CURLOPT_PROXY_TLSAUTH_USERNAME
- CURLOPT_PROXY_TLSAUTH_PASSWORD
- CURLOPT_PROXY_TLSAUTH_TYPE
- CURLOPT_TLSAUTH_USERNAME
- CURLOPT_TLSAUTH_PASSWORD
- CURLOPT_TLSAUTH_TYPE
- CURLOPT_SSL_VERIFYHOST
- CURLOPT_SSL_VERIFYPEER
- CURLOPT_SSLCERT
- CURLOPT_SSLKEY
- CURLOPT_SSLCERTTYPE
- CURLOPT_SSLKEY_BLOB
- CURLOPT_SSLCERT_BLOB
- CURLOPT_CAINFO
- CURLOPT_TIMEOUT
- CURLOPT_TIMEOUT_MS
- CURLOPT_TCP_KEEPALIVE
- CURLOPT_TCP_KEEPIDLE
- CURLOPT_CONNECTTIMEOUT
- CURLOPT_CONNECTTIMEOUT_MS
- CURLOPT_USERAGENT
For example,
-- Set the PROXYPORT option
SELECT http_set_curlopt('CURLOPT_PROXYPORT', '12345');
-- List all currently set options
SELECT * FROM http_list_curlopt();
Will set the proxy port option for the lifetime of the database connection. You can reset all CURL options to their defaults using the http_reset_curlopt()
function.
Using this extension as a background automated process without supervision (e.g as a trigger) may have unintended consequences for other servers. It is considered a best practice to share contact information with your requests, so that administrators can reach you in case your HTTP calls get out of control.
Certain API policies (e.g. Wikimedia User-Agent policy) may even require sharing specific contact information
with each request. Others may disallow (via robots.txt
) certain agents they don't recognize.
For such cases you can set the CURLOPT_USERAGENT
option
SELECT http_set_curlopt('CURLOPT_USERAGENT',
'Examplebot/2.1 (+http://www.example.com/bot.html) Contact abuse@example.com');
SELECT status, content::json ->> 'user-agent' FROM http_get('http://httpbun.com/user-agent');
status | user_agent
--------+-----------------------------------------------------------
200 | Examplebot/2.1 (+http://www.example.com/bot.html) Contact abuse@example.com
Keep-Alive & Timeouts
The http_reset_curlopt()
approach described above is recommended. The global variables below will be deprecated and removed over time.
By default each request uses a fresh connection and assures that the connection is closed when the request is done. This behavior reduces the chance of consuming system resources (sockets) as the extension runs over extended periods of time.
High-performance applications may wish to enable keep-alive and connection persistence to reduce latency and enhance throughput. The following GUC variable changes the behavior of the http extension to maintain connections as long as possible:
http.keepalive = 'on'
By default a 5 second timeout is set for the completion of a request. If a different timeout is desired the following GUC variable can be used to set it in milliseconds:
http.timeout_msec = 200
Installation
Debian / Ubuntu apt.postgresql.org
Replace 17 with your version of PostgreSQL
apt install postgresql-17-http
UNIX: Compile from Source
If you have PostgreSQL (>= 9.3) devel packages and CURL devel packages installed (>= 0.7.20), you should have pg_config
and curl-config
on your path, so you should be able to just run make
(or gmake
), then make install
, then in your database CREATE EXTENSION http
.
If you already installed a previous version and you just want to upgrade, then ALTER EXTENSION http UPDATE
.
Compiling for Apt based systems, using apt postgresql packages
Refer to https://wiki.postgresql.org/wiki/Apt for pulling packages from apt.postgresql.org repo
# replace the postgresql-server-dev-14 with your current version
sudo apt install postgresql-server-dev-14 libcurl4-openssl-dev make g++
make
sudo make install
If there several PostgreSQL installations available, you might need to edit the Makefile before running make
to something like this:
...
#PG_CONFIG = pg_config
PG_CONFIG = /usr/lib/postgresql/14/bin/pg_config
...
Windows
There is a build available at postgresonline, not maintained by me.
Why This is a Bad Idea
- "What happens if the web page takes a long time to return?" Your SQL call will just wait there until it does. Make sure your web service fails fast. Or (dangerous in a different way) run your query within pg_background.
- "What if the web page returns junk?" Your SQL call will have to test for junk before doing anything with the payload.
- "What if the web page never returns?" Set a short timeout, or send a cancel to the request, or just wait forever.
- "What if a user queries a page they shouldn't?" Restrict function access, or just don't install a footgun like this extension where users can access it.
To Do
- The new http://www.postgresql.org/docs/9.3/static/bgworker.html background worker support could be used to set up an HTTP request queue, so that pgsql-http can register a request and callback and then return immediately.
- Inevitably some web server will return gzip content (Content-Encoding) without being asked for it. Handling that gracefully would be good.