Monday, June 10, 2013

Design of Efficito's PostgreSQL-centric automation environment

One of the challenges of setting up a cloud hosting environment is ensuring that systems are as configured and that the desired state is one which can be rebuilt if there is a problem.  We at Efficito focus on data backups and rebuilding software images to consistent states rather than backing up full images and restoring them.  This helps ensure that in the event of a disaster we can ensure that VMs are restored to a consistent software state with data restored from backup.

Our choice on architecture was guided by the following requirements:

  1. Configuration and building of virtual machines should be subject to automation without human intervention, with full integration of payment frameworks and the like.
  2. Configuration and building of virtual machines should be such that virtual machines can be fully rebuilt in the event of disaster recovery requirements.
  3. Configuration changes should be able to be automated and retriable.
  4. This can be specific to hosted clouds for specific applications (we only host LedgerSMB as an ERP solution).
Further posts will probably cover very small pieces of our system.  The entire system cannot be published here in part because we want to preserve our trade secrets.  This post however just covers some ways we use PostgreSQL as the centerpiece of this environment.

The Basic Structure and Role of PostgreSQL


Our approach is relatively simple.  Data comes in through either an administrative or customer portal, transmitted to a limited API which then goes into our configuration database.  Information can be requests for new virtual machines, configuration changes and the like.  Additionally payment notifications can come in through these interfaces as well.

PostgreSQL is then attached to the configuration system which picks up notifications of needed configuration changes and orchestrates these on the system.  This also allows us to pull information on our service deployments into our financial system for billing purposes (we use LedgerSMB beta versions of 1.4 internally, eating our own dogfood so to speak).

In this regard PostgreSQL acts as an information backplane.  It allows our software to talk to eachother and allows messages to be sent between components with both transitory and permanent information being recorded in the database for later record-keeping (transitory information can be periodically truncated).

Information Flow


The system is still developing with various components coming together.  Nonetheless, the idea is that the customer or administrator enters information into a front-end tool, which, through a limited API, inserts the data into the database.

On database commit, triggers are fired which queue a message for reading by the configuration system.  We use pg_message_queue for this, in part because it supports both NOTIFY and periodic polling and we intend to add much better multiple listener support as we need it.

From there the listener gets information about what portions of the system need to be changed, makes the changes, and on success commits the transaction that dequeued the notification.  On a failure, a system alert is raised  and the system goes on to the next request (the item returned back to the queue for later processing on the next polling cycle).

What this means is that to a large extent this is a hands-off system.  We provide configuration options, customers select them, and once every aspect is running, the customer can control the software configuration of their vm's within limits, but without root access.  We do offer root access but only if the customer is willing to set up the ssl key and certificate (we can't give root access if our wildcard cert is on the vm!).

Saturday, June 8, 2013

Tangent: Design thougths about next gen PKI in today's world

With the revelations of massive surveillance by the NSA on Americans, I have thought a bit about how to securely set up end to end cryptography in order to offer guarantees of security.  This is by no means limited to offering security relative to governments, but includes the fact that organized criminals can mount attacks similar to wiretaps using man in in the middle and other possible attack techniques.

Designing a perfectly secure system is probably not possible.  A determined enough attacker will be able to gain access to any communications given enough effort, resources, and determination.  The goals of the system I am describing however would be to maximize the resources required,  and thus force evesdroppers to focus on only the most valuable targets possible and causing as narrow compromises as possible.  Additionally metadata is to some extent impossible to protect to the same extent content is.

In general SSL is good enough for key negotiation etc. if the system can be made resistant (not perfectly secure) against man in the middle attacks and if authorities can be sufficiently trusted and validated over time.

Most key exchange approaches focus solely on minimizing risk at the moment of key exchange (i.e. reducing synchronic risk).  This approach is different in that it focuses on resistance to exposure over time (reducing diachronic risk) and seeking to provide as much notification of compromised communications as possible.

Such an approach will *not* protect people against government spying in jurisdictions where keys can be demanded via subpoena or even warrant.  However this approach seeks to force authorities to seek the keys from the people under surveillance and not rely on digital surveillance.

In general as much as I dislike SSL/TLS (as I dislike pretty much every attempt to port OSI protocols to TCP/IP) it is well developed and well understood and the security dimensions of the protocol are well documented.  Additionally unlike IPSec, it is appropriate for cases where the data may need to be routed and rerouted among different servers, possibly altering routing destinations.  It is therefore the approach I suggest building upon.

This design is not intended to be entirely anti-government.  Given the amount of cyber-espionage a framework may be of interest to governments trying to assure the security of their own communications.

Vulnerabilities of SSL


SSL has several known vulnerabilities in its design and architecture.  Many of these are the result of basic design tradeoffs in the protocol.  I think that the risk model has shifted (in favor of very large organized crime and surveillance by well funded governments including China and the United States) make the world very different.

The threat model that SSL is designed to address is where you are protecting a large number of low- to mid-value targets against relatively casual or trivial attacks.  As attacks by foreign and domestic governments and organized criminals have become more sophisticated, the basic structure of how we use SSL and TLS has not evolved at the same pace.

The two major vulnerabilities of SSL are:

  • Large, central certificate authorities present single points of attack, and
  • Man in the middle attacks are possible provided that there are no expectations about certificate authorities on both sides.
My proposal below focuses on broadening the threat type against which security is provable.  This is further not inconsistent with existing approaches regarding central certificate authorities, but adds a layer of diachronic protection as well.

I do not believe this is particularly useful for criminal organizations and it poses problems when moving down to the individual level (these can be solved however).  Nonetheless it should give corporations and governments and even individuals an ability to ensure that their communications are not being unlawfully reviewed without their knowledge.

Solution to Third Party CA Vulnerability


Third party CA's are a current vulnerability of the SSL system.  If keys are registered or obtained through a third party, their processes or systems can be attacked to get bogus certificates or keys retrieved sufficient to forge certificates.  Third party CA's thus have to be incredibly secure.

The problem though is that no system is that secure.  When computer viruses have been planted in US drone operation centers, and spear phishing attacks have been successful against some of the most secure US government

My proposal is to divide trust between an external certificate authority and an internal certificate authority, both of which are tracked over time in the key negotiation process.  The external certificate authority's job remains to validate that the certificate is issued to the proper individual or organization (essentially an internet-based notary public), while the internal certificate authority is to identify individuals and services at an organization.  Because of the structure of the internet, and because of current practice, I would recommend keying this to the purchased domain name.

This by itself essentially means that the operational encryption keys are not certified by certificate authorities by rather by an internal tier.  In essence root certificate authorities would issue only certificates certifying things like "this domain is owned by who we issued the certificate to" while subdomains would be a local matter.

This does not interfere with provable security if the following rule is enforced:  Operational certificates for resources at a domain MUST be regarded provably secure ONLY IF they are issued by a certificate authority whose certificate indicates it was issued for the domain in question (and possibly transitively) by a trusted root authority.

Note that this means two things:  An attack on a root ca can no longer reveal keys useful in eavesdropping on key exchange, but it could reveal keys useful for carrying out a man in the middle attack.  It thus increases the effort modestly in carrying out eavesdropping on SSL-protected connections, but this is not much protection given the complexity of the attack needed to make it even an issue.  The real value comes from looking at diachronic protection against man in the middle and here is where the division really comes in valuable.

This is not limited to three levels, but it is worth noting that it would be necessary to check the certificates at each level to make sure that the above resource chain was not broken.  A fourth level might be necessary in scaling down to individual consumers (who typically do not own the domains they send emails from).


Diachronic Protection against the Man in the Middle


The approach mentioned above is chiefly useful because it allows for one to track certificate authorities' and their keys over time relative to a domain.  A sudden change could indicate a man in the middle, and with mutual authentication both sides should get alarms.

I would propose extending the new certificate to require signing with the previous key as well as the current one.  A revoked and re-issued certificate would then be signed both by the certificate authority (as evidence of continuity) and by the parent certificate authority.

This means that you have strong evidence that the certificate was not only issued to someone the certificate authority is apparently willing to vouch for, but also that it was received by the holder of the private key of the previous certificate.  Now in the event that the key is fundamentally lost, and a certificate re-issued, the holder would probably want to say something.

Now, this establishes a timeline of key changes which can be tracked, and what this means is that as keys are issued, timelines diverge.  The key issued by the root CA is no longer sufficient to establish new connections without warning of a diverged timeline, meaning that connections with previously unknown parties can be evesdropped on but those who have been in previous contact will detect that something is wrong and hopefully alert their users to a possible problem.  This provides both sides an opportunity to avoid problems.

Of course an alert may just mean massive loss of information and the previous key was lost so it does not necessarily mean a man in the middle.  However unexplained ones do indicate a problem.

Active Man in the Middle Detection


Additionally this structure should enable us to do man in the middle detection for any real-time bidirectional communication.  This depends on a certificate cache for effectiveness and this allows diachronic tracking of certificates for previously known resources.  However for new connections there is a problem.

One solution is to orchestrate several additional requests for certificates from several other resources one knows about with the legitimate request occurring at a random spot.  The observer in the middle cannot determine which is the legitimate request, and so at first will not, without extensive review first, be able to even guess which ones are unknown.   This would make it particularly  difficult to eavesdrop on volumes of communications.

Additionally once one contact pair is eavesdropped on, removing the eavesdrop triggers a diachronic protection warning.

Scaling Down to the Personal


The big thing that is required for scaling this down is to recognize that an additional tier is required, because individuals often send emails through the domains of their ISP's or email providers.  In this view one would get a personal CA certificate, which would then issue one certificate for each of things like web access, email, etc.

Extensions to X509 Required


The fundamental extension required would be a way of having all relevant internal CA certificates serially in a single format, up to the root CA which could be independently verified.  Additionally there may be some others.  A reasonable overlap time may need to be specified for certificate authorities transitioning certificates to a new certificate.  The determination of reasonable policies for such transition periods is beyond the scope of this proposal, however a temporary change (before reverting to the old cert) would be definitely suspicious.

Additionally, one would need to have a Key Version List, where keys could be listed in sequence for a period of time.  This may need to be added to the certificate structure as an extension.

Limitations


Security here is provable over time only given the following assumptions:

1.  The private key has not been compromised on either end.
2.  Continuity in changes regarding private keys is known and can be shown.

The reliance on unconditional trust of root certificate authorities is reduced, though some reliance is necessary, and the effort needed to mount an attack would be higher.  However the above limitations mean that false positives for security concerns may occur in the case where keys are lost and certificates re-issued, and false negatives in the event where private keys are compromised.

In the event where authorities (in jurisdiction which allow this) subpoena a private key, they can eavesdrop on connections.  Similarly spearphishing could be used to obtain keys by organized crime.  Thus these things are outside the threat model protects against.

However the limitations are narrower and help reduce the risk that certificate authorities face, as well as enabling people to better protect their communications.

Tuesday, June 4, 2013

New ventures and new directions for this blog

I have agreed to help found a LedgerSMB hosting business called Efficito.  We have a very basic web page up (which is in the process of further development) but we intend to offer hosting specifically of LedgerSMB 1.3 and higher to customers who want it.  If there is interest, please feel free to email me.

One thing we have committed to do is to leverage PostgreSQL for the overall management of our hosting services.  We intend to use the database as a point of original entry, allowing other processes to enact changes saved in the database.  This is something very different from a standard 3-Tier architecture in that the entry of data into the database triggers real-world side-effects in a distributed environment.

Obviously I cannot disclose our full codebase to the world, but I expect to be covering a number of aspects of how we will use PostgreSQL, and include small snippets of past or current versions of the code in these posts.  The examples will be naturally minimalistic and lack the context of the full system, but they should be interesting from a question of how one works with PostgreSQL in an automated environment and a number of other topics.

In essence what started off as a blog about LedgerSMB, which has expanded to include object-relational design generally, will now be expanded yet again to include database-centric automation.

I expect to cover  a few pieces in the near future including cross-type referential integrity where inet addresses match cidr blocks, and the use of ip4r to construct exclusion constraints for network ranges in table types.  The first post is probably more generally useful because understanding how to manually do referential integrity can help solve broader problems, like referential integrity over inheritance trees.

Anyway I hope you enjoy some of the newer areas of focus as well.

Monday, May 27, 2013

Introducing the CPAN PGObject namespace

So I have decided to begin work on a set of CPAN modules, essentially implementing the stored procedure interface work that I have done for LedgerSMB, and incorporating the lessons learned by myself and others.  It is my hope that Perl programmers will find this useful in building sophisticated applications on PostgreSQL.  The idea is to turn PostgreSQL into an encapsulated database sever with a discoverable API, and then allow programs to discover the API directly.  In my experience this leads to more robust code because code contracts between stored procedures and applications are more readily enforcible.

I just got preliminary versions of two of them on CPAN.  The first is PGObject which implements the database-facing routines, and PGObject::Simple which is developer-facing.  The reason for breaking things off this way is to allow for multiple different developer-facing systems off the same backend-facing services.  These are functional, but ones with a great deal more functionality are on their way.

I feel a need to justify "Simple" in the name of the latter.  PGObject::Simple is a quick and dirty stored procedure mapping convention system based on our approach in LedgerSMB.  It is not reduced in functionality, but the overall conventions are simple, and therefore require less overhead to use.  This also means, however, that the flexibility is such that it requires a certain discipline to use effectively.  Objects can quickly become amorphous but used with discipline it is a very powerful system.

For additional structure, PGObject::Simple::Role is under development which creates a Moo role, which can be used with or without Moose, to provide this sort of functionality in more formalized object systems.

These modules are designed to facilitate mapping of stored procedure functionality into applications, allowing PostgreSQL databases to become encapsulated intelligent information servers in their own right.  In future posts I will be going over both of these in detail.

For now though start with the documentation in the links above.

Wednesday, May 1, 2013

Arrays and Indexing, problems and solutions

Arrays in PostgreSQL are relatively well supported by the indexing engines of this RDBMS.  There are a fair number of options however to be aware of and an understanding of how arrays are indexed is very helpful.  Before going forward, please read my intro on arrays so that you understand the basics.

I am leaving out hash indexes which are not very useful for arrays (or much of anything else).  There are probably cases where hash indexes are useful but if you need them you will know....

BTREE Indexing for Ordinality-centric arrays


The default type of index used by PostgreSQL is a btree index, which provides a search tree using only three operators: <, =, >.  Ordering and ordinality of arrays here is quite important and btree indexes are essentially useless when indexing arrays where ordinality is unimportant.

One should remember that btree indexes are useful primarily in ordering based searches.  The following could use a btree index (and might depending on table statistics):

  • SELECT id FROM mytable WHERE myarray < '{0,2,1,2,3}';
  • SELECT id FROM mytable WHERE myarray = '{0,6,5,4,3}';
In theory a prefix search might be possible if such an operator existed and appropriate work was done on the back-end but this is not currently possible without using functional index.  Again in theory the following might work, but GIN indexes would be better for reasons that will become clear.

  • SELECT id FROM mytable WHERE myarray[1] = '5';

The following cannot work with a btree index under any circumstances:

  • SELECT id FROM mytable WHERE myarray[2] = 5;
 The reason this cannot work is because while myarray[1] would correspond to something we could search the index on, myarray[2] would not be exposed to the tree view of the index until we already know myarray[1].  Thus we cannot traverse a tree on the basis of  array elements beyond the first.

Btree indexes may be useful in some cases with arrays, particularly where ordinality is very important to the search.  However, they are entirely useless where ordinality is unimportant.

GiST and GIN indexing


PostgreSQL provides two more general indexing opportunities that work for more general (and non-1NF uses) of arrays than do Btree indexes.

In general the tradeoff is that GIN indexes are more costly to maintain and take up more disk space but are less costly to  query.   As the read to write ratio goes up, GiST indexes start looking more desirable and GIN indexes start looking less desirable.

These indexes are helpful because they suppose a wider range of searches than do BTree indexes.  In particular you can do overlap searches (select * from pages where tags && array['PostgreSQL']), and much more.  GiST and GIN indexes also support other operators.  Also it is worth noting that GiST indexes are originally designed for geometric and geospacial searches, while GIN indexes are designed for arrays.

The supported GiST operators are:
  • <<  Strictly left of
  • &< Extends to the left of
  • &> Extends to the right of
  • >> Strictly right of
  • <<| Strictly Below
  • &<| Does not extend above
  • |&> Does not extend below
  • |>> Strictly above
  • @> Contains
  • <@ Contained in
  • ~=  Is the same as (identical to)
  • && Overlaps
Now this supports a significant superset of GIN index functionality (see below) but it does not really necessarily apply to arrays in a straightforward way.    In general GIN indexing is a better match, but it is worth noting you could easily write functions that could be used to check some of these index conditions and add that functionality if you need it.    In general, however, this is an advanced topic and well beyond the scope of this post.   If you ever need to compare two arrays though and see which has the highest single, or the largest dimensions, or some other objective metric, you might be able to hook this into GiST indexing.

The supported GIN operators are:
  • <@    Is contained in
  •  @> Contains
  •  =     equals
  • &&  Overlaps
 These are generally particularly useful for arrays, and since they are lossless, they are significantly faster for lookups.  For arrays these are preferred most of the time if you are looking primarily for matching specific elements in the array.

On the whole the two most important indexes depending on what you are doing with the array are btree and GIN indexes.  Btrees cover well the case where ordinality matters and the search is primarily on the beginning of the array.  GIN indexes cover more general (and in particular non-1NF setups) relatively well.  GiST indexes seem nice, but on the whole they do not offer a good performance/functionality tradeoff out of the box.  They would be powerful tools however for the advanced developer.

Monday, April 29, 2013

Introduction to PostgreSQL arrays, basic concepts and normalization considerations

This is part of a two part series.  The second will discuss indexing arrays which is a complex topic.

PostgreSQL has very good support for arrays of other data types.  One can have nested data types where an attribute of a relation stores an array of tuples, and each of these tuples can hold arrays of other tuples or primitives.

Arrays are extremely powerful in PostgreSQL but they pose a number of significant issues as well.  They can be used in ways which break first normal form but mere use of an array does not necessarily break first normal form's atomicity requirement because arrays differ in significant fundamental ways from relations.  Understanding arrays in PostgreSQL can help avoid costly errors.  Relations and arrays are subject to fundamentally different constraints and therefore are equivalent in only a few very special cases.

Arrays derive their power in PostgreSQL due to the fact that they have strong definitional requirements.

An Array is a Mathematical Matrix


PostgreSQL arrays follow the basic definition of a mathematical matrix.  The array must be rectangular so if one element has an array in it, all other elements must have an array of the same dimensions.   All members of an array must be of the same data types.

The following are thus valid Integer arrays:

  • '{1,2,3,4,5}'
  • '{{1,2},{3,4},{5,6},{7,8},{9,10}}'

The following are not valid integer arrays:

  • '{1,2,3,{4, 5}}'
  • '{{1,2},{3,4},{5}}'
These constraints make arrays particularly useful in PostgreSQL.  One can use text arrays to represent sets of tuples being input into stored procedures for example.  One can also write functions to do matrix math on numerical arrays (we will look at an example below).  If PostgreSQL lacked true arbitrary precision math via the numeric type, we could implement something similar using arrays.  These checks all make arrays safe to work with in ways that most programming languages do not.

Arrays vs Relations vs Tuples (and First Normal Form)


Arrays bear some relationship to relations and tuples, but all three are different in significant ways which means they are best at solving different problems.

Like tuples, arrays are ordered.  However:

  • Unlike tuples every array element must have the same data type.  This means that arrays of text can represent tuples of any type.  
  • Unlike tuples, arrays do not have a fixed number of elements.  Elements can be added without disturbing the basic type.
Like arrays, relations are basically rectangular, and open ended (items can be added or removed from the end).  However:

  • Arrays are ordered, relations are not.  This means that an array value is a domain, while a relation value is a set or bag of domains (depending on constraints).
  • All data types in an array must be identical.  Relations do not have this restriction.
What this means basically is that the mere use of an array in a column of a table does not necessarily violate 1NF, if the array is used in a way that suggests that ordinality matters.

For example we could store matrices for matrix arithmetic as numeric[] arrays.  We could store some problems that are transformed into numeric[] matrices into the db as well (for example simultaneous linear equations) and these would not necessarily violate 1NF.

Example:  Solving Simultaneous Linear Equations in PL/PGSQL


I will leave understanding this as an exercise to the reader.  It is, however, a small example.  This is a very basic program I originally wrote in BASIC on my C64 when I was younger as a homework solving program, and have since written a number other incarnations as a demonstration.  it solves simultaneous linear equations by arraying them in matrix and reducing the matrix accordingly.  The program will only accept an n by n+1 matrix, and then will calculate.

This example is useful because it shows that anything you can solve through matrix math you can solve using PostgreSQL arrays....

 create or replace function solve_linear(numeric[]) returns numeric[]
language plpgsql as
$$
declare retval numeric[];
        c_outer int; -- counters
        c_inner int;
        c_working int;
        upper1 int;
        upper2 int;
        ratio numeric; --caching of calculation
begin
    IF array_upper($1, 1) <> array_upper($1, 2) - 1 THEN
        RAISE EXCEPTION 'bad input, must be n x n+1 matrix';
    END IF;
    upper1 := array_upper($1, 1);
    upper2 := array_upper($1, 2);
    FOR c_outer IN 1 .. upper1 LOOP
        FOR c_inner IN 1 .. upper1 LOOP
            IF c_inner = c_outer THEN CONTINUE;
            END IF;
            ratio := $1[c_inner][c_outer]/$1[c_outer][c_outer];
            for c_working in 1 .. upper2 loop
                $1[c_inner][c_working] := $1[c_inner][c_working]
                                    - ($1[c_outer][c_working] * ratio);
            end loop;
        end loop;
    end loop;
    retval := '{}';
    for c_outer in 1 .. array_upper($1,1) loop
       retval := retval || $1[c_outer][array_upper($1,1) + 1]/ $1[c_outer][c_outer];
    end loop;
    return retval;
end;
$$;


As an example:

 select * from solve_linear('{{1,2,3},{2,3,4}}'::numeric[]);
                       solve_linear                      
----------------------------------------------------------
 {-1.00000000000000000000000000000000,2.0000000000000000}
(1 row)

In other words, if we have 1x + 2y = 3 and 2x + 3y = 4, then these will intersect where x is -1 and y is 2.

Yes, it is order independent.

 select * from solve_linear('{{2,3,4},{1,2,3}}'::numeric[]);
                             solve_linear                            
----------------------------------------------------------------------
 {-1.0000000000000000000000000000000000000000,2.00000000000000000000}
(1 row)

However, ordering within a subarray is important:

select * from solve_linear('{{2,4,3},{1,3,2}}'::numeric[]);
                            solve_linear                            
---------------------------------------------------------------------
 {0.5000000000000000000000000000000000000000,0.50000000000000000000}
(1 row)


One minor issue with the above code is that it throws a division by 0 if the lines are parallel:

select * from solve_linear('{{1,2,3},{1,2,6}}'::numeric[]);
ERROR:  division by zero
CONTEXT:  PL/pgSQL function "solve_linear" line 19 at assignment

Consequently error handling could be improved, but it isn't too bad for a demonstration.

As it turns out this is a fairly generalizable class of problems and may be particularly helpful when doing geometric math.

I hope this helps show some of the uses and value of PostgreSQL array types.  In the next post we will talk about arrays and indexes.....

Wednesday, April 24, 2013

A few cases where EAV modelling really works in LedgerSMB.

Entity-attribute-value (EAV) modelling is typically seen as an antipattern and for good reasons.  Misapplied it leads to inflexible queries, poor performance and a number of other bad things.  Nonetheless there are classes of problems where EAV is the right solution for the problem at hand.  Two specific areas of LedgerSMB will demonstrate this, namely account/dropdown management and the menu schema.  These actually represent different classes of problems but they have a number of things in common.  The goal of this post is to help readers better understand where EAV modelling can be of great help.

In general, the areas of LedgerSMB which use EAV modelling are stable, rarely changing data with relatively simple to non-existent validation routines (the menu is expected to be externally validated).   In some cases the basic expectations of EAV are modified slightly.  In both these cases (which represent relatively different classes of problems) we have been very happy with EAV and while our approach will no doubt be refined in the future, it is unlikely that these portions of the software will ever move from EAV.

Note the LedgerSMB development team does not advocate starting with EAV for most problems.  Usually where EAV is appropriate it will naturally emerge in the schema design process.

What is EAV?


EAV is a way to model relatively free-form data in such a way as to dispense with the need for extremely complex, long tables for specifying sparse data.  A typical EAV approach has three basic tables representing entity data, attributes, and value data.

To make EAV work successfully, as much data as possible should be put in the entity table or parent/child tables.  It's only where this approach breaks down that EAV is a good idea.  The examples below will flesh this out more.  However  a basic typical design might look like:

CREATE TABLE entity (
    id serial not null unique,
    label text primary key,
    ....
);

CREATE TABLE attribute (
   id serial not null unique,
   att_name text not null,
   att_datatype text not null,
   ....
);

CREATE TABLE value_table (
   id bigserial not null unique,
   entity_id int not null references entity(id),
   attribute_id int not null references attribute(id),
   value text not null
);

In portions below we well explore what is right and what is wrong with this approach.  This post is hardly a definitive guide to the subject but it is hoped it is a step towards exploring this area.

Why EAV?


EAV dispenses with the requirement for rigid schemas.  Particularly in cases where data validation is not complex or not demanded, EAV can solve problems that standard row-oriented models cannot readily solve.  Some classes of problems (tags to social media for example) fall readily in a modified EAV model.

In essence EAV can sometimes(!) be a useful approach to use when the data does not fit well into other models.  Unfortunately when it is grabbed for too quickly bad things result, but this does not prevent it from being useful in cases where it is at home.

Why Not EAV?


EAV, improperly applied, leads to a significant number of problems.  Improperly applied, it leads to an inability to declaratively validate data within the schema of the database itself beyond very simple rules.  Improperly applied it leads to performance problems, and basic data management problems.

In general my own view is that EAV is best kept as a tool to solve very specific problems, and that most other problems are better solved by careful table design.

Modified EAV in the Account/Dropdown Management Schema


In LedgerSMB chart of accounts are attached to drop downs.  The schema looks like:

CREATE TABLE account (
  id serial not null unique,
  accno text primary key,
  description text,
  category CHAR(1) NOT NULL,
  gifi_accno text,
  heading int not null references account_heading(id),
  contra bool not null default false,
  tax bool not null default false
);



CREATE TABLE account_link_description (
    description text    primary key,
    summary     boolean not null,
    custom      boolean not null
);


CREATE TABLE account_link (
   account_id int references account(id),
   description text references account_link_description(description),
   primary key (account_id, description)
);


Now what is obviously missing here in an EAV perspective are values.  We have entities, and attributes, but the presence of the attribute itself is the value.  It would be like EAV with merely boolean values.

The architecture poses all the classic issues one has with EAV.  There are rules regarding what combinations regarding which combinations of drop-downs are invalid.  These are not complex rules but they could change over time.  Currently if an account_link is a summary account, then it is only allowed a single drop down link, so no others can exist.  Custom accounts are not validated and can break these rules.

Currently validation occurs via a stored procedure which encodes the rules in its own logic.

Normally the tables involved here are small.  A typical business will have no more than a few dozen account entries, and maybe a hundred or two account_link entries, and there are only 27 account link descriptions.  However larger businesses could have thousands of accounts and tens of thousands of account_link entries.

The approach here scales well because there are only really three typical search patterns. 

The first is a search of accounts for a specific link description (dropdown box).  This will usually pull only a portion of the accounts and can be managed through indexes.

The second is a search of account_links for a given account.  This is trivial and usually no more than about 20 items.

Finally we may pull all entries from all tables, and join/aggregate.  This is not going to require multiple table scans, comparing results, in order to determine the appropriate rows to return.

One thing that is important to note is that chart of accounts data is pretty stable.  It may change a few records over time, but radical changes or mass updates are very rare.  Thus we can sacrifice some immediate performance over the lon run.
 

Lightweight EAV in the menu definition schema


Our menu schema looks like this:

CREATE TABLE menu_node (
    id serial NOT NULL,
    label character varying NOT NULL,
    parent integer,
    "position" integer NOT NULL
);


CREATE TABLE menu_attribute (
    node_id integer NOT NULL references menu_node(id),
    attribute character varying NOT NULL,
    value character varying NOT NULL,
    id serial NOT NULL,
    primary key(node_id, attribute)
);


CREATE TABLE menu_acl (
    id serial NOT NULL,
    role_name character varying,
    acl_type character varying,
    node_id integer,
    CONSTRAINT menu_acl_acl_type_check CHECK ((((acl_type)::text = 'allow'::text) OR ((acl_type)::text = 'deny'::text))),
    PRIMARY KEY (node_id, role_name)
);


What is omitted here is the attribute table.  In essence our menu model has no data validation and the data is extremely stable.  It is expected that the developer validates against the application.

The tables here are relatively small.  By default a couple hundred rows in menu_node and nearly a thousand in menu_attribute.  This structure stretches what relational db's will let you do gracefully since menu_node represents a tree hierarchy, and the rest establishes a very open (unvalidated) EAV model.

Access patterns here are typically once per login, pulling all data from these tables (two scans, one for permissions), and then aggregating the attributes such that we get enough information to create a menu structure (<ul> tags with hyperlinks inside <li> tags).

Menu items are usually only created once and are extraordinarily stable. 

Conclusions


These use cases have a number of striking things in common.  The access patterns are simple, as are the in-db data validation rules (if they exist at all).  The data is rarely written.

The second point is something higher level that these cases have in common.  They represent a way of the application storing its own API in the database.  In other words they represent a way of storing application configuration information, or hooks or references to application logic (though not the logic itself).  These areas are semantically very flexible but typically the data is long-lived once everything is set up.