[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 12:52:28 UTC 2024
Hello again,
Just a quick update about the issue: the issue only occurs when invoking the export with `--all`.
Requesting an export for individual zones with `--zone <zone>` works fine.
I have two zones (`example.com` and `example.net`) on my test signer.
I can export each of them separately but `--all` fails.
I added a third test zone (`example.org`): no change.
I deleted all three zones, purged keys, purged policies, re-added policies, re-created keys, re-added zones: no change.
I suppose one of the tests conditions in `perform_keystate_export` (from `keystate_export_cmd.c`) fails in the case `all == 1`.
As all error cases yield the same error message, I can't narrow this further down w/o recompiling from source.
On 2024-07-26 10:14, Guillaume-Jean Herbiet via Opendnssec-user wrote:
> 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,
>
>
> _______________________________________________
> Opendnssec-user mailing list
> Opendnssec-user at lists.opendnssec.org
> https://lists.opendnssec.org/mailman/listinfo/opendnssec-user
--
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/e1f35b4f/attachment.bin>
More information about the Opendnssec-user
mailing list