Unable to connect to RDS Proxy

[Octogonapus]

This is more of a question than a bug report. I'm not sure if the problem is in my code or not.

I'm trying to connect to an RDS MySQL DB via RDS Proxy and I'm unable to do so via MySQL.jl. Using the mysql CLI works fine. This shell code runs fine (connects and runs the SQL code):

nc -z $DB_ENDPOINT $DB_PORT && echo "can connect to DB endpoint" || echo "can't connect to DB endpoint"
nc -z $PROXY_ENDPOINT $DB_PORT && echo "can connect to proxy endpoint" || echo "can't connect to proxy endpoint"

echo "connecting to the DB endpoint via mysql CLI"
mysql --host=$DB_ENDPOINT --port=$DB_PORT --ssl-ca=/runtime/global-bundle.pem --ssl-mode=VERIFY_IDENTITY --enable-cleartext-plugin --user=$DB_USERNAME --password=$DB_PASSWORD < /runtime/debug.sql

echo "generating DB auth token"
db_auth_token=$(aws rds generate-db-auth-token --hostname $PROXY_ENDPOINT --port $DB_PORT --region $AWS_DEFAULT_REGION --username $DB_USERNAME)

echo "connecting to the proxy endpoint via mysql CLI"
mysql --host=$PROXY_ENDPOINT --port=$DB_PORT --ssl-ca=/runtime/AmazonRootCA1.pem --ssl-mode=VERIFY_IDENTITY --enable-cleartext-plugin --user=$DB_USERNAME --password=$db_auth_token < /runtime/debug.sql

So in summary, the mysql CLI can connect to both the DB endpoint and proxy endpoint just fine.

Here's my code to connect to the DB endpoint via MySQL.jl:

with_connection(;
    host = ENV["DB_ENDPOINT"],
    username = ENV["DB_USERNAME"],
    password = ENV["DB_PASSWORD"],
    unix_socket = "",
    db = "main",
    port = parse(Int, string(ENV["DB_PORT"])),
    ssl_ca = "/runtime/global-bundle.pem",
    ssl_verify_server_cert = true,
    ssl_enforce = true,
) do conn
    println(execute_df(conn, read("/runtime/debug.sql", String)))
end

with_connection passes all arguments to connect so for this example, they are the same.
As expected, this works fine.

Here's my code to connect to the proxy endpoint via MySQL.jl:

with_connection(;
    host = ENV["PROXY_ENDPOINT"],
    username = ENV["DB_USERNAME"],
    password = db_auth_token,
    unix_socket = "",
    db = "main",
    port = parse(Int, string(ENV["DB_PORT"])),
    ssl_ca = "/runtime/AmazonRootCA1.pem",
    ssl_verify_server_cert = true,
    ssl_enforce = true,
) do conn
    println(execute_df(conn, read("/runtime/debug.sql", String)))
end

AFAICT this should have the same behavior as the mysql CLI code.
However, this does not work; I get this error back:

│    (1045): Access denied for user 'root'@'<internal ip>' (using password: NO)
│    Stacktrace:
│      [1] connect
│        @ ~/.julia/packages/MySQL/LGSYW/src/api/capi.jl:12 [inlined]
│      [2] MySQL.Connection(host::String, user::String, passwd::SubString{String}, db::String, port::Int64, unix_socket::String; kw::Base.Pairs{Symbol, Any, Tuple{Symbol, Symbol, Symbol}, NamedTuple{(:ssl_ca, :ssl_verify_server_cert, :ssl_enforce), Tuple{String, Bool, Bool}}})
│        @ MySQL ~/.julia/packages/MySQL/LGSYW/src/MySQL.jl:34
│      [3] #connect#5
│        @ ~/.julia/packages/MySQL/LGSYW/src/MySQL.jl:288 [inlined]

Do you see anything wrong with my Julia code to open the connection to the proxy endpoint?

Julia v1.8.2. MySQL.jl v1.4.2.

[quinnj]

Hey @Octogonapus, thanks for opening an issue. The main difference I see is that you're setting ssl-mode=VERIFY_IDENTITY and we currently don't have the ability to set that.

So I opened this PR that should allow you to do so. In your case, you'd do something like:

DBInterface.connect(....; ssl_mode=MySQL.API.SSL_MODE_VERIFY_IDENTITY)

Could you try that out and see if it works for you? To try out my PR, you can do the following from the REPL:

] add MySQL#jq/ssl_mode

Then reload your session and do using MySQL and then try it out. If that works, I'll merge the PR and tag a new release.

[Octogonapus]

Using that option gives me this error:

┌ Error: Failed to connect to the proxy endpoint
--
│   exception =
│    (2054): This feature is not implemented or disabled
│    Stacktrace:
│     [1] setoption(mysql::MySQL.API.MYSQL, option::MySQL.API.mysql_option, arg::MySQL.API.mysql_ssl_mode)
│       @ MySQL.API ~/.julia/packages/MySQL/O5YSd/src/api/capi.jl:4
│     [2] #setoptions!#4
│       @ ~/.julia/packages/MySQL/O5YSd/src/MySQL.jl:204 [inlined]
│     [3] MySQL.Connection(host::String, user::String, passwd::SubString{String}, db::String, port::Int64, unix_socket::String; kw::Base.Pairs{Symbol, Any, NTuple{4, Symbol}, NamedTuple{(:ssl_ca, :ssl_verify_server_cert, :ssl_enforce, :ssl_mode), Tuple{String, Bool, Bool, MySQL.API.mysql_ssl_mode}}})
│       @ MySQL ~/.julia/packages/MySQL/O5YSd/src/MySQL.jl:29
│     [4] #connect#5
│       @ ~/.julia/packages/MySQL/O5YSd/src/MySQL.jl:292 [inlined]
│     [5] main()
│       @ Main /runtime/debug_db.jl:58
│     [6] top-level scope
│       @ /runtime/debug_db.jl:80
│     [7] include(mod::Module, _path::String)
│       @ Base ./Base.jl:419
│     [8] exec_options(opts::Base.JLOptions)
│       @ Base ./client.jl:303
│     [9] _start()
│       @ Base ./client.jl:522
└ @ Main /runtime/debug_db.jl:76

Using this code:

conn = DBInterface.connect(
    MySQL.Connection,
    ENV["PROXY_ENDPOINT"],
    ENV["DB_USERNAME"],
    db_auth_token;
    unix_socket="",
    port=parse(Int, string(ENV["DB_PORT"])),
    ssl_ca="/runtime/AmazonRootCA1.pem",
    ssl_verify_server_cert=true,
    ssl_enforce=true,
    ssl_mode=MySQL.API.SSL_MODE_VERIFY_IDENTITY
)

(I think that PR is still useful nonetheless)

[Octogonapus]

In the RDS proxy logs I am seeing this:

2022-10-21T17:25:29.482Z [INFO] [proxyEndpoint=default] [clientConnection=94296180] A new client connected from <internal ip>:55392.
2022-10-21T17:25:29.601Z [INFO] [proxyEndpoint=default] [clientConnection=94296180] Proxy authentication with IAM authentication failed for user "root" with TLS on. Reason: Invalid credentials. If you provide an IAM token, make sure to either use the correct password or enable IAM authentication.

Maybe there is a problem with how the DB auth token is handled? The token is of the form <endpoint url>:3306/?Action=connect&DBUser=root&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=<67 chars>&X-Amz-Date=<16 chars>&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Security-Token=<1110 chars>&X-Amz-Signature=<64 chars>.

[Octogonapus]

It looks like this is my fault, though I don't yet understand how. The problem appears to be with the token. Hard-coding a token into my Julia script works, so the problem can't be with MySQL.jl. Thanks for your help anyway. You are welcome to close this issue if you want, but that PR you opened is still useful to me because I would use SSL_MODE_VERIFY_IDENTITY if it was supported.

[quinnj]

I did a little more research and it turns out that the MariaDB Connector C, that we're using under the hood, doesn't support the SSL_MODE thing in my PR anyway, since it's a MySQL Connector-only thing.

So you're saying if you hardcode the token in your script, even the proxy endpoint works with MySQL.jl?

[Octogonapus]

Indeed, MySQL.jl is not what was causing the issue.

I did a little more research and it turns out that the MariaDB Connector C, that we're using under the hood, doesn't support the SSL_MODE thing in my PR anyway, since it's a MySQL Connector-only thing.

So for MariaDB, setting ssl_verify_server_cert = true should be enough for CA and hostname verification, right? I remember reading that somewhere in their docs...

[quinnj]

Yeah, that's what I understand looking at their source code.

Yeah, sorry to not be more helpful here; database creds stuff can be gnarly, especially when you're dealing w/ proxies and cloud stuff.