IBM.com : Total security in a PostgreSQL database

http://www.ibm.com/developerworks/opensource/library/os-postgresecurity/...

Total security in a PostgreSQL database

Readiness for the first strike
Robert Bernier (robert@pg-live.info), PostgreSQL Business Intelligence Analyst, Medio Systems
Robert Bernier is the PostgreSQL Business Intelligence Analyst at Medio Systems, which is a leader in the emerging technology of media search. He was a PostgreSQL consultant for many leaders in their respective industries, including cellphones, Wall Street, scientific research centers, U.S. defense contractors, and IT departments of Ivy League universities and colleges. He is a PostgreSQL advocate and has written for Sys-Admin, Hakin9, PHP Solutions, and several online sites including Linux.com, PHPbuilder.com, PHP Magazine, Linux Weekly News, and the O’Reilly Web portal. He was a contributor to the books BSD Hacks and Multimedia Hacks. He 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.

Summary: Database security is the single biggest concern with today's Web-based applications. Without control, you risk exposing sensitive information about your company or, worse yet, your valuable customers. In this article, learn about security measures you can take to protect your PostgreSQL database.

Introduction

There are lots of stories in the press about crackers accessing corporate databases. Gone are the days when prepubescent teens were the authors of most cracks. Today, data harvesting is big business and is accomplished by dedicated experts who work within a corporate infrastructure. It's not a question of how you can prevent the unauthorized access attempt — you can't — but, rather, how can you reduce the effect when it does happen.
Definitions

Hacker — A hacker explores, inquires, and discovers by understanding the technology on a level rarely duplicated in normal circumstances. To be called a hacker by your peers is a badge of honor not because you do something bad but because your expertise is unparalleled.

Cracker — A hacker with malicious intent, such as vandalism, credit card fraud, identity theft, piracy, or other types of illegal activity.

This article explores the challenges of protecting your PostgreSQL (also known as Postgres) database server. PostgreSQL is a powerful open source object-relational database system. It has a proven architecture with a reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux®, UNIX®, and Windows®. It is fully ACID-compliant, and has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

Be sure to download the sample code listings used in this article.

Back to top

The ideal administrator

In the grand tradition of UNIX, PostgreSQL was designed from the ground up to complement the OS it sits upon. Leveraging PostgreSQL to its maximum potential requires knowledge beyond the mindshare typically expected of the average database administrator (DBA).

In a nutshell, the ideal PostgreSQL DBA has the following background:

* Knowledgeable of relational theory and familiar with SQL '92, '99, and 2003, respectively.
* Knows how to read source code, preferably C, and can compile source code on Linux.
* Can system administrate and is comfortable with the system-V UNIX or Linux.
* Can maintain, if required, the various hardware items typically found in an IT shop. Understands the TCP OS layer, can subnet a network, tune firewalls, etc.

Many DBAs have only the skills to administrate, monitor, and tune the database itself. However, PostgreSQL is built to also rely upon the OS's utilities. It's a rare DBA who excels in all these disciplines, but having this knowledge enables the PostgreSQL DBA to accomplish more in less time than would otherwise be possible.

Back to top

Review of access privileges

Knowing what a database role can do is paramount if you are to appreciate possible attack vectors. First, you have to control access to data by the granting and revoking of permissions.

Roles, and granting rights and privileges

Just how safe is an ordinary role with default rights and privileges? The user account can be created with one of the following commands:

* The SQL statement CREATE USER
* The SQL statement CREATE ROLE
* The Postgres command-line utility createuser

These three methods of creating user accounts behave dissimilarly, and result in drastically different default rights and privileges.

For an ordinary role, the typical user can:

* Access any database if the data cluster uses the default authentication policy as described in pg_hba.conf.
* Create objects in the PUBLIC schema of any database the user can access.
* Create session (temporary) objects in temporary sessions, such as schema pg_temp_?
* Alter runtime parameters.
* Create user-defined functions.
* Execute user-defined functions created by other users in the PUBLIC schema (as long as they interact only with objects that the user has been granted privileges to access).

It's important to know what the user is allowed to do, but it's equally important to understand the activities that the ordinary user cannot do by default. Ordinary users cannot:

* Create a database or a schema.
* Create other users.
* Access objects created by other users.
* Log in (applies only to the statement CREATE ROLE).

Back to top

Superuser rights and privileges

Though an ordinary user can't execute the rights and privileges defined as superuser capabilities, the ordinary user can still cause quite a bit of grief with defaulted rights and privileges.

This section discusses the attack vectors that the ordinary user can manipulate.

Accessing objects

An extremely common, and unsafe, practice occurs when PostgreSQL is used as the back end to a Web server. The developer creates the ordinary user intending to only carry out those commands that manipulate the data using the commands INSERT, UPDATE, and DELETE. However, unauthorized actions are possible because the PUBLIC schema is open to all. The user can, for example, data mine those tables. It would even be possible to modify them by adding rules and triggers, saving the data in tables located in the PUBLIC schema, which can then be harvested.