Can't make TLS connection to Oracle Cloud ATP via Ecto?

[garthk]

G'day! I'm having a rough time connecting to Oracle Autonomous Transaction Processing using your driver.

The first part of the problem is a mismatch with the the Postgres connection options and MyXQL connection options, both of which expect ssl: true and ssl_opts with a list of SSL options:

config :my_app, MyApp.Repo,
  pool_size: 1,
  hostname: System.get_env("ORACLE_HOST"),
  port: String.to_integer(System.get_env("ORACLE_PORT") || "1522"),
  database: System.get_env("ORACLE_DATABASE"),
  username: System.get_env("ORACLE_USER"),
  password: System.get_env("ORACLE_PASS"),
  ssl: true,
  ssl_opts: [
    server_name_indication: to_charlist(System.get_env("ORACLE_HOST")),
    cacertfile: CAStore.file_path(),
    certfile: 'wallet/clcerts.pem',
    ciphers: :ssl.cipher_suites(:all, :"tlsv1.2"),
    keyfile: 'wallet/priv.pem',
    verify: :verify_peer
  ]

There's no code supporting that convention for getting SSL options through to Jamdb.Oracle.connect/1 to :jamdb_oracle.start_link/1:

https://github.com/erlangbureau/jamdb_oracle/blob/d221f7495f443a8ded28868aabb057079955b653/lib/jamdb_oracle.ex#L62-L75

https://github.com/erlangbureau/jamdb_oracle/blob/d221f7495f443a8ded28868aabb057079955b653/src/jamdb_oracle_conn.erl#L36-L40

Examining the code suggests we could use parameters, instead, but its documentation doesn't cover ssl:

https://github.com/erlangbureau/jamdb_oracle/blob/d221f7495f443a8ded28868aabb057079955b653/lib/jamdb_oracle_ecto.ex#L55 https://github.com/erlangbureau/jamdb_oracle/blob/d221f7495f443a8ded28868aabb057079955b653/lib/jamdb_oracle_ecto.ex#L65-L76

Assuming that's safe, we can edit our configuration...

config :my_app, MyApp.Repo,
  parameters: [
    ssl: [

... and reach :jamdb_oracle.start_link/1. I'd be more confident if it worked, but the connection fails:

[info] TLS :client: In state :downgrade at ssl_record.erl:450 generated CLIENT ALERT: Fatal - Bad Record MAC - :decryption_failed [error] Jamdb.Oracle (#PID<0.544.0>) failed to connect: ** (DBConnection.ConnectionError) {{:badmatch, {:error, :closed}}, [ {:jamdb_oracle_conn, :sock_renegotiate, 3, [file: 'src/jamdb_oracle_conn.erl', line: 385]}, {:jamdb_oracle_conn, :handle_login, 1, [file: 'src/jamdb_oracle_conn.erl', line: 141]}, {:jamdb_oracle, :init, 1, [file: 'src/jamdb_oracle.erl', line: 36]}, …

I've watched the calls to :ssl with :dbg: there's a call to :ssl.send/2 with a packet ending "(DESCRIPTION=(CONNECT_DATA=…))", we get back <<0, 8, 0, 0, 11, 8, 0, 0>> on our next call to :ssl.recv/2, that's hitting the TNS_RESEND branch in handle_login/1 and—am I reading this right?—the code expects an :ssl.close/2 and :ssl.connect/3 will help? In any case, it can't because something has gone wrong:

[info] TLS :client: In state :downgrade at ssl_record.erl:450 generated CLIENT ALERT: Fatal - Bad Record MAC - :decryption_failed

I've confirmed I can make a connection with OpenSSL 1.1 using the same certificates:

$ openssl s_client -connect $ORACLE_HOST:1522 -key wallet/priv.pem -cert wallet/clcerts.pem -CAfile _build/dev/lib/castore/priv/cacerts.pem < packet.bin
…
SSL handshake has read 4373 bytes and written 1804 bytes
Verification: OK
---
New, TLSv1.2, Cipher is AES256-GCM-SHA384
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : AES256-GCM-SHA384
    Session-ID: [REDACTED]
    Session-ID-ctx: 
    Master-Key: [REDACTED]
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    Start Time: 1624780588
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: no
---
DONE

Any ideas?

[vstavskyi]

I can make tls connection only with pathed SSL application.

config.exs.example_ssl

jamdb_oracle_test.hrl.example_ssl

ssl-10.8-otp-25.patch

ssl-10.4-otp-24.patch

ssl-9.6-otp-22.patch

[vstavskyi]

link

Oracle closed the underlying transport connection and error {error, closed} occured, if close_notify alert was sent. During downgrade а close_notify alert is not mandatory for Oracle

[redgreat]

I can make tls connection only with pathed SSL application.

config.exs.example_ssl

jamdb_oracle_test.hrl.example_ssl

ssl-10.8-otp-25.patch

ssl-10.4-otp-24.patch

ssl-9.6-otp-22.patch

In OTP 26.2.4, SSL has been upgraded to 11.1.3. How should I adjust this file?

i change the file like this :

but it didn't work,

erlshell:

2> jamdb_oracle:start(ConnOpts).
{error,{socket,closed}}

[kjellwinblad]

@redgreat we are also hitting this issue in OTP 26. I just wonder if you have managed to fix this already?

[kjellwinblad]

I have tried to apply ssl-10.8-otp-25.patch on

• OTP-25.3.2.12 and
• OTP-25.2.3 (ssl app vsn 10.8.7).

Unfortunately, it causes ssl:connect/2 to get stuck.

For example:

ssl:connect("google.com", 443, [{verify, verify_none}]).

gets stuck and never returns after the patch has been applied.

It seems like the patch "fits" so I don't understand why this happens.

@vstavskyi what exact OTP version did you use for the patch? OTP OTP-25.0?

[redgreat]

otp 27 have update and mention that

ssl
    The ssl client can negotiate and handle certificate status request (OCSP stapling support on the client side).

did it works well with otp27 no more config otp files