Invariant Properties

I’m sure I’ve discussed this a number of years ago but a question came up after the recent Boulder Linux User Group meeting and I decided this would be a good time to revisit it.

The question is how do you protected sensitive information from illicit insertion or modification when the attacker has full SQL access as the website user?

Important: I am focused on approaches we can use in the database itself, not our application, since the former will protect our data even if an attacker has full access to the database. These approaches are invisible to our database frameworks, e.g., JPA, once we have created the tables.

An Approach Without Triggers

At a minimum we can ensure that the database was properly configured with multiple users:

app_owner – owns the schema and tables. Often does not have INSERT/UPDATE/DELETE (or even SELECT) privileges on the tables.

app_user – owns the data but cannot modify the schema, tables, etc.

We can make this much more secure by splitting app_user into two users, app_reader and app_writer. The former user only has SELECT privileges on the tables. This is the only account used by user-facing code. The app_writer user adds INSERT/UPDATE/DELETE privileges and is only used by the methods that actually need to modify the data. Data is typically read so much more often that it is written that it often makes sense to view an application as actually two (or more) separate but related applications. In fact they may be – you can improve security by handling any data manipulation via microservices only visible to the application.

There is a big downside to this – modern database frameworks, e.g., JPA or Hibernate, make heavy use of caching to improve performance. You need to ensure that the the cache is properly updated in the app_reader cache whenever the corresponding record(s) are updated in the app_writer account.

Security Defense

This is highly database specific – does the database maintain logs that show when a user attempts to perform a non-permitted action? If so you can watch the logs on the app_reader account. Any attempt to insert or update data is a strong indication of an attacker.

Triggers Based On Related Information

A 3NF (or higher) database requires that each column be independent. In practice we often perform partial denormalization for performance reasons, e.g., adding a column for the day of the week in addition to the full date. We can easily compute the former from the latter but it takes time and can’t be indexed.

There’s a risk that a bug or intruder will introduce inconsistencies. One common solution is to use an INSERT OR UPDATE trigger that calculates the value at the time the data is inserted into the database. E.g.,

CREATE FUNCTION calculate_day_of_week() ....

CREATE TABLE date_with_dow (
    date text,
    dow  text
);

CREATE FUNCTION set_day_of_week() RETURNS trigger AS $$
    BEGIN
        NEW.date = OLD.date;
        NEW.dow = calculate_day_of_week(OLD.date);
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_day_of_week BEFORE INSERT OR UPDATE ON date_with_dow
   FOR EACH ROW EXECUTE PROCEDURE set_day_of_week();

This ensures that the day of week is properly set. A software bug, or attacker, can try specifying an invalid value but they’ll fail.

Of course we don’t really care (much) if the day of the week is incorrect. However there are other times when we care a great deal, e.g., cached attributes from digital certificates. If someone can insert a certificate with mismatched cached values, esp. if it they can replace an existing table entry, then they can do a lot of damage if the code doesn’t assume that the database could be corrupted and thus perform its own validation checks on everything it gets back. (First rule of security: never trust anything.) Even with tests we’ll only know that the data has been corrupted, not when and not how broadly.

Security Defense

Developers are information packrats. Can we learn anything from the provided day of week value?

Yes. It’s a huge red flag if the provided value doesn’t match the calculated value (modulo planned exceptions, e.g., passing null or a sentinel value to indicate that the application is deferring to the database). It’s easy to add a quick test:

CREATE FUNCTION calculate_day_of_week() ....

-- user-defined function that can do anything from adding an entry into
-- a table to sending out an email, SMS, etc., alert
CREATE FUNCTION security_alert() ....

CREATE TABLE date_with_dow (
    date text,
    dow  text
);

CREATE FUNCTION set_day_of_week() RETURNS rigger AS $$
    DECLARE
        calculated_dow text;
    BEGIN
        NEW.date = OLD.date;
        NEW.dow = calculate_day_of_week(OLD.date);
        IF (NEW.dow  OLD.date) THEN
            security_alert("bad dow value!");
            RETURN null;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_day_of_week BEFORE INSERT OR UPDATE ON date_with_dow
    FOR EACH ROW EXECUTE PROCEDURE set_day_of_week();

Sidenote: check out your database documentation for more ideas. For instance many applications use @PrePersist annotations to autofill creationDate and lastUpdateDate. It’s easy to do this via a trigger – and using a trigger ensures that the data updated even if an attacker does it via SQL injection or direct access. More impressively you can write audit information to a separate table, perhaps even in a separate schema that the app_user only has INSERT privileges for in order to prevent an attacker from learning what the system has learned about them, much less altering or deleting that information.

I’ve written triggers that generate XML representations of the OLD and NEW values and write them to an audit table together with date, etc. On INSERT the OLD data is null, on DELETE the NEW data is null. Using XML allows us to use a common audit table (table name is just a field) and potentially allows you to add transaction id, etc.

It is then easy to use a bit of simple XML diff code to see exactly what changed when by reviewing the audit table.

Resources:

• PostgreSQL
• MySQL
• Oracle

Triggers Based On Secrets

What about tables where there’s no “related” columns? Can we use a trigger to detect an illicit attempt to INSERT or UPDATE a record?

Yes!

In this case we want to add an extra column to the table. It can be anything – the sole purpose is to create a way to pass a validation token to the trigger.

What are validation tokens?

A validation token can be anything you want. A few examples are:

A constant – this is the easiest but will be powerful as long as you can keep it secret. An example is ’42’. An obvious variant is the sum of several of the other columns of the table. This value should not be written to the database or it will be exposed to anyone with SELECT privileges.

A time-based value – your webserver and database will have closely synced clocks so you can use a time-based protocol such as Time-based One-time Password (TOTP) Algorithm. If both the database and application servers use NTP you can keep the window as small as a few seconds. Just remember to include one tick on either side when validating the token – NTP keeps the clocks synchronized but there can still be a very small skew plus network latency to consider.

Note: TOTP requires a shared secret and is independent of the contents of the INSERT or UPDATE statement.

You can save a time-based value but it is meaningless without a timestamp – and some algorithms can be cracked if you have a series of values and the starting time.

An HMAC value – most people will be familiar with standard cryptographic hashes such as MD-5 or SHA-1 (both considered cracked) or SHA-256. They’re powerful tools – but in part because everyone will compute the same values given the same input.

In our case we want an HMAC – it is a cryptographically strong message digest that also requires an encryption key. An attacker cannot generate their own HMAC but anyone with the corresponding digital certificate can verify one. An HMAC value requires something in the message to be processed and it needs to be intrinsic to the value of the record. For instance a digital certificate, a PDF document, even a hashed password. Don’t use it to hash the primary key or any value that can be readily reused.

You can freely save an HMAC value.

Subsequent validation

We would like to know that values haven’t been corrupted, e.g., by an attacker knowledgeable enough to disable the trigger, insert bad values, and then restore the trigger. The last step is important since we can / should run periodic scans to ensure all security-related features like these database triggers are still in place. Can we use these techniques to validate the records after the fact?

Constant value: no.

Time-base value: only if we record a timestamp as well, and if we do then we have to assume that the secret has been compromised. So… no.

HMAC value: yes.

Backups and Restorations

Backups and restorations have the same problems as subsequent validations. You can’t allow any magic values to be backed up (or an attacker could learn it by stealing the backup media) and you can’t allow the time-based values plus timestamps to be backed up (or an attacker could learn the shared secret by stealing the backup media). That means you would need to disable the trigger when restoring data to the database and you can’t verify that it’s properly validated afterwards. Remember: you can’t trust backup media!

The exception is HMAC tokens. They can be safely backed up and restored even if the triggers are in place.

Security Defense

You can add a token column to any table. As always it’s a balance between security and convenience and the less powerful techniques may be Good Enough for your needs. But for highly sensitive records, esp. those that are inserted or updated relatively infrequently, an HMAC token may be a good investment.

Implementation-wise: on the application side you can write a @PrePersist method that handles the creation of the TOTP or HMAC token. It’s a standard calculation and the biggest issue, as always, is key management. On the database side you’ll need to have a crypto library that supports whatever token method you choose.

Shadow Tables

Finally, there are two concerns with the last approach. First, it requires you to have crypto libraries available in your database. That may not be the case. Second if a value is inserted it’s impossible know for sure that it was your application that inserted it.

There’s a solution to this which is entirely app-side. It might not give you immediate notification of a problem but it still gives you some strong protection when you read the data.

You start as above – add a @PrePersist method that calculates an HMAC code. Only now you edit the domain bean so that the HMAC column uses a @SecondaryTable instead of the main table. (I think you can even specify a different schema if you want even higher security.) From the data perspective this is just two tables with a 1:1 relationship but from the source code perspective it’s still a single object.

Putting this into a separate table, if not a separate schema as well, means that a casual attacker will not know that it is there. They might succeed in inserting or modifying data but not realize that the changes will be detected even if audit triggers are disabled.

The final step is adding a @PostLoad method that verifies the HMAC code. If it’s good you can have confidence the data hasn’t been corrupted. If it’s incorrect or missing you know there’s a problem and you shouldn’t trust it.

For advanced users the developer won’t even know that the extra data is present – you can do a lot with AOP and some teams are organized so that the developers write unsecured code and a security team – which is focused entirely on security, not features – is responsible for adding security entirely through AOP code interwoven into the existing code. But that’s a topic for a future blog….

The the first part of this introduction I discussed setting up the Kerberos infrastructure and creating users. In this part I will demonstrate how to use it to connect to a PostgreSQL server.

Note: GSSAPI is a generic security interface that can use different protocols on the backend. Kerberos is one of many protocols that can be used with GSSAPI. Using it allows developers to write flexible code while still allowing the system administrator to have the final word on which authentication method(s) will be accepted. When given a choice you should enable GSSAPI instead of just Kerberos.

Configuring the Server

The default servicename for PostgreSQL is ‘postgres’. There is one notable exception – working with Active Directory requires a servicename of ‘POSTGRES’ and that requires recompiling the server. The full principal for a PostgreSQL server is ‘postgres/fqdn@realm’ although in a some situations you’ll need to specify postgres@fqdn.

We start by creating a keytab file for the server:

$ kadmin
kadmin% ank -randkey postgres/db.invariantproperties.com
kadmin% ktadd -k krb5.keytab postgres/db.invariantproperties.com

Note that the value of the fully-qualified domain name (fqdn) is critical. The server and client must both recognize it as the name of the server – the server uses it to identify which keytab entry to use as it comes up and the client uses it when it constructs its query to the KDC. Ideally the name will be fully supported by DNS – including reverse lookups – but it’s often enough to put entries into the /etc/hosts files if you’re only working with a few systems.

Once you have your keytab file you should copy it to /etc/postgresql/9.4/main/krb5.keytab and change the file’s ownership. You should also make sure that the file can only be read by the server.

$ sudo chown postgres:postgres krb5.keytabe
$ sudo chmod 0600 krb5.keytab

We now need to tell the server the location of the keytab file and to listen to all network interfaces. Kerberos can only be used on TCP connections.

/etc/postgresql/9.4/main/postgresql.conf

...
krb_server_keyfile = '/etc/postgresql/9.4/main/krb5.keytab'
listen_addresses = '*'

We must also tell the server which databases can be accessed via Kerberos. Security note: in production we never want to use a wildcard (‘all’) for both database and user on a single line.

/etc/postgresql/9.4/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD       OPTIONS
host    all             all             52.34.69.195/32         gss          include_realm=1 map=gss krb_realm=INVARIANTPROPERTIES.COM

We will always want to retain the Kerberos realm so we don’t confuse ‘bob@FOO.COM’ with ‘bob@BAZ.COM’ but this requires us to use the pg_ident mapping file. The name of the mapping is arbitrary – I’m using ‘gss’ for convenience.

In this case we’re being even stricter and requiring that the Kerberos domain match ‘INVARIANTPROPERTIES.COM’ specifically. This isn’t always possible but you can repeat the line if you only need to support a few realms.

We must add entries to the server’s identity lookup file. The easiest approach is to add authorized users directly:

/etc/postgresql/9.4/main/pg_ident.conf

# MAPNAME    SYSTEM-USERNAME                    PG-USERNAME
gss          bgiles@INVARIANTPROPERTIES.COM     bgiles

This is not a good long-term solution since you must manually restart the database, or at least reload the configuration files, every time you need to add or remove an authorized user. It’s much better to qualify the username, e.g., with ‘/postgres’, and then use regular expression matching in the pg_ident file.

/etc/postgresql/9.4/main/pg_ident.conf

# MAPNAME    SYSTEM-USERNAME                                    PG-USERNAME
gss           /^([^/]+)\/postgres@INVARIANTPROPERTIES\.COM$     \1

Important: do not share Kerberos credentials. Either map multiple Kerberos users to the same PostgreSQL identity or map them to different PostgreSQL identities and use the standard grants and roles to control access within the database.

We’re now ready to restart the PostgreSQL server.

$ sudo service postgresql restart

Example

The following is an example dialogue as I attempt to log into the server.

# try to log in without any Kerberos tickets
bgiles@snowflake:~$ psql -h kpg
psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code may provide more information
GSSAPI continuation error: No Kerberos credentials available

# log in as regular user, try to connect. The error is a little confusing but we do not get access.
bgiles@snowflake:~$ kinit bgiles
Password for bgiles@INVARIANTPROPERTIES.COM: 

bgiles@snowflake:~$ psql -h kpg
psql: duplicate GSS authentication request

# log in as database user, try to connect.
bgiles@snowflake:~$ kinit bgiles/postgres
Password for bgiles/postgres@INVARIANTPROPERTIES.COM: 

bgiles@snowflake:~$ psql -h kpg
psql (9.4.7, server 9.4.6)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

bgiles=# 

Establishing a JDBC Connection

Connecting to the database via the CLI demonstrates that we have properly set up Kerberos authentication but in practice we will want to access the database programmatically. That means JDBC connections.

Is it possible?

The answer is yes and no. I’ll cover it in further detail in the next part but it’s easy to establish the connection but there’s an unexplained problem when mapping the GSS/Kerberos identity to the PostgreSQL identity. Investigation continues…

PostgreSQL currently supports Kerberos authentication if you use a simple Kerberos principal (user) and password as your connection properties. It does not support a compound Kerberos principal as I discussed above, nor does it support the use of keytab files.

I plan to submit a patch to support keytabs in the next few weeks. I don’t know how long review will take and of course it’s unlikely to be applied retroactively. Write me if you want a copy.

What About Other Databases?

Other databases also support Kerberos.

Oracle: Oracle has supported Kerberos for a long time with an optional security extension. In 2013 Oracle made it free to use for all versions of its database. I haven’t had a chance to see if I can use it with Oracle 11 XE for Linux. It was cut at about the same time but it can be tricky to set up Oracle XE on Linux. (I had it working on Ubuntu 14.04 LTS but then a routine package update broke it.)

Microsoft SQLServer: SQLServer has also supported Kerberos for a long time, at least in its Active Directory wolf’s clothing. I would expect MSSQL XE to also support AD/Kerberos.

MySQL/MariaDB: MySQL does not support Kerberos. MariaDB does via a plugin but I haven’t been able to bring my MariaDB server up to verify this.

Cassandra: Cassandra supports Kerberos. See Datastax.

MongoDB: MongoDB supports Kerberos.

Neo4j: No information.

Apache Hive: Hive supports Kerberos.

H2/Derby/embedded databases: They do not appear to support Kerberos but I can’t say this with any certainty.

(Note: if you’re not familiar with Oracle XE and MSSQL XE they’re the ‘first hit is free’ versions of the respective databases. You can use them on your website or application as long as the server is limited to a single CPU and you do not have more than 10 GB (iirc) of data. They’re usually one rev back from the commercial product. A Personal Use license lets you use the latest versions of the software but there are extremely tight restrictions – it’s basically only useful when learning how to use the respective database.)

Next Time

Next time I will discuss establishing low-level connections using Kerberos authentication.

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.

In 2011 I wrote a series of articles on PostgreSQL PL/Java. The basic information is still solid but there is a now a much easier way to install PL/Java from source. This also eliminates the need to depend on third parties to create packages. These notes will be fairly brief since I assume my readers are already familiar with git and maven.

(Note: I’ve passed this information to the PL/Java team so it may already be handled by the time you read this.)

Perform the basic build

1. Clone the PL/Java repository at https://github.com/tada/pljava.
2. Run maven not make.
3. …
4. Profit!

Of course it’s not that simple. Maven can pull in its own dependencies but we still need several specialized libraries beyond the standard GNU toolchain. On my Ubuntu system I needed:

• postgresql-server-dev-9.4
• libpg-dev
• libpgtypes3
• libecpg-dev

(I don’t know the corresponding package names for RedHat/Fedora/CentOS.)

It may take a bit of experimentation but it shouldn’t be too hard to identify all of the packages you need. Just remember that you’ll usually want the packages with the “-dev” extension.

There are a large number of compiler warnings and errors but most if not all seem to be related to sign conversions. This warrants further investigation – sign conversion warnings indicate possible attack surfaces by malicious users – but for now we should be fine as long as maven succeeds. We need three files:

./src/sql/install.sql
./pljava/target/pljava-0.0.2-SNAPSHOT.jar
./pljava-so/target/nar/pljava-so-0.0.2-SNAPSHOT-i386-Linux-gpp-shared/lib/i386-Linux-gpp/shared/libpljava-so-0.0.2-SNAPSHOT.so

Copying the files

We can now copy the three files to their respective locations.

$ sudo cp ./pljava-so/target/nar/pljava-so-0.0.2-SNAPSHOT-i386-Linux-gpp-shared/lib/i386-Linux-gpp/shared/libpljava-so-0.0.2-SNAPSHOT.so \
  /usr/lib/postgresql/9.4/lib/pljava.so

$ sudo cp ./pljava/target/pljava-0.0.2-SNAPSHOT.jar /usr/share/postgresql/9.4/extension/pljava--1.4.4.jar

$ sudo cp ./src/sql/install.sql /usr/share/postgresql/9.4/extension/pljava--1.4.4.sql

We can learn the correct target directory with the ‘pg_config’ command.

$ pg_config
PKGLIBDIR = /usr/lib/postgresql/9.4/lib
SHAREDIR = /usr/share/postgresql/9.4
...

I have changed the version from 0.0.2-SNAPSHOT to 1.4.4 since we want to capture the PL/Java version, not the pom.xml version. I hope these will soon be kept in sync.

Editing pljava–1.4.4.sql

We need to add two lines to the installation sql:

SET PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar';
SET PLJAVA.VMOPTIONS='-Xms64M -Xmx128M';

It’s important to remember that there is a unique JVM instantiated for each database connection. Memory consumption can become a major concern when you have 20+ simultaneous connections.

Create the pljava.control file

We must tell PostgreSQL about the new extension. This is handled by a control file.

/usr/share/postgresql/9.4/extension/pljava.control

# pljava extension
comment = 'PL/Java bundled as an extension'
default_version = '1.4.4'
relocatable = false

Make libjvm.so visible

We normally specify the location of the java binaries and shared libraries via the JAVA_HOME environment variable. This isn’t an option with the database server.

There are two approaches depending on whether you want to make the java shared library (libjvm.so) visible to all applications or just the database server. I think the former is easiest.

We need to create a single file

/etc/ld.so.conf.d/i386-linux-java.conf

/usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server

where most of the pathname comes from JAVA_HOME. The location may be different on your system. The directory must contain the shared library ‘libjvm.so’.

We must also tell the system to refresh its cache.

$ sudo ldconfig
$ sudo ldconfig -p | grep jvm
	libjvm.so (libc6) => /usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server/libjvm.so
	libjsig.so (libc6) => /usr/lib/jvm/java-8-openjdk-i386/jre/lib/i386/server/libjsig.so

Loading the extension

We can now easily load and unload PL/Java.

=> CREATE EXTENSION pljava;
CREATE EXTENSION

=> DROP EXTENSION pljava;
DROP EXTENSION

In case of flakiness…

If the system seems flaky you can move the two ‘set’ commands into the postgresql.conf file.

/etc/postgresql/9.4/main/postgresql.conf

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

PLJAVA.CLASSPATH='/usr/share/postgresql/9.4/extension/pljava--1.4.4.jar'
PLJAVA.VMOPTIONS='-Xms64M -Xmx128M'

A few years ago I discussed creating user-defined types using PL/java. (Introduction to PostgreSQL PL/Java, part 4: User Defined Types) Today I follow up on the comment that we would use a standard C-language PostgreSQL extension in practice.

What is an extension?

A PostgreSQL extension is nothing more than bundled SQL statements and an optional native library. SQL-only extensions contain nothing but SQL statements.

Extensions provide two major benefits over the standalone scripts. First, the statements are loaded and unloaded as a unit. It is not possible to alter anything defined in an extension. This is a big win for maintainability and security. Second, extensions are versioned. This allows the SQL artifacts to be cleanly updated over time, something particularly important with user-defined types since simply dropping the UDT will cause the loss of data.

PostgreSQL has well-defined support for extensions. See Packaging Related Objects into an Extension.

Loading an extension

CREATE EXTENSION IF NOT EXISTS pg_complex;

Unloading an extension

DROP EXTENSION pg_complex;

It is important to remember that many if not most hosted database-as-a-service (DAAS) providers, e.g., Amazon RDS, will not allow you to install arbitrary extensions. You can still load SQL-only extensions by running the creation scripts manually but C-language extensions could cause problems in the future.

What are user-defined types (UDT)?

User-defined types are extensions to the database to support new types of data. SQL purists want tables to contain nothing but the standard primitives. Data that has a structure can be captured in a separate table and a foreign key, e.g., pulling an Address table out of a Customer table.

Many developers recognize that some data is more tightly bound than others and useful operations typically require more than one primitive value. Geographic position (latitude, longitude) and cryptographic information are classic examples. These objects are often so small that it does not make sense to have a separate table for them – compare a mailing address vs. the lat-long of that address.

(There may be other reasons to store the UDTs in a separate table, e.g., for improved security of sensitive information.)

Another use for UDTs is to add type safety to BLOBs. For instance it is reasonable to have user-defined functions that return the height or width of an image or the number of pages of a PDF document. You can easily write functions that accept BLOBs (bytea) but you can’t ensure that the value passed to the function is the appropriate type. Defining a UDT, e.g. pdf or jpeg, gives the developer a powerful tool.

In conclusion UDTs should be considered when 1) the object is meaningless if any element is missing or 2) the object would otherwise be a BLOB and you want to provide type-safe stored procedures and user-defined functions. Otherwise we should stick with the standard primitives.

Complex Numbers?

I will be creating a UDT for complex numbers below. I do not see a great unmet need to store complex numbers in relational databases but it is a good choice for educational purposes since it requires custom types, functions, casts, operators, and aggregate functions. The only thing missing is ordering.

This implementation uses a PostgreSQL composite type with a combination of SQL stored procedures and C user-defined functions. Complex numbers will be shown as (a, b) instead of the conventional a + bi but the latter is possible with additional work.

SQL definitions

This is a PostgreSQL extension so we should start by defining what we expect to see in our improved database.

Defining the complex UDT

The complex UDT consists of two fields – a real (re) component and an imaginary (im) component.

CREATE TYPE complex AS (re float8, im float8);

A very simple demonstration of its use follows.

$> CREATE TABLE t (c complex);
CREATE TABLE

-- insert a value. Note that we are inserting '(1,2)', not '1,2'.
$> INSERT INTO t VALUES((1,2));
INSERT 0 1

-- select full UDT
$> SELECT c FROM t;
   c   
-------
 (1,2)

-- select components. Note that we must surround field with parentheses.
$> SELECT (c).re, (c).im FROM t;
 re | im 
----+----
  1 |  2

Autopromoting floats to a complex numbers

It is easy to extract the real component of a complex number but still a pain to convert a real number to a complex number. PostgreSQL can do this transparently if we define a CAST.

CREATE OR REPLACE FUNCTION pgx_complex_from_int(int) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_bigint(bigint) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_numeric(numeric) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_numeric(float8) RETURNS complex AS $$
   SELECT ROW($1, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

Defining arithmetic operators

Complex numbers are numbers so we want to implement the standard arithmetic operators for them. All of the C function names are smurfed since they must unique. The SQL function names do not have to be unique since the function signature is also considered.

CREATE OPERATOR = (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_eq,
   NEGATOR = <>,
   HASHES,
   MERGES
);

CREATE OPERATOR  (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_ne,
   NEGATOR = <>,
   HASHES,
   MERGES
);

CREATE OPERATOR ~= (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_near,
   NEGATOR = <~>
);

CREATE OPERATOR  (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_not_near,
   NEGATOR = <>
);

CREATE OPERATOR - (
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_negate,
   NEGATOR = -
);

CREATE OPERATOR ~ (
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_conjugate,
   NEGATOR = ~
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR + (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_add
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR - (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgxdefine_complex_subtract
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR * (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_multiply
);

-- variants mixing 'complex' and 'numeric' types elided
CREATE OPERATOR / (
   LEFT_ARG = complex,
   RIGHT_ARG = complex,
   PROCEDURE = pgx_complex_divide
);

Defining aggregate functions

Aggregate functions are one of the main ways to put intelligence into the database instead of treating it like a glorified file system. These are functions that operate on a collection of values and return an aggregate value of some type. Aggregate functions can have multiple parameters.

Don’t assume aggregates can only consume and produce numeric data. Consider a function that takes (x, y) pairs and produces a .png plot of the results.

Aggregate functions can optionally support window functions (http://www.postgresql.org/docs/9.4/static/tutorial-window.html). These are a relatively new feature and incredibly powerful. Implementation is conceptually simple – we only need functions to add or remove a value from the aggregate function’s ‘state’ – but in practice operations may be irreversible. That is the case here – if we compute ‘1e20 + 1 – 1e20’ the results should be ‘1’ but may be zero due to limited resolution.

--
-- UDT that keeps track of sums and sums-of-squares of a collection
-- of complex values. Tracking all three values allows us to compute
-- a wide variety of statistical values.
--
CREATE TYPE complex_accum AS (
   cnt  int,
   sum  complex,
   sofs complex
);

--
-- Calculate sum of a collection of complex values
--
CREATE AGGREGATE sum(complex) (
   sfunc = pg_complex_add,
   stype = complex_accum,
   initcond = '(0, 0)',
   -- msfunc = pg_complex_add,
   -- minvfunc = pg_complex_subtract,
   -- mstype = complex_accum,
   -- minitcond = (0, 0)'
);

--
-- Calculate average of a collection of complex values.
--
CREATE AGGREGATE avg(complex) (
   sfunc = pg_complex_accum,
   stype = complex_accum,
   finalfunc = pg_complex_avg
   -- msfunc = pg_complex_accum,
   -- minvfunc = pg_complex_disaccum,
   -- mstype = complex_accum,
);

(See: http://www.postgresql.org/docs/9.4/static/xaggr.html.)

Defining user-defined functions

We now know the functions and signatures that we must implement. In this case we can do most of the functions in pure SQL but choose to do a few in C to demonstrate advanced techniques.

Note: under TDD principles we should only implement enough to allow the tests to run. In this case the functions should return null. I’m not doing that here since the functions are so simple they can be verified at a glance. Any multi-line function should follow TDD principles and return null.

--
-- create functions implemented in C.
--
CREATE OR REPLACE FUNCTION pgx_complex_near(complex, complex)
RETURNS bool
AS 'pg_complex', 'pgx_complex_near'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_divide(complex, complex)
RETURNS complex
AS 'pg_complex', 'pgx_complex_divide'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION norm(complex)
RETURNS complex
AS 'pg_complex', 'pgx_complex_norm'
LANGUAGE C IMMUTABLE STRICT;

--
-- create functions implemented in SQL.
--
CREATE OR REPLACE FUNCTION pgx_complex_from_int(int) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_bigint(bigint) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_from_numeric(numeric) RETURNS complex AS $$
   SELECT ROW($1::float8, 0)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_eq(complex, complex) RETURNS bool AS $$
   SELECT $1.re = $2.re AND $1.im = $2.im;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_ne(complex, complex) RETURNS bool AS $$
   SELECT $1.re <> $2.re OR $1.im <> $2.im;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_not_near(complex, complex) RETURNS bool AS $$
   SELECT NOT pgx_complex_near($1, $2);
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_negate(complex) RETURNS complex AS $$
   SELECT ROW(-$1.re, -$1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_conjugate(complex) RETURNS complex AS $$
   SELECT ROW($1.re, -$1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_add(complex, complex) RETURNS complex AS $$
   SELECT ROW($1.re + $2.re, $1.im + $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_add_f8(float8, complex) RETURNS complex AS $$
   SELECT ROW($1 + $2.re, $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_add_f8(complex, float8) RETURNS complex AS $$
   SELECT ROW($1.re + $2, $1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_subtract(complex, complex) RETURNS complex AS $$
   SELECT ROW($1.re - $2.re, $1.im - $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_subtract_f8(float8, complex) RETURNS complex AS $$
   SELECT ROW($1 - $2.re, -$2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_subtract_f8(complex, float8) RETURNS complex AS $$
   SELECT ROW($1.re - $2, $1.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_multiply(complex, complex) RETURNS complex AS $$
   SELECT ROW($1.re * $2.re - $1.im * $2.im, $1.re * $2.im + $1.im * $2.re)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_multiply_f8(float8, complex) RETURNS complex AS $$
   SELECT ROW($1 * $2.re, $1 * $2.im)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION pgx_complex_multiply_f8(complex, float8) RETURNS complex AS $$
   SELECT ROW($1.re * $2, $1.im * $2)::complex;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION magnitude(complex) RETURNS float8 AS $$
   SELECT sqrt($1.re * $1.re + $1.im * $1.im);
$$ LANGUAGE SQL IMMUTABLE STRICT;

Creating a skeleton extension

We’re now ready to create a skeleton extension. This extension follows test-driven development (TDD) practices – we want the minimal amount of code that fails. In this case that means the extension will load and define the user-defined functions and type but all functions and operators immediately return NULL.

The easiest way to do this is the PGXN utilities.

First, make sure that the following packages are installed:

• pgxnclient
• postgresql-server-dev-9.4
• make
• ruby
• ruby2.1-dev
• gcc

(This is for PostgreSQL 9.4 under Ubuntu. Adjust accordingly.)

Second, clone the github repository guedes/pgxn-utils.

Third, install these tools.

$ sudo pgxnclient install pgxn_utils

# verify utilities have been installed.
$ pgxn-utils help
PGXN Utils version: 0.1.4
Commands:
  pgxn-utils bundle [extension_name]  # Bundles the extension in a zip file
  pgxn-utils change [extension_name]  # Changes META's attributes in current extension
  pgxn-utils help [COMMAND]           # Describe available commands or one specific command
  pgxn-utils release filename         # Release an extension to PGXN
  pgxn-utils skeleton extension_name  # Creates an extension skeleton in current directory

Fourth, create a skeleton for a C-based PostgreSQL extension using our new utilities.

$ pgxn skeleton -m "Bear Giles <bgiles@coyotesong.com>" --template=c pg_complex
      create  pg_complex
      create  pg_complex/pg_complex.control
      create  pg_complex/.gitignore
      create  pg_complex/.template
      create  pg_complex/META.json
      create  pg_complex/Makefile
      create  pg_complex/README.md
      create  pg_complex/doc/pg_complex.md
      create  pg_complex/sql/pg_complex.sql
      create  pg_complex/sql/uninstall_pg_complex.sql
      create  pg_complex/src/pg_complex.c
      create  pg_complex/test/expected/base.out
      create  pg_complex/test/sql/base.sql

Fifth, edit the META.json, README.md and doc/pg_complex.md files to describe the extension. This would also be a good time to copy a LICENSE file into this directory if you have any plans to release the extension to others. Your future self will thank you for this documentation.

The META.json file allows us to specify dependencies among extensions.

Sixth, create a dummy implementation of each function that immediately returns null.

#include "postgres.h"
#include "fmgr.h"

PG_MODULE_MAGIC;

/*
 * Are two points "near" each other. This function requires reading
 * composite types.
 */
PG_FUNCTION_INFO_V1(pgx_complex_near);

Datum
pgx_complex_near(PG_FUNCTION_ARGS) {
	PG_RETURN_NULL();
}

/*
 * Divide one complex number by another. This function requires reading
 * and returning composite types.
 */
PG_FUNCTION_INFO_V1(pgx_complex_divide);

Datum
pgx_complex_divide(PG_FUNCTION_ARGS) {
	PG_RETURN_NULL();
}

/*
 * Scale a complex number so it on the unit circle. This function requires
 * reading and returning composite types.
 */
PG_FUNCTION_INFO_V1(pgx_complex_norm);

Datum
pgx_complex_norm(PG_FUNCTION_ARGS) {
	PG_RETURN_NULL();
}

Our problem is simple enough that the SQL stored procedures implemented the required functionality. More complex stored procedures could be implemented as plpsql stored procedures that return null.

Seventh, build the system.

$ make
$ sudo make install

You may need to load the extension before you call ‘make install’ the first time. It is not necessary to reload it afterwards.

$ sudo pgxn load ./

Eigth, run the tests.

The standard skeleton has support for regression tests.

$ make installcheck

The regression tests run all scripts under test/sql and verifies the results match the corresponding files in test/expected. The actual results are saved in results so it’s easy to write tests, modify the code as required, and then copy the file from results to test/expected once the desired behavior is seen.

You can also run tests through pgxn.

$ pgxn check -d somedb pg_complex

The optional pgTAP (http://pgtap.org/) extension gives us the ability to write xJunit-type tests. These tests will probably be more comfortable for developers than the regression tests.

For information integrating pgTAP into the build process see http://pgtap.org/integration.html and https://gkoenig.wordpress.com/2011/03/04/pgtap-unit-tests-for-postgresql/.

Ninth, install and deploy the extension outside of the test framework.

$ pgxn install --sudo -- pg_complex
$ pgxn load -d somedb --sudo -- pg_complex

You can undeploy and uninstall the extension using the analogous commands.

$ pgxn unload --sudo -- pg_complex
$ pgxn uninstall --sudo -- pg_complex

Tenth, publish the extension. You probably don’t want to do this with the skeleton implementation but this is a natural place to document the process. If we are a member of PGXN and wish to make our extension public we start by bundling our extension

$ pgxn bundle

and then upload it to https://manager.pgxn.org/.

Testing

As good test-driven development developers we start by writing our tests. In our case it’s straight SQL, or more precisely files that can be run through psql.

A typical test script is

test/sql/math.sql

\set ECHO None
BEGIN;
\i sql/complex.sql
\set ECHO all

\set c1 (1,2)::complex
\set c2 (1,1)::complex
\set c3 (3,4)::complex
\set c4 (3,8)::complex

SELECT 1::complex AS a, (1::int8)::complex AS b, 1.0::complex AS c;

SELECT (1,2)::complex AS a, -(1,2)::complex AS b, ~(1,2)::complex AS c;

SELECT :c1 + (3,4)::complex AS a, 3 + :c1 AS b, :c1 + 3 AS c;

SELECT :c1 - (3,6)::complex AS a, 3 - :c1 AS b, :c1 - 3 AS c;

SELECT :c1 * (3,5)::complex AS a, 3 * :c1 AS b, :c1 * 3 AS c;
SELECT :c1 * (3,5)::complex AS a, 3.0::double precision * :c1 AS b, :c1 * 3.0 AS c;

SELECT :c4 / :c1  AS a, (:c4 / :c1) * :c1 = :c4 AS b;
SELECT :c4 / (2,0)::complex AS a, (2,0)::complex * (:c4 / (2,0)::complex)  = :c4 AS b;
SELECT :c4 / (0,2)::complex AS a, (0,2)::complex * (:c4 / (0,2)::complex) = :c4 AS b;
SELECT :c4 / 3 AS a, 3 * (:c4 / 3) = :c4 AS b;
SELECT 3 / :c4 AS a, :c4 * (3 / :c4) = 3::complex AS b;

--
-- check magnitude
--
SELECT magnitude(:c1) AS magnitude;
SELECT magnitude(:c2) AS magnitude;
SELECT magnitude(:c3) AS magnitude;

ROLLBACK;

The corresponding expected results are

test/expected/math.out

\set ECHO None
\set c1 (1,2)::complex
\set c2 (1,1)::complex
\set c3 (3,4)::complex
\set c4 (3,8)::complex
SELECT 1::complex AS a, (1::int8)::complex AS b, 1.0::complex AS c;
   a   |   b   |   c   
-------+-------+-------
 (1,0) | (1,0) | (1,0)
(1 row)

SELECT (1,2)::complex AS a, -(1,2)::complex AS b, ~(1,2)::complex AS c;
   a   |    b    |   c    
-------+---------+--------
 (1,2) | (-1,-2) | (1,-2)
(1 row)

SELECT :c1 + (3,4)::complex AS a, 3 + :c1 AS b, :c1 + 3 AS c;
   a   |   b   |   c   
-------+-------+-------
 (4,6) | (4,2) | (4,2)
(1 row)

SELECT :c1 - (3,6)::complex AS a, 3 - :c1 AS b, :c1 - 3 AS c;
    a    |   b    |   c    
---------+--------+--------
 (-2,-4) | (2,-2) | (-2,2)
(1 row)

SELECT :c1 * (3,5)::complex AS a, 3 * :c1 AS b, :c1 * 3 AS c;
    a    |   b   |   c   
---------+-------+-------
 (-7,11) | (3,6) | (3,6)
(1 row)

SELECT :c1 * (3,5)::complex AS a, 3.0::double precision * :c1 AS b, :c1 * 3.0 AS c;
    a    |   b   |   c   
---------+-------+-------
 (-7,11) | (3,6) | (3,6)
(1 row)

SELECT :c4 / :c1  AS a, (:c4 / :c1) * :c1 = :c4 AS b;
     a     | b 
-----------+---
 (3.8,0.4) | t
(1 row)

SELECT :c4 / (2,0)::complex AS a, (2,0)::complex * (:c4 / (2,0)::complex)  = :c4 AS b;
    a    | b 
---------+---
 (1.5,4) | t
(1 row)

SELECT :c4 / (0,2)::complex AS a, (0,2)::complex * (:c4 / (0,2)::complex) = :c4 AS b;
    a     | b 
----------+---
 (4,-1.5) | t
(1 row)

SELECT :c4 / 3 AS a, 3 * (:c4 / 3) = :c4 AS b;
          a           | b 
----------------------+---
 (1,2.66666666666667) | t
(1 row)

SELECT 3 / :c4 AS a, :c4 * (3 / :c4) = 3::complex AS b;
                   a                    | b 
----------------------------------------+---
 (0.123287671232877,-0.328767123287671) | t
(1 row)

--
-- check magnitude
--
SELECT magnitude(:c1) AS magnitude;
    magnitude     
------------------
 2.23606797749979
(1 row)

SELECT magnitude(:c2) AS magnitude;
    magnitude    
-----------------
 1.4142135623731
(1 row)

SELECT magnitude(:c3) AS magnitude;
 magnitude 
-----------
         5
(1 row)

ROLLBACK;

It is probably easiest to create the ‘expected’ file by running the test once, getting the results from results/math.out, and editing that file to show the expected results. In a pure TDD implementation all of the tests should initially return null but we’ve already defined many of the functions above.

Implementation

There are three SQL-language functions to add. Details matter – the sum of no values is well-defined as 0 + 0i but the average of no values is undefined (null), not any particular value.

--
-- accumulator function is similar to float8_accum. Question: should the result
-- be the product of p * p or the product of p * ~p ?
--
CREATE OR REPLACE FUNCTION pgx_complex_accum(complex_accum, complex) RETURNS complex_accum AS $$
   SELECT CASE WHEN $1 IS NULL THEN 1 ELSE $1.cnt + 1 END,
          CASE WHEN $1 IS NULL THEN $2 ELSE $1.sum + $2 END,
          CASE WHEN $1 IS NULL THEN $2 * ~$2 ELSE $1.sofs + $2 * ~$2 END;
$$ LANGUAGE SQL;

--
-- disaccumulator(?) function is similar to pgx_complex_accum. It is required in order
-- to implement windowing functions.
--
CREATE OR REPLACE FUNCTION pgx_complex_disaccum(complex_accum, complex) RETURNS complex_accum AS $$
   SELECT pgx_complex_accum($1, -$2);
$$ LANGUAGE SQL;

--
-- average function returns quotient of sum over count.
--
CREATE OR REPLACE FUNCTION pgx_complex_avg(complex_accum) RETURNS complex AS $$
   SELECT CASE WHEN $1 IS NULL THEN NULL
               WHEN $1.cnt = 0 THEN (0,0)::complex
               ELSE $1.sum / $1.cnt END;
$$ LANGUAGE SQL;

The first C-language function demonstrates how to read a composite value and return a primitive. In this case we get the ‘re’ and ‘im’ components by name.

PG_MODULE_MAGIC;

/*
 * Test complex numbers for proximity. This avoids the problems with testing floats
 * and doubles but does not guarantee absolute equality.
 */
PG_FUNCTION_INFO_V1(pgx_complex_near);

Datum
pgx_complex_near(PG_FUNCTION_ARGS) {
    double re[2];
    double im[2];
    double p, q;
    int i;

    // unwrap values.    
    for (i = 0; i < 2; i++) {
        HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(i);
        bool isnull[2];

        Datum dr = GetAttributeByName(t, "re", &isnull[0]);
        Datum di = GetAttributeByName(t, "im", &isnull[1]);

        // STRICT prevents the &#039;complex&#039; value from being null but does
        // not prevent its components from being null.        
        if (isnull[0] || isnull[1]) {
            PG_RETURN_NULL();
        }
        
        re[i] = DatumGetFloat8(dr);
        im[i] = DatumGetFloat8(di);
    }

    // compute distance between points, distance of points from origin.
    p = hypot(re[0] - re[1], im[0] - im[1]);
    q = hypot(re[0], im[0]) + hypot(re[1], im[1]);
    
    if (q == 0) {
        PG_RETURN_BOOL(1);
    }
    
    // we consider the points &#039;near&#039; each other if the distance between them is small
    // relative to the size of them. 
    PG_RETURN_BOOL(p / q < 1e-8); 
}

The second case returns a composite value. There are two ways to return composite values. This is the older way and requires a little more work. The newer way requires everything be returned as a string – this has a modest cost with primitive values but it could be costly to marshal and unmarshal user-defined types.

/*
 * Divide complex number by another. We do this by multiplying nominator and denominator
 * by the conjugate of the denominator. The denominator then becomes the scalar square of
 * the magnitude of the number.
 */
PG_FUNCTION_INFO_V1(pgx_complex_divide);

Datum
pgx_complex_divide(PG_FUNCTION_ARGS) {
    TupleDesc tupdesc;
    HeapTuple tuple;
    double re[2];
    double im[2];
    int i;
    double q;
    Datum datum[2];
    bool isnull[2];
 
    // build a tuple descriptor for our result type 
    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("function returning record called in context "
                        "that cannot accept type record")));

    // unwrap values.    
    for (i = 0; i < 2; i++) {
        HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(i);
        bool isnull[2];
        Datum dr, di;

        dr = GetAttributeByName(t, "re", &isnull[0]);
        di = GetAttributeByName(t, "im", &isnull[1]);

        // STRICT prevents the &#039;complex&#039; value from being null but does
        // not prevent its components from being null.        
        if (isnull[0] || isnull[1]) {
            PG_RETURN_NULL();
        }
        
        re[i] = DatumGetFloat8(dr);
        im[i] = DatumGetFloat8(di);
    }

    // the denominator is the square of the magnitude of the divisor.
    q = re[1] * re[1] + im[1] * im[1];
    
    // should I throw error instead of returning null?
    if (q == 0.0) {
        PG_RETURN_NULL();
    }

    datum[0] = Float8GetDatum((re[0] * re[1] + im[0] * im[1]) / q);
    datum[1] = Float8GetDatum((im[0] * re[1] - im[1] * re[0]) / q);

    BlessTupleDesc(tupdesc);
    tuple = heap_form_tuple(tupdesc, datum, isnull);
 
    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}

The third example also consumes and produces a composite value.

/*
 * Calculate the norm of a complex number. This is the complex number on the unit
 * circle so that magnitude(norm(x)) = 1 and magnitude(x) * norm(x) = x.
 */
PG_FUNCTION_INFO_V1(pgx_complex_norm);

Datum
pgx_complex_norm(PG_FUNCTION_ARGS) {
    HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
    TupleDesc tupdesc;
    HeapTuple tuple;
    double re;
    double im;
    bool isnull[2];
    Datum datum[2];
    double m;
 
    // build a tuple descriptor for our result type 
    if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                 errmsg("function returning record called in context "
                        "that cannot accept type record")));
        
    // unwrap values.    
    datum[0] = GetAttributeByName(t, "re", &isnull[0]);
    datum[1] = GetAttributeByName(t, "im", &isnull[1]);

    // STRICT prevents the 'complex' value from being null but does
    // not prevent its components from being null.        
    if (isnull[0] || isnull[1]) {
        PG_RETURN_NULL();
    }
        
    re = DatumGetFloat8(datum[0]);
    im = DatumGetFloat8(datum[1]);

    m = hypot(re, im);
   
    // should I throw error instead of returning null?
    if (m == 0.0) {
        PG_RETURN_NULL();
    } 

    datum[0] = Float8GetDatum(re / m);
    datum[1] = Float8GetDatum(im / m);

    BlessTupleDesc(tupdesc);
    tuple = heap_form_tuple(tupdesc, datum, isnull);
 
    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}

Wrap up

This wraps up the complex number extension. Most extensions will be far more simple but this problem was chosen precisely because it demonstrates how deeply you can integrate an extension.

Source code

http://github.com/beargiles/pg-complex
http://pgxn.org/dist/complex

Additional Resources

PXGN blog
PGXN howto
PGXN META.json specification
PGXN client documentation
PGXN usage
pgTab (testing framework)

While doing other research I cam across three interesting PostgreSQL extensions. I can’t speak to their quality or utility but anyone who reads my blog might find them interesting.

PGAudit

PGAudit (and pgxn.org) is a module that adds improved auditing to PostgreSQL. PostgreSQL already has a logging mechanism but it can only be used in certain situations. This module uses hooks to capture additional events. If nothing else this extension can be used as a model for how to access and use these hooks.

For the security conscious – I’ve already written the authors about adding features to provide secure logging.

sslinfo

sslinfo is an official contrib extension that provides information about the SSL certificate, if any, used by the user when establishing a connection to the database.

To be honest I don’t know how much weight I would give this information. Did PostgreSQL perform proper checks? Did it perform any checks? My gut tells me that I’ll want to get the certificate and do these checks myself. If nothing else this extension tells us where to start looking for that information.

sepgsql

sepgsql is an official contrib extension that brings Security Enhanced Linux (SELinux) labels to the database.

It isn’t clear but I think the subject label (the client of the database) is distinct from the database user. That means that we could see different behavior when accessing the database as a human running an application vs. accessing the database as an application running on a webapp server.

This is normally a Bad Thing but if carefully used it could replace a lot of work involving database users and permissions with a few well-chosen SE Labels. On the other hand that could leave you exposed if SELinux is disabled.

PostgreSQL supports user-defined types (UDT). These types can be used to provide type-safety on user-defined functions when we would otherwise be forced to use simple BLOB objects.

This comes at a significant cost. Many databases support UDT but implementation details vary widely so there’s a significant amount of vendor lock-in. In addition C language UDT require deployment via PostgreSQL extensions containing shared libraries and that is rarely available when using SAAS, e.g., the Amazon cloud. This forces us to maintain our own database servers instead of relying on a SAAS provider.

On the other hand user-defined types and functions give us far more flexibility, e.g., a centralized location with dedicated cryptographic hardware.

I use the OpenSSL library for the simple reason that it’s already included by PostgreSQL to support encrypted channels. This eliminates the need to worry about library dependencies or legal restrictions on cryptographic software – I am making the reasonable assumption that this software is legal to use at any location that is already using the OpenSSL library.

Motivation

It is very common for sites to store x509 digital certificates as a blob with a number of additional columns used for indexing and searching.

CREATE TABLE certs (
   cert       BLOB NOT NULL,
   name       VARCHAR[100] NOT NULL,
   not_before TIMESTAMP NOT NULL,
   not_after  TIMESTAMP NOT NULL
);

The problem is that there’s no consistency enforced between the cert and the indexed fields. It’s unlikely for an attacker to slip in a different certificate but it can’t be ruled out and the results could be catastrophic.

A far better solution is to use triggers on insert and update

-- create trigger
CREATE CONSTRAINT TRIGGER cert_update() BEFORE INSERT OR UPDATE
    ON certs NOT DEFERRABLE FOR EACH ROW
    EXECUTE PROCEDURE cert_update_proc ();

-- create function that ensures indexed fields reflect cert
CREATE OR REPLACE FUNCTION cert_update_proc RETURNING trigger $$
    BEGIN
        INSERT INTO certs(cert, X509_name(cert), X509_not_before(cert), X509_not_after(cert));
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

This ensures that an attacker can never replace the cert without all of the indexed values being updated. Updates to the indexed fields will be ignored. It’s not 100% – a sophisticated attacker could drop the trigger – but it is far more secure than relying on the user to maintain this information. Remember that the schema, including triggers, should be owned by a different database user than the application database user so a compromised application cannot drop the trigger.

(The table definition or stored procedure can also perform sanity checks, e.g., ensuring that the “not_after” timestamp is strictly later than the “not_before” timestamp.)

We are not forced to use a user-defined type but it allows us to be more precise when defining our helper functions. It is also much more user friendly since they can insert and retrieve standard OpenSSL PEM values instead of dealing with BLOBs.

In the long term we can create user-defined functions that can use the OpenSSL types to perform actual work. We can also apply more sophisticated checks that involve internal queries, e.g., ensuring that any certificate added or modified is signed by another certificate in the database. That is beyond the scope of this blog entry.

OpenSSL engines

It is not widely known that OpenSSL supports cryptographic hardware via the “engine” interface. The default behavior is to use a software implementation but with well-written code it should be straightforward to use dedicated cryptographic hardware instead.

This can result in significant performance gains. The software implementation is adequate for development and small sites but may become a limiting factor at large sites. The hardware solutions that reduce this problem.

A more subtle point is that encryption keys are sensitive information and many organizations will not want them exposed under any conditions. No files, no web services. Hardware implementations have the ability to generate their own encryption keys and there is no mechanism for exposing the keys. (At most there’s a way to clone the keys from one hardware device to a second one.)

Design

The design is based on a simple consideration – I only want to store valid objects. The best way to ensure this is to have the user-defined types convert the external PEM values into internal OpenSSL objects and vice versa. We can perform additional sanity checks but that isn’t required.

This has one unfortunate drawback – traditional keys cannot be encrypted. I don’t consider this a problem since traditional keys shouldn’t be used anyway (IMHO) – they should be stored in containers such as keystores (PKCS8 and PKCS12).

That said the traditional keys are so simple that they’re a great platform for developing our PGXS skills.

Implementation Constraints

The constraints imposed by PostgreSQL are specified in section 35.9.5 of the manual http://www.postgresql.org/docs/9.4/static/xfunc-c.html. Most of them are handled by the tools at PGXN – see my earlier blog entry for details.

One particularly nasty constraint is that PostgreSQL, like most long-running services, has its own memory management library. OpenSSL uses the standard memory management library by default. It is possible to override this with the CRYPTO_set_mem_functions function but it’s dangerous to call this in a server because you don’t know what objects have already been created, e.g., when establishing a secure connection to the database. It could cause a server crash if an object is malloc’ed but then pfree’d.

Instead we must be very careful to always convert and release any object created by the OpenSSL library.

Defining a RSA keypair UDT

We are now ready to create an RSA keypair UDT. As mentioned earlier keys should be stored in a PKCS8 or PKCS12 object instead of a traditional RSA keypair object since the latter must be stored unencrypted.

We start by defining the RSA UDT itself.

--
-- Create shell type.
--
CREATE TYPE RSA;

--
-- Create function that converts string to internal format.
--
CREATE OR REPLACE FUNCTION rsa_in(cstring)
RETURNS RSA
AS 'pgopenssltypes', 'rsa_in'
LANGUAGE C IMMUTABLE STRICT;

--
-- Create function that converts internal format to string.
--
CREATE OR REPLACE FUNCTION rsa_out(RSA)
RETURNS CSTRING
AS 'pgopenssltypes', 'rsa_out'
LANGUAGE C IMMUTABLE STRICT;

--
-- Redefine type with necessary functions.
--
CREATE TYPE RSA (
    INPUT   = rsa_in,
    OUTPUT  = rsa_out
);

A UDT can specify about a dozen C functions (see CREATE TYPE) but the only two mandatory functions are the INPUT and OUTPUT functions that convert the object between a C string and bytea representations.

A plain UDT is pretty boring so let’s also define two user-defined functions:

--
-- Generate RSA keypair. This is an expensive operation
-- so it should not be called casually.
--
CREATE OR REPLACE FUNCTION rsa_generate_keypair(int)
RETURNS RSA
AS 'pgopenssltypes', 'rsa_generate_keypair'
LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE RSA_INFO AS (
    BITS int,
    N    BN,
    E    BN,
    D    BN,
    P    BN,
    Q    BN
);

--
-- Get details about RSA keypair.
--
CREATE OR REPLACE FUNCTION rsa_get_details(RSA)
RETURNS RSA_INFO
AS 'pgopenssltypes', 'rsa_get_details'
LANGUAGE C IMMUTABLE STRICT;

RSA_INFO is a composite type. It is not possible to return multiple columns from a function but sometimes values are intrinsically related and you want to return them as a unit. PostgreSQL supports this with composite types. You can access the fields easily.

bgiles=# SELECT rsa_get_details(rsa_generate_keypair(256)) AS details INTO sample;

bgiles=# \d sample
     Table "public.sample"
 Column  |   Type   | Modifiers 
---------+----------+-----------
 details | rsa_info | 

bgiles=# SELECT (details).P, (details).Q FROM sample;
                    p                    |                    q                    
-----------------------------------------+-----------------------------------------
 331128053999826595053108455708184431513 | 294756634092692440982306957700237950609
(1 row)

In this case there’s no reason why you would want to persist a ROW_INFO object – I can’t imagine using it anywhere except in stored procedures. But it’s a good tool to have in your toolbox.

Implementing a RSA keypair UDT (INPUT/OUTPUT)

The implementation of the basic INPUT and OUTPUT methods is straightforward once you understand the OpenSSL library. The public functions are:

/*
 * Read PEM format.
 */
PG_FUNCTION_INFO_V1(rsa_in);

Datum rsa_in(PG_FUNCTION_ARGS) {
    char *txt;
    bytea *result;
    RSA *rsa;

    // write RSA keypair into buffer
    txt = PG_GETARG_CSTRING(0);
    rsa = rsa_from_string(txt);
    result = rsa_to_bytea(rsa);
    RSA_free(rsa);

    // return bytea
    PG_RETURN_BYTEA_P(result);
}

/*
 * Write PEM format.
 */
PG_FUNCTION_INFO_V1(rsa_out);

Datum rsa_out(PG_FUNCTION_ARGS) {
    bytea *raw;
    char *result;
    RSA *rsa;

    // write RSA keypair into buffer
    raw = PG_GETARG_BYTEA_P(0);
    rsa = rsa_from_bytea(raw);
    result = rsa_to_string(rsa);
    RSA_free(rsa);

    PG_RETURN_CSTRING(result);
}

I didn’t check for a NULL value since the user-defined function was declared STRICT and it clutters the code. In practice it won’t do much harm to always check for a null value. The main takeaway is that I create an OpenSSL object, use it, and then immediately discard it.

This public methods use four static convenience methods:

/*
 * Convert string to RSA.
 */
static RSA * rsa_from_string(const char *txt) {
    BIO *inp;
    RSA *rsa = RSA_new();

    inp = BIO_new_mem_buf((char *) txt, strlen(txt));
    PEM_read_bio_RSAPrivateKey(inp, &rsa, 0, NULL);
    BIO_free(inp);

    return rsa;
}

/*
 * Convert bytea to RSA.
 */
static RSA * rsa_from_bytea(const bytea *raw) {
    BIO *bio;
    RSA *rsa;

    // convert into RSA keypair
    bio = BIO_new_mem_buf(VARDATA(raw), VARSIZE(raw) - VARHDRSZ);
    BIO_set_close(bio, BIO_NOCLOSE);
    rsa = RSA_new();
    d2i_RSAPrivateKey_bio(bio, &rsa);
    BIO_free(bio);

    if (rsa == NULL) {
        ereport(ERROR,
            (errcode(ERRCODE_DATA_CORRUPTED), errmsg(
                "unable to decode RSA keypair record")));
    }

    return rsa;
}

/*
 * Convert RSA to string.
 */
static char * rsa_to_string(const RSA *rsa) {
    BIO *bio;
    int len;
    char *ptr, *result;

    // write RSA keypair into buffer
    // arguments: ..., cipher, keyptr, keylen, passwd_cb, passwd_cb_data
    bio = BIO_new(BIO_s_mem());
    PEM_write_bio_RSAPrivateKey(bio, (RSA *) rsa, NULL, NULL, 0, NULL, NULL);

    // create results.
    len = BIO_number_written(bio);
    BIO_get_mem_data(bio, &ptr);
    result = palloc(len + 1);
    strncpy(result, ptr, len);
    result[len] = '';
    BIO_free(bio);

    return result;
}

/*
 * Convert RSA to bytea.
 */
static bytea * rsa_to_bytea(const RSA *rsa) {
    BIO *bio;
    int len;
    bytea *result;
    char *ptr;

    // write RSA keypair into buffer
    bio = BIO_new(BIO_s_mem());
    i2d_RSAPrivateKey_bio(bio, (RSA *) rsa);

    // create bytea results.
    len = BIO_number_written(bio);
    BIO_get_mem_data(bio, &ptr);
    result = (bytea *) palloc(len + VARHDRSZ);
    memcpy(VARDATA(result), ptr, len);
    SET_VARSIZE(result, len + VARHDRSZ);
    BIO_free(bio);

    return result;
}

The bytea is a PostgreSQL type that contains a variable amount of memory. The first four bytes (VARHDRSZ) are the length, and the data itself is accessed through a convenience macro (VARDATA). Since I’m only storing a single value I’m using the macros directly, more complex objects could define a type and cast it to a bytea object.

This code may be baffling if you’re not familiar with the OpenSSL library but there are two simple observations that will make it a lot clearer. First, all I/O is handled through a “Basic Input/Output (BIO)” abstraction. It’s a pain at first but it allows you to stack data manipulations such as compression and encryption.

Second, objects are read and written using d2i and i2d functions. These are “DER to internal” and “internal to DER”, respectively. PEM and DER formats are identical – PEM is base-64 encoded and has a brief comment describing the contents but there’s no additional information.

The rest you get from experience, man pages and googling for examples.

Generating a new keypair

Onward to the interesting user-defined functions. First up is generating a new keypair. This is not something you’ll do very often but it’s very convenient during development and testing since it’s extremely fast to generate 256-bit RSA keys.

The public method just makes sure we have valid and sane values. A warning is printed if a small key is requested but it’s not prohibited. This would be easy to do – use ERROR instead of INFO.

/**
 * Generate a random keypair
 */
PG_FUNCTION_INFO_V1(rsa_generate_keypair);

Datum rsa_generate_keypair(PG_FUNCTION_ARGS) {
    bytea *result;
    int bits;
    RSA *rsa;

    bits = PG_GETARG_INT32(0);
    if (bits <= 0) {
        bits = 2048;
    }

    if (bits < 2048) {
        // elog(INFO, "RSA keys should be at least 2048 bits.")
        ereport(INFO,
                  (errcode(ERRCODE_CHECK_VIOLATION,
                           errmsg("RSA keys should be at least 2048 bits.")));
    }

    rsa = rsa_generate_keypair_internal(bits);
    result = rsa_to_bytea(rsa);
    RSA_free(rsa);

    // return bytea
    PG_RETURN_BYTEA_P(result);
}

/*
 * actual key generation
 */
RSA * rsa_generate_keypair_internal(int bits) {
    BIGNUM *ep;
    RSA *rsa;

    rsa = RSA_new();
    ep = BN_new();
    BN_dec2bn(&ep, "65537");
    RSA_generate_key_ex(rsa, bits, ep, NULL);
    BN_free(ep);

    return rsa;
}

The key uses the standard exponent – 65537 (0x10001) – since there’s little value in making it configurable.

Retrieving keypair details

Retrieving keypair details is a bit more complicated and you will definitely want to read the PostgreSQL documentation while looking at this code.

/**
 * Get details about an RSA keypair
 */
PG_FUNCTION_INFO_V1( rsa_get_details);

Datum rsa_get_details( PG_FUNCTION_ARGS) {
    bytea *raw;
    RSA *rsa;
    TupleDesc desc;
    HeapTuple tuple;
    Datum *values;
    bool *retNulls;

    // check for null value.
    raw = PG_GETARG_BYTEA_P(0);
    if (raw == NULL || VARSIZE(raw) == VARHDRSZ) {
        PG_RETURN_NULL();
    }

    // read keypair, verify success.
    rsa = rsa_from_bytea(raw);
    if (rsa == NULL) {
        ereport(ERROR,
                (errcode(ERRCODE_DATA_CORRUPTED), errmsg(
                         "unable to decode RSA keypair record")));
        PG_RETURN_NULL();
    }

    // read details about return value.
    if (get_call_result_type(fcinfo, NULL, &desc) != TYPEFUNC_COMPOSITE) {
        RSA_free(rsa);
        ereport(ERROR,
                (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg(
                        "function returning record called in context "
                                "that cannot accept type record")));
    }
    desc = BlessTupleDesc(desc);

    // these values are freed by PostgreSQL
    values = (Datum *) palloc(6 * sizeof(Datum));
    retNulls = (bool *) palloc(6 * sizeof(bool));

    // set return values
    values[0] = Int32GetDatum(8 * RSA_size(rsa));
    retNulls[0] = false;

    if (rsa->n == NULL) {
        retNulls[1] = true;
    } else {
        retNulls[1] = false;
        values[1] = BnGetDatum(rsa->n);
    }

    if (rsa->e == NULL) {
        retNulls[2] = true;
    } else {
        retNulls[2] = false;
        values[2] = BnGetDatum(rsa->e);
    }

    if (rsa->d == NULL) {
        retNulls[3] = true;
    } else {
        retNulls[3] = false;
        values[3] = BnGetDatum(rsa->d);
    }

    if (rsa->p == NULL) {
        retNulls[4] = true;
    } else {
        retNulls[4] = false;
        values[4] = BnGetDatum(rsa->p);
    }

    if (rsa->q == NULL) {
        retNulls[5] = true;
    } else {
        retNulls[5] = false;
        values[5] = BnGetDatum(rsa->q);
    }

    RSA_free(rsa);

    // convert to tuple.
    tuple = heap_form_tuple(desc, values, retNulls);
    FreeTupleDesc(desc);

    // return datum.
    PG_RETURN_DATUM(HeapTupleGetDatum(tuple));
}

It’s a long function with unfamiliar calls but it follows the usual pattern. Read data, verify it, do something with it, prepare results, return results.

This function refers to the “BN” UDT. I don’t include that here but it is a wrapper for the OpenSSL “BIGNUM” type. If you’re interested the details are at my git repository.

The results are what we expect.

bgiles=# select * from sample;
                                                                                                                       details                                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (256,97602190650652191344500377647616852664474030847991993174417850011862837141417,65535,49816062791999163016436913587169239410285445043144541244445777903161695571583,331128053999826595053108455708184431513,294756634092692440982306957700237950609)
(1 row)

bgiles=# select (details).bits from sample;
 bits 
------
  256
(1 row)

bgiles=# select (details).e from sample;
   e   
-------
 65535
(1 row)

bgiles=# select (details).d from sample;
                                       d                                       
-------------------------------------------------------------------------------
 49816062791999163016436913587169239410285445043144541244445777903161695571583
(1 row)

bgiles=# 

Source code and other resources

The source code shown above is available at https//github.com/beargiles/pgopenssltypes. This is very much a work in progress but I’m posting it since this idea has been on my blog backlog for over a year and I’ll soon be pulled to other tasks.

Here’s a good article from 2007: http://linuxgazette.net/142/peterson.html.

Many experienced database developers are familiar with user-defined functions implemented in SQL or a procedural language. PostgreSQL itself supports PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, and has third-party support for PL/sh, PL/Java, PL/Ruby, PL/PHP and even PL/R.

Recent versions of PostgreSQL also support SQL/MED (foreign data) with foreign data wrappers (FDW). This allows the database user to access foreign data sources – other RDMSes such as Oracle or MySQL, noSQL databases, even services such as twitter, as though they were PostgreSQL tables. Obviously there can be performance issues but if an architect only needs to perform a simple task, e.g., sending a tweet when certain conditions are met, it may be easier to use a FDW and database query than to incorporate native twitter support.

Sometimes this is not enough. We may wish to add support for a new procedural language or foreign data wrapper. We may wish to perform work involving external libraries on the database server instead of the application server for efficiency. We may wish to hide implementation details from the database user.

A subset of the last item is management of cryptographic material. Most webapps manage the cryptographic material themselves but proper key management is difficult. For instance encryption keys should be rotated on a regular basis but many webapps have never changed their database keys because there is no provision for it. Their only option is to shut down the application, run an application that loads every record, decrypts it, reencrypts it, and then updates each record. Key management on the database side is still a difficult problem but the solutions are far more likely to be reusable.

PostgreSQL Extensions

It is possible to create user-defined functions in a sql script:

CREATE OR REPLACE FUNCTION dgst_sha1(text)
RETURNS text
AS 'pgopenssltypes', 'dgst_sha1'
LANGUAGE C IMMUTABLE STRICT

and provide the implementation by dropping the appropriate shared library in the $libdir directory. See C-Language Functions for details, or my own Introduction to PL/Java for examples of the SQL definitions for functions, triggers, operators, and indexes.

This is unmaintainable for anything other than the smallest tasks. We want to bundle everything we need into one object that we can load and unload it in a single operation. This ensures there are no oversights or inconsistencies. PostgreSQL already has a solution to this problem: PostgreSQL Extensions.

PostgreSQL Extension Network (PGXN)

Writing a PostgreSQL extension from scratch can be tricky. A better approach is to create a skeleton using the PGXN utils package and then install it with the pgxn-client utility from the PostgreSQL Extension Network.

Source Code

I am not including any source code since there are numerous projects listed at the PGXN site. For instance semver (semantic version data type, git or pgaudit. As always all public code should be viewed critically – you don’t want to learn the wrong lessons by reading bad code. (Do not take my reference to these projects as an endorsement.)

Note: if you want to use a shared library your Makefile should include the libraries in a SHLIB_LINK declaration. The system will automatically pull in any dependencies when the module is loaded.

See Also

PostgreSQL Functions By Example (slide deck)

HowTo – Create PostgreSQL C library functions

About a year ago I published a number of articles on PL/Java:

• Introduction
• Working with Lists
• Triggers
• User Defined Types
• Operations and Indices

I had always intended to publish the code but never had the time to clean it up for publication – fleshing out the unit tests, adding the copyright and licensing notices, etc.

No longer – I’ve created a googlecode project for my project. At the moment it only has two user-defined types (Rational and Complex) and the unit tests are far from complete but I’ll flesh it out. Check back in… 10 months! 🙂

Google Code Project: PostgreSQL PL/Java examples.

I should point out that there’s a known bug in both UDTs when performing implicit casts. If the first implicit cast is from a string everything works. If the first implicit cast is from an int then all implicit casts are screwed up. I’m following up on this on the pl/java mailing list.

(Sidenote: there’s also a project containing the code I was using in my discussion on digital certificates. It’s much more ambitious and still needs a lot of work but it’s reached the ‘minimally useful’ threshold. Google Code Project: Otter CA.)

This article discusses operators and indexes in PL/Java. Many user-defined types will not need the former but a hash index should be supported for their use in SQL join clauses.

Operators

Operators are normal PL/Java methods that are also marked as operators via the CREATE OPERATOR statement.

Basic arithmetic for rational numbers is supported as