PostgreSQL: System catalog modifications are currently disallowed

Today at a customer I faced this: “System catalog modifications are currently disallowed” which, honestly, I’ve never seen before. Why would someone try to modify the system catalogs, at least in a productive environment? It turned out to be pretty simple, but when I’ve seen this I was somehow lost. The command which was executed was:

postgres=# create extension pg_stat_statements;

Sometimes customers do things you just do not expect. In this case the whole deployment was Ansible driven and nobody really remembered what happens exactly. This is a downside of automation: If you blindly apply, in this case playbooks, you tend to forget what they are actually doing.

The first thing I’ve tried (and I should have read the error message in more detail) was to do the same in a psql session:

postgres=# create extension pg_stat_statements;
ERROR:  permission denied to create "pg_catalog.pg_stat_statements"
DETAIL:  System catalog modifications are currently disallowed.

Next thing I’ve tried was to create a simple table, just to be sure that at least this is working:

postgres=# create table t(a int);
ERROR:  permission denied to create "pg_catalog.t"
DETAIL:  System catalog modifications are currently disallowed.

Same error message. The point I’ve missed is that everything you need to know is in the error message, you just have to read it carefully. Why does PostgreSQL want to create that table, or the extension previously, in the the “pg_catalog” schema?

And then it was pretty clear what happened:

postgres=# show search_path ;
    search_path     
--------------------
 pg_catalog, xx, yy
(1 row)

What they did, was to modify the search_path and putting “pg_catalog” in front of the list is not a good idea (this is anyway included by default, but not at the beginning). If you do that, PostgreSQL will try to create all objects there, and this will obviously fail.

Re-setting that to a more sane value resolved the issue.