OWASP Backend Security Project PostgreSQL Hardening

= Overview =

PostgreSQL is an object-relational database management system (ORDBMS). It is an enhancement of the original POSTGRES database management system, a next-generation DBMS research prototype. While PostgreSQL retains the powerful data model and rich data types of POSTGRES, 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.

= 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 allowed to do that.

= References =

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