One of the key features of LedgerSMB 1.3 has been the move from the custom templating solution of LedgerSMB 1.2 (inherited from SQL-Ledger) to Template Toolkit. This gives us more flexibility, better cross-language support since TT has been implemented for other languages as well, and the like. However in practice in order to preserve these and avoid too close of a merging between business and presentation logic, we avoid some functions of TT, such as embedded Perl. We also make have some specific approaches to how we invoke TT in order to avoid problems and help ensure standards compliance to the best of our ability.
The big problem that one runs into is the use of percents, which denote comments in LaTeX. Leaving the percent signs in leads to parsing problems when testing templates. So it became clear that since we wanted to re-use the same template logic for both UI and printed invoices the percent signs would have to go.
We settled on opening the tag with <?lsmb and closing it with ?> The reason here is that this re-uses the SGML-standard "program instruction" tag syntax to ensure that validation can be run on the templates themselves.
A second thing we did was to add some template-specific functions. These handle internationalization and number formatting. Each of these has two forms: a general form specific to the language or preferences of the template and a non-specific form, where the localization settings must be specified in the template. This provides a consistent templating API for presentation of data, without mixing object behavior with the template layer itself.
The reason for these two forms is that there may be cases where number formatting must be specified in the template (such as when a check is printed), but there are others where user-specific settings should be used. Similarly we want to specify a primary language with a template, but might want to translate strings into a second language (for example for bilingual receipts).
This gives us a general presentation API largely based on TT, but with differences which enable us to do whatever we need to do. We had to rip out the old pagebreak handling and go instead to LaTeX longtables instead, which is probably a win anyway.
However one surprising use has come up, and that has been the ability to re-use the templating system in preparing database upgrade scripts which are then passed along to psql.
Sunday, December 18, 2011
Saturday, December 3, 2011
MYGOSSCON Observations, and Notes on Open Source in South-East Asia
This week, I attended the Malaysian Government Open Source Software Convention (MYGOSSCON), promoting PostgreSQL and LedgerSMB to both public and private sector entities. We had a simple booth and had approx 100 people stop by over the course of the two days. We also talked with a wide range of other exhibitors and came away with a sense that we can expect great things in South-East Asia regarding Open Source.
It was immediately clear that PostgreSQL was a star technology at the conference, with a strong showing by EnterpriseDB and EnterpriseDB resellers. We were the only project specializing in the "community edition" as EnterpriseDB calls it. We had a lot of people stop by to talk about PostgreSQL and share success stories, answering questions about the differences between Oracle and PostgreSQL, between PostgreSQL and MySQL, and between EnterpriseDB and the official release version of PostgreSQL (I refuse to call it the "community version"). I was told that there would be few who knew databases, but while there were only a few database experts at the conference (which was to be expected) there were a lot of people who had a solid working knowledge of database systems generally and had lots of well-thought-out and valid questions.
Indeed people would see the other signs at our booth and keep walking... until they saw the big yellow banner with the blue, white, and black PostgreSQL logo on it and then walk right over. We got questions that I knew the answer to, questions where my answers were slightly out of date (areas of Pg vs Oracle), and questions which I had not really prepared for at all (asking about Arabic support for PostgreSQL). Unfortunately with the internet connections overloaded at the conference, I had to do the best I could, and promise follow-up as necessary.
One typically goes to trade shows expecting to build awareness for one's products and services with the idea that this will grow the market later. In this case, however, we walked away with companies interested in hosting LedgerSMB, possible maintainers for other language translations, and a couple of actual, actionable sales leads. We were extremely happy with the way things went.
However in addition to talking with civil servants and educators, an important undercurrent emerged in my discussions with purely private sector individuals (often other exhibitors): open source is rapidly growing here in South-East Asia, particularly in the public sector. The major limiting factor appears to be supply if individuals with relevant expertise and experience. This is not to say that such people aren't available, but rather that demand significantly exceeds supply which is common in growing markets. This isn't just the sorts of things we experienced first hand, caused by entities trying to reduce the amount that they spend primarily to Oracle, but instead part of a much larger trend, with governments in the region.
As I typically try to point out when advocating open source, the decision to prioritize open source isn't just about money, but rather the differences in dynamics surrounding money lead to a number of important benefits including a larger percentage of the money to launch a project going into the local economy, and the added flexibility that occurs when one does not need to worry about acquisition of software licenses in order to put data to a new use. This leads to local power both economically and in terms of decision-making. According to other consultants, this is not limited to the Malaysian government and other governments in the region are increasing their commitment to open source software.
All around South-East Asia, my sense is that there is a sense that open source will be a major economic asset to the region and something to be cultivated. It is a good time to be in open source over here, but there has never been a better time (and probably never will be a better time) to be in open source databases. PostgreSQL is, in particular, poised to start penetrating Oracle's home market, with projects like Gridsql and Postgres-xc reaching maturity. I believe we are to the point where no corner of the market is safe any more, and where the adoption of an open source lifestyle for businesses and governments will become the norm rather than the exception.
I think that last sentence follows quite directly from open source database systems coming of age. The large, multi-application database systems (DB2, Oracle, MS SQL, Terradata) represent the last major piece of IT infrastructure for which open source solutions were inadequate. I say multi-application to differentiate from the "app-stack" approaches familiar to LAMP developers. Typically here we are talking about heavily engineered databases designed to have a significant number of applications solving different issues, manipulating their data. However, as this piece of infrastructure begins to fall to open source, then all the back-end software can be open source or in-house. At this point, one has to ask whether one should simply bite the bullet and start migrating the front-end (desktop/laptop) devices as well. Pushing Linux-based desktops in government and business would almost certainly have a cascading effect to consumer systems as well.
These are exciting times indeed.
It was immediately clear that PostgreSQL was a star technology at the conference, with a strong showing by EnterpriseDB and EnterpriseDB resellers. We were the only project specializing in the "community edition" as EnterpriseDB calls it. We had a lot of people stop by to talk about PostgreSQL and share success stories, answering questions about the differences between Oracle and PostgreSQL, between PostgreSQL and MySQL, and between EnterpriseDB and the official release version of PostgreSQL (I refuse to call it the "community version"). I was told that there would be few who knew databases, but while there were only a few database experts at the conference (which was to be expected) there were a lot of people who had a solid working knowledge of database systems generally and had lots of well-thought-out and valid questions.
Indeed people would see the other signs at our booth and keep walking... until they saw the big yellow banner with the blue, white, and black PostgreSQL logo on it and then walk right over. We got questions that I knew the answer to, questions where my answers were slightly out of date (areas of Pg vs Oracle), and questions which I had not really prepared for at all (asking about Arabic support for PostgreSQL). Unfortunately with the internet connections overloaded at the conference, I had to do the best I could, and promise follow-up as necessary.
One typically goes to trade shows expecting to build awareness for one's products and services with the idea that this will grow the market later. In this case, however, we walked away with companies interested in hosting LedgerSMB, possible maintainers for other language translations, and a couple of actual, actionable sales leads. We were extremely happy with the way things went.
However in addition to talking with civil servants and educators, an important undercurrent emerged in my discussions with purely private sector individuals (often other exhibitors): open source is rapidly growing here in South-East Asia, particularly in the public sector. The major limiting factor appears to be supply if individuals with relevant expertise and experience. This is not to say that such people aren't available, but rather that demand significantly exceeds supply which is common in growing markets. This isn't just the sorts of things we experienced first hand, caused by entities trying to reduce the amount that they spend primarily to Oracle, but instead part of a much larger trend, with governments in the region.
As I typically try to point out when advocating open source, the decision to prioritize open source isn't just about money, but rather the differences in dynamics surrounding money lead to a number of important benefits including a larger percentage of the money to launch a project going into the local economy, and the added flexibility that occurs when one does not need to worry about acquisition of software licenses in order to put data to a new use. This leads to local power both economically and in terms of decision-making. According to other consultants, this is not limited to the Malaysian government and other governments in the region are increasing their commitment to open source software.
All around South-East Asia, my sense is that there is a sense that open source will be a major economic asset to the region and something to be cultivated. It is a good time to be in open source over here, but there has never been a better time (and probably never will be a better time) to be in open source databases. PostgreSQL is, in particular, poised to start penetrating Oracle's home market, with projects like Gridsql and Postgres-xc reaching maturity. I believe we are to the point where no corner of the market is safe any more, and where the adoption of an open source lifestyle for businesses and governments will become the norm rather than the exception.
I think that last sentence follows quite directly from open source database systems coming of age. The large, multi-application database systems (DB2, Oracle, MS SQL, Terradata) represent the last major piece of IT infrastructure for which open source solutions were inadequate. I say multi-application to differentiate from the "app-stack" approaches familiar to LAMP developers. Typically here we are talking about heavily engineered databases designed to have a significant number of applications solving different issues, manipulating their data. However, as this piece of infrastructure begins to fall to open source, then all the back-end software can be open source or in-house. At this point, one has to ask whether one should simply bite the bullet and start migrating the front-end (desktop/laptop) devices as well. Pushing Linux-based desktops in government and business would almost certainly have a cascading effect to consumer systems as well.
These are exciting times indeed.
Tuesday, November 22, 2011
LedgerSMB (and PostgreSQL) at MYGOSSCON
Metatron Technology Consulting has rented a booth at the Malaysia Government Open Source Software Convention (MYGOSSCON), 2011. This booth will be used to advocate PostgreSQL and LedgerSMB both to private and public sectors.
Anyone going to this convention should look for us!
Anyone going to this convention should look for us!
Saturday, November 12, 2011
The Case for Intelligent Databases
LedgerSMB, as an ERP and accounting system, has over time begun to move towards an intelligent database design based on PostgreSQL. We believe that this best matches our long-term goals for the project, especially because interoperability is a very important requirement for any ERP system. Read more to see our general case in this area and see what other projects may be able to learn from our experience.
A Short Developer's Definition of Intelligent Databases
An intelligent database is a database encapsulated behind a defined API.
A Longer Description
While most programmers primarily use the database primarily as a place to simply store data, this reduces a database to basically an ACID-compliant data store with some reporting capability. This approach saves the programmer from worrying about advanced database functionality, but it also prevents the database system from being used to do a number of useful tasks that databases can really be leveraged to do.
This post will explore why LedgerSMB is going this direction, and the sorts of functionality (particularly regarding PostgreSQL) that is a part of this plan, what sorts of tradeoffs we see in it, and where we expect to benefit. This is not an article that discusses the details of implementation (see previous posts for that), but rather at a high-level how and why.
The Toolbox
The fundamental idea of encapsulating a database inside a defined API is that usually the application should hit the API and not the underlying relations where the data is stored if at all possible., The API acts as an interface between actual storage/retrieval and the application.
The major aspects of an API can include views and/or stored procedures. We use stored procedures for most things but may include views for some sorts of objects as time goes on.
What do Intelligent Databases Cost?
Many current development environments seek to save the programmer from spending heavy engineering time on a database, particularly in the early stages of a project where requirements can change. The database is relegated to a simple data store with ACID compliance. Frameworks like Rails will even generate a database schema from programming data structures.
If one goes with an intelligent design for the database, one cannot use agile programming methodologies to design the database. Instead the database requires some real engineering in its own right. This takes some time and effort in its own right and can slow down development compared to ORM-based code generators such as we see in Rails.
Such engineering requires taking a clear look at what data is expected to be collected, what internal structure it has, designing the database storage first and foremost around that analysis,and then building an API based on how it will be used. For simple schema development this doesn't take a long time but the complexity does grow with the data collected.
In the LedgerSMB project this is not the end of the world since we are re-engineering the database incrementally and so each major branch includes at least a few areas where old db structures have been pulled out and new, better engineered ones added. These areas then have an API and the application generally hits the API not the relations. Old code areas may hit the API or the relations.
Other projects may find that this adds significant development time beyond what is reasonable, but these must still be weighed against the benefits below.
What benefits do Intelligent Databases Provide?
Intelligent databases offer a number of specific benefits that LedgerSMB tries to capitalize on. The fact that these designs better match database theory is not enough. tangible benefits were required for us to reach this decision.
Some of these benefits speed up time troubleshooting and debugging code, partly offsetting the additional investment mentioned above. Others provide compelling features to third party developers who may want to integrate our software with theirs.
The single most important benefit is that of clarity. First, with very few exceptions, LedgerSMB tries to keep to one language per file. This means that database queries are in .sql files, and Perl is in .pl and .pm files. The modules which do query mapping are the only Perl modules that contain both Perl and SQL. In this way if I want to know what a query does, I can go to the relevant SQL file and read it. Using features like named inputs in PL/PGSQL, even 100 line queries can be remarkably readable. The API is also well defined, allowing us to troubleshoot issues with the query writer.
Clarity also means that performance problems can more easily be reviewed and addressed without getting into messy questions of ORMs and how they generate queries. The queries that produce a specified result set from an API call can be defined and the actual function written out in SQL.
Along with clarity, we get the ability to do as much as possible with declarative constraints. This provides, we believe, greater robustness in the application and therefore better software in the long run.
The second important benefit is in the area of integration. Because the API is defined, there are specified integration points that other applications can use to integrate without worrying as much about ensuring that corner cases are properly covered. This makes integration more robust. Moreover important areas like LISTEN and NOTIFY allow other applications to integrate in ways approximating message queues, and the like.
In fact, the integration benefit was the driving force behind the decision to move to an intelligent database design. We wanted to open up the application to add-ons written in other languages (Python at the time, but others could supported without too much effort).
Addressing Separation of Concerns
We have all heard horror stories of what happens when you put everything in the database. We have also heard of developers afraid to put anything in the database because of fear of going down the roads of these other horror stories. The secret to success here is in fact to separate concerns and to separate them well. Many lines are ultimately arbitrary in detail but bright in outlines and so they require a team dedicated to making decisions carefully rather than drawing bold lines and mindlessly following them.
Of course that's a long way of saying you can't get away with sloppy engineering and have to actually put some thought into things.
Other Options
There are other options for application development. However, they all presume that the application has intimate knowledge of the internals of the database. I am somewhat puzzled why programmers who are so careful to avoid modules understanding the internals of eachother are so careless about encapsulation in the database.
The main other options are use of an ORM, which typically involves designing the database around the ORM and the data model for the application, and thus the RDBMS becomes basically an extension of the object model. This limits interoperability, and reduces the role that the database has relative to interoperability with other applications. This being said there isn't necessarily a problem with using VIEWs and ORMs together to provide this sort of abstraction.
A second possibility is to use a dedicated store, like many NoSQL engines offer where the database really is a store for objects, and where the "schema" (in scare quotes because NoSQL usually doesn't really have a schema concept) is just a direct extension of the object model itself.
The tradeoff with NoSQL is similar to using an ORM and building your database around that ORM, but more extreme. Typically you see even more rapid development early on, combined possibly with additional scalability in some cases, but with the tradeoff of interoperability is more severe, and ad hoc reporting is generally not practical outside the relational model.
Because of our desire to focus on interoperability and a reliance on good, flexible reporting, these options were not options for our project. They might be for others though.
Conclusions
LedgerSMB has benefited greatly benefited from the decision to move towards an intelligent database layout. Reasons include our focus on interoperability, and our willingness to do engineering up front. Other projects may find their mileage varying.
A Short Developer's Definition of Intelligent Databases
An intelligent database is a database encapsulated behind a defined API.
A Longer Description
While most programmers primarily use the database primarily as a place to simply store data, this reduces a database to basically an ACID-compliant data store with some reporting capability. This approach saves the programmer from worrying about advanced database functionality, but it also prevents the database system from being used to do a number of useful tasks that databases can really be leveraged to do.
This post will explore why LedgerSMB is going this direction, and the sorts of functionality (particularly regarding PostgreSQL) that is a part of this plan, what sorts of tradeoffs we see in it, and where we expect to benefit. This is not an article that discusses the details of implementation (see previous posts for that), but rather at a high-level how and why.
The Toolbox
The fundamental idea of encapsulating a database inside a defined API is that usually the application should hit the API and not the underlying relations where the data is stored if at all possible., The API acts as an interface between actual storage/retrieval and the application.
The major aspects of an API can include views and/or stored procedures. We use stored procedures for most things but may include views for some sorts of objects as time goes on.
What do Intelligent Databases Cost?
Many current development environments seek to save the programmer from spending heavy engineering time on a database, particularly in the early stages of a project where requirements can change. The database is relegated to a simple data store with ACID compliance. Frameworks like Rails will even generate a database schema from programming data structures.
If one goes with an intelligent design for the database, one cannot use agile programming methodologies to design the database. Instead the database requires some real engineering in its own right. This takes some time and effort in its own right and can slow down development compared to ORM-based code generators such as we see in Rails.
Such engineering requires taking a clear look at what data is expected to be collected, what internal structure it has, designing the database storage first and foremost around that analysis,and then building an API based on how it will be used. For simple schema development this doesn't take a long time but the complexity does grow with the data collected.
In the LedgerSMB project this is not the end of the world since we are re-engineering the database incrementally and so each major branch includes at least a few areas where old db structures have been pulled out and new, better engineered ones added. These areas then have an API and the application generally hits the API not the relations. Old code areas may hit the API or the relations.
Other projects may find that this adds significant development time beyond what is reasonable, but these must still be weighed against the benefits below.
What benefits do Intelligent Databases Provide?
Intelligent databases offer a number of specific benefits that LedgerSMB tries to capitalize on. The fact that these designs better match database theory is not enough. tangible benefits were required for us to reach this decision.
Some of these benefits speed up time troubleshooting and debugging code, partly offsetting the additional investment mentioned above. Others provide compelling features to third party developers who may want to integrate our software with theirs.
The single most important benefit is that of clarity. First, with very few exceptions, LedgerSMB tries to keep to one language per file. This means that database queries are in .sql files, and Perl is in .pl and .pm files. The modules which do query mapping are the only Perl modules that contain both Perl and SQL. In this way if I want to know what a query does, I can go to the relevant SQL file and read it. Using features like named inputs in PL/PGSQL, even 100 line queries can be remarkably readable. The API is also well defined, allowing us to troubleshoot issues with the query writer.
Clarity also means that performance problems can more easily be reviewed and addressed without getting into messy questions of ORMs and how they generate queries. The queries that produce a specified result set from an API call can be defined and the actual function written out in SQL.
Along with clarity, we get the ability to do as much as possible with declarative constraints. This provides, we believe, greater robustness in the application and therefore better software in the long run.
The second important benefit is in the area of integration. Because the API is defined, there are specified integration points that other applications can use to integrate without worrying as much about ensuring that corner cases are properly covered. This makes integration more robust. Moreover important areas like LISTEN and NOTIFY allow other applications to integrate in ways approximating message queues, and the like.
In fact, the integration benefit was the driving force behind the decision to move to an intelligent database design. We wanted to open up the application to add-ons written in other languages (Python at the time, but others could supported without too much effort).
Addressing Separation of Concerns
We have all heard horror stories of what happens when you put everything in the database. We have also heard of developers afraid to put anything in the database because of fear of going down the roads of these other horror stories. The secret to success here is in fact to separate concerns and to separate them well. Many lines are ultimately arbitrary in detail but bright in outlines and so they require a team dedicated to making decisions carefully rather than drawing bold lines and mindlessly following them.
Of course that's a long way of saying you can't get away with sloppy engineering and have to actually put some thought into things.
Other Options
There are other options for application development. However, they all presume that the application has intimate knowledge of the internals of the database. I am somewhat puzzled why programmers who are so careful to avoid modules understanding the internals of eachother are so careless about encapsulation in the database.
The main other options are use of an ORM, which typically involves designing the database around the ORM and the data model for the application, and thus the RDBMS becomes basically an extension of the object model. This limits interoperability, and reduces the role that the database has relative to interoperability with other applications. This being said there isn't necessarily a problem with using VIEWs and ORMs together to provide this sort of abstraction.
A second possibility is to use a dedicated store, like many NoSQL engines offer where the database really is a store for objects, and where the "schema" (in scare quotes because NoSQL usually doesn't really have a schema concept) is just a direct extension of the object model itself.
The tradeoff with NoSQL is similar to using an ORM and building your database around that ORM, but more extreme. Typically you see even more rapid development early on, combined possibly with additional scalability in some cases, but with the tradeoff of interoperability is more severe, and ad hoc reporting is generally not practical outside the relational model.
Because of our desire to focus on interoperability and a reliance on good, flexible reporting, these options were not options for our project. They might be for others though.
Conclusions
LedgerSMB has benefited greatly benefited from the decision to move towards an intelligent database layout. Reasons include our focus on interoperability, and our willingness to do engineering up front. Other projects may find their mileage varying.
Saturday, November 5, 2011
Elegance and Simplicity
When it comes to software engineering, I always think back to my first year Calculus teacher, Mike Lavender, and his admonishment that "in mathematics, power tools are consider inelegant where hand tools will do." Words to live by in programming and engineering.
So I'd go so far as to redefine elegance in software programming and engineering as "simplicity perfected." The simple fact is that where requirements change, a well engineered simple solution will usually turn out to be more agile than a heavily engineered, complex one (whether programmed with "agile" methodologies or not).
When one takes the idea of trying to perfect simplicity in the art of programming (and software engineering), it becomes easier to recognize foot-guns and inherent problems, and tradeoffs often become more conscious and controlled.
Wednesday, November 2, 2011
10 Underrated Features of PostgreSQL
Here are a list of features which I think are generally underrated in PostgreSQL and why. Many though not all of these are being used in the development version of LedgerSMB, and more of these will likely be used as time goes on either by the main software or by addons.
These are functions which I greatly miss when I set down and work with many other RDBMS's, or which are fairly new and often underutilized.
10: Language-Specific Full Text Searches
What it is: Language-specific searches on related words.
Why I like it: This function allows for a full text search to query related words in a post. These can be indexed using functional indexes below as long as the system knows which language to search under. So imagine if you put a text field that says "I am going to test this for the customer next week" but when you go back and search you search for "testing for customer" the result will still show up, because PostgreSQL knows that in English, "test" and "testing" can be two forms of the same word.
How I'd use it: Any application which requires full text searches can take advantage of this to provide a much richer experience.
The LedgerSMB budget module currently uses full text searches for the descriptions of budgets. Currently this is locale specific. Future versions will probably allow notes to be in various languages and searched via a full text search per language.
9: Pgcrypto
What it is: Cryptographic extensions to PostgreSQL
Why I like it: With pgcrypto, one can design systems which provide strong protection of data in the database, making the encryption transparent to the application.
How I'd use it: If someone wanted to store, say, credit card data in the database, this could be installed using suitable forms of managed keys such that the user could unlock a key and retrieve the data. This could then be handled such that the application itself would not need to handle encryption or decryption of the data, thus allowing other applications to handle key management and other important tasks.
8: Functional Indexes
What it is: Indexes on the outputs of immutable functions
Why I like it: Functional indexes allow us to store information functionally dependent on information actually stored in the database. Consequently just because we need to search on information. This is available in Oracle and to a far lesser extent in MS SQL and DB2. It is not available in MySQL.
How I'd use it: Functional indexes are behind language-dependent full-text indexing (and hence can make searches there a lot faster). It also has a large number of other uses.
For example, suppose I am storing a large bytea column and I want to search quickly on results. I could index md5(my_bytea) and then search on input = my_bytea and md5(input) = md5(my_bytea). This would allow an index scan to pull out matching md5's and then checking the full result against matching records. For many other databases, one would have to store both the original data and the md5 checksum.
Another use would be to ensure uniqueness based on function output. So for example, we could:
CREATE UNIQUE INDEX u_functional_index ON my_table (lower(my_col));
Then values would be rejected if they are non-unique to a case insensitive search.
7: Partial Indexes
What it is: Indexes on a subset of rows.
Why I like it: Partial indexes allow the development of relatively complex data constraints. For example, one can require a unique constraint on a subset of rows matching a certain criteria, since uniqueness is implemented on the index. Of the other major RDBMS's, only Microsoft SQL Server implements anything like this.
How I'd use it: Partial indexes can be used in two fundamentally different ways: speeding up common search criteria and enforcing partial unique constraints. Many are familiar with speeding up searches in this way, but partial unique indexes are where the real power lies.
So suppose ar and ap invoices share the same physical table, but ar invoices must have unique invoice numbers while ap invoices must be unique per vendor. We can use partial unique indexes to support both these constraints independently.
CREATE UNIQUE INDEX u_ar_invnumber ON invoices (invnumber) WHERE type = 'ar';
A second important application is adding forward-only unique constraints. These are helpful where a new unique constraint needs to be added immediately but not all data in the table conforms to that constraint. As a stop-gap measure (while data is being cleaned up), one might use a partial unique constraint to encourage uniqueness going forward. Something like:
CREATE UNIQUE INDEX u_ar_invnumber ON invoices (invnumber) WHERE type = 'ar' AND id > 12345;
Data can be cleaned up (if possible), then the index can be dropped and recreated with a different where clause.
6: Third Party Solutions
What it is: An extensive set of third party add-ons for PostgreSQL
Why I like it: There's so much to choose from!
A few of my favorites: PGTAP is a test harness for stored procedures, building on other test harness frameworks. It allows you to write production-safe test cases and run them without risking your data. Very very good.
Slony-I is a very good well-developed single-master, asynchronous replication system for PostgreSQL. it was developed to help ensure the .org domain registrar would scale appropriately. Slony-I supports, among other things, replicating between different versions of PostgreSQL, something the native solutions do not.
Speaking of replication, Bucardo allows you to do multi-master and master-slave asynchronous replication as well.
PostGIS provides an excellent basis for GIS applications, and there are many, many other third party, free software add-ons for PostgreSQL Uses are largely limited by the imagination.
5: Language Handles
What it is: Define your own stored procedure languages!
Why I like it: Makes migration often more feasible than it would be otherwise, and exposes much advanced functionality to stored procedure interfaces.
How I'd use it: There are three major uses of stored procedure handles (i.e. to create stored procedures in languages other than C, SQL, and PL/PGSQL).
The first of these is migration. If one has an Oracle database which uses a lot of Java stored procedures you can often port these to PostgreSQL and PL/J with a minimum of headaches. Similarly if you have an MS SQL database with .Net stored procedures you may be able to make these work with PL/Mono.
The second is to make advanced features of a language available to your stored procedures. If you are doing a lot of text processing, PL/Perl may be helpful, but if you are doing a lot of statistical calculations, PL/R may be very helpful.
Finally (and here is the dangerous one), language handles can make it possible to do very un-database-like things from within stored procedures. So for example, you could use PL/SH to execute shell scripts from the stored procedures. However in general for these areas, external scripts and LISTEN/NOTIFY (see below) are better tools.
4: Complex Types
What it is: Tuple-based types, often used as function output types.
Why I like it: These provide an opportunity to create a very rich API within the db. PostgreSQL supports not only pure tuples but tuples which contain arrays of other tuples as their members.
How I'd use it: In LegerSMB, we use complex types to define much of our interface for the application and this dependency will increase over time. These types allow one to represent in SQL form any well-structured representation of data in any other application.
These can then be used to define inputs and outputs of stored procedures.
3: Listen/Notify
What it is: Notification of specified database events.
Why I like it: this provides a notification service within the database. Applications listen for a specified notification handle, and other functions can raise those notifications. This allows one to create message queues (similar to Oracle's Database Notification Services), but that's not all.
Because the notifications are decoupled from low-level database operations, one can raise the notifications based on arbitrary criteria. Suppose we want to raise a notification anytime the postgres user deletes data from the audit trail? We can do that. No problem. Similarly we could raise a notification anytime
How I'd use it: You can use LISTEN/NOTIFY to send a message to a script that information has been dropped in a table and is ready to email out to a decision-maker. For example you could use this to provide real-time emailing when, for example, a part's on-hand value drops below the reorder point. In this way all manner of message queues can be created using the database as a center.
On top of that one can send notifications out around things that do not modify the database. I don't have a specific use case for this yet, but the fact that it is there means that many other things suddenly become possible.
2: Windowing Functions
What it is: The ability to have an aggregate function that aggregates incrementally over a set or portion of a set. Although most RDBMS's these days do support windowing functions (MySQL, DB2, Oracle, and MS SQL all do), this is a feature which is often overlooked.
Why I like it: This allows you to specify aggregates which are run incrementally across a set. This set can be partitioned and ordered independent of any ordering on the final set.
How I'd use it: There are huge numbers of uses for this sort of thing. For example, windowing functions make it possible to select the highest ranked rows within a set by certain criteria. They also allow one to add running totals to financial reports, or add running totals within specific transactions for financial reports.
No doubt there are many many more uses I just haven't thought of or come across. However if you think about being able to use windowing functions the sorts of queries you can write are well expanded.
1: PL/PGSQL
What it is: A very clear, concise, well organized language for stored procedures. Borrowing heavily in approach from Oracle's PL/SQL, this language is a mixture of Ada, PL/I, and SQL. The result, while different from what most programmers are used to, highly readable, easy to follow, and extremely powerful.
Why I like it: PL/PGSQL adds almost everything you could want that vanilla SQL does not offer in a procedural language. Conditionals, loops, and named variables all come to mind. But what it has going for it is conciseness. Often times I hear people say they are "just" doing stored procedures in PL/PGSQL. There is no "just" about it though. This language is very well designed for what it does.
How I'd use it: LedgerSMB currently contains most SQL in the newer code inside stored procedures written in PL/PGSQL. We use it for most procedures with large numbers of arguments or where the control structures are at all helpful. In essence we use it a LOT.
The above list is largely arbitrary. Another author might put in the fact that data definition language calls are usually transaction-safe (meaning you can roll back a transaction that dropped a table), incremental backups, or any number of other features. However the features that I have included above are generally those which allow PostgreSQL to be used as a very extensible database server. It is my view that these features, which are at their best when creating intelligent databases, are frequently under-used especially by small developers because they think of PostgreSQL as merely a data store instead of the very powerful system that it is.
What features do you think are underrated? Comment below to mention them!
These are functions which I greatly miss when I set down and work with many other RDBMS's, or which are fairly new and often underutilized.
10: Language-Specific Full Text Searches
What it is: Language-specific searches on related words.
Why I like it: This function allows for a full text search to query related words in a post. These can be indexed using functional indexes below as long as the system knows which language to search under. So imagine if you put a text field that says "I am going to test this for the customer next week" but when you go back and search you search for "testing for customer" the result will still show up, because PostgreSQL knows that in English, "test" and "testing" can be two forms of the same word.
How I'd use it: Any application which requires full text searches can take advantage of this to provide a much richer experience.
The LedgerSMB budget module currently uses full text searches for the descriptions of budgets. Currently this is locale specific. Future versions will probably allow notes to be in various languages and searched via a full text search per language.
9: Pgcrypto
What it is: Cryptographic extensions to PostgreSQL
Why I like it: With pgcrypto, one can design systems which provide strong protection of data in the database, making the encryption transparent to the application.
How I'd use it: If someone wanted to store, say, credit card data in the database, this could be installed using suitable forms of managed keys such that the user could unlock a key and retrieve the data. This could then be handled such that the application itself would not need to handle encryption or decryption of the data, thus allowing other applications to handle key management and other important tasks.
8: Functional Indexes
What it is: Indexes on the outputs of immutable functions
Why I like it: Functional indexes allow us to store information functionally dependent on information actually stored in the database. Consequently just because we need to search on information. This is available in Oracle and to a far lesser extent in MS SQL and DB2. It is not available in MySQL.
How I'd use it: Functional indexes are behind language-dependent full-text indexing (and hence can make searches there a lot faster). It also has a large number of other uses.
For example, suppose I am storing a large bytea column and I want to search quickly on results. I could index md5(my_bytea) and then search on input = my_bytea and md5(input) = md5(my_bytea). This would allow an index scan to pull out matching md5's and then checking the full result against matching records. For many other databases, one would have to store both the original data and the md5 checksum.
Another use would be to ensure uniqueness based on function output. So for example, we could:
CREATE UNIQUE INDEX u_functional_index ON my_table (lower(my_col));
Then values would be rejected if they are non-unique to a case insensitive search.
7: Partial Indexes
What it is: Indexes on a subset of rows.
Why I like it: Partial indexes allow the development of relatively complex data constraints. For example, one can require a unique constraint on a subset of rows matching a certain criteria, since uniqueness is implemented on the index. Of the other major RDBMS's, only Microsoft SQL Server implements anything like this.
How I'd use it: Partial indexes can be used in two fundamentally different ways: speeding up common search criteria and enforcing partial unique constraints. Many are familiar with speeding up searches in this way, but partial unique indexes are where the real power lies.
So suppose ar and ap invoices share the same physical table, but ar invoices must have unique invoice numbers while ap invoices must be unique per vendor. We can use partial unique indexes to support both these constraints independently.
CREATE UNIQUE INDEX u_ar_invnumber ON invoices (invnumber) WHERE type = 'ar';
A second important application is adding forward-only unique constraints. These are helpful where a new unique constraint needs to be added immediately but not all data in the table conforms to that constraint. As a stop-gap measure (while data is being cleaned up), one might use a partial unique constraint to encourage uniqueness going forward. Something like:
CREATE UNIQUE INDEX u_ar_invnumber ON invoices (invnumber) WHERE type = 'ar' AND id > 12345;
Data can be cleaned up (if possible), then the index can be dropped and recreated with a different where clause.
6: Third Party Solutions
What it is: An extensive set of third party add-ons for PostgreSQL
Why I like it: There's so much to choose from!
A few of my favorites: PGTAP is a test harness for stored procedures, building on other test harness frameworks. It allows you to write production-safe test cases and run them without risking your data. Very very good.
Slony-I is a very good well-developed single-master, asynchronous replication system for PostgreSQL. it was developed to help ensure the .org domain registrar would scale appropriately. Slony-I supports, among other things, replicating between different versions of PostgreSQL, something the native solutions do not.
Speaking of replication, Bucardo allows you to do multi-master and master-slave asynchronous replication as well.
PostGIS provides an excellent basis for GIS applications, and there are many, many other third party, free software add-ons for PostgreSQL Uses are largely limited by the imagination.
5: Language Handles
What it is: Define your own stored procedure languages!
Why I like it: Makes migration often more feasible than it would be otherwise, and exposes much advanced functionality to stored procedure interfaces.
How I'd use it: There are three major uses of stored procedure handles (i.e. to create stored procedures in languages other than C, SQL, and PL/PGSQL).
The first of these is migration. If one has an Oracle database which uses a lot of Java stored procedures you can often port these to PostgreSQL and PL/J with a minimum of headaches. Similarly if you have an MS SQL database with .Net stored procedures you may be able to make these work with PL/Mono.
The second is to make advanced features of a language available to your stored procedures. If you are doing a lot of text processing, PL/Perl may be helpful, but if you are doing a lot of statistical calculations, PL/R may be very helpful.
Finally (and here is the dangerous one), language handles can make it possible to do very un-database-like things from within stored procedures. So for example, you could use PL/SH to execute shell scripts from the stored procedures. However in general for these areas, external scripts and LISTEN/NOTIFY (see below) are better tools.
4: Complex Types
What it is: Tuple-based types, often used as function output types.
Why I like it: These provide an opportunity to create a very rich API within the db. PostgreSQL supports not only pure tuples but tuples which contain arrays of other tuples as their members.
How I'd use it: In LegerSMB, we use complex types to define much of our interface for the application and this dependency will increase over time. These types allow one to represent in SQL form any well-structured representation of data in any other application.
These can then be used to define inputs and outputs of stored procedures.
3: Listen/Notify
What it is: Notification of specified database events.
Why I like it: this provides a notification service within the database. Applications listen for a specified notification handle, and other functions can raise those notifications. This allows one to create message queues (similar to Oracle's Database Notification Services), but that's not all.
Because the notifications are decoupled from low-level database operations, one can raise the notifications based on arbitrary criteria. Suppose we want to raise a notification anytime the postgres user deletes data from the audit trail? We can do that. No problem. Similarly we could raise a notification anytime
How I'd use it: You can use LISTEN/NOTIFY to send a message to a script that information has been dropped in a table and is ready to email out to a decision-maker. For example you could use this to provide real-time emailing when, for example, a part's on-hand value drops below the reorder point. In this way all manner of message queues can be created using the database as a center.
On top of that one can send notifications out around things that do not modify the database. I don't have a specific use case for this yet, but the fact that it is there means that many other things suddenly become possible.
2: Windowing Functions
What it is: The ability to have an aggregate function that aggregates incrementally over a set or portion of a set. Although most RDBMS's these days do support windowing functions (MySQL, DB2, Oracle, and MS SQL all do), this is a feature which is often overlooked.
Why I like it: This allows you to specify aggregates which are run incrementally across a set. This set can be partitioned and ordered independent of any ordering on the final set.
How I'd use it: There are huge numbers of uses for this sort of thing. For example, windowing functions make it possible to select the highest ranked rows within a set by certain criteria. They also allow one to add running totals to financial reports, or add running totals within specific transactions for financial reports.
No doubt there are many many more uses I just haven't thought of or come across. However if you think about being able to use windowing functions the sorts of queries you can write are well expanded.
1: PL/PGSQL
What it is: A very clear, concise, well organized language for stored procedures. Borrowing heavily in approach from Oracle's PL/SQL, this language is a mixture of Ada, PL/I, and SQL. The result, while different from what most programmers are used to, highly readable, easy to follow, and extremely powerful.
Why I like it: PL/PGSQL adds almost everything you could want that vanilla SQL does not offer in a procedural language. Conditionals, loops, and named variables all come to mind. But what it has going for it is conciseness. Often times I hear people say they are "just" doing stored procedures in PL/PGSQL. There is no "just" about it though. This language is very well designed for what it does.
How I'd use it: LedgerSMB currently contains most SQL in the newer code inside stored procedures written in PL/PGSQL. We use it for most procedures with large numbers of arguments or where the control structures are at all helpful. In essence we use it a LOT.
The above list is largely arbitrary. Another author might put in the fact that data definition language calls are usually transaction-safe (meaning you can roll back a transaction that dropped a table), incremental backups, or any number of other features. However the features that I have included above are generally those which allow PostgreSQL to be used as a very extensible database server. It is my view that these features, which are at their best when creating intelligent databases, are frequently under-used especially by small developers because they think of PostgreSQL as merely a data store instead of the very powerful system that it is.
What features do you think are underrated? Comment below to mention them!
Tuesday, October 18, 2011
The Design of LedgerSMB 1.3's Authentication/Authorization/Auditing System
LedgerSMB 1.3 and higher uses database roles for group/user permissions management. Every user is a database user, and permissions are managed by roles which are granted permissions on a functional requirements basis to underlying database objects (tables and functions).
In doing so, we can take advantage of a number of pieces of advanced PostgreSQL functionality to provide a much richer and more secure application environment. At the same time there are limitations with this system which must be taken into account as well.
Basic Design Requirements
The LedgerSMB Authentication, Authorization, and Auditing system was designed to offer real security even as the older codebase inherited from SQL-Ledger did not admit of any real security controls, as well as offering an extensible set of authentication options, and an ability to flexibly and consistently apply security permissions across several databases in a cluster.
The basic use cases are as follows:
Overview of Design
The current design uses PostgreSQL roles for managing permissions. On the most basic level every user is a database user. Roles with the nologin attribute is set are used to assign permissions to underlying relations and functions. Generally, the simplest permissions are favored over the most complex. This means that operations that correspond exactly with relational operations are granted relational permissions, while those that do not are granted functional permissions.
Some of these roles are granted to other no-login roles to serve as larger aggregations of permissions. Users may be granted either sets of roles. A set of administrative functions are provided by the software to manage user accounts, permissions, and reset passwords (if applicable).
Obstacle 1: Roles are global to a cluster
This is both an obstacle and a useful tool. This allows users to be consistent across databases, but also poses a challenge regarding the larger database clusters because the roles which grant specific permissions end up being cluster-global as well.
We resolved this by making the db-specific roles prefixed with lsmb_[database_name]__ (note the double underscore after the database name).
Obstacle 2: Authentication methods
Authentication necessarily requires a few specific pieces of information to be passed to the database server, and these must be obtained from the user. The first is a username, and the second is some sort of authentication token. Any intermediary framework must then be able to pass these credentials on to the database in such a way as to properly log in. This can consist of three basic types:
The other two break down into two questions:
On the web server, for password authentication, the re-use requirement means that the only possible method for authentication is HTTP-Basic.
On the database server, client-side SSL certificates can only be used to authenticate the web server to the database server. They cannot be used to authenticate users, and so are a not a possible choice for database authentication for a web app of this sort.
Security Tradeoffs
Security, it could be said, is an endless series of tradeoffs. These tradeoffs not only include usability vs security, but also how mitigation of concerns are prioritized.
This approach prioritizes mitigating SQL injection and privilege escalation issues in the web application above possible flaws in the PostgreSQL authentication mechanisms and misconfiguration of the web server.
We believe that there is general benefit in pushing authentication back to methods which are better tested and have more peer review. The PostgreSQL community has been, in our experience, extremely professional regarding security, further raising our confidence. So this first trade-off is easily justifiable.
The second tradeoff is a little harder to justify at the outset. If passwords must be re-used by the web server, zero-knowledge authentication systems cannot work. This means the password must be passed in clear text to the web server. This leads to several kinds of attacks, including malicious admins logging passwords, use of the web server without SSL allowing passwords to be overheard by an eavesdropper. We address the second type of attack by requiring SSL in the default configuration of LedgerSMB.
The issues with malicious system administrators can never be completely mitigated using password authentication. If this is a consideration, it's best to look to something like Kerberos (which could be supported on 1.3 with a little bit of effort). With Kerberos support, the web application, the database, and the user can all maintain a healthy distrust of eachother.
Other Implementation Details
A few other basic things are worth mentioning here.
The first is that PostgreSQL allows passwords to expire. We make use of this to differentiate between user-set passwords (valid for a configurable length of time) and administrative set password (valid for one day). Users can reset their own passwords.
Administrators of the application can issue temporary password resets. The way this works is that the administator makes up a password for the user, and the user logs in, and is prompted to change it.
In doing so, we can take advantage of a number of pieces of advanced PostgreSQL functionality to provide a much richer and more secure application environment. At the same time there are limitations with this system which must be taken into account as well.
Basic Design Requirements
The LedgerSMB Authentication, Authorization, and Auditing system was designed to offer real security even as the older codebase inherited from SQL-Ledger did not admit of any real security controls, as well as offering an extensible set of authentication options, and an ability to flexibly and consistently apply security permissions across several databases in a cluster.
The basic use cases are as follows:
- A single business will only have one database. The system shouldn't be unmanageable for such a business.
- An accountant/bookkeeper might access books for several businesses, and would need consistent access across databases.
- A midsize business with several subsidiaries might need some people to have access across databases, and others to have permission only to specific databases. Such a business might also need some sort of single sign-on technology, or even integrated authentication.
Overview of Design
The current design uses PostgreSQL roles for managing permissions. On the most basic level every user is a database user. Roles with the nologin attribute is set are used to assign permissions to underlying relations and functions. Generally, the simplest permissions are favored over the most complex. This means that operations that correspond exactly with relational operations are granted relational permissions, while those that do not are granted functional permissions.
Some of these roles are granted to other no-login roles to serve as larger aggregations of permissions. Users may be granted either sets of roles. A set of administrative functions are provided by the software to manage user accounts, permissions, and reset passwords (if applicable).
Obstacle 1: Roles are global to a cluster
This is both an obstacle and a useful tool. This allows users to be consistent across databases, but also poses a challenge regarding the larger database clusters because the roles which grant specific permissions end up being cluster-global as well.
We resolved this by making the db-specific roles prefixed with lsmb_[database_name]__ (note the double underscore after the database name).
Obstacle 2: Authentication methods
Authentication necessarily requires a few specific pieces of information to be passed to the database server, and these must be obtained from the user. The first is a username, and the second is some sort of authentication token. Any intermediary framework must then be able to pass these credentials on to the database in such a way as to properly log in. This can consist of three basic types:
- Password based authentication, where the authentication is a memorized password, typed into a terminal,
- Token-based authentication, where the token is supplied from an external source, and
- Two factor authentication, where the both of the above methods are required.
The other two break down into two questions:
- What can the web server pass on to the database server? and
- What methods can the database server use to authenticate the user?
On the web server, for password authentication, the re-use requirement means that the only possible method for authentication is HTTP-Basic.
On the database server, client-side SSL certificates can only be used to authenticate the web server to the database server. They cannot be used to authenticate users, and so are a not a possible choice for database authentication for a web app of this sort.
Security Tradeoffs
Security, it could be said, is an endless series of tradeoffs. These tradeoffs not only include usability vs security, but also how mitigation of concerns are prioritized.
This approach prioritizes mitigating SQL injection and privilege escalation issues in the web application above possible flaws in the PostgreSQL authentication mechanisms and misconfiguration of the web server.
We believe that there is general benefit in pushing authentication back to methods which are better tested and have more peer review. The PostgreSQL community has been, in our experience, extremely professional regarding security, further raising our confidence. So this first trade-off is easily justifiable.
The second tradeoff is a little harder to justify at the outset. If passwords must be re-used by the web server, zero-knowledge authentication systems cannot work. This means the password must be passed in clear text to the web server. This leads to several kinds of attacks, including malicious admins logging passwords, use of the web server without SSL allowing passwords to be overheard by an eavesdropper. We address the second type of attack by requiring SSL in the default configuration of LedgerSMB.
The issues with malicious system administrators can never be completely mitigated using password authentication. If this is a consideration, it's best to look to something like Kerberos (which could be supported on 1.3 with a little bit of effort). With Kerberos support, the web application, the database, and the user can all maintain a healthy distrust of eachother.
Other Implementation Details
A few other basic things are worth mentioning here.
The first is that PostgreSQL allows passwords to expire. We make use of this to differentiate between user-set passwords (valid for a configurable length of time) and administrative set password (valid for one day). Users can reset their own passwords.
Administrators of the application can issue temporary password resets. The way this works is that the administator makes up a password for the user, and the user logs in, and is prompted to change it.
Subscribe to:
Posts (Atom)