[Opendnssec-user] "ods-enforcer key export" issue with MySQL backend (MariaDB) on OpenDNSSEC 2.1.13
Guillaume-Jean Herbiet
gjherbiet at restena.lu
Fri Jul 26 08:14:56 UTC 2024
Hello,
After reliably using OpenDNSSEC with the SQLite back-end, I am currently testing operations with the MySQL bac-kend (using MariaDB).
Everything seems to be working fine, except when trying to export DS for KSKs in the "ready" state (i.e. `ods-enforcer key export --all --keytype KSK --keystate ready`).
Particularly, listing the keys (i.e. `ods-enforcer key list [...]`) works fine.
This yields the following error (`<Verbosity>5</Verbosity>` in `conf.xml`):
```
Jul 26 09:30:57 signer-test ods-enforcerd[475]: SELECT keyData.id, keyData.rev, keyData.zoneId, keyData.hsmKeyId, keyData.algorithm, keyData.inception, keyData.role, keyData.introducing, keyData.shouldRevoke, keyData.standby, keyData.activeZsk, keyData.publish, keyData.activeKsk, keyData.dsAtParent, keyData.keytag, keyData.minimize FROM keyData
Jul 26 09:30:57 signer-test ods-enforcerd[475]: SELECT keyData.id, keyData.rev, keyData.zoneId, keyData.hsmKeyId, keyData.algorithm, keyData.inception, keyData.role, keyData.introducing, keyData.shouldRevoke, keyData.standby, keyData.activeZsk, keyData.publish, keyData.activeKsk, keyData.dsAtParent, keyData.keytag, keyData.minimize FROM keyData
Jul 26 09:30:57 signer-test ods-enforcerd[475]: [keystate_export_cmd] Error fetching from database
```
I can however run the query just fine while directly querying the local MariaDB database with the same user OpenDNSSEC is using (this is a local test VM so, yes, credentials are not secure):
```
# mysql -u opendnssec -popendnssec opendnssec -e 'SELECT keyData.id, keyData.rev, keyData.zoneId, keyData.hsmKeyId, keyData.algorithm, keyData.inception, keyData.role, keyData.introducing, keyData.shouldRevoke, keyData.standby, keyData.activeZsk, keyData.publish, keyData.activeKsk, keyData.dsAtParent, keyData.keytag, keyData.minimize FROM keyData;'
+-----+------+--------+----------+-----------+------------+------+-------------+--------------+---------+-----------+---------+-----------+------------+--------+----------+
| id | rev | zoneId | hsmKeyId | algorithm | inception | role | introducing | shouldRevoke | standby | activeZsk | publish | activeKsk | dsAtParent | keytag | minimize |
+-----+------+--------+----------+-----------+------------+------+-------------+--------------+---------+-----------+---------+-----------+------------+--------+----------+
| 3 | 7728 | 2 | 27 | 8 | 1699630161 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 18685 | 4 |
| 11 | 7063 | 1 | 2 | 8 | 1699869077 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 5 | 62291 | 4 |
| 13 | 7712 | 2 | 28 | 8 | 1699869077 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 5 | 24224 | 4 |
| 33 | 7012 | 1 | 61 | 8 | 1699955477 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 17150 | 4 |
| 34 | 7664 | 2 | 62 | 8 | 1699955477 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 20615 | 4 |
| 45 | 6983 | 1 | 81 | 8 | 1700143974 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 61357 | 4 |
| 47 | 7636 | 2 | 82 | 8 | 1700143976 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 59245 | 4 |
| 63 | 6945 | 1 | 85 | 8 | 1700230374 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 31390 | 4 |
| 64 | 7599 | 2 | 86 | 8 | 1700230376 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 48550 | 4 |
| 95 | 2810 | 1 | 92 | 8 | 1721024194 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 56810 | 4 |
| 97 | 3466 | 2 | 118 | 8 | 1721024195 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 62111 | 4 |
| 121 | 2750 | 1 | 145 | 8 | 1721110594 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 39262 | 4 |
| 122 | 3409 | 2 | 146 | 8 | 1721110595 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 551 | 4 |
| 147 | 2699 | 1 | 147 | 8 | 1721196994 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 5 | 25405 | 4 |
[...]
```
This is how the `keyData` table structure looks on my MariaDB server:
```
# mysql opendnssec -e "DESCRIBE keyData;"
+--------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| rev | int(10) unsigned | NO | | 1 | |
| zoneId | bigint(20) unsigned | NO | MUL | NULL | |
| hsmKeyId | bigint(20) unsigned | NO | MUL | NULL | |
| algorithm | int(10) unsigned | NO | | NULL | |
| inception | int(10) unsigned | NO | | NULL | |
| role | int(11) | NO | | NULL | |
| introducing | int(10) unsigned | NO | | NULL | |
| shouldRevoke | int(10) unsigned | NO | | NULL | |
| standby | int(10) unsigned | NO | | NULL | |
| activeZsk | int(10) unsigned | NO | | NULL | |
| publish | int(10) unsigned | NO | | NULL | |
| activeKsk | int(10) unsigned | NO | | NULL | |
| dsAtParent | int(11) | NO | | NULL | |
| keytag | int(10) unsigned | NO | | NULL | |
| minimize | int(10) unsigned | NO | | NULL | |
+--------------+---------------------+------+-----+---------+----------------+
```
The MariaDB structure was imported from a pre-existing SQLite `kasp.db` on this system using:
```
# /usr/share/opendnssec/convert_sqlite_to_mysql -i /var/lib/opendnssec/db/kasp.db -o opendnssec -h localhost -u opendnssec -p opendnssec
```
This is the `keyData` table structure on the `kasp.db`:
```
# sqlite3 /var/lib/opendnssec/db/kasp.db '.schema keyData'
CREATE TABLE keyData ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, rev INTEGER NOT NULL DEFAULT 1, zoneId INTEGER NOT NULL, hsmKeyId INTEGER NOT NULL, algorithm UNSIGNED INT NOT NULL, inception UNSIGNED INT NOT NULL, role INT NOT NULL, introducing UNSIGNED INT NOT NULL, shouldRevoke UNSIGNED INT NOT NULL, standby UNSIGNED INT NOT NULL, activeZsk UNSIGNED INT NOT NULL, publish UNSIGNED INT NOT NULL, activeKsk UNSIGNED INT NOT NULL, dsAtParent INT NOT NULL, keytag UNSIGNED INT NOT NULL, minimize UNSIGNED INT NOT NULL);
CREATE INDEX keyDataZoneId ON keyData ( zoneId );
CREATE INDEX keyDataHsmKeyId ON keyData ( hsmKeyId );
```
Keys are stored locally by SoftHSM.
Finally some information on my system:
* Operating System: Debian GNU/Linux 12 (bookworm)
* Kernel: `Linux signer-test 6.1.0-22-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.94-1 (2024-06-21) x86_64 GNU/Linux`
* OpenDNSSEC version: `opendnssec version 2.1.13`, manually backported to `bookworm` from `sid` packages (see: https://salsa.debian.org/debian/opendnssec)
* MariaDB version: `mariadb Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper`, installed from `bookworm` packages
* SoftHSM version: `2.6.1`, installed from `bookworm` packages
Let me know if I can submit more details to help troubleshoot this issue.
Regards,
--
Guillaume-Jean Herbiet, PhD
.lu Technical Manager
Fondation Restena
2, avenue de l'Université
L-4365 Esch-sur-Alzette
T +352 42 44 09 1
F +352 42 24 73
restena.lu | dns.lu | my.lu
PGP 0x3A4C47C7
This email may contain information for limited distribution only, please treat accordingly.
*** I am out-of-office on Wednesdays ***
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OpenPGP_signature.asc
Type: application/pgp-signature
Size: 840 bytes
Desc: OpenPGP digital signature
URL: <http://lists.opendnssec.org/pipermail/opendnssec-user/attachments/20240726/bf4ea47e/attachment.bin>
More information about the Opendnssec-user
mailing list