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

huangapple
go评论108阅读模式
英文:

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中加载证书并输出指纹的方法与此相同）

  1. 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)

  1. 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 '/+' '_-'
  1. Certificate:
  2. -----BEGIN CERTIFICATE-----
  3. MIICrzCCAZegAwIBAgIGAYk++jfeMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMT
  4. CE1vY2stSURBMB4XDTIzMDcxMDAzMTUzM1oXDTIzMDcyMDAzMTUzM1owHjEcMBoG
  5. A1UEAxMTU2lkZGhhcnRoIEsgTWFuc291cjCCASIwDQYJKoZIhvcNAQEBBQADggEP
  6. ADCCAQoCggEBAJy7TzHJJNkjlnSi87fkUr8NMM9k3UIkoAtAqiH7J4uPG1wcdgQK
  7. luX1wfhsed7TUnblrZCZXOaxqT2kN1uniC28bekQPkWs/e0Mm8s3r7ncxyTtCMlS
  8. kSlg6ZFN3bV2m3x893vFx81yOGk534Jc9O9qxouxB7WMHn8ynM9BE8k0VaNXyj2/
  9. z0E7IXqpei4UDNdTU0avmqYGjw/YTsTdlrwQebwn9clwVvld2ZFV4jdgErTqLJ/Y
  10. u7wIZmYzL3ib5kf2+tVZhY/MnqsT0Bx+TFatnd2Aout5/Hs2V2HdwSBY6ET6SXVT
  11. NXKDtH3Sw6AyNPj+jo6l5IARsuOvWioTrfsCAwEAATANBgkqhkiG9w0BAQsFAAOC
  12. AQEAgOtPRuk9IyrRGOFWyFlwJdqZxqVO+78UAJKJmBiko6xxeezkYqqiAuwcyWFj
  13. XWmvvcwlTdCyfEnGWRi74r4ma7u0h5O4U3AJxPF0/BKklCF9nabRqtSC9ENPKHpf
  14. /MAsZF/dQkzQ+k8oqCVKgg/OpgmLGg1dBFvBUOsSUtzp2Mv3GhQO8cjHb32YsS2C
  15. EL2oRcBvJ0SQ9kmYaZ4Pb08xlbTTWbNtPJDj58w4S5Xs2PFlbJr/Ibe3DZM7nYym
  16. zfeCZDzlkLcSCpEaFCMdeuZSpmdSrRaJ9gquR+Ix3uYrqKNmd6eVq+yr1F5DXu9e
  17. c6Ny6Ira8ylf96JLLRfh3b5G4w==
  18. -----END CERTIFICATE-----
  1. thumbprint value: Zv2OGTzr7Nq7L2ijjjIY8ZSUIi9fVvxFtjmO7gYU0CY

答案1

得分: 1

不要让行话困扰你。

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

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

要计算指纹：

  1. 将证书从base64转换为二进制
  2. 获取二进制证书的sha256摘要
  3. 将二进制sha256摘要转换为base64
  4. 去除末尾的=字符
  1. with invar as (
  2.   select '-----BEGIN CERTIFICATE-----
  3. MIICrzCCAZegAwIBAgIGAYk++jfeMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMT
  4. CE1vY2stSURBMB4XDTIzMDcxMDAzMTUzM1oXDTIzMDcyMDAzMTUzM1owHjEcMBoG
  5. A1UEAxMTU2lkZGhhcnRoIEsgTWFuc291cjCCASIwDQYJKoZIhvcNAQEBBQADggEP
  6. ADCCAQoCggEBAJy7TzHJJNkjlnSi87fkUr8NMM9k3UIkoAtAqiH7J4uPG1wcdgQK
  7. luX1wfhsed7TUnblrZCZXOaxqT2kN1uniC28bekQPkWs/e0Mm8s3r7ncxyTtCMlS
  8. kSlg6ZFN3bV2m3x893vFx81yOGk534Jc9O9qxouxB7WMHn8ynM9BE8k0VaNXyj2/
  9. z0E7IXqpei4UDNdTU0avmqYGjw/YTsTdlrwQebwn9clwVvld2ZFV4jdgErTqLJ/Y
  10. u7wIZmYzL3ib5kf2+tVZhY/MnqsT0Bx+TFatnd2Aout5/Hs2V2HdwSBY6ET6SXVT
  11. NXKDtH3Sw6AyNPj+jo6l5IARsuOvWioTrfsCAwEAATANBgkqhkiG9w0BAQsFAAOC
  12. AQEAgOtPRuk9IyrRGOFWyFlwJdqZxqVO+78UAJKJmBiko6xxeezkYqqiAuwcyWFj
  13. XWmvvcwlTdCyfEnGWRi74r4ma7u0h5O4U3AJxPF0/BKklCF9nabRqtSC9ENPKHpf
  14. /MAsZF/dQkzQ+k8oqCVKgg/OpgmLGg1dBFvBUOsSUtzp2Mv3GhQO8cjHb32YsS2C
  15. EL2oRcBvJ0SQ9kmYaZ4Pb08xlbTTWbNtPJDj58w4S5Xs2PFlbJr/Ibe3DZM7nYym
  16. zfeCZDzlkLcSCpEaFCMdeuZSpmdSrRaJ9gquR+Ix3uYrqKNmd6eVq+yr1F5DXu9e
  17. c6Ny6Ira8ylf96JLLRfh3b5G4w==
  18. -----END CERTIFICATE--' as cert
  19. )
  20. select rtrim(          -- 处理末尾的 = 符号
  21.          encode(                -- sha256 哈希的 base64
  22.            digest(              -- 创建 sha256 哈希
  23.              decode(            -- 从 base64 获取证书字节
  24.                regexp_replace(  -- 删除 BEGIN 和 END 行
  25.                  cert, '(-.*?-)', '', 'g'
  26.                ),
  27.                'base64'
  28.              ),
  29.              'sha256'
  30.            ),
  31.            'base64'
  32.          ),
  33.          '='
  34.        )
  35.   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:

  1. Make the cert binary from base64
  2. Get the sha256 digest of the binary cert
  3. Make the binary sha256 digest base64
  4. Get rid of trailing = characters
  1. with invar as (
  2.   select '-----BEGIN CERTIFICATE-----
  3. MIICrzCCAZegAwIBAgIGAYk++jfeMA0GCSqGSIb3DQEBCwUAMBMxETAPBgNVBAMT
  4. CE1vY2stSURBMB4XDTIzMDcxMDAzMTUzM1oXDTIzMDcyMDAzMTUzM1owHjEcMBoG
  5. A1UEAxMTU2lkZGhhcnRoIEsgTWFuc291cjCCASIwDQYJKoZIhvcNAQEBBQADggEP
  6. ADCCAQoCggEBAJy7TzHJJNkjlnSi87fkUr8NMM9k3UIkoAtAqiH7J4uPG1wcdgQK
  7. luX1wfhsed7TUnblrZCZXOaxqT2kN1uniC28bekQPkWs/e0Mm8s3r7ncxyTtCMlS
  8. kSlg6ZFN3bV2m3x893vFx81yOGk534Jc9O9qxouxB7WMHn8ynM9BE8k0VaNXyj2/
  9. z0E7IXqpei4UDNdTU0avmqYGjw/YTsTdlrwQebwn9clwVvld2ZFV4jdgErTqLJ/Y
  10. u7wIZmYzL3ib5kf2+tVZhY/MnqsT0Bx+TFatnd2Aout5/Hs2V2HdwSBY6ET6SXVT
  11. NXKDtH3Sw6AyNPj+jo6l5IARsuOvWioTrfsCAwEAATANBgkqhkiG9w0BAQsFAAOC
  12. AQEAgOtPRuk9IyrRGOFWyFlwJdqZxqVO+78UAJKJmBiko6xxeezkYqqiAuwcyWFj
  13. XWmvvcwlTdCyfEnGWRi74r4ma7u0h5O4U3AJxPF0/BKklCF9nabRqtSC9ENPKHpf
  14. /MAsZF/dQkzQ+k8oqCVKgg/OpgmLGg1dBFvBUOsSUtzp2Mv3GhQO8cjHb32YsS2C
  15. EL2oRcBvJ0SQ9kmYaZ4Pb08xlbTTWbNtPJDj58w4S5Xs2PFlbJr/Ibe3DZM7nYym
  16. zfeCZDzlkLcSCpEaFCMdeuZSpmdSrRaJ9gquR+Ix3uYrqKNmd6eVq+yr1F5DXu9e
  17. c6Ny6Ira8ylf96JLLRfh3b5G4w==
  18. -----END CERTIFICATE-----' as cert
  19. )
  20. select rtrim(          -- deal with trailing = signs
  21.          encode(                -- base64 of sha256 hash
  22.            digest(              -- create sha256 hash
  23.              decode(            -- get cert bytes from base64
  24.                regexp_replace(  -- remove BEGIN and END lines
  25.                  cert, '(-.*?-)', '', 'g'
  26.                ),
  27.                'base64'
  28.              ),
  29.              'sha256'
  30.            ),
  31.            'base64'
  32.          ),
  33.          '='
  34.        )
  35.   from invar;
  36.

Working fiddle

答案2

得分: 1

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

  1. with invar as (
  2.   select '-----BEGIN CERTIFICATE-----
  3. MIIDYjCCAkqgAwIBAgIITiw01WwRmtUwDQYJKoZIhvcNAQELBQAwgYIxCzAJBgNV
  4. BAYTAklOMQswCQYDVQQIDAJLQTESMBAGA1UEBwwJQkFOR0FMT1JFMQ0wCwYDVQQK
  5. DARJSVRCMSwwKgYDVQQLDCNNT1NJUC1URUNILUNFTlRFUiAoSURBX0tFWV9CSU5E
  6. SU5HKTEVMBMGA1UEAwwMd3d3Lm1vc2lwLmlvMB4XDTIzMDcyNjAxMDE0OVoXDTIz
  7. MTAyNDAxMDE0OVowGzEZMBcGA1UEAwwQVEVTVF9GVUxMTkFNRWVuZzCCASIwDQYJ
  8. KoZIhvcNAQEBBQADggEPADCCAQoCggEBAJWoc2F0O08cc3BIrZEF6EPuJ3Sr1FEI
  9. zTvtiQB9+zcDTp3m74quiVi9IvrncsjovMT0pDXS6nKVdfb6b8Z3tTX4LdfSp5nx
  10. 7LHGZB2L3fOzbVBRQmgnoIuUY0xH8pb9F6TUJyl7HmWU1Crd2XGnAdWuNmhauli8
  11. Lz3zbzzlYt9RZlzEnPkXaASf+SC+Nm6YfnzYvv/lCAOEt7t3fS95fdq4Zkur46bt
  12. PZvqZ4xIbxnUYWUEW5q7DeYZ48O71rdOkNQ8+nHbfwzBEe3f/FYKZsMVaRAh/UdT
  13. wNPScBS8oHujob54TJBED7jVPiQytKg06KqI6NfD7NAN7VAS87h/W4cCAwEAAaNC
  14. MEAwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUVAQRCWo8XYp6cDRWcUZ1tgFf
  15. xVQwDgYDVR0PAQH/BAQDAgKEMA0GCSqGSIb3DQEBCwUAA4IBAQB+9nlWZEio17gL
  16. BCeSGEn4UoD7SKBaycupV9AFd/zcUYrDwDdvzuvqdhgZtMGfjXan1eRrZyF4uIzg
  17. olKOMwCu/lwFIRQlvoKZVeZgHTLgZQoqBWZafIkuv08PNvsoy7V8J28TbWD5gunH
  18. Faxyx2x8fD7NMH9GYah+ZROkQSfa93KClHtmDEuu1KuurgRnICAHaKYZgxrrw390
  19. a/j3WRLnD9Ytbcqk0+bL229s20UU32SfuL0HS0Vt8akz9mgnX7H0FJF1Iamp0cCz
  20. 01pm27o1cGycmT1Q/YLZOh5MjQ7AAS3vW4AtL149JFIS7D12jDO455CGe3BnSDvJ
  21. ZZrTMS/c
  22. -----END CERTIFICATE--' as cert
  23. )
  24. select regexp_replace(  -- 处理'+'，将它们替换为'-'
  25.         rtrim(          -- 处理尾部的=号
  26.          encode(                -- sha256哈希的base64
  27.            digest(              -- 创建sha256哈希
  28.              decode(            -- 从base64获取证书字节
  29.                regexp_replace(  -- 删除BEGIN和END行
  30.                  cert, '(-.*?-)', '', 'g'
  31.                ),
  32.                'base64'
  33.              ),
  34.              'sha256'
  35.            ),
  36.            'base64'
  37.          ),
  38.          '='
  39.        ),'\+','-','g')
  40.        
  41.   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 '-'

  1. with invar as (
  2.   select '-----BEGIN CERTIFICATE-----
  3. MIIDYjCCAkqgAwIBAgIITiw01WwRmtUwDQYJKoZIhvcNAQELBQAwgYIxCzAJBgNV
  4. BAYTAklOMQswCQYDVQQIDAJLQTESMBAGA1UEBwwJQkFOR0FMT1JFMQ0wCwYDVQQK
  5. DARJSVRCMSwwKgYDVQQLDCNNT1NJUC1URUNILUNFTlRFUiAoSURBX0tFWV9CSU5E
  6. SU5HKTEVMBMGA1UEAwwMd3d3Lm1vc2lwLmlvMB4XDTIzMDcyNjAxMDE0OVoXDTIz
  7. MTAyNDAxMDE0OVowGzEZMBcGA1UEAwwQVEVTVF9GVUxMTkFNRWVuZzCCASIwDQYJ
  8. KoZIhvcNAQEBBQADggEPADCCAQoCggEBAJWoc2F0O08cc3BIrZEF6EPuJ3Sr1FEI
  9. zTvtiQB9+zcDTp3m74quiVi9IvrncsjovMT0pDXS6nKVdfb6b8Z3tTX4LdfSp5nx
  10. 7LHGZB2L3fOzbVBRQmgnoIuUY0xH8pb9F6TUJyl7HmWU1Crd2XGnAdWuNmhauli8
  11. Lz3zbzzlYt9RZlzEnPkXaASf+SC+Nm6YfnzYvv/lCAOEt7t3fS95fdq4Zkur46bt
  12. PZvqZ4xIbxnUYWUEW5q7DeYZ48O71rdOkNQ8+nHbfwzBEe3f/FYKZsMVaRAh/UdT
  13. wNPScBS8oHujob54TJBED7jVPiQytKg06KqI6NfD7NAN7VAS87h/W4cCAwEAAaNC
  14. MEAwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUVAQRCWo8XYp6cDRWcUZ1tgFf
  15. xVQwDgYDVR0PAQH/BAQDAgKEMA0GCSqGSIb3DQEBCwUAA4IBAQB+9nlWZEio17gL
  16. BCeSGEn4UoD7SKBaycupV9AFd/zcUYrDwDdvzuvqdhgZtMGfjXan1eRrZyF4uIzg
  17. olKOMwCu/lwFIRQlvoKZVeZgHTLgZQoqBWZafIkuv08PNvsoy7V8J28TbWD5gunH
  18. Faxyx2x8fD7NMH9GYah+ZROkQSfa93KClHtmDEuu1KuurgRnICAHaKYZgxrrw390
  19. a/j3WRLnD9Ytbcqk0+bL229s20UU32SfuL0HS0Vt8akz9mgnX7H0FJF1Iamp0cCz
  20. 01pm27o1cGycmT1Q/YLZOh5MjQ7AAS3vW4AtL149JFIS7D12jDO455CGe3BnSDvJ
  21. ZZrTMS/c
  22. -----END CERTIFICATE-----' as cert
  23. )
  24. select regexp_replace(  --deal with '+', replace them with '-'
  25.         rtrim(          -- deal with trailing = signs
  26.          encode(                -- base64 of sha256 hash
  27.            digest(              -- create sha256 hash
  28.              decode(            -- get cert bytes from base64
  29.                regexp_replace(  -- remove BEGIN and END lines
  30.                  cert, '(-.*?-)', '', 'g'
  31.                ),
  32.                'base64'
  33.              ),
  34.              'sha256'
  35.            ),
  36.            'base64'
  37.          ),
  38.          '='
  39.        ),'\+','-','g')
  40.        
  41.   from invar;

https://dbfiddle.uk/fagfIT3E

huangapple
  • 本文由 发表于 2023年7月28日 00:40:22
  • 转载请务必保留本文链接：https://go.coder-hub.com/76781835.html
  • cryptography
  • digital-signature
  • pgcrypto
  • postgresql
  • rsa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定