search

MariaDB / mariadb-docker

Docker Official Image packaging for MariaDB
https://mariadb.org
GNU General Public License v2.0
770 stars 438 forks source link

What is the reason why I cannot restart the server when I add the InnoDB/XtraDB Encryption Setup to the my.cnf file? #498

Closed Ramles97 closed 1 year ago

Ramles97 commented 1 year ago

I got a task to find out how to encrypt data with a database like mariadb. So I prepared the mariadb docker on my ubuntu (version 20.04), and I learned a bit how to create new Databases and tables since I am using a database for the first time.

But while trying to activate the encryption on the database (Using this guide on https://webdock.io/en/docs/how-guides/security-guides/how-to-enable-encryption-mariadb), I created a keys.enc file and also a password for it so that mariadb can use it for the encryption.

The problem that I encountered after following the steps on the above mentioned link, is that I cannot restart the server after adding the encryption configuration to the my.cnf on /etc/mysq/ path.

The configuration file of mariadb contained beforehand the following lines: The MariaDB configuration file

The MariaDB/MySQL tools read configuration files in the following order:

  1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
  2. "/etc/mysql/conf.d/*.cnf" to set global options.

  3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.

    1. "~/.my.cnf" to set user-specific options.

    If the same option is defined multiple times, the last one will apply.

    One can use all long options that the program supports. Run program with --help to get a list of available options and with --print-defaults to see which it would actually understand and use.

    This group is read both both by the client and the server use it for options that affect everything

[client-server]

Import all .cnf files from configuration directory !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mariadb.conf.d/

And then I added the File Key Management configuration and also the InnoDB/XtraDB Encryption Setup for [mariadb] File Key Management plugin_load_add = file_key_management file_key_management_filename = /etc/mysql/keys.enc file_key_management_filekey = FILE:/etc/mysql/passwordfile file_key_management_encryption_algorithm = aes_cbc

InnoDB/XtraDB Encryption Setup innodb_default_encryption_key_id = 1 innodb_encrypt_tables = ON innodb_encrypt_log = ON innodb_encryption_threads = 4

Aria Encryption Setup aria_encrypt_tables = ON

Temp & Log Encryption encrypt-tmp-disk-tables = 1 encrypt-tmp-files = 1 encrypt_binlog = ON

After saving the file I tried to restart the server using the command: "sudo service mariadb restart", all I get is: Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

Or when I try to start mariadb (sudo mariadb -u root -p) I also get an error: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Any help? So most probably something on the InnoDB Enc. Setup blocks mariadb/server to start.

grooverdan commented 1 year ago

What do the container logs look like (use Markup for formatting)? You can't start/stop mariadb like a service within the container. Pass the encryption keys as a volume, and probably not as /etc/mysql - has too many overloaded meanings already.

See #142 and 8c3d50ef67b2557bb6b348c300bec546a8a43ae6 on working examples on how it works.

Ramles97 commented 1 year ago

@grooverdan How can I pass the encryption keys as a volume? It is just weird since the one who created this guide https://webdock.io/en/docs/how-guides/security-guides/how-to-enable-encryption-mariadb could enable the encryption..

martadinata666 commented 1 year ago

the fastest way is to mount bind per file or mount bind the folder.

    volumes:
      - /etc/mysql/encryption/key.enc:/etc/mysql/encryption/key.enc
      - /etc/mysql/encryption/password_file:/etc/mysql/encryption/password_file
    volumes:
      - /etc/mysql/encryption:/etc/mysql/encryption
Ramles97 commented 1 year ago

Hi @grooverdan & @martadinata666
Thanks a lot for you comments and your help. Sorry for my too late reply.. I was doing something else the last weeks.. Now I am back at the Problem.. I mounted/tried mounting the whole file: sudo mount --bind /etc/mysql/encryption /etc/mysql/encryption which was successful (I run ls /etc/mysql/encryption/ and got two keyfile.enc keyfile.key as an output)

But still cannot run the server when I try to restart it.. I still dont know how to solve this issue.. But I am sure that something is wrong with one of the following encryption lines:

Activate Encryption:

plugin_load_add = file_key_management file_key_management_filename = /etc/mysql/encryption/keyfile.enc file_key_management_filekey = /etc/mysql/encryption/keyfile.key

file_key_management_encryption_algorithm = AES_CBC

innodb_encrypt_tables = ON

innodb_encrypt_log = ON

innodb_encryption_threads = 4

Especially these 2 lines: innodb_encrypt_tables = ON innodb_encrypt_log = ON When I comment these lines out, the service does not throw any error codes..

Any further help will be much appreciated!

grooverdan commented 1 year ago 
$ for a in 1 2 3 4 ; do echo "$a;"$(openssl rand -hex 32) ; done > keys
$  openssl rand -hex 128 > password_file
$  openssl enc -aes-256-cbc -md sha1 -pass file:password_file -in keys -out keys.enc
*** WARNING : deprecated key derivation used.
Using -iter or -pbkdf2 would be better.
$ mv password_file encryption/keyfile.key
$ mv keys.enc encryption/keyfile.enc
$ podman run -v ./enc_conf:/etc/mysql/conf.d:z -v ./encryption:/etc/mysql/encryption:z --env MARIADB_ROOT_PASSWORD=bob --name enctest --rm  mariadb:lts my_print_defaults --mysqld
--socket=/run/mysqld/mysqld.sock
--host-cache-size=0
--skip-name-resolve
--pid-file=/run/mysqld/mysqld.pid
--basedir=/usr
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--plugin_load_add=file_key_management
--file_key_management_filename=/etc/mysql/encryption/keyfile.enc
--file_key_management_filekey=FILE:/etc/mysql/encryption/keyfile.key
--innodb_encrypt_tables=ON
--innodb_encrypt_log=ON

$ podman run -v ./enc_conf:/etc/mysql/conf.d:z -v ./encryption:/etc/mysql/encryption:z --env MARIADB_ROOT_PASSWORD=bob --name enctest --rm  mariadb:lts 
2023-07-03 11:48:34+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.4+maria~ubu2204 started.
2023-07-03 11:48:34+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2023-07-03 11:48:34+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.11.4+maria~ubu2204 started.
2023-07-03 11:48:34+00:00 [Note] [Entrypoint]: Initializing database files
...
MariaDB [test]> create table t encrypted=yes select concat('chicken ', seq) from seq_1_to_30000;
Query OK, 30000 rows affected (0.201 sec)
Records: 30000  Duplicates: 0  Warnings: 0

MariaDB [test]> show global variables like '%enc%';
+------------------------------------------+---------+
| Variable_name                            | Value   |
+------------------------------------------+---------+
| aria_encrypt_tables                      | OFF     |
| encrypt_binlog                           | OFF     |
| encrypt_tmp_disk_tables                  | OFF     |
| encrypt_tmp_files                        | OFF     |
| file_key_management_encryption_algorithm | aes_cbc |
| innodb_buf_dump_status_frequency         | 0       |
| innodb_default_encryption_key_id         | 1       |
| innodb_defragment_frequency              | 40      |
| innodb_encrypt_log                       | ON      |
| innodb_encrypt_tables                    | ON      |
| innodb_encrypt_temporary_tables          | OFF     |
| innodb_encryption_rotate_key_age         | 1       |
| innodb_encryption_rotation_iops          | 100     |
| innodb_encryption_threads                | 0       |
| innodb_purge_rseg_truncate_frequency     | 128     |
+------------------------------------------+---------+
15 rows in set (0.002 sec)

MariaDB [test]> show global status like '%enc%';
+--------------------------------------------------+-------+
| Variable_name                                    | Value |
+--------------------------------------------------+-------+
| Com_alter_sequence                               | 0     |
| Com_create_sequence                              | 0     |
| Com_drop_sequence                                | 0     |
| Innodb_num_pages_encrypted                       | 0     |
| Innodb_encryption_rotation_pages_read_from_cache | 0     |
| Innodb_encryption_rotation_pages_read_from_disk  | 0     |
| Innodb_encryption_rotation_pages_modified        | 0     |
| Innodb_encryption_rotation_pages_flushed         | 0     |
| Innodb_encryption_rotation_estimated_iops        | 0     |
| Innodb_encryption_n_merge_blocks_encrypted       | 0     |
| Innodb_encryption_n_merge_blocks_decrypted       | 0     |
| Innodb_encryption_n_rowlog_blocks_encrypted      | 0     |
| Innodb_encryption_n_rowlog_blocks_decrypted      | 0     |
| Innodb_encryption_n_temp_blocks_encrypted        | 0     |
| Innodb_encryption_n_temp_blocks_decrypted        | 0     |
| Innodb_encryption_num_key_requests               | 4     |
+--------------------------------------------------+-------+
16 rows in set (0.001 sec)

Notice your last example doesn't have file_key_management_filekey beginning with FILE:

Ramles97 commented 1 year ago

@grooverdan Just added "FILE:" to the start of file_key_management_filekey... I still get the error...

grooverdan commented 1 year ago

what error? Show the container logs like previously asked.

Ramles97 commented 1 year ago

@grooverdan this is the error that I get:

Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

These are the logs from journalctl -xe: 

- 
-- A start job for unit mariadb.service has begun execution.
-- 
journalctl -xe
-- 
-- A start job for unit mariadb.service has begun execution.
-- 
-- The job identifier is 13967.
Jul 03 11:33:55 loo-VirtualBox systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAIL>
-- Subject: Unit process exited
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- An ExecStart= process belonging to unit mariadb.service has exited.
-- 
-- The process' exit code is 'exited' and its exit status is 1.
Jul 03 11:33:55 loo-VirtualBox systemd[1]: mariadb.service: Failed with result 'exit-code'.
-- Subject: Unit failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- The unit mariadb.service has entered the 'failed' state with result 'exit-code'.
Jul 03 11:33:55 loo-VirtualBox systemd[1]: Failed to start MariaDB 10.3.38 database server.
-- Subject: A start job for unit mariadb.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- A start job for unit mariadb.service has finished with a failure.
-- 
-- The job identifier is 13967 and the job result is failed.
Jul 03 11:33:55 loo-VirtualBox sudo[113327]: pam_unix(sudo:session): session closed for user root
Jul 03 11:34:38 loo-VirtualBox pkexec[113476]: pam_unix(polkit-1:session): session opened for user root by >
Jul 03 11:34:38 loo-VirtualBox pkexec[113476]: emp34ol: Executing command [USER=root] [TTY=unknown] [CWD=/h>

Otherwise these are the logs from the command "systemctl status mariadb.service"

● mariadb.service - MariaDB 10.3.38 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─override.conf
     Active: failed (Result: exit-code) since Mon 2023-07-03 11:39:35 UTC; 8s ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 113513 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, stat>
    Process: 113514 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, sta>
    Process: 113516 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..;>
    Process: 113645 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=ex>
   Main PID: 113645 (code=exited, status=1/FAILURE)
     Status: "MariaDB server is down"

Jul 03 11:39:35 loo-VirtualBox systemd[1]: Starting MariaDB 10.3.38 database server...
Jul 03 11:39:35 loo-VirtualBox systemd[1]: mariadb.service: Main process exited, code=exited, status=1/FAIL>
Jul 03 11:39:35 loo-VirtualBox systemd[1]: mariadb.service: Failed with result 'exit-code'.
Jul 03 11:39:35 loo-VirtualBox systemd[1]: Failed to start MariaDB 10.3.38 database server.

I hope these are the logs that you wanted to see.. I actually never really used ubuntu nor mariadb before.. Thanks a lot for the help!

grooverdan commented 1 year ago

First, you aren't using containers it seems, despite your original question, which is why the terminology with volumes doesn't map to your setup.

Help forums are listed here: https://mariadb.com/kb/en/getting-help-with-mariadb/, for next time.

The error log is probably the file /var/log/mysql/error.log from the 20.04 package, but you can check by running my_print_defaults --mysqld. Look at the preview tab to make your message readable using ``` on either side of your log file contents.

Ramles97 commented 1 year ago

@grooverdan first and foremost, thanks a lot for the Tip and for editing my comment. It looks way better and readable now. I checked the "/var/log/mysql/error.log" and it says exactly what I expected. The lines had an error:

2023-07-03  7:44:29 0 [Note] Starting MariaDB 10.3.38-MariaDB-0ubuntu0.20.04.1 source revision c73985f2ce8a391582787f3e310a011c1a712bec as process 1025
2023-07-03  7:44:29 0 [ERROR] mysqld: File '/etc/mysql/passwordfile' not found (Errcode: 13 "Permission denied")
2023-07-03  7:44:29 0 [ERROR] Plugin 'file_key_management' init function returned error.
2023-07-03  7:44:29 0 [ERROR] Plugin 'file_key_management' registration as a ENCRYPTION failed.
2023-07-03  7:44:29 0 [ERROR] InnoDB: cannot enable encryption, encryption plugin is not available
2023-07-03  7:44:29 0 [ERROR] Plugin 'InnoDB' init function returned error.
2023-07-03  7:44:29 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2023-07-03  7:44:29 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-07-03  7:44:29 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2023-07-03  7:44:29 0 [ERROR] Aborting

2023-07-03  7:49:43 0 [Note] Starting MariaDB 10.3.38-MariaDB-0ubuntu0.20.04.1 source revision c73985f2ce8a391582787f3e310a011c1a712bec as process 3259
2023-07-03  7:49:43 0 [ERROR] mysqld: File '/etc/mysql/passwordfile' not found (Errcode: 13 "Permission denied")
2023-07-03  7:49:43 0 [ERROR] Plugin 'file_key_management' init function returned error.
2023-07-03  7:49:43 0 [ERROR] Plugin 'file_key_management' registration as a ENCRYPTION failed.
2023-07-03  7:49:43 0 [ERROR] InnoDB: cannot enable encryption, encryption plugin is not available
2023-07-03  7:49:43 0 [ERROR] Plugin 'InnoDB' init function returned error.
2023-07-03  7:49:43 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2023-07-03  7:49:43 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-07-03  7:49:43 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2023-07-03  7:49:43 0 [ERROR] Aborting

2023-07-03  8:03:03 0 [Note] Starting MariaDB 10.3.38-MariaDB-0ubuntu0.20.04.1 source revision c73985f2ce8a391582787f3e310a011c1a712bec as process 44227
2023-07-03  8:03:03 0 [ERROR] mysqld: File '/etc/mysql/encryption/keyfile.enc' not found (Errcode: 13 "Permission denied")
2023-07-03  8:03:03 0 [ERROR] Plugin 'file_key_management' init function returned error.
2023-07-03  8:03:03 0 [ERROR] Plugin 'file_key_management' registration as a ENCRYPTION failed.
2023-07-03  8:03:03 0 [ERROR] InnoDB: cannot enable encryption, encryption plugin is not available
2023-07-03  8:03:03 0 [ERROR] Plugin 'InnoDB' init function returned error.
2023-07-03  8:03:03 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2023-07-03  8:03:03 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-07-03  8:03:03 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2023-07-03  8:03:03 0 [ERROR] Aborting

and this is what I get after the command my_print_default --mysqld ( The Output lines repeated themself twice for some reason, and I dont know why)

-plugin_load_add=file_key_management
--file_key_management_filename=/etc/mysql/encryption/keyfile.enc
--file_key_management_filekey=FILE:/etc/mysql/encryption/keyfile.key
--file_key_management_encryption_algorithm=AES_CBC
--innodb_encrypt_tables=ON
--innodb_encrypt_log=ON
--innodb_encryption_threads=4
--user=mysql
--pid-file=/run/mysqld/mysqld.pid
--socket=/run/mysqld/mysqld.sock
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--bind-address=127.0.0.1
--query_cache_size=16M
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--plugin_load_add=file_key_management
--file_key_management_filename=/etc/mysql/encryption/keyfile.enc
--file_key_management_filekey=FILE:/etc/mysql/encryption/keyfile.key
--file_key_management_encryption_algorithm=AES_CBC
--innodb_encrypt_tables=ON
--innodb_encrypt_log=ON
--innodb_encryption_threads=4
--user=mysql
--pid-file=/run/mysqld/mysqld.pid
--socket=/run/mysqld/mysqld.sock
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--bind-address=127.0.0.1
--query_cache_size=16M
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
Ramles97 commented 1 year ago

@grooverdan by the way, I found someone who had the same problem that I encountered: https://www.reddit.com/r/mariadb/comments/10rsd8m/comment/j9rc5h8/ I just change ownership and permissions on the encryption folder and keyfile. But still the server cannot start...

grooverdan commented 1 year ago

So what are the permissions/ownship you've set on /etc/mysql , /etc/mysql/encryption/*? Might need an app-armor profile of /etc/apparmor.d/usr.sbin.mysqld:

/etc/mysql/encryption/** r
Ramles97 commented 1 year ago

@grooverdan these are the ownership that are set on the encryption 2 files: -rw------- 1 mysql mysql 96 Jul 3 08:02 keyfile.enc -rw-rw-rw- 1 loo loo 257 Jul 3 11:17 keyfile.key

Update: I can start Mariadb now. But the encryption still does not work properly.. Here is what I get when I try to encrypt a table: MariaDB [mysql]> alter table test_table ENCRYPTED=Yes; ERROR 1478 (HY000): Table storage engine 'InnoDB' does not support the create option 'ENCRYPTION_KEY_ID'

grooverdan commented 1 year ago

Don't have world writeable files:

chown mysql: keyfile.key
chmod u=r,go-rw keyfile.key
grooverdan commented 1 year ago

If you did show warnings after alter table test_table ENCRYPTED=Yes; you'd probably see InnoDB: ENCRYPTION_KEY_ID 1 not available. This is because the keyid of 1 isn't in your keys file.

If you have different numbers https://mariadb.com/kb/en/innodb-system-variables/#innodb_default_encryption_key_id can be set.

encryption keys 1 & 2 should exist - https://mariadb.com/kb/en/encryption-key-management/#using-multiple-encryption-keys