{"id":11091,"date":"2018-04-21T11:39:09","date_gmt":"2018-04-21T09:39:09","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/"},"modified":"2018-04-21T11:39:09","modified_gmt":"2018-04-21T09:39:09","slug":"can-i-do-it-with-postgresql-20-create-user-identified-by-values","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/","title":{"rendered":"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user &#8230; identified by values"},"content":{"rendered":"<p>Puh, that <a href=\"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-18-instead-of-triggers-on-views\/\" target=\"_blank\" rel=\"noopener\">last post<\/a> in this series is already half a year old. Time is moving too fast \ud83d\ude41 Today, while being at a customer again, this question came up: Can I do something comparable in PostgreSQL to what I can do in Oracle, which is: Create a user and provide the hashed password so that the password is the same on the source and the target (which implies not knowing the password at all)? In Oracle you can find the hashed passwords in user$ where can I find that in PostgreSQL? Lets go.<\/p>\n<p><!--more--><\/p>\n<p>When we look at the &#8220;create user&#8221; command there is no option which seems to do that:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# h create user\nCommand:     CREATE USER\nDescription: define a new database role\nSyntax:\nCREATE USER name [ [ WITH ] option [ ... ] ]\n\nwhere option can be:\n\n      SUPERUSER | NOSUPERUSER\n    | CREATEDB | NOCREATEDB\n    | CREATEROLE | NOCREATEROLE\n    | INHERIT | NOINHERIT\n    | LOGIN | NOLOGIN\n    | REPLICATION | NOREPLICATION\n    | BYPASSRLS | NOBYPASSRLS\n    | CONNECTION LIMIT connlimit\n    | [ ENCRYPTED ] PASSWORD 'password'\n    | VALID UNTIL 'timestamp'\n    | IN ROLE role_name [, ...]\n    | IN GROUP role_name [, ...]\n    | ROLE role_name [, ...]\n    | ADMIN role_name [, ...]\n    | USER role_name [, ...]\n    | SYSID uid\n<\/pre>\n<p>Maybe we can just pass the hashed password? Lets try be creating a new user:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create user u with login password 'u';\nCREATE ROLE\n<\/pre>\n<p>The hashed passwords in PostgreSQL are stored in pg_shadow:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select passwd from pg_shadow where usename = 'u';\n               passwd                \n-------------------------------------\n md56277e2a7446059985dc9bcf0a4ac1a8f\n(1 row)\n<\/pre>\n<p>Lets use that hash and create a new user:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create user w login encrypted password 'md56277e2a7446059985dc9bcf0a4ac1a8f';\nCREATE ROLE\n<\/pre>\n<p>Can we login as w using &#8220;u&#8221; as a password?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W\nPassword for user u: \npsql: FATAL:  no pg_hba.conf entry for host \"192.168.22.99\", user \"w\", database \"postgres\", SSL off\n<\/pre>\n<p>Ok, makes sense. After fixing that:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W\nPassword for user w: \npsql: FATAL:  password authentication failed for user \"w\"\n<\/pre>\n<p>So obviously this is not the way to do it. Do we have the same hashes in pg_shadow?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select usename,passwd from pg_shadow where usename in ('w','u');\n usename |               passwd                \n---------+-------------------------------------\n u       | md56277e2a7446059985dc9bcf0a4ac1a8f\n w       | md56277e2a7446059985dc9bcf0a4ac1a8f\n(2 rows)\n<\/pre>\n<p>Hm, exactly the same. Why can&#8217;t we login then? The answer is in the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/sql-alterrole.html\" target=\"_blank\" rel=\"noopener\">documentation<\/a>:&#8221;Because MD5-encrypted passwords use the role name as cryptographic salt, &#8230;&#8221;. We can verify that be re-creating the &#8220;w&#8221; user using the same password as that of user &#8220;u&#8221;:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# drop user w;\nDROP ROLE\npostgres=# create user w login password 'u';\nCREATE ROLE\npostgres=# select usename,passwd from pg_shadow where usename in ('w','u');\n usename |               passwd                \n---------+-------------------------------------\n u       | md56277e2a7446059985dc9bcf0a4ac1a8f\n w       | md53eae63594a41739e87141e8333d15f73\n(2 rows)\n<\/pre>\n<p>The hashed values are not the same anymore. What of course is working is to re-create the user with that hash:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# drop role w;\nDROP ROLE\npostgres=# create user w login password 'md53eae63594a41739e87141e8333d15f73';\nCREATE ROLE\n<\/pre>\n<p>Now we should be able to login with the password &#8216;u&#8217;:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG10] psql -X -h 192.168.22.99 -p $PGPORT -U w postgres -W\nPassword for user w: \npsql (10.0 dbi services build)\nType \"help\" for help.\n\npostgres=&gt; \n<\/pre>\n<p>Fine. Another way of getting the password hashes is to use <a href=\"\" target=\"_blank\" rel=\"noopener\">pg_dumpall<\/a> using the &#8220;&#8211;globals-only&#8221; switch:<\/p>\n<pre class=\"brush: bash; gutter: true; first-line: 1\">\npostgres@pgbox:\/home\/postgres\/ [PG10] pg_dumpall --globals-only &gt; a.sql\npostgres@pgbox:\/home\/postgres\/ [PG10] grep -w w a.sql \nCREATE ROLE w;\nALTER ROLE w WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md53eae63594a41739e87141e8333d15f73';\n<\/pre>\n<p>Hope that helps.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Puh, that last post in this series is already half a year old. Time is moving too fast \ud83d\ude41 Today, while being at a customer again, this question came up: Can I do something comparable in PostgreSQL to what I can do in Oracle, which is: Create a user and provide the hashed password so [&hellip;]<\/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],"tags":[77],"type_dbi":[],"class_list":["post-11091","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","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>Can I do it with PostgreSQL? \u2013 19 \u2013 Create user ... identified by values - 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\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user ... identified by values\" \/>\n<meta property=\"og:description\" content=\"Puh, that last post in this series is already half a year old. Time is moving too fast \ud83d\ude41 Today, while being at a customer again, this question came up: Can I do something comparable in PostgreSQL to what I can do in Oracle, which is: Create a user and provide the hashed password so [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-21T09:39:09+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=\"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\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user &#8230; identified by values\",\"datePublished\":\"2018-04-21T09:39:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/\"},\"wordCount\":287,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/\",\"name\":\"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user ... identified by values - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-04-21T09:39:09+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user &#8230; identified by values\"}]},{\"@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":"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user ... identified by values - 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\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/","og_locale":"en_US","og_type":"article","og_title":"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user ... identified by values","og_description":"Puh, that last post in this series is already half a year old. Time is moving too fast \ud83d\ude41 Today, while being at a customer again, this question came up: Can I do something comparable in PostgreSQL to what I can do in Oracle, which is: Create a user and provide the hashed password so [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/","og_site_name":"dbi Blog","article_published_time":"2018-04-21T09:39:09+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user &#8230; identified by values","datePublished":"2018-04-21T09:39:09+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/"},"wordCount":287,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/","url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/","name":"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user ... identified by values - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-04-21T09:39:09+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-20-create-user-identified-by-values\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Can I do it with PostgreSQL? \u2013 19 \u2013 Create user &#8230; identified by values"}]},{"@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\/11091","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=11091"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11091\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11091"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11091"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11091"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11091"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}