Revision as of 00:41, 21 October 2008

Overview

PostgreSQL is an object-relational database management system (ORDBMS). It is an enhancement of the original PostgreSQL database management system, a next-generation DBMS research prototype. While PostgreSQL retains the powerful data model and rich data types of PostgreSQL, it replaces the PostQuel query language with an extended subset of SQL.

This paragraph has the objectives to define the minimum security requirements for configuring and managing PostgreSQL.

This is an ALPHA version, so it's possible that some information are incomplete.

Description

Server installation and updating

I decided to not face the installation hardening in this little guide, there is a lot of documentation about installing and chrooting software. You can find some usefull information about the PostgreSQL configuration files here:

• http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html
• http://www.postgresql.org/docs/8.3/interactive/runtime-config-resource.html
• http://www.postgresql.org/docs/8.3/interactive/preventing-server-spoofing.html

You can monitor the PostgreSQL security alert there: http://www.postgresql.org/support/security.html

pg_hba.conf - Client Authentication

pg_hba.conf is one of the main configuration file of PostgresSQL, it define the connection authorization. The file structure is:

TYPE - DATABASE - USER - CIDR_ADDRESS - METHOD

You have to:

• Disable all trust connections
• Use strong authentication (md5/kerberos etc)
  
• Limit connections only from allowed IP
• Use SSL connection
  

http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html

Users roles

Users and roles in PostgresSQL are the same (for example CREATE USER is only a wrapper to CREATE ROLE). While you are creating a new user you can assign different options.

CREATE ROLE name [ [ WITH ] option [ ... ] ]

One example of create role can be:

CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

http://www.postgresql.org/docs/8.3/interactive/sql-createrole.html

Access Privileges

After the creation of a role you have to grant it privileges to a specific database. A good pratice is to create two different user for each database, the first as the complete control, the second one is able only to read and modify the data. The second user will be used on the web application and similar, so if someone get access will not be able to modify the database structure, create trigger or functions.

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] }  ON [ TABLE ] tablename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION 

http://www.postgresql.org/docs/8.3/interactive/sql-grant.html

Removing the default "public" schema

By default PostgresSQL use a public schema used for store information about the databases, tables, procedures. This schema by default is accessible by all users, so all users can see every tables structure or procedures.

Removing the public schema

Removing the public schema from all users.

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Creating a new protected schema

CREATE SCHEMA myschema AUTHORIZATION [username];

Modify search_path of the user

SET search_path TO myschema,public;

In this way the database structure will be stored on a private schema and the access will be guaranteed only to the right user.

http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html

Limiting file access to filesystem and system routines

By default PostrgreSQL deny to all users to access filesystem and system routines, only superuser are alowed to do that. So you have to check your user and never give superuser to customers ;)

References

PostgreSQL documentation can be found at http://www.postgresql.org/docs/