{"id":12627,"date":"2019-07-11T08:43:09","date_gmt":"2019-07-11T06:43:09","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/"},"modified":"2019-07-11T08:43:09","modified_gmt":"2019-07-11T06:43:09","slug":"migrating-your-users-from-md5-to-scram-authentication-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/","title":{"rendered":"Migrating your users from md5 to scram authentication in PostgreSQL"},"content":{"rendered":"

One of the new features in PostgreSQL 10 was the introduction of stronger password authentication based on SCRAM-SHA-256<\/a>. How can you migrate your existing users that currently use md5 authentication to the new method without any interruption? Actually that is quite easy, as you will see in a few moments, but there is one important point to consider: Not every client\/driver does already support SCRAM-SHA-256 authentication so you need to check that before. Here is the list of the drivers<\/a> and their support for SCRAM-SHA-256. <\/p>\n

<\/p>\n

The default method that PostgreSQL uses to encrypt password is defined by the “password_encryption”<\/a> parameter:<\/p>\n

\npostgres=# show password_encryption;\n password_encryption \n---------------------\n md5\n(1 row)\n<\/pre>\n
Let’s assume we have a user that was created like this in the past:<\/p>\n
\npostgres=# create user u1 login password 'u1';\nCREATE ROLE\n<\/pre>\n
With the default method of md5 the hashed password looks like this:<\/p>\n
\npostgres=# select passwd from pg_shadow where usename = 'u1';\n               passwd                \n-------------------------------------\n md58026a39c502750413402a90d9d8bae3c\n(1 row)\n<\/pre>\n
As you can see the hash starts with md5 so we now that this hash was generated by the md5 algorithm. When we want this user to use scram-sha-256 instead, what do we need to do? The first step is to change the “password_encryption”<\/a> parameter:<\/p>\n
\npostgres=# alter system set password_encryption = 'scram-sha-256';\nALTER SYSTEM\npostgres=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\npostgres=# select current_setting('password_encryption');\n current_setting \n-----------------\n scram-sha-256\n(1 row)\n<\/pre>\n
From now on the server will use scram-sha-256 and not anymore md5. But what happens when our user wants to connect to the instance once we changed that? Currently this is defined in pg_hba.conf<\/a>:<\/p>\n
\npostgres=> ! grep u1 $PGDATA\/pg_hba.conf\nhost    postgres        u1              192.168.22.1\/24         md5\n<\/pre>\n
Even though the default is not md5 anymore the user can still connect to the instance because the password hash did not change for that user:<\/p>\n
\npostgres=> ! grep u1 $PGDATA\/pg_hba.conf\nhost    postgres        u1              192.168.22.1\/24         md5\n\npostgres@rhel8pg:\/home\/postgres\/ [PGDEV] psql -h 192.168.22.100 -p 5433 -U u1 postgres\nPassword for user u1: \npsql (13devel)\nType \"help\" for help.\n\npostgres=> \n<\/pre>\n
Once the user changed the password:<\/p>\n
\npostgres@rhel8pg:\/home\/postgres\/ [PGDEV] psql -h 192.168.22.100 -p 5433 -U u1 postgres\nPassword for user u1: \npsql (13devel)\nType \"help\" for help.\n\npostgres=> password\nEnter new password: \nEnter it again: \npostgres=> \n<\/pre>\n
… the hash of the new password is not md5 but SCRAM-SHA-256:<\/p>\n
\npostgres=# select passwd from pg_shadow where usename = 'u1';\n                                                                passwd                               >\n----------------------------------------------------------------------------------------------------->\n SCRAM-SHA-256$4096:CypPmOW5\/uIu4NvGJa+FNA==$PNGhlmRinbEKaFoPzi7T0hWk0emk18Ip9tv6mYIguAQ=:J9vr5CQDuKE>\n(1 row)\n<\/pre>\n
One could expect that from now on the user is not able to connect anymore as we did not change pg_hba.conf until now:<\/p>\n
\npostgres@rhel8pg:\/home\/postgres\/ [PGDEV] psql -h 192.168.22.100 -p 5433 -U u1 postgres\nPassword for user u1: \npsql (13devel)\nType \"help\" for help.\n\npostgres=> \n<\/pre>\n
But in reality that still works as the server now uses the SCRAM-SHA-256 algorithm. So once all the users changed their passwords you can safely switch the rule in pg_hba.conf and you’re done:<\/p>\n
\npostgres=> ! grep u1 $PGDATA\/pg_hba.conf\nhost    postgres        u1              192.168.22.1\/24         scram-sha-256\n\npostgres=# select pg_reload_conf();\n pg_reload_conf \n----------------\n t\n(1 row)\n<\/pre>\n
You just need to make sure that all the users do not have a hash starting with md5 but the new one starting with SCRAM-SHA-256.<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the new features in PostgreSQL 10 was the introduction of stronger password authentication based on SCRAM-SHA-256. How can you migrate your existing users that currently use md5 authentication to the new method without any interruption? Actually that is quite easy, as you will see in a few moments, but there is one important […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-12627","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nMigrating your users from md5 to scram authentication in PostgreSQL - 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\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Migrating your users from md5 to scram authentication in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"One of the new features in PostgreSQL 10 was the introduction of stronger password authentication based on SCRAM-SHA-256. How can you migrate your existing users that currently use md5 authentication to the new method without any interruption? Actually that is quite easy, as you will see in a few moments, but there is one important […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-07-11T06:43:09+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=\"3 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\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Migrating your users from md5 to scram authentication in PostgreSQL\",\"datePublished\":\"2019-07-11T06:43:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/\"},\"wordCount\":349,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/\",\"name\":\"Migrating your users from md5 to scram authentication in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-07-11T06:43:09+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Migrating your users from md5 to scram authentication in PostgreSQL\"}]},{\"@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 &\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":"Migrating your users from md5 to scram authentication in PostgreSQL - 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\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Migrating your users from md5 to scram authentication in PostgreSQL","og_description":"One of the new features in PostgreSQL 10 was the introduction of stronger password authentication based on SCRAM-SHA-256. How can you migrate your existing users that currently use md5 authentication to the new method without any interruption? Actually that is quite easy, as you will see in a few moments, but there is one important […]","og_url":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2019-07-11T06:43:09+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Migrating your users from md5 to scram authentication in PostgreSQL","datePublished":"2019-07-11T06:43:09+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/"},"wordCount":349,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/","name":"Migrating your users from md5 to scram authentication in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-07-11T06:43:09+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/migrating-your-users-from-md5-to-scram-authentication-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Migrating your users from md5 to scram authentication in PostgreSQL"}]},{"@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 &\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\/12627","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=12627"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12627\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12627"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12627"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12627"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12627"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}