This site is the archived OWASP Foundation Wiki and is no longer accepting Account Requests.
To view the new OWASP Foundation website, please visit https://owasp.org

OWASP Backend Security Project PostgreSQL Hardening

From OWASP
Jump to: navigation, search

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:

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
Argument Description Values
TYPE Connection type accepted by the server
  • local: unix-domain socket
  • host: TCP/IP connection with or without SSL
  • hostnossl: TCP/IP connection without SSL
  • hostssl: TCP/IP connection with SSL
DATABASE Database rule
  • all: connection allowed to all databases.
  • sameuser: connection allowed only to database with the same user
  • samerole/samegroup: user must be a member of the role with the same name as the requested database
  • database name: connection allowed only to database list (separated by comma)
USER Usernames
  • all: connection allowed to all users
  • username: single username allowed (Multiples usernames or groups can be allowed via comma separated field)
  • +groupname: username of a group allowed
CIDR-ADDRESS Source address
 Source network allowed to conenct to this rules. You have two way to define this parameter, the firstone is use a CIDR notation (192.168.0.0/24), the second one is use two parameter, one for the host and the second for the netmask.
METHOD Authentication method
  • trust: Allow the connection unconditionally.
  • reject: Reject the connection unconditionally.
  • md5: Authentication with MD5 encrypted password.
  • crypt: Authentication with crypt() encrypted password.
  • password: Authentication with plain text password.
  • gss: Authentication with GSSAPI.
  • sspi: Authentication with SSPI.
  • krb5: Authentication with Kerberos V5.
  • ident: Authentication via ident protocol.
  • ldap: LDAP authentication.
  • pam: PAM authentication.

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 [ ... ] ]
Option
 Description
SUPERUSER
NOSUPERUSER
 These clauses determine whether the new role is a "superuser", who can override all access restrictions within the database. Superuser status is dangerous and should be used only when really needed. You must yourself be a superuser to create a new superuser. If not specified, NOSUPERUSER is the default.
CREATEDB
NOCREATEDB
 These clauses define a role's ability to create databases. If CREATEDB is specified, the role being defined will be allowed to create new databases. Specifying NOCREATEDB will deny a role the ability to create databases. If not specified, NOCREATEDB is the default.
CREATEROLE
NOCREATEROLE
 These clauses determine whether a role will be permitted to create new roles (that is, execute CREATE ROLE). A role with CREATEROLE privilege can also alter and drop other roles. If not specified, NOCREATEROLE is the default.
CREATEUSER
NOCREATEUSER
 These clauses are an obsolete, but still accepted, spelling of SUPERUSER and NOSUPERUSER. Note that they are not equivalent to CREATEROLE as one might naively expect!
INHERIT
NOINHERIT
 These clauses determine whether a role "inherits" the privileges of roles it is a member of. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. Without INHERIT, membership in another role only grants the ability to SET ROLE to that other role; the privileges of the other role are only available after having done so. If not specified, INHERIT is the default.
LOGIN
NOLOGIN
 These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges, but are not users in the usual sense of the word. If not specified, NOLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER.
CONNECTION LIMIT connlimit
 If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit.
ENCRYPTED
UNENCRYPTED
 These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.
VALID UNTIL 'timestamp'
 The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.

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
Option
 Value
SELECT
 Allows SELECT from any column of the specified table, view, or sequence. Also allows the use of COPY TO. This privilege is also needed to reference existing column values in UPDATE or DELETE. For sequences, this privilege also allows the use of the currval function.
INSERT
 Allows INSERT of a new row into the specified table. Also allows COPY FROM.
UPDATE
 Allows UPDATE of any column of the specified table. (In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns.) SELECT ... FOR UPDATE and SELECT ... FOR SHARE also require this privilege, in addition to the SELECT privilege. For sequences, this privilege allows the use of the nextval and setval functions.

DELETE
 Allows DELETE of a row from the specified table. (In practice, any nontrivial DELETE command will require SELECT privilege as well, since it must reference table columns to determine which rows to delete.)
REFERENCES
 To create a foreign key constraint, it is necessary to have this privilege on both the referencing and referenced tables.
TRIGGER
 Allows the creation of a trigger on the specified table. (See the CREATE TRIGGER statement.)
CREATE
 For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

CONNECT
 Allows the user to connect to the specified database. This privilege is checked at connection startup (in addition to checking any restrictions imposed by pg_hba.conf).
TEMPORARY
TEMP
 Allows temporary tables to be created while using the specified database.
EXECUTE
 Allows the use of the specified function and the use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.)
USAGE
 For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of privilege that is applicable to procedural languages.

For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

For sequences, this privilege allows the use of the currval and nextval functions.

ALL PRIVILEGES
 Grant all of the available privileges at once. The PRIVILEGES key word is optional in PostgreSQL, though it is required by strict SQL.

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/

Retrieved from "https://wiki.owasp.org/index.php?title=OWASP_Backend_Security_Project_PostgreSQL_Hardening&oldid=44162"