Total Security In A PostgreSQL Database

Databases, Emerging Topics, Security
Location: Ballroom A2
Average rating: ***..
(3.71, 7 ratings)

Data security is a pretty big deal but until now there’s never been that much out there describing how to secure PostgreSQL against concerted attacks from crackers.

This tutorial is based upon a series of articles that I wrote for hakin9,, which is the leading IT security magazine in the world today. The tutorial takes an A to Z approach by going through every function, feature, module and technique that PostgreSQL is capable of executing. To my knowledge, nobody has ever made this kind of presentation before anywhere.

This hands on tutorial is geared towards “experts” as it’s going to be moving pretty fast due to the volume of techniques and concepts covered.

Here’s a summary of the authentication and encryption techniques that will be dealt in this tutorial:
Restricting access on the localhost using
Authenticated Sessions
SSL certificates
Encrypted Sessions
SSH tunnels using port forwarding
Encrypting Data In The Database
The md5 function
The chkpass contrib module
The pgcrypto contrib module
Disk Based Encryption
Best Practices And Caveats

The presentation begins by reviewing all AUTHENTICATION techniques as controlled by the host based authentication file, pg_hba.conf, and includes the following mechanisms: UNIX DOMAIN sockets, TCP, SSL, IDENT, LDAP and PAM.

The next topic, SSL, is an involved and complex task requiring an extended amount of knowledge. Leveraging this technology to its fullest begins with an understanding of symmetric and asymmetric ciphers and how public key encryption works. Thenceforth, we’ll go through SSL certificates and demonstrate how they can be used to authenticate clients and servers.

Encrypted sessions, prevents network sniffers from intercepting sensitive data. Several methods are covered that can be used to create an encrypted tunnel between the client and the server.

Data encryption in PostgreSQL is very cool: you can encrypt data with a one way hash, a symmetric cypher, and even with public key encryption. There a number of mechanisms that can be used to sign and authenticate the author of DDL and DML activitties too. Techniques will be demonstrated implementing a more rigid set of password encryption than is currently defaulted on a vanilla flavoured PostgreSQL installation.

The final phase of this tutorial brings all these techniques together summaring best practices i.e. where you can use this knowledge (translucent data) and what you should keep in mind (caveats).

Robert Bernier


Robert is a PostgreSQL advocate and is a computer systems analyst. He has written for publications such as Sys-Admin, Hakin9, PHP Solutions and several online sites including,, PHP Magazine, Linux Weekly News and the O’Reilly webportal as well as a contributor to the books “BSD Hacks” and “Multimedia Hacks”. Robert is also the maintainer of the pg-live,, which is used throughout the world at conferences, trade shows and training sessions to profile the awesome capabilities of PostgreSQL.

Comments on this page are now closed.


Jason Buberel
07/23/2009 4:05pm PDT

For me, the content was painfully obtuse and difficult to apply from the perspective of an application developer. Most of the security provisions advocated seemed to be given without regard to the practical limitations of using Postgres to support an application/website in the real world.

Rob definitely knows his Postgres, but needs to take into account the application developers who build on top of it, and not just the security paranoia of the admins who run it.

Cathy Mullican
07/22/2009 5:27am PDT

4 stars instead of 5 due to consistent use of male-gender terminology—DBAs can be female, too. Otherwise, excellent.

  • Intel
  • Microsoft
  • Google
  • Sun Microsystems
  • Facebook
  • Gear6
  • Kaltura
  • Liferay
  • MindTouch
  • Novell, Inc.
  • Open Invention Network
  • Rackspace Cloud
  • Schooner Information Technology
  • Silicon Mechanics
  • Symbian Foundation
  • Twilio
  • WSO2
  • Yabarana Corporation

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at

Download the OSCON Sponsor/Exhibitor Prospectus

Media Partner Opportunities

Download the Media & Promotional Partner Brochure (PDF) for information on trade opportunities with O'Reilly conferences or contact mediapartners@

Press and Media

For media-related inquiries, contact Maureen Jennings at

OSCON Newsletter

To stay abreast of conference news and to receive email notification when registration opens, please sign up for the OSCON newsletter (login required)

Contact Us

View a complete list of OSCON contacts