{"id":37631,"date":"2025-03-13T09:24:21","date_gmt":"2025-03-13T08:24:21","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=37631"},"modified":"2025-03-13T09:24:24","modified_gmt":"2025-03-13T08:24:24","slug":"postgresql-18-more-granular-log_connections","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/","title":{"rendered":"PostgreSQL 18: More granular log_connections"},"content":{"rendered":"\n<p>Many of our customers enable <a href=\"https:\/\/www.postgresql.org\/docs\/17\/runtime-config-logging.html#GUC-LOG-CONNECTIONS\" target=\"_blank\" rel=\"noreferrer noopener\">log_connections<\/a> because of auditing requirements. This is a simple boolean which is either turned on or off. Once this is enabled and active every new connection to a PostgreSQL database is logged into the PostgreSQL log file. Up to PostgreSQL 17, a typical line in the log file for a logged connection looks like this:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n2025-03-13 08:50:05.607 CET - 1 - 6195 - &#x5B;local] - &#x5B;unknown]@&#x5B;unknown] - 0LOG:  connection received: host=&#x5B;local]\n2025-03-13 08:50:05.607 CET - 2 - 6195 - &#x5B;local] - postgres@postgres - 0LOG:  connection authenticated: user=&quot;postgres&quot; method=trust (\/u02\/pgdata\/17\/pg_hba.conf:117)\n2025-03-13 08:50:05.607 CET - 3 - 6195 - &#x5B;local] - postgres@postgres - 0LOG:  connection authorized: user=postgres database=postgres application_name=psql\n<\/pre><\/div>\n\n\n<p>As you can see, there are three stages logged: Connection received, authenticated and authorized. This gives you an idea of how long each of the stages took to complete by comparing the timestamps logged. A consequence of this is, that it can generate quite some noise in the log file if you have many connections.<\/p>\n\n\n\n<p>With PostgreSQL 18 this will change, log_connections is not anymore a simple boolean but a list of supported values. The valid options are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>receipt<\/li>\n\n\n\n<li>authentication<\/li>\n\n\n\n<li>authorization<\/li>\n\n\n\n<li>[empty string]<\/li>\n<\/ul>\n\n\n\n<p>This list should already tell you what changed. You now have the option to enable logging of specific stages only, and not all of them at once if you don&#8217;t need them. An empty string disables connection logging.<\/p>\n\n\n\n<p>So, e.g. if you are only interested in the authorization stage you can now configure that:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,5,11,13]; title: ; notranslate\" title=\"\">\npostgres@pgbox:\/home\/postgres\/ &#x5B;pgdev] psql\npsql (18devel)\nType &quot;help&quot; for help.\n\npostgres=# select version();\n                              version                               \n--------------------------------------------------------------------\n PostgreSQL 18devel on x86_64-linux, compiled by gcc-14.2.1, 64-bit\n(1 row)\n\npostgres=# alter system set log_connections = &#039;authorization&#039;;\nALTER SYSTEM\npostgres=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n<\/pre><\/div>\n\n\n<p>From now on only the &#8220;authorization&#8221; stage is logged into the log file:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n2025-03-13 09:10:41.710 CET - 1 - 6617 - &#x5B;local] - postgres@postgres - 0LOG:  connection authorized: user=postgres database=postgres application_name=psql\n<\/pre><\/div>\n\n\n<p>This reduces the amount of logging quite a bit, if you are only interested in that stage. Adding all stages will restore the old behavior of logging all stages:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# alter system set log_connections = &#039;authorization&#039;,&#039;receipt&#039;,&#039;authentication&#039;;\nALTER SYSTEM\npostgres=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n<\/pre><\/div>\n\n\n<p>With this setting, it looks exactly like before:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: plain; title: ; notranslate\" title=\"\">\n2025-03-13 09:14:19.520 CET - 1 - 6629 - &#x5B;local] - &#x5B;unknown]@&#x5B;unknown] - 0LOG:  connection received: host=&#x5B;local]\n2025-03-13 09:14:19.521 CET - 2 - 6629 - &#x5B;local] - postgres@postgres - 0LOG:  connection authenticated: user=&quot;postgres&quot; method=trust (\/u02\/pgdata\/PGDEV\/pg_hba.conf:117)\n2025-03-13 09:14:19.521 CET - 3 - 6629 - &#x5B;local] - postgres@postgres - 0LOG:  connection authorized: user=postgres database=postgres application_name=psql\n<\/pre><\/div>\n\n\n<p>Nice, all details <a href=\"https:\/\/git.postgresql.org\/gitweb\/?p=postgresql.git;a=commitdiff;h=9219093cab2607f34ac70612a65430a9c519157f\">here<\/a>, and as usual, thank you to all involved in this.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many of our customers enable log_connections because of auditing requirements. This is a simple boolean which is either turned on or off. Once this is enabled and active every new connection to a PostgreSQL database is logged into the PostgreSQL log file. Up to PostgreSQL 17, a typical line in the log file for a [&hellip;]<\/p>\n","protected":false},"author":29,"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-37631","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 v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 18: More granular log_connections - 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-18-more-granular-log_connections\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 18: More granular log_connections\" \/>\n<meta property=\"og:description\" content=\"Many of our customers enable log_connections because of auditing requirements. This is a simple boolean which is either turned on or off. Once this is enabled and active every new connection to a PostgreSQL database is logged into the PostgreSQL log file. Up to PostgreSQL 17, a typical line in the log file for a [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-03-13T08:24:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-13T08:24:24+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\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-18-more-granular-log_connections\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 18: More granular log_connections\",\"datePublished\":\"2025-03-13T08:24:21+00:00\",\"dateModified\":\"2025-03-13T08:24:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/\"},\"wordCount\":268,\"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-18-more-granular-log_connections\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/\",\"name\":\"PostgreSQL 18: More granular log_connections - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2025-03-13T08:24:21+00:00\",\"dateModified\":\"2025-03-13T08:24:24+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 18: More granular log_connections\"}]},{\"@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\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 18: More granular log_connections - 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-18-more-granular-log_connections\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 18: More granular log_connections","og_description":"Many of our customers enable log_connections because of auditing requirements. This is a simple boolean which is either turned on or off. Once this is enabled and active every new connection to a PostgreSQL database is logged into the PostgreSQL log file. Up to PostgreSQL 17, a typical line in the log file for a [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/","og_site_name":"dbi Blog","article_published_time":"2025-03-13T08:24:21+00:00","article_modified_time":"2025-03-13T08:24:24+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 18: More granular log_connections","datePublished":"2025-03-13T08:24:21+00:00","dateModified":"2025-03-13T08:24:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/"},"wordCount":268,"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-18-more-granular-log_connections\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/","name":"PostgreSQL 18: More granular log_connections - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2025-03-13T08:24:21+00:00","dateModified":"2025-03-13T08:24:24+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-more-granular-log_connections\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 18: More granular log_connections"}]},{"@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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37631","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\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=37631"}],"version-history":[{"count":5,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37631\/revisions"}],"predecessor-version":[{"id":37636,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37631\/revisions\/37636"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=37631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=37631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=37631"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=37631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}