The PostgreSQL at 10 TB And Beyond talk has now been released on Youtube. Feel free to watch. For the folks seeing this on Planet Perl Iron Man, there is a short function which extends SQL written in Perl that runs in PostgreSQL in the final 10 minutes or so of the lecture.
This lecture discusses human and technical approaches to solving volume, velocity, and variety problems on PostgreSQL in the 10TB range on a single, non-sharded large server.
As a side but related note, I am teaching a course through Edument on the topics discussed in Sweden discussing many of the technical aspects discussed here, called Advanced PostgreSQL for Programmers. You can book the course for the end of this month. It will be held in Malmo, Sweden.
Showing posts with label perl. Show all posts
Showing posts with label perl. Show all posts
Monday, February 13, 2017
Thursday, January 26, 2017
PL/Perl and Large PostgreSQL Databases
One of the topics discussed in the large database talk is the way we used PL/Perl to solve some data variety problems in terms of extracting data from structured text documents.
It is certainly possible to use other languages to do the same, but PL/Perl has an edge in a number of important ways. PL/Perl is light-weight, flexible and fills this particular need better than any other language I have worked with.
While one of the considerations has often been knowledge of Perl in the team, PL/Perl has a number of specific reasons to recommend it:
Moreover when you mark your functions as immutable, you can index the output, and this is helpful when you want ordered records starting at a certain point.
So for example, suppose we want to be able to query on plasmid lines in UNIPROT documents but we have not set this up before we loaded the table. We could easily create a PL/Perl function like:
CREATE OR REPLACE FUNCTION plasmid_lines(uniprot text)
RETURNS text[]
LANGUAGE PLPERL IMMUTABLE AS
$$
use strict;
use warnings;
my ($uniprot) = @_;
my @lines = grep { /^OG\s+Plasmid/ } split /\n/ $uniprot;
return [ map { my $l = $_; $l =~ s/^OG\s+Plasmid\s*//; $l } @lines ];
$$;
You could then create a GIN index on the array elements:
CREATE INDEX uniprot_doc_plasmids ON uniprot_docs USING gin (plasmid_lines(doc));
Neat!
It is certainly possible to use other languages to do the same, but PL/Perl has an edge in a number of important ways. PL/Perl is light-weight, flexible and fills this particular need better than any other language I have worked with.
While one of the considerations has often been knowledge of Perl in the team, PL/Perl has a number of specific reasons to recommend it:
- It is light-weight compared to PL/Java and many other languages
- It excels at processing text in general ways.
- It has extremely mature regular expression support
Moreover when you mark your functions as immutable, you can index the output, and this is helpful when you want ordered records starting at a certain point.
So for example, suppose we want to be able to query on plasmid lines in UNIPROT documents but we have not set this up before we loaded the table. We could easily create a PL/Perl function like:
CREATE OR REPLACE FUNCTION plasmid_lines(uniprot text)
RETURNS text[]
LANGUAGE PLPERL IMMUTABLE AS
$$
use strict;
use warnings;
my ($uniprot) = @_;
my @lines = grep { /^OG\s+Plasmid/ } split /\n/ $uniprot;
return [ map { my $l = $_; $l =~ s/^OG\s+Plasmid\s*//; $l } @lines ];
$$;
You could then create a GIN index on the array elements:
CREATE INDEX uniprot_doc_plasmids ON uniprot_docs USING gin (plasmid_lines(doc));
Neat!
Sunday, August 14, 2016
Forthcoming new scalable job queue extension
So for those of you who know, I now spend most of my time doing more general PostgreSQL consulting and a fair bit of time still on LedgerSMB. One of my major projects lately has been on a large scientific computing platform currently run on PostgreSQL, but due to volume and velocity of data being moved to Hadoop (the client maintains other fairly large PostgreSQL instances with no intention of moving btw).
With this client's permission I have decided to take a lot of the work I have done in optimizing their job queue system and create an extension under PostgreSQL for it.. The job queue currently runs tens of millions of jobs per day (meaning twice that number of write queries, and a fair number of read queries too) and is one of the most heavily optimized parts of the system, so this will be based on a large number of lessons learned on what is a surprisingly hard problem.
It is worth contrasting this to pg_message_queue of which I am also the author. pg_message_queue is intended as a light-weight, easy to use message queue extension that one can use to plug into other programs to solve common problems where notification and message transfer are the main problems. This project will be an industrial scale job queuing system aimed at massive concurrency. As a result simplicity and ease of use take second place to raw power and performance under load. In other words here I am not afraid to assume the dba and programming teams know what they are doing and has the expertise to read the manual and implement appropriately.
The first version (1.x) will support all supported versions of PostgreSQL and make the following guarantees:
With this client's permission I have decided to take a lot of the work I have done in optimizing their job queue system and create an extension under PostgreSQL for it.. The job queue currently runs tens of millions of jobs per day (meaning twice that number of write queries, and a fair number of read queries too) and is one of the most heavily optimized parts of the system, so this will be based on a large number of lessons learned on what is a surprisingly hard problem.
It is worth contrasting this to pg_message_queue of which I am also the author. pg_message_queue is intended as a light-weight, easy to use message queue extension that one can use to plug into other programs to solve common problems where notification and message transfer are the main problems. This project will be an industrial scale job queuing system aimed at massive concurrency. As a result simplicity and ease of use take second place to raw power and performance under load. In other words here I am not afraid to assume the dba and programming teams know what they are doing and has the expertise to read the manual and implement appropriately.
The first version (1.x) will support all supported versions of PostgreSQL and make the following guarantees:
- massively multiparallel, non-blocking performance (we currently use with 600+ connections to PostgreSQL by worker processes).
- Partitioning, coalescing, and cancelling of jobs similar in some ways to TheSchwartz
- Exponential pushback based on number of times a job has failed
- Jobs may be issued again after deletion but that this can always be detected and bad jobs pruned
- Optionally job table partitioning.
The first client written will rely on hand-coded SQL along with DBIx::Class's schema objects. This client will guarantee that:
- Work modules done always succeeds or fails in a transaction
- A job notifier class will be shown
- Pruning of completed jobs will be provided via the perl module and a second query.
The history of this is that this came from a major client's use of The Schwartz and they out grew it for scalability reasons. While the basic approach is thus compatible, the following changes are made:
- Job arguments are in json format rather than in Storable format in bytea columns
- Highly optimized performance on PostgreSQL
- Coalesce is replaced by a single integer cancellation column
- Jobs may be requested by batches of various sizes
2.x will support 9.5+ and dispense with the need for both advisory locks and rechecking. I would like to support some sort of graph management as well (i.e. a graph link that goes from one job type to another which specifies "for each x create a job for y" type of semantics. That is still all in design.
Friday, August 5, 2016
use lib '.' considered harmful (exploits discussed)
Which the discussion of CVE-2016-1238, a quick and easy fix for broken code that has been suggested is to add the following line to the top of broken Perl scripts: Note this applies to Perl as run anywhere, whether pl/perlU, plain perl, or something else.
use lib '.';
In some corners, this has become the goto solution for the problem (pun quite deliberate). It works, gets the job done, and introduces subtle, hidden, and extremely dangerous problems in the process.
For those interested in the concerns specific to PostgreSQL, these will be discussed near the end of this article.
I am borrowing an idea here from linguistics, the idea of the garden path, as something that I think highlights a lot of subtle security problems. Consider the newspaper headline "Number of Lothian patients made ill by drinking rockets." Starts off simple enough and you get to the end, realizing you must have misread it (and you did, probably, since the number of patients increased who were made ill by drinking, not that some people got sick because they drank hydrazine). The obvious reading and the logical reading diverge and this leads to a lot of fun in linguistic circles.
The same basic problem occurs with regard to security problems. Usually security problems occur because of two problems. Either people do something obviously insecure (plain text authentication for ftp users where it matters) or they do something that looks on the surface like it is secure but behind the scenes does something unexpected.
Perl here has a few surprises here because parsing and running a script is a multi-pass process but we tend to read it as a single pass. Normally these don't cause real problems but in certain cases there are very subtle dangers lurking. Here, with use lib '.', it is possible to inject code into a running program as long as an attacker can get a file placed in the current working directory of the program.
Relative paths, including the current working directory, do have legitimate use cases, but the problems and pitfalls must be understood before selecting this method.
Perl looks for files to require or include based on an array of paths, globally defined, called @INC. Use lib stores a copy of the original lib at first use, and then ensures that the directory specified occurs at the start of the search order. So directories specified with use lib are searched before the default library directories. This becomes important as we look at how Perl programs get executed.
Perl runs a program in two passes. First it creates the parse tree, then it runs the program. This is a recurive process and because of how it works, it is possible for malicious code that gets accidently run in this process to transparently inject code into this (and other portions) of the Perl process.
Keep in mind that this makes Perl a very dynamic language which, on one hand, has serious garden path issues, but on the other ensures that it is an amazingly flexible language.
During the parse stage, Perl systematically works through the file, generating a parse tree, and running any "BEGIN" blocks, "use" statements, and "no" statements. This means that injected code can be run even if later sytnax errors appear to prevent the bulk of the program from running at all or if earlier errors cause run-time exception.
After this process finishes, Perl executes the parse tree that results. This means that Perl code can rewrite the parse tree before your code is ever really written and that code can be inserted into that part o the process.
Consider a simple Perl script:
#!/usr/bin/perl
use lib '.';
use Cwd;
use 5.010;
use strict;
use warnings;
say getcwd();
So when Cwd imports Injected, it deletes itself from the memory of having been included, deletes its caller too, reloads the correct caller (not from the current working directory) and then executes some code (here a harmless warning).
Cwd.pm then returns success
test.pl runs Cwd->import() which is now the correct one, but we have already run unintended code that could in theory do anything.
Any program capable of being run in arbitrary directories, written in Perl, which has this line in it (use lib '.') is subject to arbitrary code injection attacks using any module in the dependency tree, required or not.
As a standard partof boilerplate in any secure Perl program, I strongly recommend adding the following line to the top of any script. As long as modules don't add it back in behind your back (would be extremely rare that they would), adding the following line:
no lib '.';
Note that this strips out the current working directory even if it si supplied as a command-line argument. So it may not be possible in all cases. So use common sense, and do some testing, and document this as desired behavior. Note one can still invoke with perl -I./. in most cases so it is possible to turn this safety off..... Additionally if you put that at the start of your module, something you include could possibly put it back in.
In a case where you need a path relative to the script being executed, FindBin is the ideal solution. It gives you a fixed path relative to the script being run, which is usually sufficient for most cases of an application being installed on a system as a third party. So instead you would do:
use FindBin;
use lib $FindBin::Bin;
Then the script's directory will be in the include path.
I always add the explicit rejection of cwd in my plperlu functions. However if someone has a program that is broken by CVE-2016-1238 related fixes, it is possible that someone would add a use lib '.' to a perl module, which is a bad idea. As discussed in the previous post, careful code review is required to be absolutely safe. Additionally, it is a very good idea to periodically check the PostgreSQL data directory for perl modules which would indicate a compromised system.
use lib '.';
In some corners, this has become the goto solution for the problem (pun quite deliberate). It works, gets the job done, and introduces subtle, hidden, and extremely dangerous problems in the process.
For those interested in the concerns specific to PostgreSQL, these will be discussed near the end of this article.
Security and the Garden Path
I am borrowing an idea here from linguistics, the idea of the garden path, as something that I think highlights a lot of subtle security problems. Consider the newspaper headline "Number of Lothian patients made ill by drinking rockets." Starts off simple enough and you get to the end, realizing you must have misread it (and you did, probably, since the number of patients increased who were made ill by drinking, not that some people got sick because they drank hydrazine). The obvious reading and the logical reading diverge and this leads to a lot of fun in linguistic circles.
The same basic problem occurs with regard to security problems. Usually security problems occur because of two problems. Either people do something obviously insecure (plain text authentication for ftp users where it matters) or they do something that looks on the surface like it is secure but behind the scenes does something unexpected.
Perl here has a few surprises here because parsing and running a script is a multi-pass process but we tend to read it as a single pass. Normally these don't cause real problems but in certain cases there are very subtle dangers lurking. Here, with use lib '.', it is possible to inject code into a running program as long as an attacker can get a file placed in the current working directory of the program.
Relative paths, including the current working directory, do have legitimate use cases, but the problems and pitfalls must be understood before selecting this method.
What the lib pragma does
Perl looks for files to require or include based on an array of paths, globally defined, called @INC. Use lib stores a copy of the original lib at first use, and then ensures that the directory specified occurs at the start of the search order. So directories specified with use lib are searched before the default library directories. This becomes important as we look at how Perl programs get executed.
How Perl runs a program
Perl runs a program in two passes. First it creates the parse tree, then it runs the program. This is a recurive process and because of how it works, it is possible for malicious code that gets accidently run in this process to transparently inject code into this (and other portions) of the Perl process.
Keep in mind that this makes Perl a very dynamic language which, on one hand, has serious garden path issues, but on the other ensures that it is an amazingly flexible language.
During the parse stage, Perl systematically works through the file, generating a parse tree, and running any "BEGIN" blocks, "use" statements, and "no" statements. This means that injected code can be run even if later sytnax errors appear to prevent the bulk of the program from running at all or if earlier errors cause run-time exception.
After this process finishes, Perl executes the parse tree that results. This means that Perl code can rewrite the parse tree before your code is ever really written and that code can be inserted into that part o the process.
Transparent code injection during 'use'
Consider a simple Perl script:
#!/usr/bin/perl
use lib '.';
use Cwd;
use 5.010;
use strict;
use warnings;
say getcwd();
Looks straight-forward. And in most cases it will do exactly what it looks like it does. It loads the standard Cwd module and prints out the current working directory.
However, suppose I run it in a different directory, where I add two additional files:
Cwd.pm contains:
package Cwd;
use Injected;
1;
hmmmm that doesn't look good. What does Injected.pm do?
package Injected;
use strict;
sub import {
local @INC = @INC;
my ($module) = caller;
warn $module;
delete $INC{'Injected.pm'};
delete $INC{"$module.pm"};
@INC = grep { $_ ne '.' } @INC;
eval "require $module";
warn "Got you!, via $module";
}
1;
So when Cwd imports Injected, it deletes itself from the memory of having been included, deletes its caller too, reloads the correct caller (not from the current working directory) and then executes some code (here a harmless warning).
Cwd.pm then returns success
test.pl runs Cwd->import() which is now the correct one, but we have already run unintended code that could in theory do anything.
Any program capable of being run in arbitrary directories, written in Perl, which has this line in it (use lib '.') is subject to arbitrary code injection attacks using any module in the dependency tree, required or not.
Instead, do the opposite (where you can)
As a standard partof boilerplate in any secure Perl program, I strongly recommend adding the following line to the top of any script. As long as modules don't add it back in behind your back (would be extremely rare that they would), adding the following line:
no lib '.';
Note that this strips out the current working directory even if it si supplied as a command-line argument. So it may not be possible in all cases. So use common sense, and do some testing, and document this as desired behavior. Note one can still invoke with perl -I./. in most cases so it is possible to turn this safety off..... Additionally if you put that at the start of your module, something you include could possibly put it back in.
Safer Alternatives
In a case where you need a path relative to the script being executed, FindBin is the ideal solution. It gives you a fixed path relative to the script being run, which is usually sufficient for most cases of an application being installed on a system as a third party. So instead you would do:
use FindBin;
use lib $FindBin::Bin;
Then the script's directory will be in the include path.
PL/PerlU notes:
I always add the explicit rejection of cwd in my plperlu functions. However if someone has a program that is broken by CVE-2016-1238 related fixes, it is possible that someone would add a use lib '.' to a perl module, which is a bad idea. As discussed in the previous post, careful code review is required to be absolutely safe. Additionally, it is a very good idea to periodically check the PostgreSQL data directory for perl modules which would indicate a compromised system.
Tuesday, August 2, 2016
PostgreSQL, PL/Perl, and CVE-2016-1238
This post is about the dangers in writing user defined functions in untrusted languages, but it is also specifically about how to avoid CVE-2016-1238-related problems when writing PL/PerlU functions. The fix is simple and straight-forward and it is important for it to be in pl/perlU stored procedures and user defined functions for reasons I will discuss. This discusses actual exploits and the severity of being able to inject abritrary Perl code into the running database backend is a good reason to be disciplined and careful about the use of this language.
It is worth saying at the outset that I have been impressed by how well sensible design choices in PostgreSQL generally mitigate problems like this. In essence you have to be working in an environment where a significant number of security measures have been bypassed either intentionally or not. This speaks volumes on the security of PostgreSQL's design since it is highly unlikely that this particular attack vector was an explicit concern. In other words these decisions make many attacks even against untrusted languages far more difficult than they would be otherwise.
The potential consequences are severe enough, however, that secure coding is particularly important in this environment even with the help of the secure design. And in any language it is easy to code yourself into corners where you aren't aware you are introducing security problems until they bite you.
The current implementation, we will see, already has a fair bit of real depth of defense behind it. PostgreSQL is not, by default, vulnerable to the problems in the CVE noted in the title. However, with a little recklessness, it is possible to open the door to the possibility of real problems and it is possible for these problems to be hidden from the code reviewer by error rather than actual malice. Given the seriousness of what can happen if you can run arbitrary code in the PostgreSQL back-end, my view is that all applicable means should be employed to prevent problems.
PL/PerlU can be used in vulnerable ways, but PL/Perl (without the U) is by design safe. Even with PL/PerlU it is worth noting that multiple safety measures have to be bypassed before vulnerability becomes a concern. This is not about any vulnerabilities in PostgreSQL, but what vulnerabilities can be added through carelessly writing stored procedures or user-defined functions.
There are two lessons I would like people to take away from this. The first is how much care has been taken with regard to PostgreSQL regarding security in design. The second is how easily one can accidentally code oneself into a corner. PL/PerlU often is the right solution for many problem domains and it can be used safely but some very basic rules need to be followed to stay out of trouble.
PostgreSQL has a language handler system that allows user defined functions and stored procedures to be written in many different languages. Out of the box, Python, TCL, C, and Perl come supported out of the box. Perl and TCL come in trusted and untrusted variants (see below), while Python and C are always untrusted.
There are large numbers of external language handlers as well, and it is possible to write ones own. Consequently, PostgreSQL allows, effectively, SQL to be extended by plugins written in any language. The focus here will be on untrusted Perl, or PL/perlU. Untrusted languages have certain inherent risks in their usage and these become important, as here, when there is concern about specific attack vectors.
PostgreSQL allows languages to be marked as 'trusted' or 'untrusted' by the RDBMS. Trusted languages are made available for anyone to write functions for while untrusted languages are restricted to database superusers. Trusted languages are certified by the developers not to interact with file handles, not to engage in any other activity other than manipulating data in the database.
There is no way to 'use' or 'require' a Perl module in pl/perl (trusted) and therefore it is largely irrelevant for our discussion. However PL/PerlU can access anything else on the system and it does so with the same permissions as the database manager itself. Untrusted languages, like PL/PerlU make it possible to extend SQL in arbitrary ways by injecting (with the database superuser's permission!) code into SQL queries written in whatever langauges one wants. Untrusted languages make PostgreSQL one of the most programmable relational databases in the world, but they also complicate database security in important ways which are well beyond the responsibility of PostgreSQL as a project.
Like test harnesses, untrusted languages are insecure by design (i.e. they allow arbitrary code injection into the database backend) and this issue is as heavily mitigated as possible, making PostgreSQL one of the most security-aware databases in the market.
To define a function in an untrusted language, one must be a database superuser, so the PostgeSQL community places primary trust in the database administration team not to do anything stupid, which is generally a good policy. This article is largely in order to help that policy be effective.
The CVE referenced in the title of this article and section is one which allows attackers to inject code into a running Perl routine by placing it in the current working directory of a Perl process. If an optional dependency of a dependency is placed i the current working directory, it may be included in the current Perl interpreter without the understanding of the user. This is a problem primarily because Perl programs are sufficiently complex that people rarely understand the full dependency tree of what they are running.
If the current working directory ends up being one which includes user-writeable data of arbitrary forms, the problem exists. If not, then one is safe.
The problem however is that changing directories changes the include path. You can demonstrate this by doing as follows:
In ./test.pl create a file that contains:
use test;
use test2;
./test.pm is:
chdir test;
\
test/test2.pm is:
warn 'haxored again';
What happens is that the use test statement includes ./test.pm which changes directory, so when you use test2, you are including from test/test2.pm. This means that if any period during this cycle of the Perl interpreter's life you are in a world-writable directory, and including or requring files, you are asking for trouble.
The usual use case of PL/PerlU is where you need a CPAN module to process or handle information.. For example you may want to return json using JSON.pm, or you may want to write to a (non-transactional log).
For example, the most recent PL/PerlU function I wrote used basic regular expressions to parse a public document record stored in a database to extract information relating to patents awarded on protein sequences. It was trivial but was easier to use JSON than to write json serialization inline (and yes, performed well enough given the data sizes operate on).
Here are a few pl/perlU functions which show the relevant environment that PL/PerlU functions run in by default:
postgres=# create or replace function show_me_inc() returns setof text language plperlu as
$$
return \@INC;
$$;
CREATE FUNCTION
postgres=# select show_me_inc();
show_me_inc
------------------------------
/usr/local/lib64/perl5
/usr/local/share/perl5
/usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl
/usr/lib64/perl5
/usr/share/perl5
.
(7 rows)
postgres=# create or replace function get_cwd() returns text language plperlu as
postgres-# $$
postgres$# use Cwd;
postgres$# return getcwd();
postgres$# $$;
CREATE FUNCTION
postgres=# select get_cwd();
get_cwd
---------------------
/var/lib/pgsql/data
(1 row)
Wow. I did not expect such a sensible, secure implementation. PostgreSQL usually refuses to start if non-superusers of the system have write access to the data directory. So this is, by default, a very secure configuration right up until the first chdir() operation......
Now, in the normal use case of a user defined function using PL/PerlU, you are going to have no problems. The reason is that most of the time, if you are doing sane things, you are going to want to write immutable functions which have no side effects and maybe use helpers like JSON.pm to format data. Whether or not there are vulnerabilities exploitable via JSON.pm, they cannot be exploited in this manner.
However, sometimes people do the wrong things in the database and here, particularly, trouble can occur.
To have an exploit in pl/perlU code, several things have to happen:
So suppose company A receives a text file via an anonymous ftp drop box in X12 format (the specific format is not relevant, just that it comes in with contents and a file name that are specified by the external user). A complex format like X12 means they are highly unlikely to do the parsing themselves so they implement a module loader in PostgreSQL. The module loader operates on a file handle as such:
On import, the module loader changes directories to the incoming directory. In the actual call to get_handle, it opens a file handle, and creates an iterator based on that, and returns it. Nobody notices that the directory is not changed back because this is then loaded into the db and no other file access is done here. I have seen design problems of this magnitude go undetected for an extended period, so coding defensively means assuming they will exist.
Now, next, this is re-implemented in the database as such:
CREATE OR REPLACE FUNCTION load_incoming_file(fiilename text)
RETURNS int
language plperlu as
$$
use CompanyFileLoader '/var/incoming'; # oops, we left the data directory
use CompanyConfig 'our_format'; # oops optional dependency that falls back to .
# in terms of exploit, the rest is irrelevant
# for a proof of concept you could just return 1 here
use strict;
use warnings;
my $filename = shift;
my $records = CompanyFileLoader->get_handle($filename);
while ($r = $records->next){
# logic to insert into the db omitted
}
return $records->count;
$$;
The relevant poart of CompanyFileLoader.pm is (the rest could be replaced with stubs for a proof of concept):
package CompanyFileLoader;
use strict;
use warnings;
my @dirstack;
sub import {
my $dir = pop;
push @dirstack, $dir;
chdir $dir;
}
Now, in a real-world published module, this would cause problems but in a company's internal operations it might not pose discovered problems in a timely fashion.
The relevant part of CompanyConfig is:
package CompanyConfig;
use strict;
use warnings;
eval { require 'SupplementalConfig' };
Now, if a SupplementalConfig.pm is loaded into the same directory as the text files, it will get loaded and run as part of the pl/perlU function.
Now to exploit this someone with knowledge of the system has to place this in that directory. It could be someone internal due to failure to secure the inbound web service properly. It could be someone external who has inside knowledge (a former employee for example). Or a more standard exploit could be tried on the basis that some other shared module might have a shared dependency.
The level of inside knowledge required to pull that off is large but the consequences are actually pretty dire. When loaded, the perl module interacts with the database with the same permissions as the rest of the function, but it also has filesystem access as the database server. This means it could do any of the following:
The danger can be effectively prevented by following some basic rules:
All user defined functions and stored procedures in PL/PerlU should include the line:
no lib '.';
It is possible that modules could add this back in behind your back, but for published modules this is extremely unlikely. So local development projects should not use lib '.' in order to prevent this.
Secondly, never use chdir in a pl/perl function. Remember you can always do file operations with absolute paths. Without chdir, no initial exploit against the current working directory is possible through pl/perlu. Use of chdir circumvents important safety protections in PostgreSQL.
Thirdly it is important that one sticks to well maintained modules. Dangling chdir's in a module's load logic are far more likely to be found and fixed when lots of other people are using a module, and a dangling chdir is a near requirement to accidental vulnerability. For internal modules, they need to be reviewed both for optional dependencies usage and dangling chdir's in the module load logic.
It is worth saying at the outset that I have been impressed by how well sensible design choices in PostgreSQL generally mitigate problems like this. In essence you have to be working in an environment where a significant number of security measures have been bypassed either intentionally or not. This speaks volumes on the security of PostgreSQL's design since it is highly unlikely that this particular attack vector was an explicit concern. In other words these decisions make many attacks even against untrusted languages far more difficult than they would be otherwise.
The potential consequences are severe enough, however, that secure coding is particularly important in this environment even with the help of the secure design. And in any language it is easy to code yourself into corners where you aren't aware you are introducing security problems until they bite you.
The current implementation, we will see, already has a fair bit of real depth of defense behind it. PostgreSQL is not, by default, vulnerable to the problems in the CVE noted in the title. However, with a little recklessness, it is possible to open the door to the possibility of real problems and it is possible for these problems to be hidden from the code reviewer by error rather than actual malice. Given the seriousness of what can happen if you can run arbitrary code in the PostgreSQL back-end, my view is that all applicable means should be employed to prevent problems.
PL/PerlU can be used in vulnerable ways, but PL/Perl (without the U) is by design safe. Even with PL/PerlU it is worth noting that multiple safety measures have to be bypassed before vulnerability becomes a concern. This is not about any vulnerabilities in PostgreSQL, but what vulnerabilities can be added through carelessly writing stored procedures or user-defined functions.
There are two lessons I would like people to take away from this. The first is how much care has been taken with regard to PostgreSQL regarding security in design. The second is how easily one can accidentally code oneself into a corner. PL/PerlU often is the right solution for many problem domains and it can be used safely but some very basic rules need to be followed to stay out of trouble.
Extending SQL in PostgreSQL using Arbitrary Languages
PostgreSQL has a language handler system that allows user defined functions and stored procedures to be written in many different languages. Out of the box, Python, TCL, C, and Perl come supported out of the box. Perl and TCL come in trusted and untrusted variants (see below), while Python and C are always untrusted.
There are large numbers of external language handlers as well, and it is possible to write ones own. Consequently, PostgreSQL allows, effectively, SQL to be extended by plugins written in any language. The focus here will be on untrusted Perl, or PL/perlU. Untrusted languages have certain inherent risks in their usage and these become important, as here, when there is concern about specific attack vectors.
Trusted vs Untrusted Languages, and what PL/Perl and PL/PerlU can do
PostgreSQL allows languages to be marked as 'trusted' or 'untrusted' by the RDBMS. Trusted languages are made available for anyone to write functions for while untrusted languages are restricted to database superusers. Trusted languages are certified by the developers not to interact with file handles, not to engage in any other activity other than manipulating data in the database.
There is no way to 'use' or 'require' a Perl module in pl/perl (trusted) and therefore it is largely irrelevant for our discussion. However PL/PerlU can access anything else on the system and it does so with the same permissions as the database manager itself. Untrusted languages, like PL/PerlU make it possible to extend SQL in arbitrary ways by injecting (with the database superuser's permission!) code into SQL queries written in whatever langauges one wants. Untrusted languages make PostgreSQL one of the most programmable relational databases in the world, but they also complicate database security in important ways which are well beyond the responsibility of PostgreSQL as a project.
Like test harnesses, untrusted languages are insecure by design (i.e. they allow arbitrary code injection into the database backend) and this issue is as heavily mitigated as possible, making PostgreSQL one of the most security-aware databases in the market.
To define a function in an untrusted language, one must be a database superuser, so the PostgeSQL community places primary trust in the database administration team not to do anything stupid, which is generally a good policy. This article is largely in order to help that policy be effective.
Paths, Permissions, and CVE-2016-1238
The CVE referenced in the title of this article and section is one which allows attackers to inject code into a running Perl routine by placing it in the current working directory of a Perl process. If an optional dependency of a dependency is placed i the current working directory, it may be included in the current Perl interpreter without the understanding of the user. This is a problem primarily because Perl programs are sufficiently complex that people rarely understand the full dependency tree of what they are running.
If the current working directory ends up being one which includes user-writeable data of arbitrary forms, the problem exists. If not, then one is safe.
The problem however is that changing directories changes the include path. You can demonstrate this by doing as follows:
In ./test.pl create a file that contains:
use test;
use test2;
./test.pm is:
chdir test;
\
test/test2.pm is:
warn 'haxored again';
What happens is that the use test statement includes ./test.pm which changes directory, so when you use test2, you are including from test/test2.pm. This means that if any period during this cycle of the Perl interpreter's life you are in a world-writable directory, and including or requring files, you are asking for trouble.
Usual Cases for PL/PerlU
The usual use case of PL/PerlU is where you need a CPAN module to process or handle information.. For example you may want to return json using JSON.pm, or you may want to write to a (non-transactional log).
For example, the most recent PL/PerlU function I wrote used basic regular expressions to parse a public document record stored in a database to extract information relating to patents awarded on protein sequences. It was trivial but was easier to use JSON than to write json serialization inline (and yes, performed well enough given the data sizes operate on).
Are usual cases safe? Deceptively so!
Here are a few pl/perlU functions which show the relevant environment that PL/PerlU functions run in by default:
postgres=# create or replace function show_me_inc() returns setof text language plperlu as
$$
return \@INC;
$$;
CREATE FUNCTION
postgres=# select show_me_inc();
show_me_inc
------------------------------
/usr/local/lib64/perl5
/usr/local/share/perl5
/usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl
/usr/lib64/perl5
/usr/share/perl5
.
(7 rows)
postgres=# create or replace function get_cwd() returns text language plperlu as
postgres-# $$
postgres$# use Cwd;
postgres$# return getcwd();
postgres$# $$;
CREATE FUNCTION
postgres=# select get_cwd();
get_cwd
---------------------
/var/lib/pgsql/data
(1 row)
Wow. I did not expect such a sensible, secure implementation. PostgreSQL usually refuses to start if non-superusers of the system have write access to the data directory. So this is, by default, a very secure configuration right up until the first chdir() operation......
Now, in the normal use case of a user defined function using PL/PerlU, you are going to have no problems. The reason is that most of the time, if you are doing sane things, you are going to want to write immutable functions which have no side effects and maybe use helpers like JSON.pm to format data. Whether or not there are vulnerabilities exploitable via JSON.pm, they cannot be exploited in this manner.
However, sometimes people do the wrong things in the database and here, particularly, trouble can occur.
What gets you into trouble?
To have an exploit in pl/perlU code, several things have to happen:
- Either a previous exploit must exist which has written files to the PostgreSQL data directory, or one must change directories
- After changing directories, a vulnerable module must be loaded while in a directory the attacker has write access to.
It is possible, though unlikely, for the first to occur behind your back. But people ask me all the time how to send email from the database backend and so you cannot always guarantee people are thinking through the consequences of their actions.
So vulnerabilities can occur when people are thinking tactically and coding in undisciplined ways. This is true everywhere but here the problems are especially subtle and they are as dangerous as they are rare.
An attack scenario.
So suppose company A receives a text file via an anonymous ftp drop box in X12 format (the specific format is not relevant, just that it comes in with contents and a file name that are specified by the external user). A complex format like X12 means they are highly unlikely to do the parsing themselves so they implement a module loader in PostgreSQL. The module loader operates on a file handle as such:
On import, the module loader changes directories to the incoming directory. In the actual call to get_handle, it opens a file handle, and creates an iterator based on that, and returns it. Nobody notices that the directory is not changed back because this is then loaded into the db and no other file access is done here. I have seen design problems of this magnitude go undetected for an extended period, so coding defensively means assuming they will exist.
Now, next, this is re-implemented in the database as such:
CREATE OR REPLACE FUNCTION load_incoming_file(fiilename text)
RETURNS int
language plperlu as
$$
use CompanyFileLoader '/var/incoming'; # oops, we left the data directory
use CompanyConfig 'our_format'; # oops optional dependency that falls back to .
# in terms of exploit, the rest is irrelevant
# for a proof of concept you could just return 1 here
use strict;
use warnings;
my $filename = shift;
my $records = CompanyFileLoader->get_handle($filename);
while ($r = $records->next){
# logic to insert into the db omitted
}
return $records->count;
$$;
The relevant poart of CompanyFileLoader.pm is (the rest could be replaced with stubs for a proof of concept):
package CompanyFileLoader;
use strict;
use warnings;
my @dirstack;
sub import {
my $dir = pop;
push @dirstack, $dir;
chdir $dir;
}
Now, in a real-world published module, this would cause problems but in a company's internal operations it might not pose discovered problems in a timely fashion.
The relevant part of CompanyConfig is:
package CompanyConfig;
use strict;
use warnings;
eval { require 'SupplementalConfig' };
Now, if a SupplementalConfig.pm is loaded into the same directory as the text files, it will get loaded and run as part of the pl/perlU function.
Now to exploit this someone with knowledge of the system has to place this in that directory. It could be someone internal due to failure to secure the inbound web service properly. It could be someone external who has inside knowledge (a former employee for example). Or a more standard exploit could be tried on the basis that some other shared module might have a shared dependency.
The level of inside knowledge required to pull that off is large but the consequences are actually pretty dire. When loaded, the perl module interacts with the database with the same permissions as the rest of the function, but it also has filesystem access as the database server. This means it could do any of the following:
- Write perl modules to exploit this vulnerability in other contexts to the Pg data directory
- delete or corrupt database files
- possibly alter log files depending on setup.
- Many other really bad things.
These risks are inherent with the use of untrusted languages, that you can write vulnerable code and introduce security problems into your database. This is one example of that and I think the PostgreSQL team has done an extremely good job of making the platform secure.
Disciplined coding to prevent problems
The danger can be effectively prevented by following some basic rules:
All user defined functions and stored procedures in PL/PerlU should include the line:
no lib '.';
It is possible that modules could add this back in behind your back, but for published modules this is extremely unlikely. So local development projects should not use lib '.' in order to prevent this.
Secondly, never use chdir in a pl/perl function. Remember you can always do file operations with absolute paths. Without chdir, no initial exploit against the current working directory is possible through pl/perlu. Use of chdir circumvents important safety protections in PostgreSQL.
Thirdly it is important that one sticks to well maintained modules. Dangling chdir's in a module's load logic are far more likely to be found and fixed when lots of other people are using a module, and a dangling chdir is a near requirement to accidental vulnerability. For internal modules, they need to be reviewed both for optional dependencies usage and dangling chdir's in the module load logic.
Saturday, July 30, 2016
Notes on Security, Separation of Concerns and CVE-2016-1238 (Full Disclosure)
A cardinal rule of software security is that, when faced with a problem, make sure you fully understand it before implementing a fix. A cardinal rule of general library design is that heuristic approaches to deciding whether something is a problem really should be disfavored. These lessons were driven home when I ended up spending a lot of time debugging problems caused by a recent Debian fix for the CVE in the title. Sure everyone messes up sometimes, and so this isn't really condemnation of Debian as a project but their handling of this particular CVE is a pretty good example of what not to do.
In this article I am going to discuss actual exploits. Full disclosure has been a part of the LedgerSMB security culture since we started and discussion of exploits in this case provide administrators with real chances to secure their systems, as well as distro maintainers, Perl developers etc to write more secure software. Recommendations will be further given at the end regarding improving the security of Perl as a programming language.
Part of the problem in this case is that the CVE is poorly scoped but CVE's are often poorly scoped and it is important for developers to work with security researchers to understand a problem, understand the implications of different approaches to fix it and so forth. It is very easy to get in a view that "this must be fixed right now" but all too often (as here) a shallow fix does not completely resolve an issue and causes more problems than it resolves.
Perl's system of module inclusion (and other operations) looks for Perl modules in the current directory after exhausting other directories. Technically this is optional but most UNIX and Linux distributions have this behavior. On the whole it is bad practice as well, which is why it is not by the default behavior of shells like bash. But a lot of software depends on this (with some legitimate use cases) and so changing it is problematic.
Perl programs are also often complex and have optional dependencies which may or may not exist on a system. If those do not otherwise exist in the system Perl directories but exist in the current working directory, then these may be loaded from the current working directory. Note that this is not actually limited to the current working directory, and Perl could load the files from all kinds of places, users-specified or not.
So when one is running a Perl program in a world-writeable location, there is the opportunity for another user to put code there that may be picked up by the Perl interpreter and executed. While the CVE is limited to implicit inclusion of the current working directory, the problem is actually quite a bit broader than that. Include paths can be specified on the command line and if any of them are world-writeable, then variations of the same attacks are possible.
Some programs, of course, are intended to run arbitrary Perl code. The test harness programs are good examples of this. Special attention will be given to the ways test harness programs can be exploited here.
These features come together to create opportunities for exploits in multi-user systems which administrators need to be aware of and take immediate steps to prevent. In my view there are a few important and needed features in Perl as well.
A simple exploit:
Create the following files in a safe directory:
t/01-security.t, contents:
use Test::More;
require bar;
eval { require foo };
plan skip_all => 'nothing to do';
lib/bar.pm contents:
use 5.010;
warn "this is ok";
./foo.pm, contents:
use 5.010;
warn "Haxored!";
now run:
prove -Ilib t/01-security.t
Now, what happens here is that the optional requirement of foo.pm in the test script gets resolved to the one that happens to be in your current working directory. If that directory were world writeable, then someone could add that file and it would be run when you run your test cases.
Now, it turns out that this is not a vulnerability with prove. Because prove runs Perl in a separate process and parses the output, eliminating the resolution inside prove itself has no effect. What this means is that the directory where you run something like prove can really matter and if you happen to be in a world writeable directory when you run it (or other Perl programs) you run the risk of including unintended code supplied by other users. Not good. None of the proposed fixes address the full scope of this problem either. Note that if any directory in @INC is world-writeable, a security problem exists. And because these can be specified in the Perl command line, this is far more of a root problem than the mere inclusion of the current working directory.
All exploits of this sort can be prevented even without the recommendations being followed in the proper fixes section. System administrators should:
The approach recommended by the reporter of the problem is to make modules exclude an implicit current working directory when loading optional dependencies. This, as I will show, raises very serious separation of concerns problems and in Debian's case includes one serious bug which is not obvious from outside. Moreover it doesn't address the problems caused by running test harnesses and the like in untrusted directories. So one gets a *serious* problem with very little real security benefit.
If you are reading through the diff linked to above, you will note it is basically boilerplate that localizes @INC and removes the last entry if it is equal to a single dot. This breaks base.pm badly because inheritance in Perl no longer follows @INC the way use does. Without this patch the following are almost equivalent with the exception that the latter also runs the module's import() routine:
use base 'Myclass';
and
use Myclass;
use base 'Myclass';
But with this patch, the latter works and the former does not unless you specify -MMyclass in the Perl command line. This occurs because someone is thinking technically about an issue without comprehending that this isn't an optional dependency in base and therefore the problem doesn't apply there. But the problem is not quickly evident in the diff, nor is it evident when trying to fix this in this way on the module level. It breaks the software contract badly, and does so for no benefit.
As a general rule, modules should be expected to act sanely when it comes to their own internal structure, but playing with @INC violates basic separation of concerns and a system that cannot be readily understood cannot be readily secured (which is why this is a real issue in the first place -- nobody understands all the optional dependencies of all the dependencies of every script on their system).
There are several things that Perl and Linux distros can do to provide proper fixes to this sort of problem. These approaches do not violate the issues of separation of concerns. The first and most important is to provide an option to globally remove '.' from @INC on a per-system basis. This is one of the things that Debian did right in their reaction to this. Providing tools for administrators to secure their systems is a good thing.
A second thing is that Perl already has a number of enhanced modes for dealing with security concerns and adding another would be a good idea. In fact, this could probably be done as a pragma which would:
In this article I am going to discuss actual exploits. Full disclosure has been a part of the LedgerSMB security culture since we started and discussion of exploits in this case provide administrators with real chances to secure their systems, as well as distro maintainers, Perl developers etc to write more secure software. Recommendations will be further given at the end regarding improving the security of Perl as a programming language.
Part of the problem in this case is that the CVE is poorly scoped but CVE's are often poorly scoped and it is important for developers to work with security researchers to understand a problem, understand the implications of different approaches to fix it and so forth. It is very easy to get in a view that "this must be fixed right now" but all too often (as here) a shallow fix does not completely resolve an issue and causes more problems than it resolves.
The Problem (with exploits)
Perl's system of module inclusion (and other operations) looks for Perl modules in the current directory after exhausting other directories. Technically this is optional but most UNIX and Linux distributions have this behavior. On the whole it is bad practice as well, which is why it is not by the default behavior of shells like bash. But a lot of software depends on this (with some legitimate use cases) and so changing it is problematic.
Perl programs are also often complex and have optional dependencies which may or may not exist on a system. If those do not otherwise exist in the system Perl directories but exist in the current working directory, then these may be loaded from the current working directory. Note that this is not actually limited to the current working directory, and Perl could load the files from all kinds of places, users-specified or not.
So when one is running a Perl program in a world-writeable location, there is the opportunity for another user to put code there that may be picked up by the Perl interpreter and executed. While the CVE is limited to implicit inclusion of the current working directory, the problem is actually quite a bit broader than that. Include paths can be specified on the command line and if any of them are world-writeable, then variations of the same attacks are possible.
Some programs, of course, are intended to run arbitrary Perl code. The test harness programs are good examples of this. Special attention will be given to the ways test harness programs can be exploited here.
These features come together to create opportunities for exploits in multi-user systems which administrators need to be aware of and take immediate steps to prevent. In my view there are a few important and needed features in Perl as well.
A simple exploit:
Create the following files in a safe directory:
t/01-security.t, contents:
use Test::More;
require bar;
eval { require foo };
plan skip_all => 'nothing to do';
lib/bar.pm contents:
use 5.010;
warn "this is ok";
./foo.pm, contents:
use 5.010;
warn "Haxored!";
now run:
prove -Ilib t/01-security.t
Now, what happens here is that the optional requirement of foo.pm in the test script gets resolved to the one that happens to be in your current working directory. If that directory were world writeable, then someone could add that file and it would be run when you run your test cases.
Now, it turns out that this is not a vulnerability with prove. Because prove runs Perl in a separate process and parses the output, eliminating the resolution inside prove itself has no effect. What this means is that the directory where you run something like prove can really matter and if you happen to be in a world writeable directory when you run it (or other Perl programs) you run the risk of including unintended code supplied by other users. Not good. None of the proposed fixes address the full scope of this problem either. Note that if any directory in @INC is world-writeable, a security problem exists. And because these can be specified in the Perl command line, this is far more of a root problem than the mere inclusion of the current working directory.
Security Considerations for System Administrators and Software Developers
All exploits of this sort can be prevented even without the recommendations being followed in the proper fixes section. System administrators should:
- Make sure that all directories in @INC other than the current working directory are properly secured against write access (this is a no brainer, but is worth repeating)
- Programs such as test harnesses which execute arbitrary Perl code should ONLY be run in properly secured directories, and the only time prove should ever be run as root is when installing (as root) modules from cpan.
- Scripts intended to be run in untrusted directories should be audited and one should ensure (and add if it is missing) the following line: no lib '.';
Software developers should:
- think carefully about where a script might be executed. If it is intended to be run on directories of user-supplied files, then include the no lib '.'; (This does not apply to test harnesses and other programs which execute arbitrary Perl programs).
- Module maintainers should probably avoid using optional config modules to do configuration. These optional configuration modules provide standard points of attack. Use non-executable configuration files instead or modules which contain interfaces for a programmer to change the configuration.
What is wrong with the proposed fixes
The approach recommended by the reporter of the problem is to make modules exclude an implicit current working directory when loading optional dependencies. This, as I will show, raises very serious separation of concerns problems and in Debian's case includes one serious bug which is not obvious from outside. Moreover it doesn't address the problems caused by running test harnesses and the like in untrusted directories. So one gets a *serious* problem with very little real security benefit.
If you are reading through the diff linked to above, you will note it is basically boilerplate that localizes @INC and removes the last entry if it is equal to a single dot. This breaks base.pm badly because inheritance in Perl no longer follows @INC the way use does. Without this patch the following are almost equivalent with the exception that the latter also runs the module's import() routine:
use base 'Myclass';
and
use Myclass;
use base 'Myclass';
But with this patch, the latter works and the former does not unless you specify -MMyclass in the Perl command line. This occurs because someone is thinking technically about an issue without comprehending that this isn't an optional dependency in base and therefore the problem doesn't apply there. But the problem is not quickly evident in the diff, nor is it evident when trying to fix this in this way on the module level. It breaks the software contract badly, and does so for no benefit.
As a general rule, modules should be expected to act sanely when it comes to their own internal structure, but playing with @INC violates basic separation of concerns and a system that cannot be readily understood cannot be readily secured (which is why this is a real issue in the first place -- nobody understands all the optional dependencies of all the dependencies of every script on their system).
Recommendations for proper fixes
There are several things that Perl and Linux distros can do to provide proper fixes to this sort of problem. These approaches do not violate the issues of separation of concerns. The first and most important is to provide an option to globally remove '.' from @INC on a per-system basis. This is one of the things that Debian did right in their reaction to this. Providing tools for administrators to secure their systems is a good thing.
A second thing is that Perl already has a number of enhanced modes for dealing with security concerns and adding another would be a good idea. In fact, this could probably be done as a pragma which would:
- On load, check to see that directories in @INC are not world-writeable -- if they are, remove them from @INC and warn, and
- when using lib, check to see if the directory is world-writeable and if it is die hard.
But making this a module's responsibility to care what @INC says? That's a recipe for problems and not of solutions both security and otherwise.
Tuesday, January 26, 2016
On Contributor Codes of Conduct and Social Justice
The PostgreSQL, Ruby, and PHP communities have all been considering codes of conduct for contributors. The LedgerSMB community already uses the Ubuntu Code of Conduct. Because this addresses many projects, I am syndicating this further than where there are current issues. This is not a technical post and it covers a wide range of very divisive issues for a very diverse audience. I can only hope that the nuance I am trying to communicate comes across.
Brief History
A proximal cause seems to be an event referred to as "Opalgate" where an Italian individual who claimed to be a part of the Opal project made some unrelated tweets in an exchange about the politics of education and the question of how gender should be presented, and some people got offended and demanded his resignation (at least that is my reading of the Twitter exchange but I have been outside the US long enough to lose the context in which it would likely be read by an American in the US). The details are linked below, but the core questions involve how much major contributors to projects need to keep from saying anything at all about divisive issues seems to be a recurring topic. Moreover it is a legitimate one.
Like some of my blog posts, this goes into touchy territory. I am discussing things which require a great deal of nuance. Chances are, regardless of where you sit on some of these issues, you will be offended by things I say, but there are worse things than to be offended (one of them is never to be challenged by different viewpoints).
I write here as someone who has lived in a number of very different cultures and who can see perspectives on many of these issues which are not present in American political discourse For this reason, I think it is important for me to share the concerns I see because otherwise open source software maintainers often don't have a perspective outside of Western countries, or even outside the US.
Of course as open source software maintainers we want everyone to feel safe and valued as members of the community. But cultural tensions and ways of life do crop up and taking a position on these as a community will always do more harm than good.
Background Reading regarding Opalgate and the question of so-called "social justice warriors" in open source
It may seem strange to put a list of links for background reading near the start of an article, but I want to make sure that such material is available up front. People can read about Opalgate here and the ongoing debate between various parties about it. It's important background reading but somewhat peripheral to the overall problems involved. It may or may not be the best example of the difficulties in running cross-cultural projects but it does highlight the difficulties that come in addressing diverse community bases, those which may have deep philosophical disagreements about things which people take very personally.
- The original twitter exchange
- A writeup on Geek Feminism
- Eric Raymond's post on why Open Source Projects should eject "social justice warriors"
- Caroline Ada Ehmke's response to Eric Raymond
- Caroline Ada Ehmke's post on Opalgate
In the interest of full disclosure, I too worry that there is too much eagerness to liberate children from concepts of gender and too little thought about how this can and will be abused, and what the life costs for the children actually will be. I believe that we must be human and humane to all but I am concerned that the US is going down a path that strikes me as anything but that in the long run. That doesn't mean that the concerns of the trans community in the US should be ignored, but that doesn't mean they should be paramount either. As communities we need to come together to solve problems not fight culture wars.
Twitter is not a medium which is conducive to thoughtful exchange so I also have to cut some slack. Probably not the wisest medium to discuss controversial topics. But people around the world have deep differences in views on major controversies. My wife, for example, is far more opposed to abortion than I am, and having come to a deeper understanding of her culture, I don't disagree that in her cultural context, it is more harmful. But that brings me to another problem, that many issues are contextual and we cannot see how others really are impacted by such changes, particularly when forced from the outside.
But my view doesn't matter everywhere. It matters in my family, my discussions with people I know, and so forth. But most of the world is not my responsibility nor should it be. These are not entirely easy issues and there should be room for disagreement.
Is Open Source Political?
Caroline Ada Ehmke's basic argument is that open source is inherently political, that it seeks a positive change in the world, and therefore it should ally itself with others sharing the same drive to make the world a better place. I think this viewpoint is misguided but because it is only half-wrong.
Aristotle noted that all human relationships are necessarily political. The three he chose as primary in Politics is illustrative: master and slave (we could update to boss and worker); husband and wife; and king and subject. To Aristotle, the human being alone is incomplete. We are our relationships and our politics follows from them. While there has been an effort to separate the personal and the political in modern times, Feminist historians have kept this tradition alive and well. A notion of the political grounded in humans as social animals is fundamentally more conducive to justice than cold, mechanical, highly engineered social machinery. Moreover Aristotle notes that all communities are built on some concept of the good, that humans only want things that seem good to them and therefore we can assume that all groups seek a better world, but we don't always know which ones deliver, and that is the problem.
Open source begins not with an ideology but with a conviction. Not everybody shares the same conviction. Not everyone participates in open source for the same reason. But everyone has a reason, some conviction that what they are doing is good. There is enough commonality for us all to work together, but that commonality is not as strong as one may think.
In a previous post on this blog I argued for a very different understanding of software freedom than Richard Stallman supposes, for example. While he holds a liberal enumerated liberties view, I hold a traditionalist work-ownership view. Naturally that leads to different things we look for in an ideal license.
And the diversity in viewpoint does not stop there. Some come to open source because they believe that open source is a better way of writing software. Some because they believe that open source software delivers benefits in use. But regardless of our disagreement we share the understanding that open source software brings community and individual benefits.
And the diversity in viewpoint does not stop there. Some come to open source because they believe that open source is a better way of writing software. Some because they believe that open source software delivers benefits in use. But regardless of our disagreement we share the understanding that open source software brings community and individual benefits.
In two ways then is open source software political:
- Communities require governance and this is inherently political, and
- To the extent there is a goal to transform the software industry to one of open source that is political.
The first as we will see is a major problem. Open source communities are diverse in a way few Americans can fully comprehend (we like to think everyone is like us and there is one right way, the American Way whether that is in industry -- the right -- or formulations of rights -- the left). Thus most discussions end up being Western-normative (and in particular American-normative) and disregard perspectives from places like India, Malaysia, Indonesia, and so forth.
However it is worth coming back to the point that what brings us together is an economic vision. Yes, that is intrinsically and highly political, but it also has consequences for other causes and therefore it is worth being skeptical of alliances with groups in other directions. What would an open source-based economy look like? What would the businesses look like? Will they be the corporations of today or the perpetual family businesses and trades of yesteryear? And if the latter, what is the implication for the family? Many of these questions (just like questions of same-sex marriage) depend in large part on the current social institutions in a culture -- the implications of an industrial, corporate, weak family society adopting something like same-sex marriage are very different than in an agrarian or family-business, strong family society. My view is that these will likely have different answers in different places.
Thus when an open source community takes a position on, for example, gay rights in the name of providing a welcoming community, they make the community openly hostile to a very large portion of the world and I think that is not what we want. Moreover such a decision is usually a product of white, Western privilege and effectively marginalize those in so-called developing countries who want to see their countries economically develop in a very different direction than the US has. Worse, this is not an unintended side effect but the whole point.
Thus when an open source community takes a position on, for example, gay rights in the name of providing a welcoming community, they make the community openly hostile to a very large portion of the world and I think that is not what we want. Moreover such a decision is usually a product of white, Western privilege and effectively marginalize those in so-called developing countries who want to see their countries economically develop in a very different direction than the US has. Worse, this is not an unintended side effect but the whole point.
A brief detour into the argument over white privilege
A discussion of so-called white privilege is needed I think for three groups reading this:
- Non-Americans who will have trouble understanding the idea as it applies to American society (like all social ideas, it does not apply to all societies or even where it does apply, it may not in the same way).
- White Americans who seem to have trouble understanding what people of color in the US mean when they use the term.
- Activists who want to use the idea as a political weapon to enforce a sort of orthodoxy
I mentioned Western-Normative above. It is worth pointing out that this forms a part of a larger set of structures that define what is normal or central in a culture, and what is abnormal, marginalized (or perhaps liminal). It is further worth noting that the perception as to these models is more acute to those who are not treated as the paragons of normality. In the US, the paragon of normality is the white, straight male. But unspoken here is that it is the white, straight, urban, wealthy American male (or maybe European, they are white too). Everyone else (women, people of color, Africans, Asians, etc) should strive to be like these paragons of success (I, myself, having lived most of my life now either outside the US or in the rural parts, am most certainly not included in this paragon of normality model, but nevertheless it took years of marriage to someone from a different culture and race to begin to be able to partially see a different perspective).
Now, it doesn't follow that white straight males live up to this image (which is one reason why white privilege theory has proven controversial among the arguably privileged) even where there is wealth, one is brought up in nice neighborhood in the city, etc. But that isn't really the point. The point is that society holds these things to be *normal* and everything else to be only normal to the extent it is like this model. It would be better and more accurate to call this a model of normality rather than privilege and to state at the outset that we cannot really walk a mile in the shoes of people from across many social borders (culture included).
White privilege is real, as is male privilege (in some areas, particularly employment), urban privilege, American privilege, Western privilege, even female privilege (in some areas, particularly family law).
Issues exist in a sticky web of culture, and no culture is perfect
These issues of privilege aren't necessarily wrong in context: it seems unlikely that the workplace can be made less male-normative without men sharing equally in the duties and rights of childrearing, but enforcing that cuts against the goal by some feminists of liberating women from men (and also exists in tension with things like same-sex marriage and gender-nonessentialism). Insisting that men get the same amount of parental leave as women cuts one direction, but insisting that single women get free IVF cuts the other (both of these are either the case in Sweden or efforts are being made to make them the case). In other words, addressing male privilege requires a transformation of the economic and family order together, in such a way that having children becomes an economic investment rather than an economic burden. But then that has implications for the idea of gay rights as we understand the concept in the West because if having and raising children becomes normative then one is providing a sort of parental privilege, and gender equality becomes based on heteronormativity.
But the ultimate white privilege is to deny it is a factor when one uses one's own perception that other cultures are homophobic or transphobic to justify one's own racist paternalism. No need to understand why. We are white. We know what is right. We just need to educate them so they can join the ranks of the elite culturally white enlightened liberals as well. Most of the world, however, disagrees, and as maintainers of open source projects we have to somehow keep the peace. (Note I use the term liberal as it is used in the history of ideas -- in the West it is no less prevalent on the mainstream right than on the mainstream left, though the application may be different.)
Since many of these issues necessarily exist in tension with eachother, there is no such thing as a perfect culture. It isn't even clear the West does better than Southeast Asia on the whole (in fact I would say the SE Asia does better than the West on the whole). But all culture is an effort at these tradeoffs, and it is not the job of open source communities to push Western changes on the rest of the world.
What is Social Justice? Two Theories and a Problem
If open source is inherently political then social justice must in some way matter to open source. Naturally we must understand what social justice is and how it applies. Certainly a sense of being treated fairly by the community is essential for contributors from all walks of life. The cult of meritocracy is an effort at social justice within the community. As some argue it is not entirely without problems (see below) but as a technical community it is a start.
Western concepts of justice today tend to stress individuality, responsibility, and autonomy. The idea is that justice is something that exists between individuals, and maybe between individuals and the state. And while contemporary Western social justice theorists on the left try to relate the parts to the whole of society, it isn't clear that there is room for any parts other than the isolated individual and the state in their theories. If one starts with the view that humans are born free but everywhere in chains (Rousseau), then the job of the state is to liberate people from eachother, and that leaves no room for any other parts.
The individualist view of justice, when seen as primary, breaks down in a number of important ways. The most important is that it provides no real way of understanding parts and how they can be related to the whole. Thus, the state becomes both stronger and isolates people more from eachother, and predictability becomes more important than human judgement. Separatism cannot be tolerated, and assimilationism becomes the rallying cry when it comes to how the central model of normality should deal with those outside. In other words the only way that this approach can deal with those on the margins is to destroy their culture and assimilate the individuals remaining. Resistance must be made futile (Opalgate can be seen as such an effort). For this reason, this view of justice is incompatible with real cultural pluralism. This is not a question of the political spectrum in the US or Europe. It is a fundamental cultural assumption in the much of the West. Interestingly, the insistence that the personal is political means that intellectual feminism already exists in tension with this cold, mechanical view of justice.
Another view of justice can be found in Thomas Aquinas's view that in addition to justice between individuals, there is a need to recognize that just as individuals are parts in relation to the whole, so are other organs within society. In other words, justice is a function of power, and justice is in part about just design and proper distribution of power and responsibility. In this regard, Aquinas built on the thought experiments of Plato's Republic and the Politics of Aristotle. In this regard, key questions of social justice include the structure of an open source community and the relationship between the parts of the community (how users and developers interact and share power and responsibility), the relationship between open source projects and so forth.
In the end though, there was a reason why Socrates eventually rejected every formulation of justice he pondered. Justice itself is complex and to formulate it removes a critical component of it, namely human judgement when weighing harms which are not directly comparable. I think it is therefore quite necessary for people to remain humble about the topic and to realize that nobody sees all the pieces, and that we as humans learn more from disagreement than from agreement. Therefore every one of us is ignorant to some extent on the nature of justice and so disagreements are healthy.
Another view of justice can be found in Thomas Aquinas's view that in addition to justice between individuals, there is a need to recognize that just as individuals are parts in relation to the whole, so are other organs within society. In other words, justice is a function of power, and justice is in part about just design and proper distribution of power and responsibility. In this regard, Aquinas built on the thought experiments of Plato's Republic and the Politics of Aristotle. In this regard, key questions of social justice include the structure of an open source community and the relationship between the parts of the community (how users and developers interact and share power and responsibility), the relationship between open source projects and so forth.
In the end though, there was a reason why Socrates eventually rejected every formulation of justice he pondered. Justice itself is complex and to formulate it removes a critical component of it, namely human judgement when weighing harms which are not directly comparable. I think it is therefore quite necessary for people to remain humble about the topic and to realize that nobody sees all the pieces, and that we as humans learn more from disagreement than from agreement. Therefore every one of us is ignorant to some extent on the nature of justice and so disagreements are healthy.
Open Source Projects and So-Called Social Justice Warriors
Coraline Ehmke, in her post on ticket on Opalgate asked:
Is this what the other maintainers want to be reflected in the project? Will any transgender developers feel comfortable contributing?"
This is good question but another question needs to be asked as well. Given that a lot of people live in societies with very different family and social structures, should people feel comfortable using software if the maintainers of the project have come out as openly hostile to the traditional family structures in a culture? Does not a community that is welcoming of all need to avoid the impulse to delegitimize social institutions in other cultures, ones where one necessarily lacks an understanding into how it plays into questions of economic support and power? If open source is already political do we want to ally ourselves with groups that could alienate important portions of our user base by insisting that they change their way of life?
It is important that we maintain a community that is welcoming to all, but that means we have to work with people we disagree with. A mere difference of opinion should never be sufficient to trigger a problem with the code of conduct and expressing an opinion outside community resources should never be sufficient to consider the community unduly unwelcoming. A key component of the community is whether people can work together with people when they disagree, and forcing agreement or even silencing opposition is the opposite of social justice when it comes to a large-reaching global project.
Should open source communities eject social justice warriors as ESR suggests? Not if they are willing to work comfortably with people despite disagreements on hot button issues. Should we welcome them? If they are willing to work with people comfortably despite disagreements on hot button issues. Should we require civility? Yes. Should we as communities take stances on hot button issues internationally? Absolutely not. What about as individuals? Don't we have a civic duty to engage in our own communities as we feel best? And if both those are true, must we not be tolerant of a wide range of differences in opinion, even those we find deeply and horribly wrong?
Wednesday, September 17, 2014
PGObject Cookbook Part 2.1: Serialization and Deserialization of Numeric Fields
Preface
This article demonstrates the simplest cases regarding autoserialization and deserialization to the database of objects in PGObject. It also demonstrates a minimal subset of the problems that three valued logic introduces and the most general solutions to those problems. The next article in this series will address more specific solutions and more complex scenarios.
The Problems
Often times we want to have database fields automatically turned into object types which are useful to an application. The example here turns SQL numeric fields into Perl Math::Bigfloat objects. However the transformation isn't perfect and if not carefully done can be lossy. Most applications types don't support database nulls properly and therefore a NULL making a round trip may end up with an unexpected value if we aren't careful. Therefore we have to create our type in a way which can make round trips in a proper, lossless way.
NULLs introduce another subtle problem with such mappings, in that object methods are usually not prepared to handle them properly. One solution here is to try to follow the basic functional programming approach and copy on write. This prevents a lot of problems. Most Math::BigFloat operations do not mutate the objects so we are relatively safe there, but we still have to be careful.
The simplest way to address this is to build into one's approach a basic sensitivity into three value logic. However, this poses a number of problems, in that one can accidentally assign a value which can have other values which can impact things elsewhere.
A key principle on all our types is that they should handle a null round trip properly for the data type, i.e. a null from the db should be turned into a null on database insert. We generally allow programmers to check the types for nulls, but don't explicitly handle them with three value logic in the application (that's the programmer's job).
The Example Module and Repository
This article follows the code of PGObject::Type::BigFloat.. The code is licensed under the two-clause BSD license as is the rest of the PGObject framework. You can read the code to see the boilerplate. I won't be including it in here. I will though note that this extends the Math::BigFloat library which provides arbitrary precision arithmetic for PostgreSQL and is a good match for LedgerSMB's numeric types.
NULL handling
To solve the problem of null inputs we extend the hashref slightly with a key _pgobject_undef and allow this to be set or checked by applications with a function "is_undef." This is fairly trivial:
sub is_undef {
my ($self, $set) = @_;
$self->{_pgobject_undef} = $set if defined $set;
return $self->{_pgobject_undef};
}
How PGObject Serializes
When a stored procedure is called, the mapper class calls PGObject::call_procedure with an enumerated set of arguments. A query is generated to call the procedure, and each argument is checked for a "to_db" method. That method, if it exists, is called and the output used instead of the argument provided. This allows an object to specify how it is serialized.
The to_db method may return either a literal value or a hashref with two keys, type and value. If the latter, the value is used as the value literal and the type is the cast type (i.e. it generates ?::type for the placeholder and binds the value to it). This hash approach is automatically used when bytea arguments are found.
The code used by PGObject::Type::BigFloat is simple:
sub to_db {
my $self = shift @_;
return undef if $self->is_undef;
return $self->bstr;
}
Any type of course can specify a to_db method for serialization purposes.
How and When PGObject Deserializes
Unlike serialization, deserialization from the database can't happen automatically without the developer specifying which database types correspond to which application classes, because multiple types could serialize into the same application classes. We might even want different portions of an application (for example in a database migration tool) to handle these differently.
For this reason, PGObject has what is called a "type registry" which specifies which types are deserialized and as what. The type registry is optionally segmented into several "registries" but most uses will in fact simply use the default registry and assume the whole application wants to use the same mappings. If a registry is not specified the default subregistry is used and that is consistent throughout the framework.
Registering a type is fairly straight forward but mostly amounts to boilerplate code in both the type handler and using scripts. For this type handler:
sub register{
my $self = shift @_;
croak "Can't pass reference to register \n".
"Hint: use the class instead of the object" if ref $self;
my %args = @_;
my $registry = $args{registry};
$registry ||= 'default';
my $types = $args{types};
$types = ['float4', 'float8', 'numeric'] unless defined $types and @$types;
for my $type (@$types){
my $ret =
PGObject->register_type(registry => $registry, pg_type => $type,
perl_class => $self);
return $ret unless $ret;
}
return 1;
}
Then we can just call this in another script as:
PGObject::Type::BigFloat->register;
Or we can specify a subset of types or different types, or the like.
The deserialization logic is handled by a method called 'from_db' which takes in the database literal and returns the blessed object. In this case:
sub from_db {
my ($self, $value) = @_;
my $obj = "$self"->new($value);
$obj->is_undef(1) if ! defined $value;
return $obj;
}
Use Cases
This module is used as the database interface for numeric types in the LedgerSMB 1.5 codebase. We subclass this module and add support for localized input and output (with different decimal and thousands separators). This gives us a data type which can present itself to the user as one format and to the database as another. The module could be further subclassed to make nulls contageous (which in this module they are not) and the like.
Caveats
PGObject::Type::BigFloat does not currently handle making the null handling contageous and this module as such probably never will, as this is part of our philosophy of handing control to the programmer. Those who do want contageous nulls can override additional methods from Math::BigFloat to provide such in subclasses.
A single null can go from the db into the application and return to the db and be serialized as a null, but a running total of nulls will be saved in the db as a 0. To this point, that behavior is probably correct. More specific handling of nulls in the application, however, is passed to the developer which can check the is_undef method.
Next In Series: Advanced Serialization and Deserialization: Dates, Times, and JSON
Monday, September 15, 2014
PGObject Cookbook Part 1: Introduction
Preface
I have decided to put together a PGObject Cookbook, showing the power of this framework. If anyone is interested in porting the db-looking sides to other languages, please let me know. I would be glad to provide whatever help my time and skills allow.
The PGObject framework is a framework for integrated intelligent PostgreSQL databases into Perl applications. It addresses some of the same problems as ORMs but does so in a very different way. Some modules are almost ORM-like and more such modules are likely to be added in the future. However unlike an ORM, PGObject mostly serves as an interface to stored procedures and whatever code generation routines will be added, these are not intended to be quickly changed. Moreover it only supports PostgreSQL because we make extended use of PostgreSQL-only features.
For those who are clearly not interested in Perl, this series may still be interesting as it not only covers how to use the framework but also various problems that happen when we integrate databases with applications. And there are people who should not use this framework because it is not the right tool for the job. For example, if you are writing an application that must support many different database systems, you probably will get more out of an ORM than you will this framework. But you still may get some interesting stuff from this series so feel free to enjoy it.
Along the way this will explore a lot of common problems that happen when writing database-centric applications and how these can be solved using the PGObject framework. Other solutions of course exist and hopefully we can talk about these in the comments.
Much of the content here (outside of the prefaces) will go into a documentation module on CPAN. However I expect it to also be of far more general interest since the problems are common problems across frameworks.
Introduction
PGObject is written under the theory that the database will be built as a server of information and only loosely tied to the application. Therefore stored procedures should be able to add additional parameters without expecting that the application knows what to put there, so if the parameter can accept a null and provide the same answer as before, the application can be assured that the database is still usable.
The framework also includes a fairly large number of other capabilities. As we work through we will go through the main areas of functionality one at a time, building on the simplest capabilities and moving onto the more advanced. In general these capabilities can be grouped into basic, intermediate, and advanced:
Basic Functionality
- registered types, autoserialization, and autodeserialization.
- The simple stored procedure mapper
- Aggregates and ordering
- Declarative mapped methods
Intermediate Functionality
- The Bulk Loader
- The Composite Type stored procedure mapper
- The database admin functions
Advanced Functionality
- Memoization of Catalog Lookups
- Writing your own stored procedure mapper
This series will cover all the above functionality and likely more. As we get through the series, I hope that it will start to make sense and we will start to get a lot more discussion (and hopefully use) surrounding the framework.
Design Principles
The PGObject framework came out of a few years of experience building and maintaining LedgerSMB 1.3. In general we took what we liked and what seemed to work well and rewrote those things that didn't. Our overall approach has been based on the following principles:
- SQL-centric: Declarative, hand-coded SQL is usually more productive than application programming languages. The system should leverage hand-coded SQL.
- Leveraging Stored Procedures and Query Generators: The system should avoid having people generate SQL queries themselves as strings and executing them. It's better to store them persistently in the db or generate well-understood queries in general ways where necessary.
- Flexible and Robust: It should be possible to extend a stored procedure's functionality (and arguments) without breaking existing applications.
- DB-centric but Loosely Coupled: The framework assumes that databases are the center of the environment, and that it is a self-contained service in its own right. Applications need not be broken because the db structure changed, and the DB should be able to tell the application what inputs it expects.
- Don't Make Unnecessary Decisions for the Developer: Applications may use a framework in many atypical ways and we should support them. This means that very often instead of assuming a single database connection, we instead provide hooks in the framework so the developer can decide how to approach this. Consequently you can expect your application to have to slightly extend the framework to configure it.
This framework is likely to be very different from anything else you have used. While it shares some similarities with iBatis in the Java world, it is unique in the sense that the SQL is stored in the database, not in config files. And while it was originally inspired by a number of technologies (including both REST and SOAP/WSDL), it is very much unlike any other framework I have come across.
Next in Series: Registered Types: Autoserialization and Deserialization between Numeric and Math::BigFloat.
Sunday, September 14, 2014
LedgerSMB 1.4.0 Released
15 September 2014, London. The LedgerSMB project - all-volunteer developers and contributors - today announced LedgerSMB 1.4.0.
Based on an open source code base first released in 1999, the LedgerSMB project was formed in 2006 and saw it's 1.0 release in the same year. It has now seen continuous development for over eight years and that shows no signs of slowing down.
"LedgerSMB 1.4 brings major improvements that many businesses need," said Chris Travers, who helped found the project. "Businesses which do manufacturing or retail, or need features like funds accounting will certainly get much more out of this new release."
Better Productivity
LedgerSMB 1.4 features a redesigned contact management framework that allows businesses to better keep track of customers, vendors, employers, sales leads, and more. Contacts can be stored and categorized, and leads can be converted into sales accounts.
Additionally, a new import module has been included that allows businesses to upload csv text files to import financial transactions and much more. No longer is data entry something that needs to be done entirely by hand or involves customizing the software.
Many smaller enhancements are here as well, For example, shipping labels can now be printed for invoices and orders, user management workflows have been improved,
Better Reporting
The reporting interfaces have been rewritten in LedgerSMB 1.4.0 in order to provide greater flexibility in both reporting and in sharing reports. Almost all reports now include a variety of formatting options including PDF and CSV formats. Reports can also be easily shared within an organization using stable hyperlinks to reports. Additionally the inclusion of a reporting engine means that it is now relatively simple to write third-party reports which offer all these features. Such reports can easily integrate with LedgerSMB or be accessed via a third party web page.
Additionally, the new reporting units system provides a great deal more flexibility in tracking money and resources as they travel through the system. Not only can one track by project or department, but funds accounting and other specialized reporting needs are possible to meet.
Better Integration
Integration of third-party line of business applications is also something which continues to improve. While all integration is possible, owing to the open nature of the code and db structure, it has become easier as more logic is moved to where it can be easily discovered by applications.
There are two major improvement areas in 1.4. First additional critical information, particularly regarding manufacturing and cost of goods sold tracking, has been moved into the database where it can be easily shared by other applications. This also allows for better testability and support. Secondly LedgerSMB now offers a framework for web services, which are currently available for contact management purposes, allowing integrators to more easily connect programs together.
Commercial Options
LedgerSMB isn't just an open source project. A number of commercial companies offer support, hosting, and customization services for this ERP. A list of some of the most prominant commercial companies involved can be found at http://ledgersmb.org/topic/commercial-support
Thursday, September 11, 2014
Math and SQL Part 6: The Problem with NULLs
This will be the final installment on Math and SQL and will cover the problem with NULLs. NULL handling is probably the most poorly thought-out feature of SQL and is inconsistent generally with the relational model. Worse, a clear mathematical approach to NULLs is impossible with SQL because too many different meanings are attached to the same value.
Unfortunately, nulls are also indispensable because wider tables are more expressive than narrower tables. This makes advice such as "don't allow nulls in your database" somewhat dangerous because one ends up having to add them back in fairly frequently.
At the same time understanding the problems that NULLs introduce is key to avoiding the worst of the problems and managing the rest.
A null set is simply a set with no members. This brings us to the most obvious case of the use of a NULL, used when an outer join results in a row not being found. This sort of use by itself doesn't do too much harm but the inherent semantic ambiguity of "what does that mean?" also means you can't just substitute join tables for nullable columns and solve the problems that NULLs bring into the database. This will hopefully become more clear below.
The first major problem surfaces when we ask the question, "when I do a left join and the row to the right is not found, does that mean we don't know the answer yet or that there is no value associated?" In all cases, a missing result from an outer join will sometimes mean that the answer is not yet known, if only because we are still inserting the data in stages. But it can also mean that maybe there is an answer and that there is no value associated. In almost all databases, this may also be the case in this situation.
But then there is no additional harm done in allowing NULLs to represent unknowns in the tables themselves, right?
Handling NULLs as unknown values complicates database design and introduces problems so many experts like Chris Date tend to be generally against their use. The problem is that using joins doesn't solve the problem but instead only creates additional failure cases to be aware of. So very often times, people do use NULL in the database to mean unknown despite the problems.
NULL as unknown introduces problems to predicate logic because it introduces three value logic (true, false, and unknown), but these are typically only problems when one is storing a value (as opposed to a reference such as a key) in the table. 1 + NULL IS NULL. NULL OR FALSE IS NULL. NULL OR TRUE IS TRUE. This makes things complicated. But sometimes we must....
One severe antipattern that is frequently seen is the use of NULL to mean "Not Applicable" or "No Value." There are a few data types which have no natural empty/no-op types. Prime among these are numeric types. Worse, Oracle treats NULL as the same value as an empty string for VARCHAR types.
Now, the obvious problem here is that the database does't know here that NULL is not unknown, and therefore you end up having to track this yourself, use COALESCE() functions to convert to sane values, etc. In general, if you can avoid using NULL to mean "Not Applicable" you will find that worthwhile.
Now, if you have to do this, one strategy to make this manageable is to include other fields to tell you what the null means. Consider for example:
CREATE TABLE wage_class (
id int not null,
label text not null
);
INSERT INTO wage_class VALUES(1, 'salary'), (2, 'hourly');
CREATE TABLE wage (
ssn text not null,
emp_id int not null,
wage_class int not null references wage_class(id),
hourly_wage numeric,
salary numeric,
check (wage_class = 1 or salary is null),
check (wage_class = 2 or hourly_wage is null)
);
This approach allows us to select and handle logic based on the wage class and therefore we know based on the wage_class field whether hourly_wage is applicable or not. This is far cleaner and allows for better handling in queries than just putting nulls in and expecting them to be semantically meaningful. This solution can also be quite helpful because it ensures that one does not accidentally process an hourly wage as a salary or vice versa.
Because NULLs can represent unknowns, they introduce three-valued predicate logic. This itself can be pretty nasty. Consider the very subtle difference between:
WHERE ssn like '1234%' AND salary < 50000
vs
WHERE ssn like '1234%' AND salary < 50000 IS NOT FALSE
The latter will pull in hourly employees as well, as they have a NULL salary.
Despite all the problems, NULLs have become a bit of a necessary evil. Constraints are a big part of the reason why.
Constraints are far simpler to maintain if they are self-contained in a tuple and therefore require no further table access to verify. This means that wider tables admit to more expression relating to constraints than narrow tables.
In the example above, we can ensure that every hourly employee has no salary, and every salaried employee has no hourly wage. This level of mutual exclusion would not be possible if we were to break off salaries and wages into separate, joined tables.
Foreign keys are a special case of NULLs where the use is routine and poses no problems. NULL always means "no record referenced" in this context and because of the specifics of three-valued boolean logic, they always drop out of join conditions.
NULLs in foreign keys make foreign key constraints and 5th Normal Form possible in many cases where it would not be otherwise. Consequently they can be used routinely here with few if any ill effects.
In retrospect, SQL would be cleaner if we could be more verbose about what we mean by a NULL. UNKNOWN could then be reserved for rare cases where we really must need to store a record with incomplete data in it. NULL could be returned from outer joins, and NOVALUE could be used for foreign keys and places where we know the field is not applicable.
Unfortunately, nulls are also indispensable because wider tables are more expressive than narrower tables. This makes advice such as "don't allow nulls in your database" somewhat dangerous because one ends up having to add them back in fairly frequently.
At the same time understanding the problems that NULLs introduce is key to avoiding the worst of the problems and managing the rest.
Definition of a Null Set
A null set is simply a set with no members. This brings us to the most obvious case of the use of a NULL, used when an outer join results in a row not being found. This sort of use by itself doesn't do too much harm but the inherent semantic ambiguity of "what does that mean?" also means you can't just substitute join tables for nullable columns and solve the problems that NULLs bring into the database. This will hopefully become more clear below.
Null as Unknown
The first major problem surfaces when we ask the question, "when I do a left join and the row to the right is not found, does that mean we don't know the answer yet or that there is no value associated?" In all cases, a missing result from an outer join will sometimes mean that the answer is not yet known, if only because we are still inserting the data in stages. But it can also mean that maybe there is an answer and that there is no value associated. In almost all databases, this may also be the case in this situation.
But then there is no additional harm done in allowing NULLs to represent unknowns in the tables themselves, right?
Handling NULLs as unknown values complicates database design and introduces problems so many experts like Chris Date tend to be generally against their use. The problem is that using joins doesn't solve the problem but instead only creates additional failure cases to be aware of. So very often times, people do use NULL in the database to mean unknown despite the problems.
NULL as unknown introduces problems to predicate logic because it introduces three value logic (true, false, and unknown), but these are typically only problems when one is storing a value (as opposed to a reference such as a key) in the table. 1 + NULL IS NULL. NULL OR FALSE IS NULL. NULL OR TRUE IS TRUE. This makes things complicated. But sometimes we must....
Null as Not Applicable
One severe antipattern that is frequently seen is the use of NULL to mean "Not Applicable" or "No Value." There are a few data types which have no natural empty/no-op types. Prime among these are numeric types. Worse, Oracle treats NULL as the same value as an empty string for VARCHAR types.
Now, the obvious problem here is that the database does't know here that NULL is not unknown, and therefore you end up having to track this yourself, use COALESCE() functions to convert to sane values, etc. In general, if you can avoid using NULL to mean "Not Applicable" you will find that worthwhile.
Now, if you have to do this, one strategy to make this manageable is to include other fields to tell you what the null means. Consider for example:
CREATE TABLE wage_class (
id int not null,
label text not null
);
INSERT INTO wage_class VALUES(1, 'salary'), (2, 'hourly');
CREATE TABLE wage (
ssn text not null,
emp_id int not null,
wage_class int not null references wage_class(id),
hourly_wage numeric,
salary numeric,
check (wage_class = 1 or salary is null),
check (wage_class = 2 or hourly_wage is null)
);
This approach allows us to select and handle logic based on the wage class and therefore we know based on the wage_class field whether hourly_wage is applicable or not. This is far cleaner and allows for better handling in queries than just putting nulls in and expecting them to be semantically meaningful. This solution can also be quite helpful because it ensures that one does not accidentally process an hourly wage as a salary or vice versa.
What Nulls Do to Predicate Logic
Because NULLs can represent unknowns, they introduce three-valued predicate logic. This itself can be pretty nasty. Consider the very subtle difference between:
WHERE ssn like '1234%' AND salary < 50000
vs
WHERE ssn like '1234%' AND salary < 50000 IS NOT FALSE
The latter will pull in hourly employees as well, as they have a NULL salary.
Nulls and Constraints
Despite all the problems, NULLs have become a bit of a necessary evil. Constraints are a big part of the reason why.
Constraints are far simpler to maintain if they are self-contained in a tuple and therefore require no further table access to verify. This means that wider tables admit to more expression relating to constraints than narrow tables.
In the example above, we can ensure that every hourly employee has no salary, and every salaried employee has no hourly wage. This level of mutual exclusion would not be possible if we were to break off salaries and wages into separate, joined tables.
Nulls and Foreign Keys
Foreign keys are a special case of NULLs where the use is routine and poses no problems. NULL always means "no record referenced" in this context and because of the specifics of three-valued boolean logic, they always drop out of join conditions.
NULLs in foreign keys make foreign key constraints and 5th Normal Form possible in many cases where it would not be otherwise. Consequently they can be used routinely here with few if any ill effects.
What Nulls Should Have Looked Like: NULL, NOVALUE, UNKNOWN
In retrospect, SQL would be cleaner if we could be more verbose about what we mean by a NULL. UNKNOWN could then be reserved for rare cases where we really must need to store a record with incomplete data in it. NULL could be returned from outer joins, and NOVALUE could be used for foreign keys and places where we know the field is not applicable.
Subscribe to:
Posts (Atom)