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

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

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 's/SHA256 Fingerprint=//g' | sed 's/://g' | xxd -r -p | openssl enc -a | tr -d '=' | tr '/+' '_-'
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

不要让行话困扰你。

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

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

要计算指纹:

  1. 将证书从base64转换为二进制
  2. 获取二进制证书的sha256摘要
  3. 将二进制sha256摘要转换为base64
  4. 去除末尾的=字符
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:

  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
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(          -- 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, '(-.*?-)', '', 'g'
               ),
               'base64'
             ),
             'sha256'
           ),
           'base64'
         ),
         '='
       )
  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 '-----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(  --deal with '+', replace them with '-'
        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, '(-.*?-)', '', 'g'
               ),
               'base64'
             ),
             'sha256'
           ),
           'base64'
         ),
         '='
       ),'\+','-','g')
       
  from invar;

https://dbfiddle.uk/fagfIT3E

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

发表评论

匿名网友

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

确定