Bug #80720 Unclear error message while using encryption if there is a permission issue
Submitted: 14 Mar 2016 7:52 Modified: 5 Jan 2018 18:26
Reporter: Shahriyar Rzayev (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.7.11 OS:Any
Assigned to: CPU Architecture:Any

[14 Mar 2016 7:52] Shahriyar Rzayev
Description:
While trying to use encryption:

mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION='Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

Checked and it is loaded:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

If master key is missing then why to load the plugin? In fact loaded plugin can not be used.

From error log:

[ERROR] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file 
or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'

For me it is better to invalidate plugin if keyring file is missing or the directory is unreadable.

Maybe the error message should be improved to indicate related problem with path:

mysql> select @@keyring_file_data;
+---------------------------------------+
| @@keyring_file_data                   |
+---------------------------------------+
| /opt/percona-5.7.11-4/keyring/keyring |
+---------------------------------------+
1 row in set (0.00 sec)

For eg:
"Permission denied (13) for keyring file path : /opt/percona-5.7.11-4/keyring/keyring"

How to repeat:
See description

Suggested fix:
Invalidate keyring plugin if there is an issue with master key path.
Improve error messages.
[22 Mar 2016 10:08] Umesh Shastry
Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh
[22 Mar 2016 10:08] Umesh Shastry
-- 5.7.11

mysql> use test
Database changed
mysql> CREATE TABLE `sbtest1` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `k` int(10) unsigned NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   `pad` char(60) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`),
    ->   KEY `k_1` (`k`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=473089 DEFAULT CHARSET=utf8 MAX_ROWS=1000000 COMPRESSION='lz4' ENCRYPTION='Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

mysql> show plugins;
+----------------------------+----------+--------------------+-----------------+---------+
| Name                       | Status   | Type               | Library         | License |
+----------------------------+----------+--------------------+-----------------+---------+
| keyring_file               | ACTIVE   | KEYRING            | keyring_file.so | GPL     |
.
.
+----------------------------+----------+--------------------+-----------------+---------+
45 rows in set (0.01 sec)

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE plugin_name='keyring_file';
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_file | ACTIVE        |
+--------------+---------------+
1 row in set (0.00 sec)

mysql> \q

-- From error log I see

2016-03-22T09:55:40.176825Z 0 [ERROR] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'
[4 Dec 2017 14:39] Georgi Kodinov
Posted by developer:
 
Invalidating the plugin is probably not a very good idea. Here's why:
The keyring file location is settable at runtime. So you don't need to unload or reload the keyring plugin to correct errors there. 
Once you're done with fixing the path you may to a SET keyring_file_data and it'll make another attempt at initializing.

Now on the error message.

FYI, I'm, getting the following two messages when trying a similar thing on windows:
2017-12-04T14:13:04.482912Z 0 [ERROR] [000000] Plugin keyring_file reported: 'File 'C:/Program Files/MySQL/MySQL Server 8.0/keyring\keyring' not found (Errcode: 2 - No such file or directory)'
2017-12-04T14:13:10.411540Z 0 [ERROR] [000000] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'

Can you please double-check your error log ? 
It's printed as a separate message usually. 

I'm going to check 5.7 next.
[4 Dec 2017 15:20] Shahriyar Rzayev
Hi Georgi,
To be honest I don't remember the real intention for opening this report :)
Tested with 5.7.20 and from error log it is clear that there is permission denied issue:

2017-12-04T15:10:14.347995Z 0 [ERROR] Plugin keyring_file reported: 'File '/home/shahriyar.rzaev/sandboxes/msb_5_7_20/keyring-file/keyring' not found (Errcode: 13 - Permission denied)'
2017-12-04T15:10:14.348008Z 0 [ERROR] Plugin keyring_file reported: 'keyring_file initialization failure. Please check if the keyring_file_data points to readable keyring file or keyring file can be created in the specified location. The keyring_file will stay unusable until correct path to the keyring file gets provided'

Maybe I was thinking about to indicate this directly in sql error if it is applicable. Because this thing is related to file itself.

Instead of this one:
> create table t1(id int) encryption='Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded

Something like:

> create table t1(id int) encryption='Y';
ERROR 3185 (HY000): Can't find master key from keyring(Errcode: 13 - Permission denied), please check keyring plugin is loaded properly.

Does it make sense?
[4 Dec 2017 16:41] Georgi Kodinov
Posted by developer:
 
Good that we are in sync wrt to the error message in the server log. 
FYI, I get the same with 5.7 too (just checked). 

As for returning it to the user: IMHO there are two kinds of problems. A set of problems for the DBA to solve and a set of problems for the user to solve. Typical DBA problems include (you guessed it) no access to the file system, lack of space, disk corruption, memory exhausted etc. And typical user issues are: bad query syntax, resource temporarily unavailable etc. 

We tend to convey the first kind into the error log (as this is what a typical DBA reads I guess). And the second kind is sent to the user via SQL status codes. Kind of like what PHP does with errors. 

Note that there's a security angle to this too. Why would a typical user need to know there's a permission denied ? I can get that it's needed in a development situation. And this is a neat feature request. But it should not be on by default.

Thus I'm afraid all I can offer at this point is a better worded error message. What would that be in your view ? 
FYI, the current one is in fact very factual: since it couldn't read the keyring data file the keyring is empty.
[4 Dec 2017 18:44] Shahriyar Rzayev
Fair enough.
You convinced me as was in Romania)

But let me add some point here:

> create table t1(id int) encryption='Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded

For me this is == there is no such file created == the keyring plugin is not loaded. But in fact it is loaded and there is such file, but it has lacking permissions to be read. But yes, for user, it is roughly equal to non-existing file and it should be checked by dba.

Beside this, nothing important here. Feel free to close.
[5 Dec 2017 9:06] Georgi Kodinov
Posted by developer:
 
Man, Bucharest! Good times!

I decided to add some more text to the SQL error: 
Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.

Hope that increases the discoverabillity of the issue at hand.
[5 Dec 2017 9:19] Shahriyar Rzayev
Really, Good times)

Yes, that will be great message.
[5 Jan 2018 18:26] Paul Dubois
Posted by developer:
 
Fixed in 5.7.22, 8.0.4, 9.0.0.

Cosmetic change to error message. No changelog entry needed.