How to log superuser queries

A few weeks ago, one of my customers asked me if they could log all queries coming from superuser users. They have to make sure that none of their DBA could fire a query without that query being logged.

You can already log queries with the log_statement, or log_min_duration_statement GUCs. You can set these GUCs cluster-wide or for specific users. But there's a big issue here: the user can change the value of these GUCs, making it impossible to be sure everything they do is logged.

I first thought of writing a patch to handle this in PostgreSQL, but it also has a major issue: it won't be available for 9.0, the release they use. All new features are added to the development release, and I don't want to maintain a custom PostgreSQL release for anyone (that's too much burden). So, I came up with a nicer way of doing it.

PostgreSQL allows you to add hooks to the executor since 8.4. And you can use these hooks to know which queries are executed and by who. Actually, that's how pg_stat_statements works. So, all I needed to do was to create a small shared library that will add hooks to the executor. The hooks will check that the user executing a query is a superuser, and if that's true, log the query. Quite simple, here is the code of the hook:

static void
pgluq_log(const char *query)
    Assert(query != NULL);

    if (superuser())
        elog(log_level, "%s %s: %s", log_label, GetUserNameFromId(GetUserId()), query);

Of course, the library has more code than just that: to add/remove the hooks, to take care of two module GUCs (log level, and log prefix), but the most important stuff is on the pglug_log() function.

The module is available right now on github, and I intend to publish it via the website once my account will be approved. Docs are available here. It's compatible with PostgreSQL 8.4, 9.0, and 9.1.

Next thing I will do for this module is to add support for syslog. Right now, it only logs to the same location than PostgreSQL.


1. Le samedi, octobre 29 2011, 19:03 par Hans

Why not include an option for pg_stat_statements that is only read when the server starts (thus requiring a server restart if an admin wants to hide the queries)

2. Le samedi, octobre 29 2011, 19:37 par Guillaume Lelarge

pg_stat_statements uses shared memory to keep track of each query, and make them available in a system catalog. I don't need that. I just need to log queries done by some users.

GUCs available on pg_log_userqueries also require restarting PostgreSQL, which makes it quite hard to change without someone noticing something.

3. Le mercredi, novembre 2 2011, 11:39 par Cédric Villemain

It is also possible to use native solution like this one :

 ALTER ROLE dba SET log_min_duration_statement = 0;

The issues are the same : the superuser can still hide a query (either alter role in my case, or UNLOAD in your example).

4. Le mercredi, novembre 2 2011, 12:43 par Guillaume Lelarge

About log_min_duration_statement, that's actually what I said in the blog post.

About the UNLOAD, did you try it? because there is no UNLOAD statement. And the PostgreSQL documentation makes it really clear that current releases of PostgreSQL do not unload shared libraries. Which means that a superuser cannot hide a query with this module.

5. Le jeudi, novembre 3 2011, 14:08 par Cédric Villemain

Woops! no UNLOAD ... and I've already pested after this lack for pgfincore ... memory fault.

6. Le jeudi, décembre 8 2011, 23:30 par Denish

Nice trick.

it might be off-topic !! Is it possible to expand it to LOG query from only on specific regex ?
For example,

I would like to log all SELECT queries on specific table.
7. Le vendredi, décembre 9 2011, 08:13 par Guillaume Lelarge

Oh yes, it's quite possible. Actually, it already does this on user names and database names. So it would be quite easy to do it on the query. But I guess it would be pretty hard to make it log only for specific tables.

Ajouter un commentaire

Les commentaires peuvent être formatés en utilisant une syntaxe wiki simplifiée.

Fil des commentaires de ce billet