{"id":1476,"date":"2013-08-20T13:05:17","date_gmt":"2013-08-20T01:05:17","guid":{"rendered":"http:\/\/www.zoyinc.com\/?p=1476"},"modified":"2013-09-29T19:47:36","modified_gmt":"2013-09-29T06:47:36","slug":"resetting-a-postgresql-root-password","status":"publish","type":"post","link":"http:\/\/www.zoyinc.com\/?p=1476","title":{"rendered":"Resetting a PostgreSQL root password"},"content":{"rendered":"

Recently I upgraded to the latest Postgres version. I had a number of pain points doing this including not being able to start the DBs because the version upgrade was too big – had to export from old version and import to new. But one of the problems was that for some reason my password didn’t work.<\/p>\n

One of the more important config files in Postgres is pg_hba.conf. It is the one that controls access. In my case the uncommented bit looks like:<\/p>\n

\u00a0\u00a0 # TYPE\u00a0 DATABASE\u00a0\u00a0\u00a0 USER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CIDR-ADDRESS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 METHOD\r\n\r\n\u00a0\u00a0 # \"local\" is for Unix domain socket connections only\r\n\u00a0\u00a0 local\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 password\r\n\u00a0\u00a0 # IPv4 local connections:\r\n\u00a0\u00a0 host\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 192.168.0.0\/16\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 md5\r\n\u00a0\u00a0 host\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 127.0.0.1\/32\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 md5<\/pre>\n
Running on Fedora 18 this is located in:<\/p>\n
\/var\/lib\/pgsql\/data\/pg_hba.conf<\/p>\n
To reset the password you need to change the first line from “password” to “trust” this means that it will log you in with the Unix account “postgres” rather than the Postgres account “root”. So change the file as below:<\/p>\n
#local\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 password\r\nlocal\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 all\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 trust<\/pre>\n
You will need to restart Postgres for it to be effective !<\/p>\n
The following commands should reset the password:<\/p>\n
su \u2013 postgres\r\npsql -U postgres\r\nALTER USER postgres WITH PASSWORD 'mySuperSecretpassword';\r\n\\q<\/pre>\n
Obviously the above assumes you are running Postgres under the account “postgres”. Note that “\/q” is the command to exit psql.<\/p>\n
Obviously afterwards you need to undo the change to “pg_hba.conf” and restart PosgreSQL<\/p>\n","protected":false},"excerpt":{"rendered":"
Recently I upgraded to the latest Postgres version. I had a number of pain points doing this including not being able to start the DBs because the version upgrade was too big – had to export from old version and import to new. But one of the problems was that for some reason my password […]<\/p>\n","protected":false},"author":2,"featured_media":1424,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[103],"tags":[168,166,165,158,164,167],"class_list":["post-1476","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","tag-database-2","tag-lost","tag-password","tag-postgresql","tag-postres","tag-reset"],"_links":{"self":[{"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=\/wp\/v2\/posts\/1476","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1476"}],"version-history":[{"count":5,"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=\/wp\/v2\/posts\/1476\/revisions"}],"predecessor-version":[{"id":1648,"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=\/wp\/v2\/posts\/1476\/revisions\/1648"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=\/wp\/v2\/media\/1424"}],"wp:attachment":[{"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1476"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.zoyinc.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}