We often need to store structured binary data in our database – images, pdf documents, etc., but also have a need to search by, or index on, attributes of that data. E.g., we might store the height and width of an image, or the OCR text from a PDF document (for full text searches).
The normal solution is to store the object as a BLOB, programmatically extract the required information, and store it as additional columns. In nearly all cases that’s easy to do and good enough to solve our problem.
Release the kraken!
That’s “nearly” all cases. There are times when we need to exercise more care. Times when kraken, I mean lawyers, might be involved. Times when you might be asked if you took all reasonable steps to ensure that the data was not illicitly modified without you realizing it.
In these cases it’s not enough to just cache a value. We need to enlist the database to help us.
Threat model
There are two threats. First, the contents of the BLOB could be modified while all extracted values are left unchanged. This would mean that extra matches are found during searches. On the bright side we can verify the results actually matched and be alerted there’s a problem.
The second threat is more subtle. Extra results for some queries will be matched by fewer results in other queries. This means we can make records “disappear” even if there are measures in place to detect or prevent the deletion of records.
As a concrete example the first threat could be that an arrest record summary is changed from a felony to a misdemeanor. The second threat is that the search for prior arrests comes up empty.
X.509 digital certificates
For the rest of this article I’ll use X.509 digital certificates as a specific example. There are three reasons for this. First, they’re non-trivial and require a C-language extension to implement the user-defined functions and types – these are the types of things that are usually handled entirely at the application level. Second, they’re a real world example of the need to search and index values by cached values since it’s too expensive to recalculate them for each use. Finally, and not least, I’m familiar with them.
This article uses my PostgreSQL cert extension. This is a highly unstable extension (hence the 0.1.0 version as I write this) but it contains the minimum functions to store a digital certificate as a ‘cert’ type and to extract useful information from it. The details are not important – we just need an example of extracting “interesting” values from a BLOB. If this is distracting just replace any “cert” with a “blob” in the examples below.
Hardening the database
The database almost certainly does not have the functions we need to extract information from our BLOB. If it did it would probably be a first-class type, not a BLOB. This means we have to define our own functions. We might even define our own user-defined type (UDT) so we have some measure of type safety since we can put object validation into the methods that convert the object between internal and external formats.
Constraints
We can start by adding constraints on our table that ensure the cached values match the computed values.
--
-- Create a table containing digital certificates and cached values.
--
create table certs (
cert cert,
serial_number bignum,
not_before timestamp,
not_after timestamp,
issuer text,
subject text,
-- define constraints
CONSTRAINT cert_serial CHECK (serial_number = get_serial_number(cert)),
CONSTRAINT cert_not_before CHECK (not_before = get_not_before(cert)),
CONSTRAINT cert_not_after CHECK (not_after = get_not_after(cert)),
CONSTRAINT cert_issuer CHECK (issuer = get_issuer(cert)),
CONSTRAINT cert_subject CHECK (subject = get_subject(cert))
);The constraints make it impossible to insert or update invalid cached values. This gives us false confidence though – if an attacker has sufficient privileges it is possible to drop constraints, insert bad data, and then restore the constraints. This works since existing rows are not checked when constraints are added.
A good countermeasure to this is to periodically validate the constraints.
ALTER TABLE certs VALIDATE CONSTRAINT check_serial; ALTER TABLE certs VALIDATE CONSTRAINT check_not_before; ALTER TABLE certs VALIDATE CONSTRAINT check_not_after; ALTER TABLE certs VALIDATE CONSTRAINT check_subject; ALTER TABLE certs VALIDATE CONSTRAINT check_issuer;
Triggers
Another drawback to using constraints is that you must determine the correct values to insert. This either requires maintaining code in two places – the database and the application or writing much more complex INSERT and UPDATE statements. Fortunately it is easy write a trigger that sets the fields to the correct value upon any INSERT or UPDATE.
--
-- Create a stored procedure that sets several columns by calling the
-- appropriate function instead of accepting the value provided to the
-- INSERT or UPDATE call.
--
CREATE FUNCTION cert_trigger_proc() RETURNS trigger AS $$
BEGIN
NEW.serial_number = get_serial_number(NEW.cert);
NEW.not_before = get_not_before(NEW.cert);
NEW.not_after = get_not_after(NEW.cert);
NEW.issuer = get_issuer(NEW.cert);
NEW.subject = get_subject(NEW.cert);
RETURN NEW;
END
$$ LANGUAGE plpgsql;
--
-- Define a trigger on the 'certs' table that will be called whenever
-- a row is inserted or updated.
--
CREATE TRIGGER cert_trigger BEFORE INSERT OR UPDATE ON certs
FOR EACH ROW EXECUTE PROCEDURE cert_trigger_proc();Example
Here is an example of the results of inserting two values with a trigger in place.
insert into certs values (
cert('-----BEGIN CERTIFICATE-----
MIIDEzCCAnygAwIBAgIBATANBgkqhkiG9w0BAQQFADCBxDELMAkGA1UEBhMCWkEx
FTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYD
VQQKExRUaGF3dGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlv
biBTZXJ2aWNlcyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEm
MCQGCSqGSIb3DQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wHhcNOTYwODAx
MDAwMDAwWhcNMjAxMjMxMjM1OTU5WjCBxDELMAkGA1UEBhMCWkExFTATBgNVBAgT
DFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYDVQQKExRUaGF3
dGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlvbiBTZXJ2aWNl
cyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEmMCQGCSqGSIb3
DQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wgZ8wDQYJKoZIhvcNAQEBBQAD
gY0AMIGJAoGBANOkUG7I/1Zr5s9dtuoMaHVHoqrC2oQl/Kj0R1HahbUgdJSGHg91
yekIYfUGbTBuFRkC6VLAYttNmZ7iagxEOM3+vuNkCXDF/rFrKbYvScg71CcEJRCX
L+eQbcAoQpnXTEPew/UhbVSfXcNY4cDk2VuwuNy0e982OsK1ZiIS1ocNAgMBAAGj
EzARMA8GA1UdEwEB/wQFMAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAB/pMaVz7lcxG
7oWDTSEwjsrZqG9JGubaUeNgcGyEYRGhGshIPllDfU+VPaGLtwtimHp1it2ITk6e
QNuozDJ0uW8NxuOzRAvZim+aKZuZGCg70eNAKJpaPNW15yAbi8qkq43pUdniTCxZ
qdq5snUb9kLy78fyGPmJvKP/iiMucEc=
-----END CERTIFICATE-----')),
(cert('-----BEGIN CERTIFICATE-----
MIICPDCCAaUCED9pHoGc8JpK83P/uUii5N0wDQYJKoZIhvcNAQEFBQAwXzELMAkG
A1UEBhMCVVMxFzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFz
cyAxIFB1YmxpYyBQcmltYXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTk2
MDEyOTAwMDAwMFoXDTI4MDgwMjIzNTk1OVowXzELMAkGA1UEBhMCVVMxFzAVBgNV
BAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFzcyAxIFB1YmxpYyBQcmlt
YXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MIGfMA0GCSqGSIb3DQEBAQUAA4GN
ADCBiQKBgQDlGb9to1ZhLZlIcfZn3rmN67eehoAKkQ76OCWvRoiC5XOooJskXQ0f
zGVuDLDQVoQYh5oGmxChc9+0WDlrbsH2FdWoqD+qEgaNMax/sDTXjzRniAnNFBHi
TkVWaR94AoDa3EeRKbs2yWNcxeDXLYd7obcysHswuiovMaruo2fa2wIDAQABMA0G
CSqGSIb3DQEBBQUAA4GBAFgVKTk8d6PaXCUDfGD67gmZPCcQcMgMCeazh88K4hiW
NWLMv5sneYlfycQJ9M61Hd8qveXbhpxoJeUwfLaJFf5n0a3hUKw8fGJLj7qE1xIV
Gx/KXQ/BUpQqEZnae88MNhPVNdwQGVnqlMEAv3WP2fr9dgTbYruQagPZRjXZ+Hxb
-----END CERTIFICATE-----'));
select * from certs;
cert | serial_number | not_before | not_after | issuer | subject
------------------------------------------------------------------+----------------------------------------+--------------------------+--------------------------+------------------------+------------------------
-----BEGIN CERTIFICATE----- +| 1 | Thu Aug 01 00:00:00 1996 | Thu Dec 31 23:59:59 2020 | C=ZA/ST=Western Cape/L | C=ZA/ST=Western Cape/L
MIIDEzCCAnygAwIBAgIBATANBgkqhkiG9w0BAQQFADCBxDELMAkGA1UEBhMCWkEx+| | | | |
FTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYD+| | | | |
VQQKExRUaGF3dGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlv+| | | | |
biBTZXJ2aWNlcyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEm+| | | | |
MCQGCSqGSIb3DQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wHhcNOTYwODAx+| | | | |
MDAwMDAwWhcNMjAxMjMxMjM1OTU5WjCBxDELMAkGA1UEBhMCWkExFTATBgNVBAgT+| | | | |
DFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYDVQQKExRUaGF3+| | | | |
dGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlvbiBTZXJ2aWNl+| | | | |
cyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEmMCQGCSqGSIb3+| | | | |
DQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wgZ8wDQYJKoZIhvcNAQEBBQAD+| | | | |
gY0AMIGJAoGBANOkUG7I/1Zr5s9dtuoMaHVHoqrC2oQl/Kj0R1HahbUgdJSGHg91+| | | | |
yekIYfUGbTBuFRkC6VLAYttNmZ7iagxEOM3+vuNkCXDF/rFrKbYvScg71CcEJRCX+| | | | |
L+eQbcAoQpnXTEPew/UhbVSfXcNY4cDk2VuwuNy0e982OsK1ZiIS1ocNAgMBAAGj+| | | | |
EzARMA8GA1UdEwEB/wQFMAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAB/pMaVz7lcxG+| | | | |
7oWDTSEwjsrZqG9JGubaUeNgcGyEYRGhGshIPllDfU+VPaGLtwtimHp1it2ITk6e+| | | | |
QNuozDJ0uW8NxuOzRAvZim+aKZuZGCg70eNAKJpaPNW15yAbi8qkq43pUdniTCxZ+| | | | |
qdq5snUb9kLy78fyGPmJvKP/iiMucEc= +| | | | |
-----END CERTIFICATE----- +| | | | |
| | | | |
-----BEGIN CERTIFICATE----- +| 84287173645887463140025226144593929437 | Mon Jan 29 00:00:00 1996 | Wed Aug 02 23:59:59 2028 | C=US/O=VeriSign, Inc./ | C=US/O=VeriSign, Inc./
MIICPDCCAaUCED9pHoGc8JpK83P/uUii5N0wDQYJKoZIhvcNAQEFBQAwXzELMAkG+| | | | |
A1UEBhMCVVMxFzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFz+| | | | |
cyAxIFB1YmxpYyBQcmltYXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTk2+| | | | |
MDEyOTAwMDAwMFoXDTI4MDgwMjIzNTk1OVowXzELMAkGA1UEBhMCVVMxFzAVBgNV+| | | | |
BAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFzcyAxIFB1YmxpYyBQcmlt+| | | | |
YXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MIGfMA0GCSqGSIb3DQEBAQUAA4GN+| | | | |
ADCBiQKBgQDlGb9to1ZhLZlIcfZn3rmN67eehoAKkQ76OCWvRoiC5XOooJskXQ0f+| | | | |
zGVuDLDQVoQYh5oGmxChc9+0WDlrbsH2FdWoqD+qEgaNMax/sDTXjzRniAnNFBHi+| | | | |
TkVWaR94AoDa3EeRKbs2yWNcxeDXLYd7obcysHswuiovMaruo2fa2wIDAQABMA0G+| | | | |
CSqGSIb3DQEBBQUAA4GBAFgVKTk8d6PaXCUDfGD67gmZPCcQcMgMCeazh88K4hiW+| | | | |
NWLMv5sneYlfycQJ9M61Hd8qveXbhpxoJeUwfLaJFf5n0a3hUKw8fGJLj7qE1xIV+| | | | |
Gx/KXQ/BUpQqEZnae88MNhPVNdwQGVnqlMEAv3WP2fr9dgTbYruQagPZRjXZ+Hxb+| | | | |
-----END CERTIFICATE----- +| | | | |
| | | | |
(2 rows)The truncated ‘issuer’ and ‘subject’ fields are a known bug. (Version 0.1.0, remember?) It highlights a key point though – this is not a magic bullet and a buggy function may still let things through. Modifying the functions used to create cached values means you’ll need to drop the constraints, reinitialize all cached values, and then restore the constraints.
Database security
Finally I need to come back to the point of someone dropping a constraint and then reinstating it. None of this works without solid database security. A handful of rules will go a long way.
A dedicated database user should own the schema. This user should not be used for any other purpose – it should only be used when creating and modifying the schema.
No other user should have ALTER privileges. A dedicated user isn’t enough if other users can alter the schema anyway. The owner should be the only user with ALTER privileges by default but it would not be a bad idea to double-check.
Periodically audit the schema. This doesn’t have to be very sophisticated and can be done programmatically. Periodically query the metadata for the database and ensure tables and functions are owned by the correct users, that they have the correct privileges, that there are no unexpected tables in the schema, that there are no unexpected columns in the tables.
| Reference: | Storing X.509 Digital Certificates (And Other Messy Things) from our SCG partner Bear Giles at the Invariant Properties blog. |
