Database Logging (PostgreSQL and MySQL)

Nobody cares about database logging, but I really recommend enabling them to see what is happening behind the scenes (specially for web applications).

To enable on PostgreSQL (and be compatible with OSSEC):

# Adding the timestamp, hostname and database.
log_line_prefix = '[%m] %h:%d '

# Recommended settings:
log_connections = on
log_disconnections = on
log_duration = on

# Maybe a good idea to reduce the default log level to info:
client_min_messages = info
log_min_messages = info

# To enable query logging (all for everything or mod for inserts, updates, etc)
log_statement = 'all'

On MySQL:

To enable the generic Query log on MySQL (the error log in on by default), you need to start MySQL with –log:

/bin/sh /usr/bin/mysqld_safe --log

The best option is to modify /etc/init.d/mysqld (if using Centos) and inside the –log in there.

Nothing new, but I was searching for this information online and couldn’t find much info.