3

I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious attacker can get access to all the queries successfully executed on the PostgreSQL server, if I have disabled logging as much as possible. Is this possible? Or once a query has been successfully executed (might be a SELECT or UPDATE query) I can be 100% sure that the DB server has no memory of successfully executed queries and therefore no one else can get access to this information. I am using PostgreSQL 9.3.

  • Out of interest ... why? Protecting crypto keys or something along those lines? Describing your use case when asking about security issues generally helps make sure that answers cover the appropriate areas. – Craig Ringer Apr 11 '15 at 8:32
  • @CraigRinger I am doing research, so it is not really a real-world application. Let' say I have found a way to magically encrypt rows on the server by a separate key per row. If someone queries the server it will get nothing (data is encrypted). I can query the DB server by providing a query and the respective key to unencrypt data. But if someone gets the query history it will get access to the keys (at least for the rows queried). So, I want to avoid that. – Alexandros Apr 11 '15 at 8:39
3

[can I] be 100% sure that the DB server has no memory of successfully executed queries and therefore no one else can get access to this information?

No, you can never be 100% sure of that.

PostgreSQL maintains an internal plan cache. Currently this cache is only used for prepared statements; one-shot statements do not go into the cache. If you use a prepared statement though, the plan will be cached, and when no longer required the cached plan is invalidated but not necessarily deleted until the PostgreSQL backend that cached it exits.

PostgreSQL stores the query string in an internal debug variable. When a new query replaces the old one the debug variable is overwritten. If the new query is shorter than the old one, some of the old query won't be overwritten.

A query could be running a backend that has pages of memory written to disk in swap space because of memory pressure. That memory might contain the query string and/or parameters. This swap space isn't necessary overwritten even after the query has finished and the backend has exited. If the server is powered off abruptly the hard drive could be forensically analyzed and might discover query text.

When PostgreSQL frees internal query planner/executor objects, it does not zero them. It just marks the memory as free for re-use. This memory remains mapped until the PostgreSQL backend exits, and may contain query text, parameter values, etc.

When a PostgreSQL backend exits, the kernel doesn't generally zero its memory, just marks it as freed. An attacker who can exploit the kernel to gain raw memory read access could then see some past (and, of course, all current) queries.

So if:

  • You have encrypted swap space with a password supplied interactively on boot;

  • You accept that anyone with physical access to the machine will probably be able to exploit it to retrieve recent query history;

  • You understand that anyone with root access, or access as the postgres system user, can trace PostgreSQL's execution and capture query data (just not retroactively); and

  • You only care about query text recoverability after the individual PostgreSQL backend that ran that query has exited

then turning off logging is probably good enough. You also need to avoid using extensions like pg_stat_plans and pg_stat_statements that might capture query text and record it in the database.

Note that if PL/Python, PL/Perl, etc are installed, or some contrib modules are present, then it might be fairly easy to escalate access from the postgres database user (or other superuser) to the postgres shell user. From there you can reconfigure the database to enable logging, install extensions, inject C code, etc. There is no guarantee you would detect such an exploit, so they might be logging activity for some time.

An attacker who gets access even as the table owner might install a FOR EACH STATEMENT trigger on tables of interest that logs queries affecting those tables. That won't work for SELECT, but will for everything else. They don't even need to be database superuser for that.

Generally your queries shouldn't be security sensitive so much as your query parameter data. You can greatly reduce the chances of it being captured or logged by using proper bind parameters, e.g. libpq's PQexecParams, PgJDBC's PreparedStatement class, etc.

Most importantly, though ... if you want to maintain database security, don't lose privileged access credentials. Don't use a superuser account except from the DB server and set pg_hba.conf up so the superuser can't log in remotely. Don't have applications connect to the database as the same account that owns the tables they use, use a different account and GRANT only the rights they require. etc.


IMO PostgreSQL needs an extension in the protocol that lets you mark query parameters as "sensitive". Such query parameters should never be emitted in log files, shown in pg_stat_activity or otherwise recorded. This feature does not currently exist, though, and I'm not aware of anybody working on it

  • Thanks Craig. Nobody will have access to the physical machine. My case was mainly focused if someone hacks only the DB server (e.g., get postgres password) and then execute a query to get the history. – Alexandros Apr 11 '15 at 8:34
  • If they get access as the PostgreSQL superuser it is relatively simple to escalate that to shell-level access as the postgres user on many systems. (Not installing extensions and procedural languages makes it harder, but if e.g. PL/Python is installed shell access takes 5 seconds). From there they can change your logging settings, directly read raw database files, or even inject C code as a PostgreSQL extension. If someone gets superuser access to the database you have lost. They can't get history... but they can start collecting it. – Craig Ringer Apr 11 '15 at 8:36
  • You have no guarantee that you will detect any such break-in/compromise, so someone could set up logging/tracing to capture query history despite your efforts to turn it off. – Craig Ringer Apr 11 '15 at 8:38
  • @Alexandros For this reason limiting the superuser to access only from the local host, and not using a superuser account for other things, is generally a good idea. – Craig Ringer Apr 11 '15 at 8:39
  • Local superuser, got it. If they get local superuser I am doomed anyway, because they may even destroy the DB, so there is no way to protect against this anyway. – Alexandros Apr 11 '15 at 8:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.