Identifying which certificate is needed in order to perform https post using Oracle utl_http

Question

Short story
I'm trying to send a POST request from a PL/SQL script using the utl_http utility in Oracle. I've been able to send the request using http, but not https. I've added what I thought was the necessary certificates to a Oracle Wallet, and I believe they are being imported and used (but in all honesty, this is a little hard to verify). My current assumption is that calls from our DB server are passing through a proxy server, and that that is somehow messing up some part of the https / certificate functionality.

Supporting evidence (possibly?): I tried to make calls (POST requests) to a dummy service at webhook.site. Again, I got this working with http, but not https - the latter results in a cert validation error.

I then tried to replicate the behavior using postman, and that basically produces the same result, unless I fiddle around with the settings:

Initial Postman result:

Could not get any response

There was an error connecting to https://webhook.site/950...

Disabling SSL verification
Under the Post man settings, I turned off SSL Certificate Verification, and tried again. This time, I got a 200 OK response, and confirmed that the webhook received the post request fine.

It seems clear that the error is due to a missing cert, but I can't figure out which, or how to configure it. My assumption is that if I can get this to work for a webhook-url from Postman (without disabling cert verification), then I should also be able to get it to work from PL/SQL later.

When I look at the webhook site in a browser and inspect the certs, the webhook cert is the lowest cert (leaf node?). Above it there is one intermediate cert related to the company I'm working for, and then a root cert also related to the company. The root node is named something like "Company Proxy Server CA" - So I'm assuming the proxy somehow manipulates my requests and inserts it's own cert here.

I've tried downloading all of these certs and importing them into my cert store, as well as importing them under the Postman settings (under Certificates) in various combinations, but nothing seems to make any difference; all attempts at posting with HTTPS produces the following error in my Postman Console:

POST https://webhook.site/9505...

Error: unable to verify the first certificate

Any ideas about how to resolve this, or at least obtain more information about what to do would be greatly appreciated.

Answer 1

A couple of possible issues:

1. The server doesn't actually support HTTPS. Connect a browser to the URL that you POST to, and see if you receive a response. (It looks like you already did this, but I'm documenting it for completeness.)
2. The server uses the Server Name Indication (SNI) extension to determine what certificate chain to send back, but your POSTing client doesn't send that extension. You can identify this case by looking up the IP for the host you're POSTing to, then going to https://nnn.nnn.nnn.nnn/ (obviously use the IP here, instead of the literal string 'nnn.nnn.nnn.nnn') in your browser, and checking the certificate chain it returns. If it is not the same as you get from step 1, this is your problem, and you need to figure out how to either get SNI support in your Oracle PL/SQL client or get the POST endpoint exposed on that hostname. (alternatively, you might be able to use these certificates to prime your Oracle Wallet, but they might have an issue with the hostname in the certificate not matching the hostname you connect to.)
3. You have a proxy in the way. I don't think this is what's going on, since that would basically only cause problems if you were doing client-side certificate authentication. (If this is the problem or is a condition, you need to import those certificates into your trusted wallet; you also need to ensure that the server you're posting from is going through the same proxy. Otherwise, you need to ensure that the certificate authority for the proxy that the machine actually running the code sees is in the wallet. This may require the assistance of the system/network administrators who run that machine and its connection to the network.)

HTTPS is a finicky beast. Many, many things must work exactly correctly for TLS connections to work and the certificates to correctly verify (the TLS port must respond, the client and server must agree to speak the same version of TLS, the client and server must agree to use the same cipher combination, the certificate chain presented by the server must be issued by a CA the client recognizes, and the leaf certificate in that chain must certify the name client requested).

SNI is needed to support multiple names on a single host without messing with the certifications of other names on the same host. Unfortunately, SNI is one of those things that has been standardized for over a decade (RFC 3546), but many enterprise-grade softwares haven't implemented.

Answer 2

You said... ""Company Proxy Server CA" - So I'm assuming the proxy somehow manipulates my requests and inserts it's own cert here."

That means your client software needs your Company Proxy Server CA in its trusted certificates list. If that client's list is that of the oracle wallet...

https://knowledge.digicert.com/solution/SO979.html

This talks about how to do that.

Also, if your system running postman has a non-oracle based wallet trusted certificate (probably the operating system?) you'll have to execute something like adding the trust to your account on the workstation

https://www.thewindowsclub.com/manage-trusted-root-certificates-windows

in order to have the proxy server certificate trusted.

Once the certificate you're making the connection with has a root of trust per the effective configuration of the client being used, then you'll be able to verify the certificate.

Answer 3

Switching OFF "SSL Certificate Verification" in Postman only means that it (i.e. Postman) will not check the validity of SSL certificates while making a request. Meaning that it will just send the certificates as they are. Because your connection fails if the setting in ON, this means Postman cannot verify the validity of your certificates.

This is most likely the case with the actual service you're trying to POST to, they cannot verify the certificates. Is that service outside your company network? And is it a public one or one owned by your company? Where is that service hosted? What certificate do they need?

BTW, TLS client certificates are sent as part of establishing the SSL connection, not as part of the HTTP request. The TLS handshake (and exchange/validation of client and server certificates) happens before any HTTP message is sent.

I'm thinking this might be a blocked port issue.