{"id":26673,"date":"2023-07-12T17:11:52","date_gmt":"2023-07-12T15:11:52","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=26673"},"modified":"2023-07-12T17:11:54","modified_gmt":"2023-07-12T15:11:54","slug":"postgresql-system-catalog-modifications-are-currently-disallowed","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/","title":{"rendered":"PostgreSQL: System catalog modifications are currently disallowed"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Today at a customer I faced this: &#8220;System catalog modifications are currently disallowed&#8221; which, honestly, I&#8217;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&#8217;ve seen this I was somehow lost. The command which was executed was:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\npostgres=# create extension pg_stat_statements;\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Sometimes customers do things you just do not expect. In this case the whole deployment was <a href=\"https:\/\/github.com\/ansible\/ansible\" target=\"_blank\" rel=\"noreferrer noopener\">Ansible<\/a> 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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The first thing I&#8217;ve tried (and I should have read the error message in more detail) was to do the same in a psql session:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# create extension pg_stat_statements;\nERROR:  permission denied to create &quot;pg_catalog.pg_stat_statements&quot;\nDETAIL:  System catalog modifications are currently disallowed.\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Next thing I&#8217;ve tried was to create a simple table, just to be sure that at least this is working:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# create table t(a int);\nERROR:  permission denied to create &quot;pg_catalog.t&quot;\nDETAIL:  System catalog modifications are currently disallowed.\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">Same error message. The point I&#8217;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 &#8220;pg_catalog&#8221; schema? <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">And then it was pretty clear what happened:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# show search_path ;\n    search_path     \n--------------------\n pg_catalog, xx, yy\n(1 row)\n<\/pre><\/div>\n\n\n<p class=\"wp-block-paragraph\">What they did, was to modify the <a href=\"https:\/\/www.postgresql.org\/docs\/15\/ddl-schemas.html#DDL-SCHEMAS-PATH\" target=\"_blank\" rel=\"noreferrer noopener\">search_path<\/a> and putting &#8220;pg_catalog&#8221; 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. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Re-setting that to a more sane value resolved the issue.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today at a customer I faced this: &#8220;System catalog modifications are currently disallowed&#8221; which, honestly, I&#8217;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&#8217;ve seen this I was somehow lost. The command which was executed [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-26673","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL: System catalog modifications are currently disallowed - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL: System catalog modifications are currently disallowed\" \/>\n<meta property=\"og:description\" content=\"Today at a customer I faced this: &#8220;System catalog modifications are currently disallowed&#8221; which, honestly, I&#8217;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&#8217;ve seen this I was somehow lost. The command which was executed [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-12T15:11:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-07-12T15:11:54+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"PostgreSQL: System catalog modifications are currently disallowed\",\"datePublished\":\"2023-07-12T15:11:52+00:00\",\"dateModified\":\"2023-07-12T15:11:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/\"},\"wordCount\":274,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/\",\"name\":\"PostgreSQL: System catalog modifications are currently disallowed - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-07-12T15:11:52+00:00\",\"dateModified\":\"2023-07-12T15:11:54+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-system-catalog-modifications-are-currently-disallowed\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL: System catalog modifications are currently disallowed\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL: System catalog modifications are currently disallowed - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL: System catalog modifications are currently disallowed","og_description":"Today at a customer I faced this: &#8220;System catalog modifications are currently disallowed&#8221; which, honestly, I&#8217;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&#8217;ve seen this I was somehow lost. The command which was executed [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/","og_site_name":"dbi Blog","article_published_time":"2023-07-12T15:11:52+00:00","article_modified_time":"2023-07-12T15:11:54+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"PostgreSQL: System catalog modifications are currently disallowed","datePublished":"2023-07-12T15:11:52+00:00","dateModified":"2023-07-12T15:11:54+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/"},"wordCount":274,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/","name":"PostgreSQL: System catalog modifications are currently disallowed - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-07-12T15:11:52+00:00","dateModified":"2023-07-12T15:11:54+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-system-catalog-modifications-are-currently-disallowed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL: System catalog modifications are currently disallowed"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/26673","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=26673"}],"version-history":[{"count":5,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/26673\/revisions"}],"predecessor-version":[{"id":26678,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/26673\/revisions\/26678"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=26673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=26673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=26673"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=26673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}