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.

Burnt Out Light Bulbs vs Burnt Out Managers

Encountered a great way to describe the management mindset during a weekly chat recently. Say there's a burnt out lightbulb in the office. You're from the maintenance department. What you perceive as the problem and how you go about resolving it is very different depending on whether you're normal staff or manager/team leader:

• Staff: Problem - burnt out lightbulb. Solution - Replace bulb.
• Manager/Team Leader: Problem - team has not replaced lightbulb. Solution - examine team and processes to improve them and ensure that all burnt out bulbs will be dealt with in a timely fashion in the future.

This highlights the major shift in thinking anytime someone begins moving up the management ranks. Too often, I've seen recently promoted team leaders/managers try to take all the burden of work onto themselves. Anytime they see something wrong, they try to fix it themselves. That is not their job and in the long run, it only burns them out. It is a short term solution that merely masks the underlying problems.

Management isn't about doing the task. It's about training, enabling and motivating your team to do the tasks. The focus is no longer on what you as a person can do but about what you can enable your team to do. You must clean out the obstacles in their way, provide missing training and organize resources so that the team functions at peak productivity.

Another example is watching a team that is struggling because of insufficient teammates. Because there aren't enough people around to do the job, the manager often rolls up his/her sleeves and jumps into the tasks, trying to add 1 more person's productivity to the team. That is fine if and only if the manager has already done everything in his/her power to increase team size. Has every avenue to expand the team been thoroughly explored? Are there no other resources the manager can beg/borrow/steal? In many cases, it is better to let team productivity suffer in the short term while management resources are devoted to building for the future.

Being a manager isn't about what you can do. It is all about what your team can do. It is a very different way of thinking.