FOSSLC is a non-profit organization that specializes in technology and know-how to record conferences with excellent quality. Click on the icons below to view great videos from communities we are actively involved with:

 

PostgreSQL

Content related to the PostgreSQL RDBMS.

LAPP/SELinux

in PGCon, PGCon2010, Programming, PostgreSQL
PostgreSQL

Location

Ottawa, ON
Canada
59° 46' 16.0824" S, 73° 49' 41.25" W

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.

Event: 
PGCon2009
Speaker: 
KaiGai Kohei

pg_statsinfo

in PGCon, PGCon2010, Programming, PostgreSQL
PostgreSQL

Location

Ottawa, ON
Canada
45° 24' 41.6592" N, 75° 41' 53.4984" W

More useful statistics information for DBAs

NTT has developed "pg_statsinfo", that collects database activities and statistics automatically, and shows the information to DBAs in user-friendly shapes. Also pg_statsinfo can collect statistics from multiple DBs, so this tool makes it much easier to monitor the status of many DB servers

PostgreSQL provides many useful statistics about DB activities and conditions via system views and contrib modules. But for many DBAs, it is difficult to see whether a DB has problems or not from original statistics information.

This presentation will cover the following topics.

    * How pg_statsinfo collects statistics from PostgreSQL
    * Architecture of the reporting tool
    * Requirements for PostgreSQL core to collect more useful information

Event: 
PGCon2010
Speaker: 
Tatsuhito Kasahara

Exposing PostgreSQL Internals with User-Defined Functions

in PGCon, PGCon2010, Programming, PostgreSQL
PostgreSQL

Location

Ottawa, ON
Canada
45° 24' 41.6592" N, 75° 41' 53.4984" W

Easing into PostgreSQL Hacking

User-defined functions are one of the easiest ways to get started hacking on the PostgreSQL codebase and produce something useful in a short time. Watch a whole new trivial feature get added with one, and learn how to step over some of the more common confusing parts of the codebase along the way.

PostgreSQL's user-defined function (UDF) mechanism is useful for all sorts of things. You can add your own custom C code to the database, for performance or extensibility reasons. You can expose database internals that you wouldn't otherwise be able to access. And it's a great way to get started hacking on PostgreSQL with quick results.

This talk leads you through a quick tour of creating a UDF that exposes a useful bit of information about how you're using the shared_buffers cache on your system. Consider it a "hello, world" for writing a PostgreSQL patch that adds a tiny feature as a function. You'll learn some tricks for how to find useful code to borrow too. Knowing where some simple examples are at is most of the battle when getting started here.

The material is based on several conversations about the most confusing PostgreSQL hacking basics with those completely new to that area, in hopes that you won't have to get stuck on the same things they did. You'll need a basic understanding of coding in C or similar languages to follow the examples, but not any previous exposure to the PostgreSQL code.

Event: 
PGCon2009
Speaker: 
Greg Smith

Hypothetical Indexes towards self-tuning in PostgreSQL

in PGCon, PGCon2010, Programming, PostgreSQL
PostgreSQL

Location

Ottawa, ON
Canada
45° 24' 41.6592" N, 75° 41' 53.4984" W

We propose to add hypothetical (or virtual) indexes in order to offer both what-if querying and automatic index tuning.

Hypothetical indexes are simulated index structures created solely in the database catalog. This type of index has no physical extension and, therefore, cannot be used to answer actual queries. The main benefit is to provide a means for simulating how query execution plans would change if the hypothetical indexes were actually created in the database. This feature is quite useful for database tuners and DBAs.

Index selection tools, such as Microsoft's SQL Server Index Tuning Wizard, make use of hypothetical (or virtual) indexes in the database server to evaluate candidate index configurations.

We have made some server extensions to PostgreSQL 8.* to include the notion of hypothetical indexes in the system. We have introduced three new commands: create hypothetical index, drop hypothetical index and explain hypothetical.

After implementing the server extensions for hypothetical indexes, we could use it for future automatic indexing with PostgreSQL besides simples, yet useful, what-if queries.

Event: 
PGCon2010
Speaker: 
Sergio Lifschitz