As we discussed in our initial blog post on set_user, the Sarbanes–Oxley Act, passed in 2002, requires publicly owned companies to audit a user’s (esp. superuser’s) access and interactions with data. Companies storing financial or human subject data are particularly susceptible to hacking.

What you need is x-ray vision

Your audit trail should always include log entries that track what your user’s with superuser powers have been up to in your databases.

This is where the set_user extension is essential. It provides an additional layer of logging and control when unprivileged users must escalate themselves to superuser or object owner roles in order to perform needed maintenance tasks. When calling the set_user or set_user_u function, several actions occur:

  • The current effective user becomes another user.
  • The role transition is logged, with specific notation if transitioning to a superuser.
  • log_statement setting is set to ’all’, meaning every SQL statement executed while in this state will also get logged.

When finished with required actions, the reset user() function is executed to restore the original user. At that point, these actions occur:

  • Role transition is logged.
  • log_statement setting is reset to its original value.

Exercise: exploring set_user generated logging

In this exercise, we will view how set_user audits superuser activity in the current generated logfile.

Connect to the testdb database as dba_user and run the following commands (you learned about these in the last blog post):

psql -U dba_user testdb         
testdb=> SELECT set_user_u('postgres');
testdb=# SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
testdb=# SELECT reset_user();
testdb=# \q

Now, navigate to the log directory

cd <postgres installation directory>/data/logs/pg96

Open today’s logfile (e.g. postgres-Fri.log) with your favorite text editor and scroll to the bottom of the file. You should see something similar to this:

STATEMENT:  SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
LOG:  Role dba_user transitioning to Superuser Role postgres
STATEMENT:  SELECT set_user_u('postgres');
SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
LOG:  statement: SELECT reset_user();
LOG:  Superuser Role postgres transitioning to Role dba_user
STATEMENT:  SELECT reset_user();        

That’s it! Verbose logging to monitor user’s with superuser privileges to create an audit trail.

In our next post, Ensuring Superuser Auditing, we will discuss how to require superuser auditing with ALTER USER postgres NOLOGIN.