{"id":17098,"date":"2022-01-31T13:58:53","date_gmt":"2022-01-31T12:58:53","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/"},"modified":"2022-01-31T13:58:53","modified_gmt":"2022-01-31T12:58:53","slug":"updating-password-in-postgresql-from-md5-to-scram-sha-256","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/","title":{"rendered":"Updating Password in PostgreSQL from md5 to scram-sha-256"},"content":{"rendered":"<p>Many installations have a history of many major PostgreSQL releases.<br \/>\nWith PostgreSQL 10 comes scram-sha-256 for hashing passwords, by installing from packages scram-sha-256 is the default setting for new installations since PostgreSQL 13.<\/p>\n<p>With this small blog I will describe how to update password from md5 to scram-sha-256.<br \/>\nFor the installation of PostgreSQL there are many blogs at dbi or articles from dbi.<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/handling-postgresql-installation-from-packages-internal-activity\/\">Blog at dbi-services.com<\/a><br \/>\n<a href=\"https:\/\/www.heise.de\/ratgeber\/PostgreSQL-installieren-mit-den-Community-Paketen-4877556.html\">Article at heise.de<\/a><\/p>\n<p>So I will not repeat this steps.<\/p>\n<p>The passwords in PostgreSQL are sotred in the table pg_authid and for this blog i have a user test.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n$ postgres=# select rolpassword from pg_authid where rolname = 'test';\n$              rolpassword\n$ -------------------------------------\n$  md56e4b266b2a0fbaa2c08d61bdefe7ee48\n$ (1 row)\n$ \n$ postgres=#\n<\/pre>\n<p>Visible is that the password is hashed in md5.<\/p>\n<p>Mostly for ip ranges, sometimes also for specified users there is a corosponding entry within pg_hba.conf.<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n$ # TYPE  DATABASE        USER            ADDRESS                 METHOD\n$ host    all             test            127.0.0.1\/32            md5\n<\/pre>\n<p>At first step we need to switch the parameter password_encryption from md5 to scram-sha-256.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n$ postgres=# alter system set password_encryption = 'scram-sha-256';\n$ ALTER SYSTEM\n$ postgres=#\n<\/pre>\n<p>And activate this change.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n$ postgres=# select pg_reload_conf();\n$  pg_reload_conf\n$ ----------------\n$  t\n$ (1 row)\n$ \n$ postgres=#\n<\/pre>\n<p>But keep the line within pg_hba till all affected passwords are updated.<\/p>\n<p>Updating the passwords to change from md5 to scram-sha-256.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n$ postgres=# alter role test with password 'password';\n$ ALTER ROLE\n$ postgres=#\n<\/pre>\n<p>Check the new hashing.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n$ postgres=# select rolpassword from pg_authid where rolname = 'test';\n$                                                               rolpassword\n$ ---------------------------------------------------------------------------------------------------------------------------------------\n$  SCRAM-SHA-256$4096:OSi8R7U5YM0ejUq982OX\/g==$82TTXF0cnuq5puyN1mnpTFsSlkLFPDbP7+3TdxtX0B4=:QCRU75g5bDKONib5s9hwsjJsweeiswkyMBFUG0IF1Ts=\n$ (1 row)\n$ \n$ postgres=#\n<\/pre>\n<p>Now change the entry for this user within pg_hba.conf<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\n$ # TYPE  DATABASE        USER            ADDRESS                 METHOD\n$ host    all             test            127.0.0.1\/32            scram-sha-256\n<\/pre>\n<p>Reload the configuration of PostgreSQL again.<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n$ postgres=# select pg_reload_conf();\n$  pg_reload_conf\n$ ----------------\n$  t\n$ (1 row)\n$ \n$ postgres=#\n<\/pre>\n<p>Now the password encryption change is completed from md5 to scram-sha-256.<\/p>\n<p>In many cases old systems where migrated to a complete new environment, new OS, latest PostgreSQL verion, by using pg_dump and pg_restore.<br \/>\nIn this cases all users can be migrated to the new environment by using pg_dumpall -r &gt; users.sql or pg_dumpall &#8211;roles-only &gt; users.sql.<br \/>\nThese files can be imported with psql -f users.sql, there will be a error messages that postgres user exits but this can be ignored.<br \/>\nAll these imported users will still have the md5 hashed passwords, so it make sense to update these user passwords directly afterwards.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many installations have a history of many major PostgreSQL releases. With PostgreSQL 10 comes scram-sha-256 for hashing passwords, by installing from packages scram-sha-256 is the default setting for new installations since PostgreSQL 13. With this small blog I will describe how to update password from md5 to scram-sha-256. For the installation of PostgreSQL there are&hellip;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133,83],"tags":[101,73],"class_list":["post-17098","post","type-post","status-publish","format-standard","category-database","category-postgresql","tag-installation","tag-linux"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.9) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Updating Password in PostgreSQL from md5 to scram-sha-256 - dbi services 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\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Updating Password in PostgreSQL from md5 to scram-sha-256\" \/>\n<meta property=\"og:description\" content=\"Many installations have a history of many major PostgreSQL releases. With PostgreSQL 10 comes scram-sha-256 for hashing passwords, by installing from packages scram-sha-256 is the default setting for new installations since PostgreSQL 13. With this small blog I will describe how to update password from md5 to scram-sha-256. For the installation of PostgreSQL there are&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-01-31T12:58:53+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\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\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"headline\":\"Updating Password in PostgreSQL from md5 to scram-sha-256\",\"datePublished\":\"2022-01-31T12:58:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/\"},\"wordCount\":302,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\"},\"keywords\":[\"Installation\",\"Linux\"],\"articleSection\":[\"Database\",\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/\",\"name\":\"Updating Password in PostgreSQL from md5 to scram-sha-256 - dbi services Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-01-31T12:58:53+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/updating-password-in-postgresql-from-md5-to-scram-sha-256\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Updating Password in PostgreSQL from md5 to scram-sha-256\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\"},\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\",\"name\":\"dbi services\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialized in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Updating Password in PostgreSQL from md5 to scram-sha-256 - dbi services 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\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/","og_locale":"en_US","og_type":"article","og_title":"Updating Password in PostgreSQL from md5 to scram-sha-256","og_description":"Many installations have a history of many major PostgreSQL releases. With PostgreSQL 10 comes scram-sha-256 for hashing passwords, by installing from packages scram-sha-256 is the default setting for new installations since PostgreSQL 13. With this small blog I will describe how to update password from md5 to scram-sha-256. For the installation of PostgreSQL there are&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/","og_site_name":"dbi services Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2022-01-31T12:58:53+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","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\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"headline":"Updating Password in PostgreSQL from md5 to scram-sha-256","datePublished":"2022-01-31T12:58:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/"},"wordCount":302,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/#organization"},"keywords":["Installation","Linux"],"articleSection":["Database","PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/","url":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/","name":"Updating Password in PostgreSQL from md5 to scram-sha-256 - dbi services Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-01-31T12:58:53+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Updating Password in PostgreSQL from md5 to scram-sha-256"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/#organization"},"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":"Organization","@id":"https:\/\/www.dbi-services.com\/#organization","name":"dbi services","url":"https:\/\/www.dbi-services.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialized in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","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\/17098","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=17098"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17098\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17098"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17098"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17098"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}