We often need to store structured binary data in our database \u2013 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).<\/p>\n
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\u2019s easy to do and good enough to solve our problem.<\/p>\n
That\u2019s \u201cnearly\u201d 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.<\/p>\n
In these cases it\u2019s not enough to just cache a value. We need to enlist the database to help us.<\/p>\n
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\u2019s a problem.<\/p>\n
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 \u201cdisappear\u201d even if there are measures in place to detect or prevent the deletion of records.<\/p>\n
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.<\/p>\n
For the rest of this article I\u2019ll use X.509 digital certificates as a specific example. There are three reasons for this. First, they\u2019re non-trivial and require a C-language extension to implement the user-defined functions and types \u2013 these are the types of things that are usually handled entirely at the application level. Second, they\u2019re a real world example of the need to search and index values by cached values since it\u2019s too expensive to recalculate them for each use. Finally, and not least, I\u2019m familiar with them.<\/p>\n
This article uses my PostgreSQL cert<\/a> 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 \u2018cert\u2019 type and to extract useful information from it. The details are not important \u2013 we just need an example of extracting \u201cinteresting\u201d values from a BLOB. If this is distracting just replace any \u201ccert\u201d with a \u201cblob\u201d in the examples below.<\/p>\n 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.<\/p>\n We can start by adding constraints on our table that ensure the cached values match the computed values.<\/p>\n The constraints make it impossible to insert or update invalid cached values. This gives us false confidence though \u2013 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.<\/p>\n A good countermeasure to this is to periodically validate the constraints.<\/p>\n Another drawback to using constraints is that you must determine the correct values to insert. This either requires maintaining code in two places \u2013 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.<\/p>\n Here is an example of the results of inserting two values with a trigger in place.<\/p>\n The truncated \u2018issuer\u2019 and \u2018subject\u2019 fields are a known bug. (Version 0.1.0, remember?) It highlights a key point though \u2013 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\u2019ll need to drop the constraints, reinitialize all cached values, and then restore the constraints.<\/p>\n 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.<\/p>\n A dedicated database user should own the schema<\/b>. This user should not be used for any other purpose \u2013 it should only be used when creating and modifying the schema.<\/p>\n No other user should have ALTER privileges<\/b>. A dedicated user isn\u2019t 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.<\/p>\n Periodically audit the schema<\/b>. This doesn\u2019t 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.<\/p>\nHardening the database<\/h2>\n
Constraints<\/h4>\n
--\r\n-- Create a table containing digital certificates and cached values.\r\n--\r\ncreate table certs (\r\n cert cert,\r\n serial_number bignum,\r\n not_before timestamp,\r\n not_after timestamp,\r\n issuer text,\r\n subject text,\r\n\r\n -- define constraints\r\n CONSTRAINT cert_serial CHECK (serial_number = get_serial_number(cert)),\r\n CONSTRAINT cert_not_before CHECK (not_before = get_not_before(cert)),\r\n CONSTRAINT cert_not_after CHECK (not_after = get_not_after(cert)),\r\n CONSTRAINT cert_issuer CHECK (issuer = get_issuer(cert)),\r\n CONSTRAINT cert_subject CHECK (subject = get_subject(cert))\r\n);<\/pre>\n
ALTER TABLE certs VALIDATE CONSTRAINT check_serial;\r\nALTER TABLE certs VALIDATE CONSTRAINT check_not_before;\r\nALTER TABLE certs VALIDATE CONSTRAINT check_not_after;\r\nALTER TABLE certs VALIDATE CONSTRAINT check_subject;\r\nALTER TABLE certs VALIDATE CONSTRAINT check_issuer;<\/pre>\n
Triggers<\/h4>\n
--\r\n-- Create a stored procedure that sets several columns by calling the\r\n-- appropriate function instead of accepting the value provided to the\r\n-- INSERT or UPDATE call.\r\n-- \r\nCREATE FUNCTION cert_trigger_proc() RETURNS trigger AS $$\r\nBEGIN\r\n NEW.serial_number = get_serial_number(NEW.cert);\r\n NEW.not_before = get_not_before(NEW.cert);\r\n NEW.not_after = get_not_after(NEW.cert);\r\n NEW.issuer = get_issuer(NEW.cert);\r\n NEW.subject = get_subject(NEW.cert);\r\n\r\n RETURN NEW;\r\nEND\r\n$$ LANGUAGE plpgsql;\r\n\r\n--\r\n-- Define a trigger on the 'certs' table that will be called whenever\r\n-- a row is inserted or updated.\r\n--\r\nCREATE TRIGGER cert_trigger BEFORE INSERT OR UPDATE ON certs\r\n FOR EACH ROW EXECUTE PROCEDURE cert_trigger_proc();<\/pre>\n
Example<\/h4>\n
insert into certs values (\r\ncert('-----BEGIN CERTIFICATE-----\r\nMIIDEzCCAnygAwIBAgIBATANBgkqhkiG9w0BAQQFADCBxDELMAkGA1UEBhMCWkEx\r\nFTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYD\r\nVQQKExRUaGF3dGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlv\r\nbiBTZXJ2aWNlcyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEm\r\nMCQGCSqGSIb3DQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wHhcNOTYwODAx\r\nMDAwMDAwWhcNMjAxMjMxMjM1OTU5WjCBxDELMAkGA1UEBhMCWkExFTATBgNVBAgT\r\nDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYDVQQKExRUaGF3\r\ndGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlvbiBTZXJ2aWNl\r\ncyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEmMCQGCSqGSIb3\r\nDQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wgZ8wDQYJKoZIhvcNAQEBBQAD\r\ngY0AMIGJAoGBANOkUG7I\/1Zr5s9dtuoMaHVHoqrC2oQl\/Kj0R1HahbUgdJSGHg91\r\nyekIYfUGbTBuFRkC6VLAYttNmZ7iagxEOM3+vuNkCXDF\/rFrKbYvScg71CcEJRCX\r\nL+eQbcAoQpnXTEPew\/UhbVSfXcNY4cDk2VuwuNy0e982OsK1ZiIS1ocNAgMBAAGj\r\nEzARMA8GA1UdEwEB\/wQFMAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAB\/pMaVz7lcxG\r\n7oWDTSEwjsrZqG9JGubaUeNgcGyEYRGhGshIPllDfU+VPaGLtwtimHp1it2ITk6e\r\nQNuozDJ0uW8NxuOzRAvZim+aKZuZGCg70eNAKJpaPNW15yAbi8qkq43pUdniTCxZ\r\nqdq5snUb9kLy78fyGPmJvKP\/iiMucEc=\r\n-----END CERTIFICATE-----')),\r\n(cert('-----BEGIN CERTIFICATE-----\r\nMIICPDCCAaUCED9pHoGc8JpK83P\/uUii5N0wDQYJKoZIhvcNAQEFBQAwXzELMAkG\r\nA1UEBhMCVVMxFzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFz\r\ncyAxIFB1YmxpYyBQcmltYXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTk2\r\nMDEyOTAwMDAwMFoXDTI4MDgwMjIzNTk1OVowXzELMAkGA1UEBhMCVVMxFzAVBgNV\r\nBAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFzcyAxIFB1YmxpYyBQcmlt\r\nYXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MIGfMA0GCSqGSIb3DQEBAQUAA4GN\r\nADCBiQKBgQDlGb9to1ZhLZlIcfZn3rmN67eehoAKkQ76OCWvRoiC5XOooJskXQ0f\r\nzGVuDLDQVoQYh5oGmxChc9+0WDlrbsH2FdWoqD+qEgaNMax\/sDTXjzRniAnNFBHi\r\nTkVWaR94AoDa3EeRKbs2yWNcxeDXLYd7obcysHswuiovMaruo2fa2wIDAQABMA0G\r\nCSqGSIb3DQEBBQUAA4GBAFgVKTk8d6PaXCUDfGD67gmZPCcQcMgMCeazh88K4hiW\r\nNWLMv5sneYlfycQJ9M61Hd8qveXbhpxoJeUwfLaJFf5n0a3hUKw8fGJLj7qE1xIV\r\nGx\/KXQ\/BUpQqEZnae88MNhPVNdwQGVnqlMEAv3WP2fr9dgTbYruQagPZRjXZ+Hxb\r\n-----END CERTIFICATE-----'));\r\nselect * from certs;\r\n cert | serial_number | not_before | not_after | issuer | subject \r\n------------------------------------------------------------------+----------------------------------------+--------------------------+--------------------------+------------------------+------------------------\r\n -----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\r\n MIIDEzCCAnygAwIBAgIBATANBgkqhkiG9w0BAQQFADCBxDELMAkGA1UEBhMCWkEx+| | | | | \r\n FTATBgNVBAgTDFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYD+| | | | | \r\n VQQKExRUaGF3dGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlv+| | | | | \r\n biBTZXJ2aWNlcyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEm+| | | | | \r\n MCQGCSqGSIb3DQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wHhcNOTYwODAx+| | | | | \r\n MDAwMDAwWhcNMjAxMjMxMjM1OTU5WjCBxDELMAkGA1UEBhMCWkExFTATBgNVBAgT+| | | | | \r\n DFdlc3Rlcm4gQ2FwZTESMBAGA1UEBxMJQ2FwZSBUb3duMR0wGwYDVQQKExRUaGF3+| | | | | \r\n dGUgQ29uc3VsdGluZyBjYzEoMCYGA1UECxMfQ2VydGlmaWNhdGlvbiBTZXJ2aWNl+| | | | | \r\n cyBEaXZpc2lvbjEZMBcGA1UEAxMQVGhhd3RlIFNlcnZlciBDQTEmMCQGCSqGSIb3+| | | | | \r\n DQEJARYXc2VydmVyLWNlcnRzQHRoYXd0ZS5jb20wgZ8wDQYJKoZIhvcNAQEBBQAD+| | | | | \r\n gY0AMIGJAoGBANOkUG7I\/1Zr5s9dtuoMaHVHoqrC2oQl\/Kj0R1HahbUgdJSGHg91+| | | | | \r\n yekIYfUGbTBuFRkC6VLAYttNmZ7iagxEOM3+vuNkCXDF\/rFrKbYvScg71CcEJRCX+| | | | | \r\n L+eQbcAoQpnXTEPew\/UhbVSfXcNY4cDk2VuwuNy0e982OsK1ZiIS1ocNAgMBAAGj+| | | | | \r\n EzARMA8GA1UdEwEB\/wQFMAMBAf8wDQYJKoZIhvcNAQEEBQADgYEAB\/pMaVz7lcxG+| | | | | \r\n 7oWDTSEwjsrZqG9JGubaUeNgcGyEYRGhGshIPllDfU+VPaGLtwtimHp1it2ITk6e+| | | | | \r\n QNuozDJ0uW8NxuOzRAvZim+aKZuZGCg70eNAKJpaPNW15yAbi8qkq43pUdniTCxZ+| | | | | \r\n qdq5snUb9kLy78fyGPmJvKP\/iiMucEc= +| | | | | \r\n -----END CERTIFICATE----- +| | | | | \r\n | | | | | \r\n -----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.\/\r\n MIICPDCCAaUCED9pHoGc8JpK83P\/uUii5N0wDQYJKoZIhvcNAQEFBQAwXzELMAkG+| | | | | \r\n A1UEBhMCVVMxFzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFz+| | | | | \r\n cyAxIFB1YmxpYyBQcmltYXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTk2+| | | | | \r\n MDEyOTAwMDAwMFoXDTI4MDgwMjIzNTk1OVowXzELMAkGA1UEBhMCVVMxFzAVBgNV+| | | | | \r\n BAoTDlZlcmlTaWduLCBJbmMuMTcwNQYDVQQLEy5DbGFzcyAxIFB1YmxpYyBQcmlt+| | | | | \r\n YXJ5IENlcnRpZmljYXRpb24gQXV0aG9yaXR5MIGfMA0GCSqGSIb3DQEBAQUAA4GN+| | | | | \r\n ADCBiQKBgQDlGb9to1ZhLZlIcfZn3rmN67eehoAKkQ76OCWvRoiC5XOooJskXQ0f+| | | | | \r\n zGVuDLDQVoQYh5oGmxChc9+0WDlrbsH2FdWoqD+qEgaNMax\/sDTXjzRniAnNFBHi+| | | | | \r\n TkVWaR94AoDa3EeRKbs2yWNcxeDXLYd7obcysHswuiovMaruo2fa2wIDAQABMA0G+| | | | | \r\n CSqGSIb3DQEBBQUAA4GBAFgVKTk8d6PaXCUDfGD67gmZPCcQcMgMCeazh88K4hiW+| | | | | \r\n NWLMv5sneYlfycQJ9M61Hd8qveXbhpxoJeUwfLaJFf5n0a3hUKw8fGJLj7qE1xIV+| | | | | \r\n Gx\/KXQ\/BUpQqEZnae88MNhPVNdwQGVnqlMEAv3WP2fr9dgTbYruQagPZRjXZ+Hxb+| | | | | \r\n -----END CERTIFICATE----- +| | | | | \r\n | | | | | \r\n(2 rows)<\/pre>\nDatabase security<\/h2>\n