Logging SQL Commands of Selected Users in PostgreSQL

Recently looked a bit into logging PostgreSQL for one of our production setups. The reason was that we wanted to find a secure way to log the SQL commands run by ordinary users but ignore the commands run by the server users. This was for security and auditing purposes. We tried logging all SQL commands once - was enough to bring the disks to a total halt due to the traffic.

After some investigation, turns out the trick is using the ROLES settings to turn on logging for the users you are interested in. As long as the user is not a superuser, this works fine. Even with superusers, you should at least see the command that turns off logging. A snippet of the configuration:

CREATE ROLE viewer;
CREATE ROLE updater;
CREATE ROLE server;

CREATE ROLE admin CREATEDB CREATEROLE;
ALTER ROLE viewer, updater, admin SET log_statement TO 'all';

CREATE ROLE ken LOGIN INHERIT;
GRANT updater TO ken;

This means that all users with viewer, updater or admin role will have all their statements logged. Of course, you still have to set logging to a secure location.