diff options
Diffstat (limited to '2026/2026-08-25-crt-sh-psql.md')
| -rw-r--r-- | 2026/2026-08-25-crt-sh-psql.md | 199 |
1 files changed, 199 insertions, 0 deletions
diff --git a/2026/2026-08-25-crt-sh-psql.md b/2026/2026-08-25-crt-sh-psql.md new file mode 100644 index 0000000..276cf93 --- /dev/null +++ b/2026/2026-08-25-crt-sh-psql.md @@ -0,0 +1,199 @@ +```bash +$ psql -h crt.sh -p 5432 -U guest certwatch +certwatch-> \d + + List of relations + Schema | Name | Type | Owner +--------+----------------------------------------------+-------------------+----------- + public | accepted_roots | table | certwatch + public | applicable_purpose | table | certwatch + public | bugzilla_attachment | table | certwatch + public | bugzilla_bug | table | certwatch + public | bugzilla_mention | table | certwatch + public | ca | table | certwatch + public | ca_bak | table | certwatch + public | ca_certificate | table | certwatch + public | ca_id_seq | sequence | certwatch + public | ca_issuer | table | certwatch + public | ca_trust_purpose | table | certwatch + public | cached_response | table | certwatch + public | ccadb_caowner | table | certwatch + public | ccadb_certificate | table | certwatch + public | certificate | partitioned table | certwatch + public | certificate_2013andbefore | table | certwatch + public | certificate_2014 | table | certwatch + public | certificate_2015 | table | certwatch + public | certificate_2016 | table | certwatch + public | certificate_2017 | table | certwatch + public | certificate_2018 | table | certwatch + public | certificate_2019 | table | certwatch + public | certificate_2020 | table | certwatch + public | certificate_2021 | table | certwatch + public | certificate_2022 | table | certwatch + public | certificate_2023 | table | certwatch + public | certificate_2024 | table | certwatch + public | certificate_2025 | table | certwatch + public | certificate_2026 | table | certwatch + public | certificate_2027q1 | table | certwatch + public | certificate_2027q1_id_seq | sequence | certwatch + public | certificate_2027q2 | table | certwatch + public | certificate_2027q2_id_seq | sequence | certwatch + public | certificate_2027q3 | table | certwatch + public | certificate_2027q3_id_seq | sequence | certwatch + public | certificate_2027q4 | table | certwatch + public | certificate_2027q4_id_seq | sequence | certwatch + public | certificate_2028q1 | table | certwatch + public | certificate_2028q1_id_seq | sequence | certwatch + public | certificate_2028q2 | table | certwatch + public | certificate_2028q2_id_seq | sequence | certwatch + public | certificate_2028q3 | table | certwatch + public | certificate_2028q3_id_seq | sequence | certwatch + public | certificate_2028q4 | table | certwatch + public | certificate_2028q4_id_seq | sequence | certwatch + public | certificate_2029q1 | table | certwatch + public | certificate_2029q1_id_seq | sequence | certwatch + public | certificate_2029q2 | table | certwatch + public | certificate_2029q2_id_seq | sequence | certwatch + public | certificate_2029q3 | table | certwatch + public | certificate_2029q3_id_seq | sequence | certwatch + public | certificate_2029q4 | table | certwatch + public | certificate_2029q4_id_seq | sequence | certwatch + public | certificate_2030andbeyond | table | certwatch + public | certificate_2030andbeyond_id_seq | sequence | certwatch + public | certificate_and_identities | view | certwatch + public | certificate_id_seq | sequence | certwatch + public | certificate_identity | view | certwatch + public | certificate_lifecycle | view | certwatch + public | crl | table | certwatch + public | crl_revoked | table | certwatch + public | crl_revoked_seq | sequence | certwatch + public | ct_log | table | certwatch + public | ct_log_bak | table | certwatch + public | ct_log_entry | partitioned table | certwatch + public | ct_log_entry_2013 | table | certwatch + public | ct_log_entry_2014 | table | certwatch + public | ct_log_entry_2015 | table | certwatch + public | ct_log_entry_2016 | table | certwatch + public | ct_log_entry_2017 | table | certwatch + public | ct_log_entry_2018 | table | certwatch + public | ct_log_entry_2019 | table | certwatch + public | ct_log_entry_2020 | table | certwatch + public | ct_log_entry_2021 | table | certwatch + public | ct_log_entry_2022 | table | certwatch + public | ct_log_entry_2023 | table | certwatch + public | ct_log_entry_2024 | table | certwatch + public | ct_log_entry_2025 | table | certwatch + public | ct_log_entry_2026 | table | certwatch + public | ct_log_entry_snap | table | certwatch + public | ct_log_operator | table | certwatch + public | debian_weak_key | table | certwatch + public | debian_weak_key_new | table | certwatch + public | digicert_temp | table | certwatch + public | getsth_update_rob | table | certwatch + public | google_blocklist_import | table | certwatch + public | google_crlset_import | table | certwatch + public | google_revoked | table | certwatch + public | importleafcerts_log | table | certwatch + public | importleafcerts_log_id_seq | sequence | certwatch + public | invalid_certificate | table | certwatch + public | invalid_certificate_id_seq | sequence | certwatch + public | lint_cert_issue | table | certwatch + public | lint_issue | table | certwatch + public | lint_issue_id_seq | sequence | certwatch + public | lint_summary | table | certwatch + public | linter_version | table | certwatch + public | microsoft_disallowedcert | table | certwatch + public | mozilla_cert_validation_success | table | certwatch + public | mozilla_cert_validation_success_import | table | certwatch + public | mozilla_onecrl | table | certwatch + public | mozilla_root_hashes | table | certwatch + public | ocsp_responder | table | certwatch + public | pg_stat_statements | view | postgres + public | process_new_entries_log | table | certwatch + public | process_new_entries_log_id_seq | sequence | certwatch + public | root_trust_purpose | table | certwatch + public | temp_1mil | table | certwatch + public | temp_accv | table | certwatch + public | temp_accv2 | table | certwatch + public | temp_accv_org_notrevoked | table | certwatch + public | temp_accv_org_revoked | table | certwatch + public | temp_argon20240918sofar | table | certwatch + public | temp_argon20240918sofar_2 | table | certwatch + public | temp_argon_sectigo | table | certwatch + public | temp_argon_sectigo_2 | table | certwatch + public | temp_ca_revocationday | table | certwatch + public | temp_ca_revocationhour | table | certwatch + public | temp_caowner_revocationday | table | certwatch + public | temp_cc | table | certwatch + public | temp_certificate_hold | table | certwatch + public | temp_certs_with_dodo_scts | table | certwatch + public | temp_cfca_bug1886135 | table | certwatch + public | temp_cfca_bug1886135_missing | table | certwatch + public | temp_cisco | table | certwatch + public | temp_cisco_preaug24 | table | certwatch + public | temp_crl_old | table | certwatch + public | temp_dec2022_ovev | table | certwatch + public | temp_dec2023_ovev | table | certwatch + public | temp_dec2024_ovev | table | certwatch + public | temp_digicert_smime | table | certwatch + public | temp_digicert_smime_revocations | table | certwatch + public | temp_dodo_embedded_scts | table | certwatch + public | temp_entrust_allov | table | certwatch + public | temp_entrust_attachment9396137 | table | certwatch + public | temp_entrust_attachment9396137_sans | table | certwatch + public | temp_entrust_attachment9396137_with_replaced | table | certwatch + public | temp_entrust_ev | table | certwatch + public | temp_entrust_ev2 | table | certwatch + public | temp_entrust_ev_attachment | table | certwatch + public | temp_entrust_ev_attachment2 | table | certwatch + public | temp_entrust_ev_attachment3 | table | certwatch + public | temp_entrust_ev_certs | table | certwatch + public | temp_entrust_ev_policy | table | certwatch + public | temp_entrust_ev_take1 | table | certwatch + public | temp_entrust_latecps | table | certwatch + public | temp_extension_criticality_20240322 | table | certwatch + public | temp_malku | table | certwatch + public | temp_ms_20260309 | table | certwatch + public | temp_precertsignercas | table | certwatch + public | temp_precertsigningcerts | table | certwatch + public | temp_rob_20260416 | table | certwatch + public | temp_rob_crlurls | table | certwatch + public | temp_rob_crlurls2 | table | certwatch + public | temp_rob_crlurls3 | table | certwatch + public | temp_rob_crlurls4 | table | certwatch + public | temp_rob_crlurls5 | table | certwatch + public | temp_rob_crlurls6 | table | certwatch + public | temp_rob_crlurls7 | table | certwatch + public | temp_russian_certs | table | certwatch + public | temp_russian_certs_summary | table | certwatch + public | temp_smime_no_dirname_constraint | table | certwatch + public | temp_ssldotcom | table | certwatch + public | temp_telekomsecurity_bug1875820 | table | certwatch + public | temp_telekomsecurity_bug1875820_missing | table | certwatch + public | temp_twca_bug1885132 | table | certwatch + public | temp_twca_bug1885132_missing | table | certwatch + public | temp_vmc | table | certwatch + public | temp_xenon20240918sofar | table | certwatch + public | temp_xenon20240918sofar_2 | table | certwatch + public | trust_context | table | certwatch + public | trust_purpose | table | certwatch +(172 rows) +``` + +--- + +```bash +$ psql -h crt.sh -p 5432 -U guest -d certwatch -q -c "SELECT c.ID, x509_commonName(c.CERTIFICATE) FROM certificate c WHERE to_tsquery('certwatch', 'rud.is:*') @@ identities(c.CERTIFICATE)" + id | x509_commonname +-------------+------------------------ + 36847446 | rud.is + 1096789 | rud.is + 3895274 | rud.is + 12708165 | l.rud.is + 15434572 | rud.is + 16818224 | l.rud.is + 23050430 | l.rud.is + 30616638 | l.rud.is + 22109666 | rud.is +... +``` |