Using own SSL CA for sqlpage.fetch()

[nip1904]

I am trying to use sqlpage.fetch() with internal RootCA.
The underlying Linux Server has the RootCA Cert and it works with the OS.

With sqpage i got Failed to connect to host: invalid peer certificate: UnknownIssuer

[lovasoa]

Hello !

SQLPage's http client currently uses a baked-in list of trusted root certificates (from https://www.ccadb.org/). This allows distributing a single binary, that is secure and works everywhere.

Currently there is no way to customize this. I opened a new issue regarding the implementation of that here: #507

In the meantime, you can use sqlpage.exec to make an HTTP request using an external program, such as curl:

SET my_response = sqlpage.exec('curl', '--cacert', '/path/to/custom_ca.pem', 'https://my-secure-custom-service')

[lovasoa]

Since SQLPage v0.26, you can make sqlpage use the system's native certificate store, using

{
  "system_root_ca_certificates": true
}

in sqlpage/sqlpage.json or by setting the SSL_CERT_FILE or SSL_CERT_DIR environment variables.

[accforgithubtest]

Hi @lovasoa , I am seeking clarity on how to use this feature correctly, as I am unable to get it to work properly.

Without any of the configurations mentioned here, I am able to use sqlpage.fetch with a public url.com with a valid certificate signed by a proper CA, successfully without any issues.
I now also want to use sqlpage.fetch(url) in another .sql file with a abc.local.host url which returns a json.

I am on the latest version of sqlpage v0.34.0, running in docker (compose).

I have mapped a read-only volume /certs that contains the wildcard certificates (mkcert) and keys in .pem format, for my home-only use domain *.local.host.
(Additional context - I have been able to use this certificates successfully using caddy for https reverse proxy via docker compose.)

When I configured these environment variables via docker compose -

1. SYSTEM_ROOT_CA_CERTIFICATES: true, Both the public url with a valid CA, as well as the internal url with self signed cert are complaining Failed to connect to host: invalid peer certificate: UnknownIssuer.

2. SSL_CERT_FILE: '/certs/_wildcard.local.host.pem', Both the public url with a valid CA, as well as the internal url with self signed cert are complaining Failed to connect to host: invalid peer certificate: UnknownIssuer`.

Ideally, I assumed that additional configurations would not impact the public url with a cert signed by a valid CA, however from my set-up / testing above, looks like using these settings will impact even sites with valid CA certs, is that supposed to behave like that ?

I haven't been able to get the self signed cert working for abc.local.host either. Are there any additional configurations needed for a docker based set up ?

Thanks for your help / guidance.

[lovasoa]

Hi @accforgithubtest !

I think the best solution to your problem is to use a different docker base image:

FROM debian:stable-slim
COPY --from=sqlpage/SQLPage:main /usr/local/bin/sqlpage /usr/local/bin/sqlpage

From there, you can use debian's native certs and add yours
https://stackoverflow.com/questions/42292444/how-do-i-add-a-ca-root-certificate-inside-a-docker-image

[accforgithubtest]

Thanks for your suggestion, will give this a try.