Database Logging (PostgreSQL and MySQL)

Very few people pay attention to database logging, but I really recommend enabling them to see what is happening behind the scenes (specially for web applications).

To enable logging on PostgreSQL (and be compatible with OSSEC), add the following to your postgres config:

# 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'

And for MySQSL, to enable the generic Query Log (the error log is 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.

Posted in   logging   databases     by Daniel Cid (dcid)

Coding for fun and profit. Often fun and little profit.