PostgreSQL tool for transferring data with URL syntax, supporting DICT, FILE, FTP, FTPS, GOPHER, GOPHERS, HTTP, HTTPS, IMAP, IMAPS, LDAP, LDAPS, MQTT, POP3, POP3S, RTMP, RTMPS, RTSP, SCP, SFTP, SMB, SMBS, SMTP, SMTPS, TELNET, TFTP, WS and WSS.

# configuration

pg_curl exposes two GUCs:

- `pg_curl.whitelist` (superuser-settable, e.g. via `ALTER ROLE ... SET`) — comma-separated `file://` and `http(s)://` prefixes the current role is allowed to reach. A `file://` entry ending in `/` allows anything under that directory; without a trailing slash it allows only that exact file. An `http(s)://` entry allows any URL sharing that prefix, matched up to a path segment boundary (`https://host/api` does not allow `https://host/api2` or `https://host.evil.net/`). Userinfo (`user@`) and a default port (`:80` for `http`, `:443` for `https`) are ignored and paths are compared percent-decoded, on both sides; a URL with a `.` or `..` path segment never matches an entry with a path below the root.

  It is checked before every request URL (`curl_easy_setopt_url`) and before every option that has curl open a local file or socket: `curl_mime_file`, `curl_easy_setopt_cookiefile`, `curl_easy_setopt_cookiejar`, `curl_easy_setopt_crlfile`, `curl_easy_setopt_cainfo`, `curl_easy_setopt_capath`, `curl_easy_setopt_issuercert`, `curl_easy_setopt_sslcert`, `curl_easy_setopt_sslkey`, `curl_easy_setopt_pinnedpublickey` (when given a file rather than `sha256//` hashes), their `curl_easy_setopt_proxy_*` counterparts (including `proxy_crlfile`), `curl_easy_setopt_ssh_private_keyfile`, `curl_easy_setopt_ssh_public_keyfile`, `curl_easy_setopt_ssh_knownhosts`, `curl_easy_setopt_random_file`, `curl_easy_setopt_egdsocket` and `curl_easy_setopt_unix_socket_path`. The path is resolved with `realpath()`, so it must already exist — including the `cookiejar` file, which curl writes. `curl_easy_setopt_abstract_unix_socket` has no path to whitelist and is denied whenever the whitelist applies, as are `curl_easy_setopt_sslengine` (an OpenSSL engine is a shared library loaded into the backend) and `curl_easy_setopt_netrc` with anything but `curl_netrc_ignored()` (`.netrc` holds the postgres OS user's credentials). Redirects (`curl_easy_setopt_followlocation`) are followed only to URLs the whitelist allows: every redirect target is checked the same way as the request URL, before curl resolves its host name, again before it connects to the host (a name already in curl's DNS cache is not resolved again), and again before each request, including one on a connection reused from the previous hop. This needs libcurl 7.80.0 or later; with an older one `curl_easy_setopt_followlocation` with a non-zero value is denied, and following is switched off at perform time for a handle that had it enabled before the whitelist applied. Hosts curl would connect to besides the request URL are unavailable altogether: `curl_easy_setopt_proxy`, `curl_easy_setopt_pre_proxy` and `curl_easy_setopt_doh_url` are denied, and reset to their defaults at perform time (so a proxy from the server's environment, e.g. `http_proxy`, still applies).

  A request URL is classified the way libcurl parses it (scheme case-insensitive, scheme-less URLs guessed): `http(s)://` URLs are matched against `http(s)://` entries, `file://` URLs against `file://` entries, and any other scheme (`ftp://`, `gopher://`, `dict://`, ...) is denied whenever the whitelist applies.

  While `pg_curl.whitelist` is unset, superusers are unrestricted; once it is set, it limits superusers to the listed prefixes too. Every other role is denied by default until a superuser grants specific prefixes:
  ```sql
  ALTER ROLE app_user SET pg_curl.whitelist = 'https://api.example.com/,file:///var/lib/postgresql/uploads/';
  ```

- `pg_curl.transaction` (boolean, default `true`) — keep curl handles in the current transaction's memory context instead of the top memory context, so they are cleaned up automatically at transaction end.

# http get
```sql
CREATE OR REPLACE FUNCTION get(url TEXT) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        curl_easy_reset(),
        curl_easy_setopt_url(url),
        curl_easy_perform(),
        curl_easy_getinfo_data_in()
    ) SELECT convert_from(curl_easy_getinfo_data_in, 'utf-8') FROM s;
$BODY$;
```

# http urlencoded form post
```sql
CREATE OR REPLACE FUNCTION post(url TEXT, request JSON) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        curl_easy_reset(),
        ( WITH s AS (
            SELECT (json_each_text(request)).*
        ) SELECT array_agg(curl_postfield_append(key, value)) FROM s),
        curl_easy_setopt_url(url),
        curl_easy_perform(),
        curl_easy_getinfo_data_in()
    ) SELECT convert_from(curl_easy_getinfo_data_in, 'utf-8') FROM s;
$BODY$;
```

# http multipart/form-data form post
```sql
CREATE OR REPLACE FUNCTION post(url TEXT, request JSON) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        curl_easy_reset(),
        ( WITH s AS (
            SELECT (json_each_text(request)).*
        ) SELECT array_agg(curl_mime_data(value, name:=key)) FROM s),
        curl_easy_setopt_url(url),
        curl_easy_perform(),
        curl_easy_getinfo_data_in()
    ) SELECT convert_from(curl_easy_getinfo_data_in, 'utf-8') FROM s;
$BODY$;
```

# mime part with multiple headers

The `head` argument of `curl_mime_data` and `curl_mime_file` also accepts an array of complete header lines:

```sql
SELECT curl_mime_data(
    'image data',
    head := ARRAY[
        'Content-ID: <picture1>',
        'Content-Disposition: inline; filename="picture1.png"'
    ],
    name := 'picture',
    type := 'image/png'
);
```

`curl_mime_data` accepts either `text` or `bytea` content. Array elements must not contain CR or LF characters or be null. A null or empty array leaves the part without custom headers; cast an untyped null or empty array literal as `NULL::text[]` or `'{}'::text[]` to select the array overload.

# http json post
```sql
CREATE OR REPLACE FUNCTION post(url TEXT, request JSON) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        curl_easy_reset(),
        curl_easy_setopt_postfields(convert_to(request::TEXT, 'utf-8')),
        curl_easy_setopt_url(url),
        curl_header_append('Content-Type', 'application/json; charset=utf-8'),
        curl_easy_perform(),
        curl_easy_getinfo_data_in()
    ) SELECT convert_from(curl_easy_getinfo_data_in, 'utf-8') FROM s;
$BODY$;
```

# email
```sql
CREATE OR REPLACE FUNCTION email(url TEXT, username TEXT, password TEXT, subject TEXT, sender TEXT, recipient TEXT, body TEXT, type TEXT) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        curl_easy_reset(),
        curl_easy_setopt_mail_from(sender),
        curl_easy_setopt_password(password),
        curl_easy_setopt_url(url),
        curl_easy_setopt_username(username),
        curl_header_append('From', sender),
        curl_header_append('Subject', subject),
        curl_header_append('To', recipient),
        curl_mime_data(body, type:=type),
        curl_recipient_append(recipient),
        curl_easy_perform(),
        curl_easy_getinfo_header_in()
    ) SELECT curl_easy_getinfo_header_in FROM s;
$BODY$;
```

# ftp upload
```sql
CREATE OR REPLACE FUNCTION upload(url TEXT, username TEXT, password TEXT, file BYTEA) RETURNS TEXT LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        curl_easy_reset(),
        curl_easy_setopt_password(password),
        curl_easy_setopt_readdata(file),
        curl_easy_setopt_url(url),
        curl_easy_setopt_username(username),
        curl_easy_perform(),
        curl_easy_getinfo_header_in()
    ) SELECT curl_easy_getinfo_header_in FROM s;
$BODY$;
```
# ftp download
```sql
CREATE OR REPLACE FUNCTION download(url TEXT, username TEXT, password TEXT) RETURNS BYTEA LANGUAGE SQL AS $BODY$
    WITH s AS (SELECT
        curl_easy_reset(),
        curl_easy_setopt_password(password),
        curl_easy_setopt_url(url),
        curl_easy_setopt_username(username),
        curl_easy_perform(),
        curl_easy_getinfo_data_in()
    ) SELECT curl_easy_getinfo_data_in FROM s;
$BODY$;
```

# convert http headers to table
```sql
WITH s AS (
    SELECT regexp_matches(curl_easy_getinfo_header_in(), E'([^ \t\r\n\f]+): ?([^\t\r\n\f]+)', 'g') AS s
) SELECT s[1] AS key, s[2] AS value FROM s;
```