{"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":"2024-11-08T15:19:11","modified_gmt":"2024-11-08T14:19:11","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":"

Many installations have a history of many major PostgreSQL releases.
\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

With this small blog I will describe how to update password from md5 to scram-sha-256.
\nFor the installation of PostgreSQL there are many blogs at dbi or articles from dbi.<\/p>\n

Blog at dbi-services.com<\/a>
\nArticle at heise.de<\/a><\/p>\n

So I will not repeat this steps.<\/p>\n

The passwords in PostgreSQL are sotred in the table pg_authid and for this blog i have a user test.<\/p>\n

\n$ postgres=# select rolpassword from pg_authid where rolname = 'test';\n$              rolpassword\n$ -------------------------------------\n$  md56e4b266b2a0fbaa2c08d61bdefe7ee48\n$ (1 row)\n$ \n$ postgres=#\n<\/pre>\n
Visible is that the password is hashed in md5.<\/p>\n
Mostly for ip ranges, sometimes also for specified users there is a corosponding entry within pg_hba.conf.<\/p>\n
\n$ # TYPE  DATABASE        USER            ADDRESS                 METHOD\n$ host    all             test            127.0.0.1\/32            md5\n<\/pre>\n
At first step we need to switch the parameter password_encryption from md5 to scram-sha-256.<\/p>\n
\n$ postgres=# alter system set password_encryption = 'scram-sha-256';\n$ ALTER SYSTEM\n$ postgres=#\n<\/pre>\n
And activate this change.<\/p>\n
\n$ postgres=# select pg_reload_conf();\n$  pg_reload_conf\n$ ----------------\n$  t\n$ (1 row)\n$ \n$ postgres=#\n<\/pre>\n
But keep the line within pg_hba till all affected passwords are updated.<\/p>\n
Updating the passwords to change from md5 to scram-sha-256.<\/p>\n
\n$ postgres=# alter role test with password 'password';\n$ ALTER ROLE\n$ postgres=#\n<\/pre>\n
Check the new hashing.<\/p>\n
\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
Now change the entry for this user within pg_hba.conf<\/p>\n
\n$ # TYPE  DATABASE        USER            ADDRESS                 METHOD\n$ host    all             test            127.0.0.1\/32            scram-sha-256\n<\/pre>\n
Reload the configuration of PostgreSQL again.<\/p>\n
\n$ postgres=# select pg_reload_conf();\n$  pg_reload_conf\n$ ----------------\n$  t\n$ (1 row)\n$ \n$ postgres=#\n<\/pre>\n
Now the password encryption change is completed from md5 to scram-sha-256.<\/p>\n
In many cases old systems where migrated to a complete new environment, new OS, latest PostgreSQL verion, by using pg_dump and pg_restore.
\nIn this cases all users can be migrated to the new environment by using pg_dumpall -r > users.sql or pg_dumpall –roles-only > users.sql.
\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.
\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":"
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 […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,83],"tags":[101,73,2602],"type_dbi":[],"class_list":["post-17098","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-postgresql","tag-installation","tag-linux","tag-postgresql-2"],"acf":[],"yoast_head":"\nUpdating Password in PostgreSQL from md5 to scram-sha-256 - 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\/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 […]\" \/>\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 Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-01-31T12:58:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-08T14:19:11+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\/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\/59554f0d99383431eb6ed427e338952b\"},\"headline\":\"Updating Password in PostgreSQL from md5 to scram-sha-256\",\"datePublished\":\"2022-01-31T12:58:53+00:00\",\"dateModified\":\"2024-11-08T14:19:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/\"},\"wordCount\":302,\"commentCount\":0,\"keywords\":[\"Installation\",\"Linux\",\"postgresql\"],\"articleSection\":[\"Database Administration & Monitoring\",\"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 Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2022-01-31T12:58:53+00:00\",\"dateModified\":\"2024-11-08T14:19:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b\"},\"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\":\"Accueil\",\"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 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\/59554f0d99383431eb6ed427e338952b\",\"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":"Updating Password in PostgreSQL from md5 to scram-sha-256 - 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\/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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/","og_site_name":"dbi Blog","article_published_time":"2022-01-31T12:58:53+00:00","article_modified_time":"2024-11-08T14:19:11+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\/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\/59554f0d99383431eb6ed427e338952b"},"headline":"Updating Password in PostgreSQL from md5 to scram-sha-256","datePublished":"2022-01-31T12:58:53+00:00","dateModified":"2024-11-08T14:19:11+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/updating-password-in-postgresql-from-md5-to-scram-sha-256\/"},"wordCount":302,"commentCount":0,"keywords":["Installation","Linux","postgresql"],"articleSection":["Database Administration & Monitoring","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 Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-01-31T12:58:53+00:00","dateModified":"2024-11-08T14:19:11+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/59554f0d99383431eb6ed427e338952b"},"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":"Accueil","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 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\/59554f0d99383431eb6ed427e338952b","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\/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":1,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17098\/revisions"}],"predecessor-version":[{"id":35667,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17098\/revisions\/35667"}],"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"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17098"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}