{"id":4609,"date":"2015-04-26T19:17:47","date_gmt":"2015-04-26T11:17:47","guid":{"rendered":"http:\/\/rmohan.com\/?p=4609"},"modified":"2015-04-26T19:17:47","modified_gmt":"2015-04-26T11:17:47","slug":"resetting-the-rootpostgres-password-for-postgresql","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=4609","title":{"rendered":"Resetting the root\/postgres password for PostgreSQL"},"content":{"rendered":"<p>The following is required to reset the root\/postgres user password for PostgreSQL. The distribution used in my example is CentOS 5.5 and PostgreSQL 8.4.<\/p>\n<p><strong>Note: By default there\u2019s no password for the postgres user.<\/strong><\/p>\n<p>In step 2 and 5 you will most likely not be using \u201cident\u201d but rather \u201cpassword\u201d or \u201cmd5?.<\/p>\n<h2>1. Shut down PostgreSQL<\/h2>\n<pre># service postgresql stop<\/pre>\n<h2>2. Reset the authentication mechanism (assuming defaults are already being used)<\/h2>\n<p>Edit the \/usr\/lib\/pgsql\/data\/pg_hba.conf file<\/p>\n<pre># nano \/usr\/lib\/pgsql\/data\/pg_hba.conf<\/pre>\n<p>Navigate down to the line that says<\/p>\n<pre>local all all ident<\/pre>\n<p>Edit it to<\/p>\n<pre>local all postgres trust<\/pre>\n<p>And now save the file.<\/p>\n<h2>3. Start PostgreSQL<\/h2>\n<pre># service postgresql start<\/pre>\n<h2>4. Log in and change the password<\/h2>\n<pre># su - postgres<\/pre>\n<pre>$ psql -d template1 -U postgres<\/pre>\n<pre>alter user postgres with password 'new_password';<\/pre>\n<p>Or alternatively do it all in one go with the following command<\/p>\n<pre>&gt; psql -U postgres template1 -c \"alter user postgres with password 'new_password';\"<\/pre>\n<h2>5. Reverse the actions you did in step 2<\/h2>\n<p>Edit the \/usr\/lib\/pgsql\/data\/pg_hba.conf file<\/p>\n<pre># nano \/usr\/lib\/pgsql\/data\/pg_hba.conf<\/pre>\n<p>Navigate down to the line that says<\/p>\n<pre>local all all trust<\/pre>\n<p>Edit it to<\/p>\n<pre>local all postgres ident<\/pre>\n<p>And now save the file.<\/p>\n<h2>6. Start PostgreSQL<\/h2>\n<pre># service postgresql start<\/pre>\n<p>Success!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The following is required to reset the root\/postgres user password for PostgreSQL. The distribution used in my example is CentOS 5.5 and PostgreSQL 8.4.<\/p>\n<p>Note: By default there\u2019s no password for the postgres user.<\/p>\n<p>In step 2 and 5 you will most likely not be using \u201cident\u201d but rather \u201cpassword\u201d or \u201cmd5?.<\/p>\n<p> 1. Shut down [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,4,29],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4609"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4609"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4609\/revisions"}],"predecessor-version":[{"id":4610,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/4609\/revisions\/4610"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4609"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4609"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4609"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}