Location

The Millburn Corporation is a hedge fund which uses complex data-driven trading models based on the daily prices of various commodities, currencies and other inputs. As part of our application development process, we use independent staging and development instances of our production database to let us have a smoother and less mistake-prone deployment of new models and price streams.

Last year, I presented a talk at PgCon 2009 that examined in broad detail how we make heavy use of different schemas to compare and reconcile data between our different environments. In this talk, I'll examine in detail our attempt to solve a problem we face in our database environment: how to test complex triggers and functions before they're deployed; and how to reconcile and track changes in these functions as they move from our development to our staging and production environments. Specifically, I'll describe how we make use of subversion, pg_dump and pgTAP to roll trigger and function changes to our development environment nightly; and how we test the integrity of the functions and trigger functions on our staging and production environments nightly using pgTAP.

Last year's talk covered how we use simple cross-schema queries and DBI-Link (for cross-database queries) to reconcile data between different databases. In more complex cases, we also use inherited tables with non-overlapping sequences and custom accessor functions to access different data streams in different schemas.

Function comparison and trigger comparisons across schemas and databases are more complex. I will examine how we use MD5 hashes to compare function bodies; walk through most of the common system catalog queries we use to verify function arguments and return values -- stealing ideas from Greg Sabino Mullane's check_postgres script; and then show the testing and deployment framework we've developed at Millburn to test triggers and functions using temporary schemas and fixture files (canned data) to regression test our functions.

Difficulties to be discussed: how to keep the same trigger code in different environments, even if the trigger must use different search paths in different environments; pitfalls of function caching; many other issues tk...

Location

Hi-Media online services all run atop PostgreSQL, and use some form of replication. This talk will present what problems we solve with replication, and how.

As we only use Skytools (Londiste) for replicating data, the talk will summarize what we've found in this project after having been using it for 2 years in production. From the community aspects to the failure experience and the impact on the database management (rollouts, etc).

Location

A secure web-application stack using SE-PostgreSQL

Nowadays, many web applications are closely combined with database systems, using the database to provide various kinds of dynamic content. In these environments, you cannot just focus on individual applications, databases and the operating systems. You need to consider the whole system.

This session describes why you should apply consistent and centralized access control policy, how SE-PostgreSQL can be utilized to improve web application security, and shows a working example of the stack named as LAPP/SELinux.

There are two major issues in web application security that can be improved by using an approach like LAPP/SELinux.

In most cases, a web-system consists of multiple layers called a stack, such as LAPP, and each layer of the stack has its own individual access control facilities. It is hard to maintain each of them to always apply consistent access control decisions without a centralized reference. SE-PostgreSQL applies its own access controls based on the SELinux security policy, in addition to the default PG privilege checks. Those policies are also applied on access attempts to the filesystem and other OS resources, so you will always see consistent access control decisions across the system.

Another issue is the privileges of web application instances. When these are launched by a web server, they inherit the privileges of the server process. That makes it hard to enforce meaningful access controls, because the OS and DB cannot distinguish among individual users. This leaves you very exposed to bugs and vulnerabilities in your web applications. The Apache/SELinux plus module launches web application instances with individual privileges based on http authentication. Unlike application level checks, these are always applied prior to invocation of the web application, so you can't bypass them. Then when the application requests resources from the OS and DB, they can make their access control decisions based on the privileges assigned.

We call this stack LAPP/SELinux. It enables web applications to run with the minimal privilege set appropriate for individual users/groups.

We assume audiences are interested in security issues and have basic knowledge of access controls. We do not expect any previous knowledge of SELinux.