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

Question

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

score 1 · Answer 1 · answered Jul 27 '23 at 17:21

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 '-----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

Thanks, the query worked, tried for different certificate, it needed one correction to replace '+' with '-' here's the link to that https://dbfiddle.uk/fagfIT3E — Hitesh jain, Jul 28 '23 at 06:00

score 1 · Accepted Answer · answered Jul 28 '23 at 06:05

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

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

2 Answers2