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, http://www.en.hakin9.org/, 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
UNIX DOMAIN SOCKETS
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 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 linux.com, phpbuilder.com, 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, http://pg-live.info, 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.
For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at firstname.lastname@example.org
Download the OSCON Sponsor/Exhibitor Prospectus
For media-related inquiries, contact Maureen Jennings at email@example.com
To stay abreast of conference news and to receive email notification when registration opens, please sign up for the OSCON newsletter (login required)
View a complete list of OSCON contacts