9 ms·

PostgreSQL Support for Certificate Transparency Logs Now Available

57 points by tracymiranda 2y ago

dboreham 2y ago
Translation: we had data loss and had to change storage provider to one that works.
- phoronixrly 2y ago
  Where works means "does not eat data"... A very basic requirement for storage :)
remram 2y ago
> a CT log failure earlier this year due to MariaDB corruption after disk space exhaustion provided the motivation for a change.
- deleted 2y ago
  [deleted]
- nijave 2y ago
  That seems like a rather serious bug. Disappointing there's not more follow up with MariaDB
  - mobilemidget 2y ago
    If you let a disk run full weird shit happens. But it reads to me as _we_, not mariadb, made a big oopsie and TADA \o/ now we change software. So I do strongly hope that besides changing software, they added some disk space monitoring.
    - nijave 2y ago
      I've ran Postgres out of disk space many times in lower environments and it's never corrupted the data. In progress transactions fail and rollback or are lost It sounded like some of the motivation was the team having better understanding around Postgres. I think it's fair to replatform to something you have more experience running after having an incident with a different piece of software
    - agwa 2y ago
      > If you let a disk run full weird shit happens. Only in buggy software that ignores errors from system calls. Obviously you can expect availability problems when you run out of space, but there's no excuse for losing data from committed transactions given that the OS will reliably report the error. > So I do strongly hope that besides changing software, they added some disk space monitoring. One of the action items in their incident report was improving monitoring and alerting: https://groups.google.com/a/chromium.org/g/ct-policy/c/038B7F4g8cU/m/_fP2TIBoBgAJ https://groups.google.com/a/chromium.org/g/ct-policy/c/038B7...
      - evanelias 2y ago
        Keep in mind the report doesn't mention what MariaDB version was being used, nor what storage engine. The description in the incident report simply doesn't align with how InnoDB behaves, at all. (InnoDB is the default storage engine in MariaDB and MySQL, and by far the most widely used storage engine in these DBs.) For example, the author says "Whilst its primary key index still includes the ~1,300 unsequenced rows, the table itself no longer contains them." This is simply impossible in InnoDB, which uses a clustered index – the primary key B-tree literally is the table with InnoDB's design. The statement in the incident report is complete nonsense. Also if the storage volume runs out of space, InnoDB rolls back the statement and returns an error. See https://dev.mysql.com/doc/refman/5.7/en/innodb-error-handling.html https://dev.mysql.com/doc/refman/5.7/en/innodb-error-handlin... (MySQL 5.7 doc, but this also aligns with MariaDB's behavior for this situation) So maybe they were using MyISAM, which is ancient and infamously non-crash-safe and should pretty much never be used for anything of consequence, which has been well-known in the database world for 15+ years. Who knows, they didn't say. The report directly says "Our teams have very little expertise with MySQL/MariaDB", which also makes me question their conclusion "that it is not possible" to restore/recover. Also this whole incident happened to Sectigo, take from that what you will. The one thing I'd criticize MariaDB for here is that they're lacking an additional safety mechanism which MySQL has: by default MySQL will shut down the database server if a write to the binary log cannot be successfully made, ensuring that any writes on the primary can be replicated as well. (iirc this was a contribution from Facebook.)
      - bawolff 2y ago
    - remram 2y ago
      Bad things can happen when your disk is full, but "the whole table is now lost beyond repair" is not reasonable for a database.
      - evanelias 2y ago
        Completely agreed, but "the whole table is now lost beyond repair" is simply not how InnoDB behaves, at all! So the whole incident report is quite fishy. See my reply to the sibling comment.
  - agwa 2y ago
    They know about the problem but appear uninterested in improving the situation: https://mariadb.com/kb/en/database-corruption-and-data-loss-in-mariadb/#comment_5186 https://mariadb.com/kb/en/database-corruption-and-data-loss-...
    - bawolff 2y ago
      I don't really think your link supports that conclusion.
politelemon 2y ago
This title reads strange to me, as though postgres certificates, used when connecting using TLS, will be visible in the CTL. Better would be: CTL can now use a postgres backend.
chatmasta 2y ago
Semi-relatedly, you can connect to the crt.sh Postgres instance and query it directly with SQL: psql -h crt.sh -p 5432 -U guest certwatch To generate the SQL queries in the web UI, simply click “advanced” and then the “Show SQL” checkbox, or append it to the URL, like so: https://crt.sh/?q=www.comodo.com&showSQL=Y (Note the generated SQL at the bottom of that page.) Steampipe also has a crt.sh connector: https://hub.steampipe.io/plugins/turbot/crtsh/tables/crtsh_certificate https://hub.steampipe.io/plugins/turbot/crtsh/tables/crtsh_c...
westurner 2y ago
Are there Merkle hashes between the rows in the PostgreSQL CT store like there are in the Trillian CT store? Sigstore Rekor also has centralized Merkle hashes.
- sluongng 2y ago
  Isn’t Rekor runs on top of Trillian?
bawolff 2y ago
> However, a CT log failure earlier this year due to MariaDB corruption after disk space exhaustion provided the motivation for a change. PostgreSQL, with its robust Write-ahead Logging (WAL) and strict adherence to ACID (Atomicity, Consistency, Isolation, Durability) principles, made it a better option for avoiding corruption and improving data integrity of the log. This sounds incredibly suspicious to me. I wonder which storage engine they were using or if there was any unusual configuration that contributed to this issue.

57 points by tracymiranda 2y ago

dboreham 2y ago

Translation: we had data loss and had to change storage provider to one that works.

phoronixrly 2y ago
Where works means "does not eat data"... A very basic requirement for storage :)

remram 2y ago

> a CT log failure earlier this year due to MariaDB corruption after disk space exhaustion provided the motivation for a change.

deleted 2y ago
[deleted]
nijave 2y ago
That seems like a rather serious bug. Disappointing there's not more follow up with MariaDB
- mobilemidget 2y ago
  If you let a disk run full weird shit happens. But it reads to me as _we_, not mariadb, made a big oopsie and TADA \o/ now we change software. So I do strongly hope that besides changing software, they added some disk space monitoring.
  - nijave 2y ago
    I've ran Postgres out of disk space many times in lower environments and it's never corrupted the data. In progress transactions fail and rollback or are lost It sounded like some of the motivation was the team having better understanding around Postgres. I think it's fair to replatform to something you have more experience running after having an incident with a different piece of software
  - agwa 2y ago
    > If you let a disk run full weird shit happens. Only in buggy software that ignores errors from system calls. Obviously you can expect availability problems when you run out of space, but there's no excuse for losing data from committed transactions given that the OS will reliably report the error. > So I do strongly hope that besides changing software, they added some disk space monitoring. One of the action items in their incident report was improving monitoring and alerting: https://groups.google.com/a/chromium.org/g/ct-policy/c/038B7F4g8cU/m/_fP2TIBoBgAJ https://groups.google.com/a/chromium.org/g/ct-policy/c/038B7...
    - evanelias 2y ago
      Keep in mind the report doesn't mention what MariaDB version was being used, nor what storage engine. The description in the incident report simply doesn't align with how InnoDB behaves, at all. (InnoDB is the default storage engine in MariaDB and MySQL, and by far the most widely used storage engine in these DBs.) For example, the author says "Whilst its primary key index still includes the ~1,300 unsequenced rows, the table itself no longer contains them." This is simply impossible in InnoDB, which uses a clustered index – the primary key B-tree literally is the table with InnoDB's design. The statement in the incident report is complete nonsense. Also if the storage volume runs out of space, InnoDB rolls back the statement and returns an error. See https://dev.mysql.com/doc/refman/5.7/en/innodb-error-handling.html https://dev.mysql.com/doc/refman/5.7/en/innodb-error-handlin... (MySQL 5.7 doc, but this also aligns with MariaDB's behavior for this situation) So maybe they were using MyISAM, which is ancient and infamously non-crash-safe and should pretty much never be used for anything of consequence, which has been well-known in the database world for 15+ years. Who knows, they didn't say. The report directly says "Our teams have very little expertise with MySQL/MariaDB", which also makes me question their conclusion "that it is not possible" to restore/recover. Also this whole incident happened to Sectigo, take from that what you will. The one thing I'd criticize MariaDB for here is that they're lacking an additional safety mechanism which MySQL has: by default MySQL will shut down the database server if a write to the binary log cannot be successfully made, ensuring that any writes on the primary can be replicated as well. (iirc this was a contribution from Facebook.)
    - bawolff 2y ago
  - remram 2y ago
    Bad things can happen when your disk is full, but "the whole table is now lost beyond repair" is not reasonable for a database.
    - evanelias 2y ago
      Completely agreed, but "the whole table is now lost beyond repair" is simply not how InnoDB behaves, at all! So the whole incident report is quite fishy. See my reply to the sibling comment.
- agwa 2y ago
  They know about the problem but appear uninterested in improving the situation: https://mariadb.com/kb/en/database-corruption-and-data-loss-in-mariadb/#comment_5186 https://mariadb.com/kb/en/database-corruption-and-data-loss-...
  - bawolff 2y ago
    I don't really think your link supports that conclusion.

politelemon 2y ago

This title reads strange to me, as though postgres certificates, used when connecting using TLS, will be visible in the CTL. Better would be: CTL can now use a postgres backend.

chatmasta 2y ago

Semi-relatedly, you can connect to the crt.sh Postgres instance and query it directly with SQL: psql -h crt.sh -p 5432 -U guest certwatch To generate the SQL queries in the web UI, simply click “advanced” and then the “Show SQL” checkbox, or append it to the URL, like so: https://crt.sh/?q=www.comodo.com&showSQL=Y (Note the generated SQL at the bottom of that page.) Steampipe also has a crt.sh connector: https://hub.steampipe.io/plugins/turbot/crtsh/tables/crtsh_certificate https://hub.steampipe.io/plugins/turbot/crtsh/tables/crtsh_c...

westurner 2y ago

Are there Merkle hashes between the rows in the PostgreSQL CT store like there are in the Trillian CT store? Sigstore Rekor also has centralized Merkle hashes.

sluongng 2y ago
Isn’t Rekor runs on top of Trillian?

bawolff 2y ago

> However, a CT log failure earlier this year due to MariaDB corruption after disk space exhaustion provided the motivation for a change. PostgreSQL, with its robust Write-ahead Logging (WAL) and strict adherence to ACID (Atomicity, Consistency, Isolation, Durability) principles, made it a better option for avoiding corruption and improving data integrity of the log. This sounds incredibly suspicious to me. I wonder which storage engine they were using or if there was any unusual configuration that contributed to this issue.