Confusion about EdgelessDB using TLS connections

[water5-cmd]

Hi, @thomasten, I would like to ask you about TLS connection. The test user was created in manifest.json with the following details

{
    "sql": [
        "CREATE USER root REQUIRE ISSUER '/CN=My CA' SUBJECT '/CN=rootuser'",
        "GRANT ALL ON *.* TO root WITH GRANT OPTION",
    "CREATE USER test IDENTIFIED BY '123'",
        "grant all privileges on *.* to test@'%' with grant option",
        "flush privileges"
    ],
    "ca": "-----BEGIN CERTIFICATE-----\nMIIDATCCAemgAwIBAgIUfVlQIGpmfApr5zMnhCTOksTMyUQwDQYJKoZIhvcNAQEL\nBQAwEDEOMAwGA1UEAwwFTXkgQ0EwHhcNMjExMjMwMDMwODMxWhcNMzExMjI4MDMw\nODMxWjAQMQ4wDAYDVQQDDAVNeSBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCC\nAQoCggEBAM33GlaTcBlqyBzomTZ84KDdbMzZv7WTi0ttl3mSRVawZjwP0bXRyTPT\nBbXqCYxoCNSeBciuivi9Wo8IxenOaXkN1wSzCjmGhWo0cU1QvEcX44UBL70uk3Gg\nVGL/E0Vi9NaIQDj9PXng8w23QfOLgWw6R0fRPgichVFKA4iU7OypQMqNirO8iIlY\nLz9Z8Z28z6QkOhpS7jKZuOnGnRX6/gkwCghUzZ6yHa3a1oWkA0ANiyMy+J3pAS/N\ncShPiwygZWMNImYYttpKfl/x4iM8yt3SoN6wNNmW4c3NfHrHz1tMJrtYcG7apzXn\nax8dbzZAifNsUhKhFoEUfFmq0ERH3IcCAwEAAaNTMFEwHQYDVR0OBBYEFGjLZBN8\nLdbeiqrkahcG+ZEe805fMB8GA1UdIwQYMBaAFGjLZBN8LdbeiqrkahcG+ZEe805f\nMA8GA1UdEwEB/wQFMAMBAf8wDQYJKoZIhvcNAQELBQADggEBAMNhuGrO/YpjE5pd\nNm/S4QUzHqsOPEPS86QB5u3vSxa548V8U8cB/2f18jWYSYZ1HpFoBmifXrKCaAEx\n6pNwfOVEMJAeuAgnOaclA7x0tn9wJtwJ8fHRzCxmc2o4RQXriXl5M7LU2RxJ1+3O\nygeczwTQpQ2PyIJWy7LbdsXr9QxFUNFRX9JBJCg+Tezo9UCkQ2k05GMgZru0gr2h\nVnIp25WXEHoSqlkgCc/65b0JrX//GIXgVEUxMOld2bMu9POHAitL4e0z/5JC2U7C\nS438HALNWWkzsyyZ7E7r2mt46ziTEqLY06QizpdyDugTkP6WmvVlnQxXISDjTnMS\nVER9mFE=\n-----END CERTIFICATE-----\n",
    "debug": true
}

Now, I can use two ways to connect to the database.

//without tls
root@daier:/home/daier/SGX_database/customhost/edgelessdb/build# mysql -h127.0.0.1 -utest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.5.5-10.5.11-MariaDB-debug-log Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
// the database output
2022-04-07 10:23:04 17 [Warning] Aborted connection 17 to db: 'unconnected' user: 'test' host: '127.0.0.1' (This connection closed normally)

//use tls
root@daier:/home/daier/SGX_database/customhost/edgelessdb/build# mysql -h127.0.0.1 -utest -p123 --ssl-ca edb.pem --ssl-cert=/home/daier/SGX_database/edgelessdb/cert.pem --ssl-key=/home/daier/SGX_database/edgelessdb/key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.5-10.5.11-MariaDB-debug-log Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
// the database output
2022-04-07 10:26:28 18 [Warning] Aborted connection 18 to db: 'unconnected' user: 'test' host: '127.0.0.1' (This connection closed normally)

So, I can connect to the database with or without TLS. But EdgelessDB enforces TLS connection, why won't non-tls connections be rejected? And Why does the database print out the correct test user when exiting using a non-TLS connection? From what I understand, users who connect non-TLS are initially rejected; even if the connection is successful, the database will get ciphertext data, and parsing the ciphertext data will not result in the correct username. I don't know if I understand it right.

[water5-cmd]

I may know the reason for the problem. I checked whether ssl authentication was enabled for the root and test users and found that only the root user had ssl authentication enabled, which presumably explains why the test user could use a non-TLS connection.

root@daier:/home/daier/SGX_database# mysql -h127.0.0.1 -utest -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.5-10.5.11-MariaDB-debug-log Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select ssl_type from user where user='test' and host='%';
+----------+
| ssl_type |
+----------+
|          |
+----------+
1 row in set (0.01 sec)

mysql> select ssl_type from user where user='root' and host='%';
+-----------+
| ssl_type  |
+-----------+
| SPECIFIED |
+-----------+
1 row in set (0.01 sec)

mysql> 

But EdgelessDB enforces TLS connection, why should I use tls=skip-verify or tls=custom to connect via code, while I can use mysql -h127.0.0.1 -utest -p from the MySQL client under linux?

[thomasten]

Hi, We need to distinguish between the TLS connection itself, server authentication, and client authentication here.

EdgelessDB always enforces TLS. Try mysql -h127.0.0.1 -utest -p123 --ssl-mode=DISABLED. It won't work. (If you don't use any --ssl args, the mysql client will try TLS first and falls back to unencrypted. That's why it works without these args.)

TLS without server auth is not secure. Unfortunately, the mysql client doesn't enforce it by default. You can enforce it by mysql -h127.0.0.1 -utest -p123 --ssl-mode=VERIFY_CA. It won't work without also using --ssl-ca. (When using --ssl-ca, ssl-mode is implicitly set to VERIFY_CA, so you can omit that one.)

EdgelessDB can't enforce server auth. This can only happen on the client side.

Regarding client auth, EdgelessDB enforces it for users created with REQUIRE ISSUER. Connecting as root without --ssl-cert and --ssl-key won't work.

When connecting as user test, ssl-cert and ssl-key are ignored because the user isn't created with REQUIRE ISSUER.