2023年7月28日 00:40:22go评论78阅读模式

英文:

Calculate x5t#s256 thumbprint of a public certificate present in postgres database using pgcrypto library

问题

我正在开发一个模块，在该模块中，我正在评估向数据库中的所有公钥添加指纹（x5t#256）值的方法。使用JavaScript库和Java库进行转换是可行的。但是尝试使用pgcrypto进行相同操作似乎不太可行，因为它是作为SQL迁移脚本的一部分。是否有任何PostgreSQL库可以帮助我执行相同的操作。

这是我在公钥上运行的shell命令，用于生成指纹并尝试转换为SQL。（在Java或JavaScript中加载证书并输出指纹的方法与此相同）

echo $(openssl x509 -in public_key.pem -fingerprint -noout -sha256) | sed 's/SHA256 Fingerprint=//g' | sed 's/://g' | xxd -r -p | openssl enc -a | tr -d '=' | tr '/+' '_-'

证书：
-----BEGIN CERTIFICATE-----
MIICrzCCAZegAwIBAgIGAYk++jfeMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMT
CE1vY2stSURBMB4XDTIzMDcxMDAzMTUzM1oXDTIzMDcyMDAzMTUzM1owHjEcMBoG
A1UEAxMTU2lkZGhhcnRoIEsgTWFuc291cjCCASIwDQYJKoZIhvcNAQEBBQADggEP
ADCCAQoCggEBAJy7TzHJJNkjlnSi87fkUr8NMM9k3UIkoAtAqiH7J4uPG1wcdgQK
luX1wfhsed7TUnblrZCZXOaxqT2kN1uniC28bekQPkWs/e0Mm8s3r7ncxyTtCMlS
kSlg6ZFN3bV2m3x893vFx81yOGk534Jc9O9qxouxB7WMHn8ynM9BE8k0VaNXyj2/
z0E7IXqpei4UDNdTU0avmqYGjw/YTsTdlrwQebwn9clwVvld2ZFV4jdgErTqLJ/Y
u7wIZmYzL3ib5kf2+tVZhY/MnqsT0Bx+TFatnd2Aout5/Hs2V2HdwSBY6ET6SXVT
NXKDtH3Sw6AyNPj+jo6l5IARsuOvWioTrfsCAwEAATANBgkqhkiG9w0BAQsFAAOC
AQEAgOtPRuk9IyrRGOFWyFlwJdqZxqVO+78UAJKJmBiko6xxeezkYqqiAuwcyWFj
XWmvvcwlTdCyfEnGWRi74r4ma7u0h5O4U3AJxPF0/BKklCF9nabRqtSC9ENPKHpf
/MAsZF/dQkzQ+k8oqCVKgg/OpgmLGg1dBFvBUOsSUtzp2Mv3GhQO8cjHb32YsS2C
EL2oRcBvJ0SQ9kmYaZ4Pb08xlbTTWbNtPJDj58w4S5Xs2PFlbJr/Ibe3DZM7nYym
zfeCZDzlkLcSCpEaFCMdeuZSpmdSrRaJ9gquR+Ix3uYrqKNmd6eVq+yr1F5DXu9e
c6Ny6Ira8ylf96JLLRfh3b5G4w==
-----END CERTIFICATE-----

指纹值：Zv2OGTzr7Nq7L2ijjjIY8ZSUIi9fVvxFtjmO7gYU0CY

英文:

I'm working on a module where I'm evaluating method to add thumbprint (x5t#256) value to all public keys present in the DB. Conversion using javascript libraries, java libraries is doable. however trying to convert the same using pgcrypto doesn't seem to feasible as a part of sql migration script. are there any postgres libraries which could help me perform the same operation.

This the shell command i run on a public key to generate a fingerprint and trying to port to sql. (the same is as easy as loading certificate to an object in java or JavaScript and they have methods to output fingerprint)

echo $(openssl x509 -in public_key.pem -fingerprint -noout -sha256) | sed &#39;s/SHA256 Fingerprint=//g&#39; | sed &#39;s/://g&#39; | xxd -r -p | openssl enc -a | tr -d &#39;=&#39; | tr &#39;/+&#39; &#39;_-&#39;

Certificate:
-----BEGIN CERTIFICATE-----
MIICrzCCAZegAwIBAgIGAYk++jfeMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMT
CE1vY2stSURBMB4XDTIzMDcxMDAzMTUzM1oXDTIzMDcyMDAzMTUzM1owHjEcMBoG
A1UEAxMTU2lkZGhhcnRoIEsgTWFuc291cjCCASIwDQYJKoZIhvcNAQEBBQADggEP
ADCCAQoCggEBAJy7TzHJJNkjlnSi87fkUr8NMM9k3UIkoAtAqiH7J4uPG1wcdgQK
luX1wfhsed7TUnblrZCZXOaxqT2kN1uniC28bekQPkWs/e0Mm8s3r7ncxyTtCMlS
kSlg6ZFN3bV2m3x893vFx81yOGk534Jc9O9qxouxB7WMHn8ynM9BE8k0VaNXyj2/
z0E7IXqpei4UDNdTU0avmqYGjw/YTsTdlrwQebwn9clwVvld2ZFV4jdgErTqLJ/Y
u7wIZmYzL3ib5kf2+tVZhY/MnqsT0Bx+TFatnd2Aout5/Hs2V2HdwSBY6ET6SXVT
NXKDtH3Sw6AyNPj+jo6l5IARsuOvWioTrfsCAwEAATANBgkqhkiG9w0BAQsFAAOC
AQEAgOtPRuk9IyrRGOFWyFlwJdqZxqVO+78UAJKJmBiko6xxeezkYqqiAuwcyWFj
XWmvvcwlTdCyfEnGWRi74r4ma7u0h5O4U3AJxPF0/BKklCF9nabRqtSC9ENPKHpf
/MAsZF/dQkzQ+k8oqCVKgg/OpgmLGg1dBFvBUOsSUtzp2Mv3GhQO8cjHb32YsS2C
EL2oRcBvJ0SQ9kmYaZ4Pb08xlbTTWbNtPJDj58w4S5Xs2PFlbJr/Ibe3DZM7nYym
zfeCZDzlkLcSCpEaFCMdeuZSpmdSrRaJ9gquR+Ix3uYrqKNmd6eVq+yr1F5DXu9e
c6Ny6Ira8ylf96JLLRfh3b5G4w==
-----END CERTIFICATE-----

thumbprint value: Zv2OGTzr7Nq7L2ijjjIY8ZSUIi9fVvxFtjmO7gYU0CY

答案1

得分: 1

不要让行话困扰你。

除了少数不可否认的天才之外，加密/安全领域是中等智力者的游戏，他们智力有限，但有强烈的动机来威胁外部人员，以掩盖他们通过将同样的四五个构建模块链接成一堆半成熟建筑物来维持生计的事实。

这有点像玩乐高的孩子可能不会在他们的手臂/手/脸上留下锐利的构造套件的锋利边缘的童年伤疤，但他们在现实生活中建造的东西往往因设计不当而倒塌。

要计算指纹：

将证书从base64转换为二进制
获取二进制证书的sha256摘要
将二进制sha256摘要转换为base64
去除末尾的=字符

with invar as (
  select '-----BEGIN CERTIFICATE-----
MIICrzCCAZegAwIBAgIGAYk++jfeMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMT
CE1vY2stSURBMB4XDTIzMDcxMDAzMTUzM1oXDTIzMDcyMDAzMTUzM1owHjEcMBoG
A1UEAxMTU2lkZGhhcnRoIEsgTWFuc291cjCCASIwDQYJKoZIhvcNAQEBBQADggEP
ADCCAQoCggEBAJy7TzHJJNkjlnSi87fkUr8NMM9k3UIkoAtAqiH7J4uPG1wcdgQK
luX1wfhsed7TUnblrZCZXOaxqT2kN1uniC28bekQPkWs/e0Mm8s3r7ncxyTtCMlS
kSlg6ZFN3bV2m3x893vFx81yOGk534Jc9O9qxouxB7WMHn8ynM9BE8k0VaNXyj2/
z0E7IXqpei4UDNdTU0avmqYGjw/YTsTdlrwQebwn9clwVvld2ZFV4jdgErTqLJ/Y
u7wIZmYzL3ib5kf2+tVZhY/MnqsT0Bx+TFatnd2Aout5/Hs2V2HdwSBY6ET6SXVT
NXKDtH3Sw6AyNPj+jo6l5IARsuOvWioTrfsCAwEAATANBgkqhkiG9w0BAQsFAAOC
AQEAgOtPRuk9IyrRGOFWyFlwJdqZxqVO+78UAJKJmBiko6xxeezkYqqiAuwcyWFj
XWmvvcwlTdCyfEnGWRi74r4ma7u0h5O4U3AJxPF0/BKklCF9nabRqtSC9ENPKHpf
/MAsZF/dQkzQ+k8oqCVKgg/OpgmLGg1dBFvBUOsSUtzp2Mv3GhQO8cjHb32YsS2C
EL2oRcBvJ0SQ9kmYaZ4Pb08xlbTTWbNtPJDj58w4S5Xs2PFlbJr/Ibe3DZM7nYym
zfeCZDzlkLcSCpEaFCMdeuZSpmdSrRaJ9gquR+Ix3uYrqKNmd6eVq+yr1F5DXu9e
c6Ny6Ira8ylf96JLLRfh3b5G4w==
-----END CERTIFICATE--' as cert
)
select rtrim(          -- 处理末尾的 = 符号
         encode(                -- sha256 哈希的 base64
           digest(              -- 创建 sha256 哈希
             decode(            -- 从 base64 获取证书字节
               regexp_replace(  -- 删除 BEGIN 和 END 行
                 cert, '(-.*?-)', '', 'g'
               ),
               'base64'
             ),
             'sha256'
           ),
           'base64'
         ),
         '='
       )
  from invar;

在 fiddle 上可行。

英文:

Do not let the jargon confuse you.

Outside of a small handful of undeniable geniuses, crypto/security is a game for midwits crouching under a low intellectual ceiling with a major incentive to intimidate outsiders to hide the fact that they put food on the table by chaining the same four or five building blocks into a dizzying array of half-baked edifices.

It's kind of like how kids who played with legos may not have childhood scars on their arms/hands/face from the sharp edges of erector sets, but things they put up in real life tend to collapse due to incompetent design.

To calculate the thumbprint:

Make the cert binary from base64
Get the sha256 digest of the binary cert
Make the binary sha256 digest base64
Get rid of trailing = characters

with invar as (
  select &#39;-----BEGIN CERTIFICATE-----
MIICrzCCAZegAwIBAgIGAYk++jfeMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMT
CE1vY2stSURBMB4XDTIzMDcxMDAzMTUzM1oXDTIzMDcyMDAzMTUzM1owHjEcMBoG
A1UEAxMTU2lkZGhhcnRoIEsgTWFuc291cjCCASIwDQYJKoZIhvcNAQEBBQADggEP
ADCCAQoCggEBAJy7TzHJJNkjlnSi87fkUr8NMM9k3UIkoAtAqiH7J4uPG1wcdgQK
luX1wfhsed7TUnblrZCZXOaxqT2kN1uniC28bekQPkWs/e0Mm8s3r7ncxyTtCMlS
kSlg6ZFN3bV2m3x893vFx81yOGk534Jc9O9qxouxB7WMHn8ynM9BE8k0VaNXyj2/
z0E7IXqpei4UDNdTU0avmqYGjw/YTsTdlrwQebwn9clwVvld2ZFV4jdgErTqLJ/Y
u7wIZmYzL3ib5kf2+tVZhY/MnqsT0Bx+TFatnd2Aout5/Hs2V2HdwSBY6ET6SXVT
NXKDtH3Sw6AyNPj+jo6l5IARsuOvWioTrfsCAwEAATANBgkqhkiG9w0BAQsFAAOC
AQEAgOtPRuk9IyrRGOFWyFlwJdqZxqVO+78UAJKJmBiko6xxeezkYqqiAuwcyWFj
XWmvvcwlTdCyfEnGWRi74r4ma7u0h5O4U3AJxPF0/BKklCF9nabRqtSC9ENPKHpf
/MAsZF/dQkzQ+k8oqCVKgg/OpgmLGg1dBFvBUOsSUtzp2Mv3GhQO8cjHb32YsS2C
EL2oRcBvJ0SQ9kmYaZ4Pb08xlbTTWbNtPJDj58w4S5Xs2PFlbJr/Ibe3DZM7nYym
zfeCZDzlkLcSCpEaFCMdeuZSpmdSrRaJ9gquR+Ix3uYrqKNmd6eVq+yr1F5DXu9e
c6Ny6Ira8ylf96JLLRfh3b5G4w==
-----END CERTIFICATE-----&#39; as cert
)
select rtrim(          -- deal with trailing = signs
         encode(                -- base64 of sha256 hash
           digest(              -- create sha256 hash
             decode(            -- get cert bytes from base64
               regexp_replace(  -- remove BEGIN and END lines
                 cert, &#39;(-.*?-)&#39;, &#39;&#39;, &#39;g&#39;
               ),
               &#39;base64&#39;
             ),
             &#39;sha256&#39;
           ),
           &#39;base64&#39;
         ),
         &#39;=&#39;
       )
  from invar;

Working fiddle

答案2

得分: 1

这是来自https://stackoverflow.com/users/13808319/mike-organek的回答的更新查询，还处理了将'+'替换为'-'的其他情况：

with invar as (
  select '-----BEGIN CERTIFICATE-----
MIIDYjCCAkqgAwIBAgIITiw01WwRmtUwDQYJKoZIhvcNAQELBQAwgYIxCzAJBgNV
BAYTAklOMQswCQYDVQQIDAJLQTESMBAGA1UEBwwJQkFOR0FMT1JFMQ0wCwYDVQQK
DARJSVRCMSwwKgYDVQQLDCNNT1NJUC1URUNILUNFTlRFUiAoSURBX0tFWV9CSU5E
SU5HKTEVMBMGA1UEAwwMd3d3Lm1vc2lwLmlvMB4XDTIzMDcyNjAxMDE0OVoXDTIz
MTAyNDAxMDE0OVowGzEZMBcGA1UEAwwQVEVTVF9GVUxMTkFNRWVuZzCCASIwDQYJ
KoZIhvcNAQEBBQADggEPADCCAQoCggEBAJWoc2F0O08cc3BIrZEF6EPuJ3Sr1FEI
zTvtiQB9+zcDTp3m74quiVi9IvrncsjovMT0pDXS6nKVdfb6b8Z3tTX4LdfSp5nx
7LHGZB2L3fOzbVBRQmgnoIuUY0xH8pb9F6TUJyl7HmWU1Crd2XGnAdWuNmhauli8
Lz3zbzzlYt9RZlzEnPkXaASf+SC+Nm6YfnzYvv/lCAOEt7t3fS95fdq4Zkur46bt
PZvqZ4xIbxnUYWUEW5q7DeYZ48O71rdOkNQ8+nHbfwzBEe3f/FYKZsMVaRAh/UdT
wNPScBS8oHujob54TJBED7jVPiQytKg06KqI6NfD7NAN7VAS87h/W4cCAwEAAaNC
MEAwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUVAQRCWo8XYp6cDRWcUZ1tgFf
xVQwDgYDVR0PAQH/BAQDAgKEMA0GCSqGSIb3DQEBCwUAA4IBAQB+9nlWZEio17gL
BCeSGEn4UoD7SKBaycupV9AFd/zcUYrDwDdvzuvqdhgZtMGfjXan1eRrZyF4uIzg
olKOMwCu/lwFIRQlvoKZVeZgHTLgZQoqBWZafIkuv08PNvsoy7V8J28TbWD5gunH
Faxyx2x8fD7NMH9GYah+ZROkQSfa93KClHtmDEuu1KuurgRnICAHaKYZgxrrw390
a/j3WRLnD9Ytbcqk0+bL229s20UU32SfuL0HS0Vt8akz9mgnX7H0FJF1Iamp0cCz
01pm27o1cGycmT1Q/YLZOh5MjQ7AAS3vW4AtL149JFIS7D12jDO455CGe3BnSDvJ
ZZrTMS/c
-----END CERTIFICATE--' as cert
)
select regexp_replace(  -- 处理'+'，将它们替换为'-'
        rtrim(          -- 处理尾部的=号
         encode(                -- sha256哈希的base64
           digest(              -- 创建sha256哈希
             decode(            -- 从base64获取证书字节
               regexp_replace(  -- 删除BEGIN和END行
                 cert, '(-.*?-)', '', 'g'
               ),
               'base64'
             ),
             'sha256'
           ),
           'base64'
         ),
         '='
       ),'\+','-','g')
       
  from invar;

https://dbfiddle.uk/fagfIT3E

英文:

Here's the update query from https://stackoverflow.com/users/13808319/mike-organek 's response which also handles additional case of replacing '+' with '-'

with invar as (
  select &#39;-----BEGIN CERTIFICATE-----
MIIDYjCCAkqgAwIBAgIITiw01WwRmtUwDQYJKoZIhvcNAQELBQAwgYIxCzAJBgNV
BAYTAklOMQswCQYDVQQIDAJLQTESMBAGA1UEBwwJQkFOR0FMT1JFMQ0wCwYDVQQK
DARJSVRCMSwwKgYDVQQLDCNNT1NJUC1URUNILUNFTlRFUiAoSURBX0tFWV9CSU5E
SU5HKTEVMBMGA1UEAwwMd3d3Lm1vc2lwLmlvMB4XDTIzMDcyNjAxMDE0OVoXDTIz
MTAyNDAxMDE0OVowGzEZMBcGA1UEAwwQVEVTVF9GVUxMTkFNRWVuZzCCASIwDQYJ
KoZIhvcNAQEBBQADggEPADCCAQoCggEBAJWoc2F0O08cc3BIrZEF6EPuJ3Sr1FEI
zTvtiQB9+zcDTp3m74quiVi9IvrncsjovMT0pDXS6nKVdfb6b8Z3tTX4LdfSp5nx
7LHGZB2L3fOzbVBRQmgnoIuUY0xH8pb9F6TUJyl7HmWU1Crd2XGnAdWuNmhauli8
Lz3zbzzlYt9RZlzEnPkXaASf+SC+Nm6YfnzYvv/lCAOEt7t3fS95fdq4Zkur46bt
PZvqZ4xIbxnUYWUEW5q7DeYZ48O71rdOkNQ8+nHbfwzBEe3f/FYKZsMVaRAh/UdT
wNPScBS8oHujob54TJBED7jVPiQytKg06KqI6NfD7NAN7VAS87h/W4cCAwEAAaNC
MEAwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUVAQRCWo8XYp6cDRWcUZ1tgFf
xVQwDgYDVR0PAQH/BAQDAgKEMA0GCSqGSIb3DQEBCwUAA4IBAQB+9nlWZEio17gL
BCeSGEn4UoD7SKBaycupV9AFd/zcUYrDwDdvzuvqdhgZtMGfjXan1eRrZyF4uIzg
olKOMwCu/lwFIRQlvoKZVeZgHTLgZQoqBWZafIkuv08PNvsoy7V8J28TbWD5gunH
Faxyx2x8fD7NMH9GYah+ZROkQSfa93KClHtmDEuu1KuurgRnICAHaKYZgxrrw390
a/j3WRLnD9Ytbcqk0+bL229s20UU32SfuL0HS0Vt8akz9mgnX7H0FJF1Iamp0cCz
01pm27o1cGycmT1Q/YLZOh5MjQ7AAS3vW4AtL149JFIS7D12jDO455CGe3BnSDvJ
ZZrTMS/c
-----END CERTIFICATE-----&#39; as cert
)
select regexp_replace(  --deal with &#39;+&#39;, replace them with &#39;-&#39;
        rtrim(          -- deal with trailing = signs
         encode(                -- base64 of sha256 hash
           digest(              -- create sha256 hash
             decode(            -- get cert bytes from base64
               regexp_replace(  -- remove BEGIN and END lines
                 cert, &#39;(-.*?-)&#39;, &#39;&#39;, &#39;g&#39;
               ),
               &#39;base64&#39;
             ),
             &#39;sha256&#39;
           ),
           &#39;base64&#39;
         ),
         &#39;=&#39;
       ),&#39;\+&#39;,&#39;-&#39;,&#39;g&#39;)
       
  from invar;

https://dbfiddle.uk/fagfIT3E

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

使用pgcrypto库计算在postgres数据库中存在的公共证书的x5t#s256指纹。

问题

答案1

答案2

GO语言中，如何将有效的签名验证为无效的签名？

使用github.com/go-pg/pg库，通过连接两个表创建更新查询。

Hood “config.json” 值

将多个表连接到数组列上

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论