EnterpriseDB: Security Oracle Tips by Burleson Consulting

When connecting to an EnterpriseDB database, you must specify a database name to connect to and a user name to connect as.   Depending on your security set up, you will probably also provide a password.

Your connection security is configured in a file called pg_hba.conf.  In this file, you identify the type of connection (local, host, hostssl, hostnossl).  Local covers connections from the local host.  Host, hostssl and hostnossl covers TCP/IP connections either with or without SSL.

You will also identify the database to connect to, the connecting user and an authentication method.  If it is a host type connection, you will also identify IP addresses.

Table 2.2 lists Authentication methods and a description of each.

AUTHENTICATION METHOD	DESCRIPTION
Trust	No password required.  Anyone can connect to any user.  Not recommended.
Reject	Reject all connections.  You can black list certain IP addresses this way.
Md5	MD5 encrypted password.
Password	Same as MD5 but sent unencrypted over the network.  Not recommended.
Krb4 or 5	Kerberos authentication.
Ident	A way to map OS usernames to DB usernames.
Pam	Pluggable Authentication Module

Table 2.2: Authentication Methods

A sample line from the pg_hba.conf file might look like this:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         127.0.0.1/32          md5

This is a host type connection (with or without SSL), allowing connections to ALL databases, for ALL users from IP Address 127.0.0.1 (the localhost) using md5 password encryption.

Another sample line from the pg_hba.conf file might look like this:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    edb         jschmoe     192.168.100.2/32      md5

This is a host type connection (with or without SSL), allowing connections to the edb database, for user jschmoe from IP Address 192.168.100.2 using md5 password encryption.

To reject all connections from a particular host:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
host    all         all         10.10.32.5/32         reject

This is a host type connection (with or without SSL) that will reject all connections to any database or from any user coming from 10.10.32.5.

EnterpriseDB also ships with a file called pg_ident.conf.  This file is used if you chose to use ident style authentication.  This type of authentication is a lot like Oracle’s OS authentication (also called OPS$ authentication).  The downside to ident authentication is that it is easily spoofed and generally considered insecure.

The pg_ident.conf file will allow you to enter a mapname, an OS Name and an EnterpriseDB User Name.  You would use that map name in your pg_hba.conf ident entry.  That’s all of the detail I will provide on ident.

EnterpriseDB Auditing

EnterpriseDB has built-in auditing capabilities.  The administrator can choose what to audit as well as where to put the logs and how to store the output (csv or xml).  Auditing is set via parameters in the postgresql.conf configuration file (Table 2.3).

PARAMETER	DESCRIPTION
edb_audit	This option turns auditing on or off.  There are three possible values for this option:  none, xml or csv.  None is the default and turns auditing off.  Xml will create XML log files and csv will create csv log files.
edb_audit_directory	Directory to store audit log files.  A full path can be specified or it can be relative to the EnterpriseDB data directory.  ‘/tmp’ would be a full path whereas ‘audit_data’ would be relative.
edb_audit_filename	File name as stored in the OS.  The file name can contain certain parameters: %Y = Year %m = Month %d = day %H = hour %M = minute %S = Second audit_%Y-%m%d_%H%M%S will create a file name like: audit_2006-10-01_123208 The extension will either be .xml or .csv depending on type of log created.
edb_audit_rotation_day	Day of week to force a new audit log file.  Can be: none – no rotation based on day of week (default) every – start a new log every day mon, tue, wed, thu, fri, sat, sun You can combine multiple values by separating with a comma, i.e. edb_rotation_day=’wed,fri’
edb_audit_rotation_size	File size that will force a new file to be created.  If not set or set to 0 (the default), no size limit is set.
edb_audit_rotation_seconds	Amount of time in seconds since last file creation that will force a new file to be created.  If not set or set to 0 (the default), no time limit is set.
edb_audit_connect	Audit connections to the database.  Valid values are none (the default) turns off connection auditing, failed audits failed connection attempts and all will audit all connection attempts.
edb_audit_disconnect	Audit dis-connects from the database.  Valid values are none (the default) turns off dis-connect auditing or all which will audit all dis-connects.
edb_audit_statement	This option will define the types of statements that will be audited.

Valid values are:

none – No statement auditing

dml – All data manipulation statements (insert, update, etc)

ddl – All data definition statements (create, drop, etc)

select – All select statements

error – Any statements that generate errors

all – All statements

Values can be mixed such as:

edb_audit_statement=’dml,select,error’

Table 2.3: Audit Configuration Options

A sample listing of an audit file:

<event process_id="1112"
       time="2006-10-06 16:48:39 "
       type="startup">
  <message>AUDIT:  database system is ready</message>
</event>

<event user="enterprisedb"
       database="mgmtsvr"
       remote_host_and_port="127.0.0.1(1033)"
       process_id="2736"
       session_id="4526c191.ab0"
       transaction="10391"
       time="2006-10-06 16:50:31 "
       type="ddl">
  <command>AUDIT:  statement: drop table HILOSEQUENCES</command>
</event>
<event user="enterprisedb"
       database="mgmtsvr"
       remote_host_and_port="127.0.0.1(1033)"
       process_id="2736"
       session_id="4526c191.ab0"
       transaction="10393"
       time="2006-10-06 16:50:33 "
       type="ddl">
  <command>AUDIT:  statement: create table HILOSEQUENCES (
                       SEQUENCENAME varchar(50) not null,
                       HIGHVALUES integer not null,
                       constraint hilo_pk primary key (SEQUENCENAME)
                    )</command>
</event>

<event user="enterprisedb"
       database="edb"
       remote_host_and_port="127.0.0.1(1041)"
       process_id="3208"
       session_id="4526c1d5.c88"
       transaction="0"
       time="2006-10-06 16:51:33 "
       type="connect failed">
  <message>AUDIT:  password authentication failed
           for user &quot;enterprisedb&quot;</message>
</event>

Audit files can get large very quickly.  If you need to audit, make sure you only audit what is necessary.  You should also plan to archive your audit logs frequently.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.