I forgot the password I entered during postgres installation












157















I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can't seem to be able to run it and I get the following error:



psql: FATAL:  password authentication failed for user "hisham"
hisham-agil: hisham$ psql


Is there anyway to reset the password or how do I create a new user with superuser privileges?



I am new to Postgres and just installed it for the first time. I am trying to use it with Rails and I am running Mac OS X Lion.










share|improve this question

























  • I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was 'postgres' to log in, then I could reset the password

    – Pascale
    Dec 10 '16 at 8:28
















157















I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can't seem to be able to run it and I get the following error:



psql: FATAL:  password authentication failed for user "hisham"
hisham-agil: hisham$ psql


Is there anyway to reset the password or how do I create a new user with superuser privileges?



I am new to Postgres and just installed it for the first time. I am trying to use it with Rails and I am running Mac OS X Lion.










share|improve this question

























  • I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was 'postgres' to log in, then I could reset the password

    – Pascale
    Dec 10 '16 at 8:28














157












157








157


59






I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can't seem to be able to run it and I get the following error:



psql: FATAL:  password authentication failed for user "hisham"
hisham-agil: hisham$ psql


Is there anyway to reset the password or how do I create a new user with superuser privileges?



I am new to Postgres and just installed it for the first time. I am trying to use it with Rails and I am running Mac OS X Lion.










share|improve this question
















I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can't seem to be able to run it and I get the following error:



psql: FATAL:  password authentication failed for user "hisham"
hisham-agil: hisham$ psql


Is there anyway to reset the password or how do I create a new user with superuser privileges?



I am new to Postgres and just installed it for the first time. I am trying to use it with Rails and I am running Mac OS X Lion.







postgresql postgresql-9.1 forgot-password






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Oct 24 '16 at 2:21









Ben

543717




543717










asked Jun 1 '12 at 7:14









hilarlhilarl

2,202103854




2,202103854













  • I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was 'postgres' to log in, then I could reset the password

    – Pascale
    Dec 10 '16 at 8:28



















  • I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was 'postgres' to log in, then I could reset the password

    – Pascale
    Dec 10 '16 at 8:28

















I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was 'postgres' to log in, then I could reset the password

– Pascale
Dec 10 '16 at 8:28





I cannot comment on answers yet, so have to do it this way. I did what SaiyanGirl said, however I still needed to enter a password, which was 'postgres' to log in, then I could reset the password

– Pascale
Dec 10 '16 at 8:28












11 Answers
11






active

oldest

votes


















304
















  1. find the file pg_hba.conf - it may be located, for example in /etc/postgresql-9.1/pg_hba.conf.



    cd /etc/postgresql-9.1/




  2. Back it up



    cp pg_hba.conf pg_hba.conf-backup



  3. place the following line (as either the first uncommented line, or as the only one):




For all occurrence of below (local and host) , exepct replication
section if you don't have any it has to be changed as follow ,no MD5
or Peer autehication should be present.




`local  all   all   trust`




  1. restart your PostgreSQL server (e.g., on Linux:)



    sudo /etc/init.d/postgresql restart



    If the service (daemon) doesn't start reporting in log file:




    local connections are not supported by this build




    you should change



    local all all trust



    to



    host all all 127.0.0.1/32 trust




  2. you can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)



    psql -U postgres



    or



    psql -h 127.0.0.1 -U postgres



    (note that with the first command you will not always be connected with local host)




  3. Reset password ('replace my_user_name with postgres since you are resetting postgres user)



    ALTER USER my_user_name with password 'my_secure_password';




  4. Restore the old pg_hba.conf as it is very dangerous to keep around



    cp pg_hba.conf-backup pg_hba.conf




  5. restart the server, in order to run with the safe pg_hba.conf



    sudo /etc/init.d/postgresql restart




Further Reading about that pg_hba file: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html






share|improve this answer





















  • 8





    And then start psql? I'm still getting prompted for a password, and I do not know it

    – CodyBugstein
    Aug 24 '14 at 23:01








  • 3





    this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

    – Scaramouche
    Sep 12 '16 at 1:16






  • 3





    anyone for Windows?

    – Mahesha999
    Nov 14 '16 at 8:02






  • 2





    Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

    – Tagar
    Mar 14 '17 at 5:46






  • 3





    If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done

    – sean.hudson
    Mar 28 '17 at 19:02



















75














When connecting to postgres from command line, don't forget to add -h localhost as command line parameter. If not, postgres will try to connect using PEER authentication mode.



The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.



# sudo -u postgres psql
could not change directory to "/root"
psql (9.1.11)
Type "help" for help.

postgres=# password
Enter new password:
Enter it again:
postgres=# q


Failing:



# psql -U postgres -W
Password for user postgres:
psql: FATAL: Peer authentication failed for user "postgres"


Working with -h localhost:



# psql -U postgres -W  -h localhost
Password for user postgres:
psql (9.1.11)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#





share|improve this answer





















  • 6





    Thanks for the tip regarding the "-h" option, helped me.

    – Bunkerbewohner
    Mar 3 '14 at 23:23











  • Unneeded on Windows cmd.exe

    – Fabien Haddadi
    Jul 12 '18 at 6:13



















56














The pg_hba.conf (C:Program FilesPostgreSQL9.3data) file has changed since these answers were given. What worked for me, in Windows, is to open the file and change the METHOD from md5 to trust:



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust


Then, using pgAdmin III, I logged in using no password and changed user postgres' password by going to File -> Change Password






share|improve this answer
























  • It worked on windows, thanks

    – zakaria amine
    Mar 21 '17 at 9:28











  • This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password...

    – gene b.
    Nov 15 '17 at 15:11











  • Once you change the method to trust you can follow this and alter password in cmd

    – Gilad Green
    Feb 17 '18 at 8:38



















6














Just a note, on Linux You can simply run sudo su - postgres to become the postgres user and from there change what required using psql.






share|improve this answer

































    1














    If you are in windows you can just run



    net user postgres postgres


    and login in postgres with postgres/postgres as user/password






    share|improve this answer































      1














      For Windows installation, a Windows user is created. And "psql" use this user for connection to the port. If you change the PostgreSQL user's password, it won't change the Windows one.
      The commandline juste below works only if you have access to commandline.



      Instead you could use Windows GUI application "c:Windowssystem32lusrmgr.exe". This app manage users created by Windows. So you can now modify the password.






      share|improve this answer































        1
















        1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:



          local   all             postgres                                md5


        2. Edit the line and change md5 at the end to trust and save the file



        3. Reload the postgresql service



          $ sudo service postgresql reload



        4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell



          $ psql -U postgres



        5. Update the postgres user's password



          alter user postgres with password 'secure-passwd-here';


        6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file



        7. Reload the postgresql service



          $ sudo service postgresql reload



        8. Verify that the password change is working



          $ psql -U postgres -W







        share|improve this answer
























        • Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows??

          – gene b.
          Nov 15 '17 at 15:09



















        1














        What I did to resolve the same problem was:



        Open pg_hba.conf file with gedit editor from the terminal:



        sudo gedit /etc/postgresql/9.5/main/pg_hba.conf


        It will ask for password. Enter your admin login password.
        This will open gedit with the file. Paste the following line:



        host  all   all  127.0.0.1/32  trust


        just below -



        # Database administrative login by Unix domain socket


        Save and close it.
        Close the terminal and open it again and run this command:



        psql -U postgres


        You will now enter the psql console.
        Now change the password by entering this:



        ALTER USER [your prefered user name] with password '[desired password]';


        If it says user does not exist then instead of ALTER use CREATE.



        Lastly, remove that certain line you pasted in pg_hba and save it.






        share|improve this answer































          1














          Adding the answer for Windows User for the latest postgres version (>10),



          Go to your postgres installation location, and search for pg_hba.conf,
          you will find it in ..postgresdatapg_hba.conf



          Open that file with notepad, find this line,



          # TYPE  DATABASE        USER            ADDRESS                 METHOD

          # IPv4 local connections:
          host all all 127.0.0.1/32 md5
          # IPv6 local connections:
          host all all ::1/128 md5
          #..


          Change the method from md5 to trust,



          # TYPE  DATABASE        USER            ADDRESS                 METHOD

          # IPv4 local connections:
          host all all 127.0.0.1/32 trust
          # IPv6 local connections:
          host all all ::1/128 trust
          # ...


          Now go to your SQL Shell(PSQL) and leave everything blank,



          Server [localhost]:
          Database [postgres]:
          Port [8000]:
          Username [postgres]:


          It will not ask for password this time, and you will be logged in,



          Now run this line,
          ALTER USER yourusername WITH SUPERUSER



          Now you can leave the shell with q



          Again go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it.



          Now login with your new user and password and you can check du for its attributes.






          share|improve this answer































            0














            The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).



            This file should contain lines of the following format:



            hostname:port:database:username:password



            (You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or , escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.



            On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.






            share|improve this answer































              -1














              This is what worked for me on windows:



              Edit the pg_hba.conf file locates at C:Program FilesPostgreSQL9.3data.



              # IPv4 local connections:
              host all all 127.0.0.1/32 trust



              Change the method from trust to md5 and restart the postgres service on windows.



              After that, you can login using postgres user without password by using pgadmin. You can change password using File->Change password.



              If postgres user does not have superuser privileges , then you cannot change the password. In this case , login with another user(pgsql)with superuser access and provide privileges to other users by right clicking on users and selecting properties->Role privileges.






              share|improve this answer























                Your Answer






                StackExchange.ifUsing("editor", function () {
                StackExchange.using("externalEditor", function () {
                StackExchange.using("snippets", function () {
                StackExchange.snippets.init();
                });
                });
                }, "code-snippets");

                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "1"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: true,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: 10,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10845998%2fi-forgot-the-password-i-entered-during-postgres-installation%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                11 Answers
                11






                active

                oldest

                votes








                11 Answers
                11






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                304
















                1. find the file pg_hba.conf - it may be located, for example in /etc/postgresql-9.1/pg_hba.conf.



                  cd /etc/postgresql-9.1/




                2. Back it up



                  cp pg_hba.conf pg_hba.conf-backup



                3. place the following line (as either the first uncommented line, or as the only one):




                For all occurrence of below (local and host) , exepct replication
                section if you don't have any it has to be changed as follow ,no MD5
                or Peer autehication should be present.




                `local  all   all   trust`




                1. restart your PostgreSQL server (e.g., on Linux:)



                  sudo /etc/init.d/postgresql restart



                  If the service (daemon) doesn't start reporting in log file:




                  local connections are not supported by this build




                  you should change



                  local all all trust



                  to



                  host all all 127.0.0.1/32 trust




                2. you can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)



                  psql -U postgres



                  or



                  psql -h 127.0.0.1 -U postgres



                  (note that with the first command you will not always be connected with local host)




                3. Reset password ('replace my_user_name with postgres since you are resetting postgres user)



                  ALTER USER my_user_name with password 'my_secure_password';




                4. Restore the old pg_hba.conf as it is very dangerous to keep around



                  cp pg_hba.conf-backup pg_hba.conf




                5. restart the server, in order to run with the safe pg_hba.conf



                  sudo /etc/init.d/postgresql restart




                Further Reading about that pg_hba file: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html






                share|improve this answer





















                • 8





                  And then start psql? I'm still getting prompted for a password, and I do not know it

                  – CodyBugstein
                  Aug 24 '14 at 23:01








                • 3





                  this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

                  – Scaramouche
                  Sep 12 '16 at 1:16






                • 3





                  anyone for Windows?

                  – Mahesha999
                  Nov 14 '16 at 8:02






                • 2





                  Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

                  – Tagar
                  Mar 14 '17 at 5:46






                • 3





                  If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done

                  – sean.hudson
                  Mar 28 '17 at 19:02
















                304
















                1. find the file pg_hba.conf - it may be located, for example in /etc/postgresql-9.1/pg_hba.conf.



                  cd /etc/postgresql-9.1/




                2. Back it up



                  cp pg_hba.conf pg_hba.conf-backup



                3. place the following line (as either the first uncommented line, or as the only one):




                For all occurrence of below (local and host) , exepct replication
                section if you don't have any it has to be changed as follow ,no MD5
                or Peer autehication should be present.




                `local  all   all   trust`




                1. restart your PostgreSQL server (e.g., on Linux:)



                  sudo /etc/init.d/postgresql restart



                  If the service (daemon) doesn't start reporting in log file:




                  local connections are not supported by this build




                  you should change



                  local all all trust



                  to



                  host all all 127.0.0.1/32 trust




                2. you can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)



                  psql -U postgres



                  or



                  psql -h 127.0.0.1 -U postgres



                  (note that with the first command you will not always be connected with local host)




                3. Reset password ('replace my_user_name with postgres since you are resetting postgres user)



                  ALTER USER my_user_name with password 'my_secure_password';




                4. Restore the old pg_hba.conf as it is very dangerous to keep around



                  cp pg_hba.conf-backup pg_hba.conf




                5. restart the server, in order to run with the safe pg_hba.conf



                  sudo /etc/init.d/postgresql restart




                Further Reading about that pg_hba file: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html






                share|improve this answer





















                • 8





                  And then start psql? I'm still getting prompted for a password, and I do not know it

                  – CodyBugstein
                  Aug 24 '14 at 23:01








                • 3





                  this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

                  – Scaramouche
                  Sep 12 '16 at 1:16






                • 3





                  anyone for Windows?

                  – Mahesha999
                  Nov 14 '16 at 8:02






                • 2





                  Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

                  – Tagar
                  Mar 14 '17 at 5:46






                • 3





                  If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done

                  – sean.hudson
                  Mar 28 '17 at 19:02














                304












                304








                304









                1. find the file pg_hba.conf - it may be located, for example in /etc/postgresql-9.1/pg_hba.conf.



                  cd /etc/postgresql-9.1/




                2. Back it up



                  cp pg_hba.conf pg_hba.conf-backup



                3. place the following line (as either the first uncommented line, or as the only one):




                For all occurrence of below (local and host) , exepct replication
                section if you don't have any it has to be changed as follow ,no MD5
                or Peer autehication should be present.




                `local  all   all   trust`




                1. restart your PostgreSQL server (e.g., on Linux:)



                  sudo /etc/init.d/postgresql restart



                  If the service (daemon) doesn't start reporting in log file:




                  local connections are not supported by this build




                  you should change



                  local all all trust



                  to



                  host all all 127.0.0.1/32 trust




                2. you can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)



                  psql -U postgres



                  or



                  psql -h 127.0.0.1 -U postgres



                  (note that with the first command you will not always be connected with local host)




                3. Reset password ('replace my_user_name with postgres since you are resetting postgres user)



                  ALTER USER my_user_name with password 'my_secure_password';




                4. Restore the old pg_hba.conf as it is very dangerous to keep around



                  cp pg_hba.conf-backup pg_hba.conf




                5. restart the server, in order to run with the safe pg_hba.conf



                  sudo /etc/init.d/postgresql restart




                Further Reading about that pg_hba file: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html






                share|improve this answer

















                1. find the file pg_hba.conf - it may be located, for example in /etc/postgresql-9.1/pg_hba.conf.



                  cd /etc/postgresql-9.1/




                2. Back it up



                  cp pg_hba.conf pg_hba.conf-backup



                3. place the following line (as either the first uncommented line, or as the only one):




                For all occurrence of below (local and host) , exepct replication
                section if you don't have any it has to be changed as follow ,no MD5
                or Peer autehication should be present.




                `local  all   all   trust`




                1. restart your PostgreSQL server (e.g., on Linux:)



                  sudo /etc/init.d/postgresql restart



                  If the service (daemon) doesn't start reporting in log file:




                  local connections are not supported by this build




                  you should change



                  local all all trust



                  to



                  host all all 127.0.0.1/32 trust




                2. you can now connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)



                  psql -U postgres



                  or



                  psql -h 127.0.0.1 -U postgres



                  (note that with the first command you will not always be connected with local host)




                3. Reset password ('replace my_user_name with postgres since you are resetting postgres user)



                  ALTER USER my_user_name with password 'my_secure_password';




                4. Restore the old pg_hba.conf as it is very dangerous to keep around



                  cp pg_hba.conf-backup pg_hba.conf




                5. restart the server, in order to run with the safe pg_hba.conf



                  sudo /etc/init.d/postgresql restart




                Further Reading about that pg_hba file: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Mar 12 at 8:55









                danielad

                4,72354163




                4,72354163










                answered Jun 1 '12 at 7:42









                Arsen7Arsen7

                10.3k23557




                10.3k23557








                • 8





                  And then start psql? I'm still getting prompted for a password, and I do not know it

                  – CodyBugstein
                  Aug 24 '14 at 23:01








                • 3





                  this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

                  – Scaramouche
                  Sep 12 '16 at 1:16






                • 3





                  anyone for Windows?

                  – Mahesha999
                  Nov 14 '16 at 8:02






                • 2





                  Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

                  – Tagar
                  Mar 14 '17 at 5:46






                • 3





                  If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done

                  – sean.hudson
                  Mar 28 '17 at 19:02














                • 8





                  And then start psql? I'm still getting prompted for a password, and I do not know it

                  – CodyBugstein
                  Aug 24 '14 at 23:01








                • 3





                  this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

                  – Scaramouche
                  Sep 12 '16 at 1:16






                • 3





                  anyone for Windows?

                  – Mahesha999
                  Nov 14 '16 at 8:02






                • 2





                  Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

                  – Tagar
                  Mar 14 '17 at 5:46






                • 3





                  If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done

                  – sean.hudson
                  Mar 28 '17 at 19:02








                8




                8





                And then start psql? I'm still getting prompted for a password, and I do not know it

                – CodyBugstein
                Aug 24 '14 at 23:01







                And then start psql? I'm still getting prompted for a password, and I do not know it

                – CodyBugstein
                Aug 24 '14 at 23:01






                3




                3





                this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

                – Scaramouche
                Sep 12 '16 at 1:16





                this answer did NOT work for me, after I do step 3, the service won´t start, I don´t know, maybe it´s the OS(W8), it just won´t.

                – Scaramouche
                Sep 12 '16 at 1:16




                3




                3





                anyone for Windows?

                – Mahesha999
                Nov 14 '16 at 8:02





                anyone for Windows?

                – Mahesha999
                Nov 14 '16 at 8:02




                2




                2





                Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

                – Tagar
                Mar 14 '17 at 5:46





                Keep in mind order of entries in pg_hba.conf is important. If you add "local all all trust" to the end of the file, it'll not work as you expect, as previous records will be matched first. So put that at the top of the file to have what you expect.

                – Tagar
                Mar 14 '17 at 5:46




                3




                3





                If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done

                – sean.hudson
                Mar 28 '17 at 19:02





                If you're on windows with this error, keep scrolling to @SaiyanGirl 's answer. Just modfiy the METHOD columns of the existing entries to 'trust', then change it back when you're done

                – sean.hudson
                Mar 28 '17 at 19:02













                75














                When connecting to postgres from command line, don't forget to add -h localhost as command line parameter. If not, postgres will try to connect using PEER authentication mode.



                The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.



                # sudo -u postgres psql
                could not change directory to "/root"
                psql (9.1.11)
                Type "help" for help.

                postgres=# password
                Enter new password:
                Enter it again:
                postgres=# q


                Failing:



                # psql -U postgres -W
                Password for user postgres:
                psql: FATAL: Peer authentication failed for user "postgres"


                Working with -h localhost:



                # psql -U postgres -W  -h localhost
                Password for user postgres:
                psql (9.1.11)
                SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
                Type "help" for help.

                postgres=#





                share|improve this answer





















                • 6





                  Thanks for the tip regarding the "-h" option, helped me.

                  – Bunkerbewohner
                  Mar 3 '14 at 23:23











                • Unneeded on Windows cmd.exe

                  – Fabien Haddadi
                  Jul 12 '18 at 6:13
















                75














                When connecting to postgres from command line, don't forget to add -h localhost as command line parameter. If not, postgres will try to connect using PEER authentication mode.



                The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.



                # sudo -u postgres psql
                could not change directory to "/root"
                psql (9.1.11)
                Type "help" for help.

                postgres=# password
                Enter new password:
                Enter it again:
                postgres=# q


                Failing:



                # psql -U postgres -W
                Password for user postgres:
                psql: FATAL: Peer authentication failed for user "postgres"


                Working with -h localhost:



                # psql -U postgres -W  -h localhost
                Password for user postgres:
                psql (9.1.11)
                SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
                Type "help" for help.

                postgres=#





                share|improve this answer





















                • 6





                  Thanks for the tip regarding the "-h" option, helped me.

                  – Bunkerbewohner
                  Mar 3 '14 at 23:23











                • Unneeded on Windows cmd.exe

                  – Fabien Haddadi
                  Jul 12 '18 at 6:13














                75












                75








                75







                When connecting to postgres from command line, don't forget to add -h localhost as command line parameter. If not, postgres will try to connect using PEER authentication mode.



                The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.



                # sudo -u postgres psql
                could not change directory to "/root"
                psql (9.1.11)
                Type "help" for help.

                postgres=# password
                Enter new password:
                Enter it again:
                postgres=# q


                Failing:



                # psql -U postgres -W
                Password for user postgres:
                psql: FATAL: Peer authentication failed for user "postgres"


                Working with -h localhost:



                # psql -U postgres -W  -h localhost
                Password for user postgres:
                psql (9.1.11)
                SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
                Type "help" for help.

                postgres=#





                share|improve this answer















                When connecting to postgres from command line, don't forget to add -h localhost as command line parameter. If not, postgres will try to connect using PEER authentication mode.



                The below shows a reset of the password, a failed login with PEER authentication and a successful login using a TCP connection.



                # sudo -u postgres psql
                could not change directory to "/root"
                psql (9.1.11)
                Type "help" for help.

                postgres=# password
                Enter new password:
                Enter it again:
                postgres=# q


                Failing:



                # psql -U postgres -W
                Password for user postgres:
                psql: FATAL: Peer authentication failed for user "postgres"


                Working with -h localhost:



                # psql -U postgres -W  -h localhost
                Password for user postgres:
                psql (9.1.11)
                SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
                Type "help" for help.

                postgres=#






                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Aug 23 '16 at 17:35

























                answered Feb 2 '14 at 10:21









                SaeXSaeX

                7,85054969




                7,85054969








                • 6





                  Thanks for the tip regarding the "-h" option, helped me.

                  – Bunkerbewohner
                  Mar 3 '14 at 23:23











                • Unneeded on Windows cmd.exe

                  – Fabien Haddadi
                  Jul 12 '18 at 6:13














                • 6





                  Thanks for the tip regarding the "-h" option, helped me.

                  – Bunkerbewohner
                  Mar 3 '14 at 23:23











                • Unneeded on Windows cmd.exe

                  – Fabien Haddadi
                  Jul 12 '18 at 6:13








                6




                6





                Thanks for the tip regarding the "-h" option, helped me.

                – Bunkerbewohner
                Mar 3 '14 at 23:23





                Thanks for the tip regarding the "-h" option, helped me.

                – Bunkerbewohner
                Mar 3 '14 at 23:23













                Unneeded on Windows cmd.exe

                – Fabien Haddadi
                Jul 12 '18 at 6:13





                Unneeded on Windows cmd.exe

                – Fabien Haddadi
                Jul 12 '18 at 6:13











                56














                The pg_hba.conf (C:Program FilesPostgreSQL9.3data) file has changed since these answers were given. What worked for me, in Windows, is to open the file and change the METHOD from md5 to trust:



                # TYPE  DATABASE        USER            ADDRESS                 METHOD

                # IPv4 local connections:
                host all all 127.0.0.1/32 trust
                # IPv6 local connections:
                host all all ::1/128 trust


                Then, using pgAdmin III, I logged in using no password and changed user postgres' password by going to File -> Change Password






                share|improve this answer
























                • It worked on windows, thanks

                  – zakaria amine
                  Mar 21 '17 at 9:28











                • This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password...

                  – gene b.
                  Nov 15 '17 at 15:11











                • Once you change the method to trust you can follow this and alter password in cmd

                  – Gilad Green
                  Feb 17 '18 at 8:38
















                56














                The pg_hba.conf (C:Program FilesPostgreSQL9.3data) file has changed since these answers were given. What worked for me, in Windows, is to open the file and change the METHOD from md5 to trust:



                # TYPE  DATABASE        USER            ADDRESS                 METHOD

                # IPv4 local connections:
                host all all 127.0.0.1/32 trust
                # IPv6 local connections:
                host all all ::1/128 trust


                Then, using pgAdmin III, I logged in using no password and changed user postgres' password by going to File -> Change Password






                share|improve this answer
























                • It worked on windows, thanks

                  – zakaria amine
                  Mar 21 '17 at 9:28











                • This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password...

                  – gene b.
                  Nov 15 '17 at 15:11











                • Once you change the method to trust you can follow this and alter password in cmd

                  – Gilad Green
                  Feb 17 '18 at 8:38














                56












                56








                56







                The pg_hba.conf (C:Program FilesPostgreSQL9.3data) file has changed since these answers were given. What worked for me, in Windows, is to open the file and change the METHOD from md5 to trust:



                # TYPE  DATABASE        USER            ADDRESS                 METHOD

                # IPv4 local connections:
                host all all 127.0.0.1/32 trust
                # IPv6 local connections:
                host all all ::1/128 trust


                Then, using pgAdmin III, I logged in using no password and changed user postgres' password by going to File -> Change Password






                share|improve this answer













                The pg_hba.conf (C:Program FilesPostgreSQL9.3data) file has changed since these answers were given. What worked for me, in Windows, is to open the file and change the METHOD from md5 to trust:



                # TYPE  DATABASE        USER            ADDRESS                 METHOD

                # IPv4 local connections:
                host all all 127.0.0.1/32 trust
                # IPv6 local connections:
                host all all ::1/128 trust


                Then, using pgAdmin III, I logged in using no password and changed user postgres' password by going to File -> Change Password







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Sep 19 '14 at 22:26









                SaiyanGirlSaiyanGirl

                9,25283049




                9,25283049













                • It worked on windows, thanks

                  – zakaria amine
                  Mar 21 '17 at 9:28











                • This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password...

                  – gene b.
                  Nov 15 '17 at 15:11











                • Once you change the method to trust you can follow this and alter password in cmd

                  – Gilad Green
                  Feb 17 '18 at 8:38



















                • It worked on windows, thanks

                  – zakaria amine
                  Mar 21 '17 at 9:28











                • This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password...

                  – gene b.
                  Nov 15 '17 at 15:11











                • Once you change the method to trust you can follow this and alter password in cmd

                  – Gilad Green
                  Feb 17 '18 at 8:38

















                It worked on windows, thanks

                – zakaria amine
                Mar 21 '17 at 9:28





                It worked on windows, thanks

                – zakaria amine
                Mar 21 '17 at 9:28













                This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password...

                – gene b.
                Nov 15 '17 at 15:11





                This doesn't work because PgAdmin still asks for the current password... I reset to 'trust' and restarted PgAdmin. Still can't reset without typing in the CURRENT password...

                – gene b.
                Nov 15 '17 at 15:11













                Once you change the method to trust you can follow this and alter password in cmd

                – Gilad Green
                Feb 17 '18 at 8:38





                Once you change the method to trust you can follow this and alter password in cmd

                – Gilad Green
                Feb 17 '18 at 8:38











                6














                Just a note, on Linux You can simply run sudo su - postgres to become the postgres user and from there change what required using psql.






                share|improve this answer






























                  6














                  Just a note, on Linux You can simply run sudo su - postgres to become the postgres user and from there change what required using psql.






                  share|improve this answer




























                    6












                    6








                    6







                    Just a note, on Linux You can simply run sudo su - postgres to become the postgres user and from there change what required using psql.






                    share|improve this answer















                    Just a note, on Linux You can simply run sudo su - postgres to become the postgres user and from there change what required using psql.







                    share|improve this answer














                    share|improve this answer



                    share|improve this answer








                    edited Mar 12 '18 at 16:43

























                    answered Mar 12 '18 at 12:58









                    DanielDaniel

                    7113




                    7113























                        1














                        If you are in windows you can just run



                        net user postgres postgres


                        and login in postgres with postgres/postgres as user/password






                        share|improve this answer




























                          1














                          If you are in windows you can just run



                          net user postgres postgres


                          and login in postgres with postgres/postgres as user/password






                          share|improve this answer


























                            1












                            1








                            1







                            If you are in windows you can just run



                            net user postgres postgres


                            and login in postgres with postgres/postgres as user/password






                            share|improve this answer













                            If you are in windows you can just run



                            net user postgres postgres


                            and login in postgres with postgres/postgres as user/password







                            share|improve this answer












                            share|improve this answer



                            share|improve this answer










                            answered Jun 7 '16 at 16:36









                            ivofernandesivofernandes

                            314




                            314























                                1














                                For Windows installation, a Windows user is created. And "psql" use this user for connection to the port. If you change the PostgreSQL user's password, it won't change the Windows one.
                                The commandline juste below works only if you have access to commandline.



                                Instead you could use Windows GUI application "c:Windowssystem32lusrmgr.exe". This app manage users created by Windows. So you can now modify the password.






                                share|improve this answer




























                                  1














                                  For Windows installation, a Windows user is created. And "psql" use this user for connection to the port. If you change the PostgreSQL user's password, it won't change the Windows one.
                                  The commandline juste below works only if you have access to commandline.



                                  Instead you could use Windows GUI application "c:Windowssystem32lusrmgr.exe". This app manage users created by Windows. So you can now modify the password.






                                  share|improve this answer


























                                    1












                                    1








                                    1







                                    For Windows installation, a Windows user is created. And "psql" use this user for connection to the port. If you change the PostgreSQL user's password, it won't change the Windows one.
                                    The commandline juste below works only if you have access to commandline.



                                    Instead you could use Windows GUI application "c:Windowssystem32lusrmgr.exe". This app manage users created by Windows. So you can now modify the password.






                                    share|improve this answer













                                    For Windows installation, a Windows user is created. And "psql" use this user for connection to the port. If you change the PostgreSQL user's password, it won't change the Windows one.
                                    The commandline juste below works only if you have access to commandline.



                                    Instead you could use Windows GUI application "c:Windowssystem32lusrmgr.exe". This app manage users created by Windows. So you can now modify the password.







                                    share|improve this answer












                                    share|improve this answer



                                    share|improve this answer










                                    answered Mar 21 '17 at 15:31









                                    cpunkycpunky

                                    111




                                    111























                                        1
















                                        1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:



                                          local   all             postgres                                md5


                                        2. Edit the line and change md5 at the end to trust and save the file



                                        3. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell



                                          $ psql -U postgres



                                        5. Update the postgres user's password



                                          alter user postgres with password 'secure-passwd-here';


                                        6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file



                                        7. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        8. Verify that the password change is working



                                          $ psql -U postgres -W







                                        share|improve this answer
























                                        • Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows??

                                          – gene b.
                                          Nov 15 '17 at 15:09
















                                        1
















                                        1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:



                                          local   all             postgres                                md5


                                        2. Edit the line and change md5 at the end to trust and save the file



                                        3. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell



                                          $ psql -U postgres



                                        5. Update the postgres user's password



                                          alter user postgres with password 'secure-passwd-here';


                                        6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file



                                        7. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        8. Verify that the password change is working



                                          $ psql -U postgres -W







                                        share|improve this answer
























                                        • Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows??

                                          – gene b.
                                          Nov 15 '17 at 15:09














                                        1












                                        1








                                        1









                                        1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:



                                          local   all             postgres                                md5


                                        2. Edit the line and change md5 at the end to trust and save the file



                                        3. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell



                                          $ psql -U postgres



                                        5. Update the postgres user's password



                                          alter user postgres with password 'secure-passwd-here';


                                        6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file



                                        7. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        8. Verify that the password change is working



                                          $ psql -U postgres -W







                                        share|improve this answer















                                        1. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and find the following line:



                                          local   all             postgres                                md5


                                        2. Edit the line and change md5 at the end to trust and save the file



                                        3. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        4. This will load the configuration files. Now you can modify the postgres user by logging into the psql shell



                                          $ psql -U postgres



                                        5. Update the postgres user's password



                                          alter user postgres with password 'secure-passwd-here';


                                        6. Edit the file /etc/postgresql/<version>/main/pg_hba.conf and change trust back to md5 and save the file



                                        7. Reload the postgresql service



                                          $ sudo service postgresql reload



                                        8. Verify that the password change is working



                                          $ psql -U postgres -W








                                        share|improve this answer












                                        share|improve this answer



                                        share|improve this answer










                                        answered Aug 17 '17 at 2:55









                                        Ray HunterRay Hunter

                                        10.6k23845




                                        10.6k23845













                                        • Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows??

                                          – gene b.
                                          Nov 15 '17 at 15:09



















                                        • Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows??

                                          – gene b.
                                          Nov 15 '17 at 15:09

















                                        Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows??

                                        – gene b.
                                        Nov 15 '17 at 15:09





                                        Is it so hard to give a Windows solution? Not everyone uses Linux with sudo. How would I do it on Windows??

                                        – gene b.
                                        Nov 15 '17 at 15:09











                                        1














                                        What I did to resolve the same problem was:



                                        Open pg_hba.conf file with gedit editor from the terminal:



                                        sudo gedit /etc/postgresql/9.5/main/pg_hba.conf


                                        It will ask for password. Enter your admin login password.
                                        This will open gedit with the file. Paste the following line:



                                        host  all   all  127.0.0.1/32  trust


                                        just below -



                                        # Database administrative login by Unix domain socket


                                        Save and close it.
                                        Close the terminal and open it again and run this command:



                                        psql -U postgres


                                        You will now enter the psql console.
                                        Now change the password by entering this:



                                        ALTER USER [your prefered user name] with password '[desired password]';


                                        If it says user does not exist then instead of ALTER use CREATE.



                                        Lastly, remove that certain line you pasted in pg_hba and save it.






                                        share|improve this answer




























                                          1














                                          What I did to resolve the same problem was:



                                          Open pg_hba.conf file with gedit editor from the terminal:



                                          sudo gedit /etc/postgresql/9.5/main/pg_hba.conf


                                          It will ask for password. Enter your admin login password.
                                          This will open gedit with the file. Paste the following line:



                                          host  all   all  127.0.0.1/32  trust


                                          just below -



                                          # Database administrative login by Unix domain socket


                                          Save and close it.
                                          Close the terminal and open it again and run this command:



                                          psql -U postgres


                                          You will now enter the psql console.
                                          Now change the password by entering this:



                                          ALTER USER [your prefered user name] with password '[desired password]';


                                          If it says user does not exist then instead of ALTER use CREATE.



                                          Lastly, remove that certain line you pasted in pg_hba and save it.






                                          share|improve this answer


























                                            1












                                            1








                                            1







                                            What I did to resolve the same problem was:



                                            Open pg_hba.conf file with gedit editor from the terminal:



                                            sudo gedit /etc/postgresql/9.5/main/pg_hba.conf


                                            It will ask for password. Enter your admin login password.
                                            This will open gedit with the file. Paste the following line:



                                            host  all   all  127.0.0.1/32  trust


                                            just below -



                                            # Database administrative login by Unix domain socket


                                            Save and close it.
                                            Close the terminal and open it again and run this command:



                                            psql -U postgres


                                            You will now enter the psql console.
                                            Now change the password by entering this:



                                            ALTER USER [your prefered user name] with password '[desired password]';


                                            If it says user does not exist then instead of ALTER use CREATE.



                                            Lastly, remove that certain line you pasted in pg_hba and save it.






                                            share|improve this answer













                                            What I did to resolve the same problem was:



                                            Open pg_hba.conf file with gedit editor from the terminal:



                                            sudo gedit /etc/postgresql/9.5/main/pg_hba.conf


                                            It will ask for password. Enter your admin login password.
                                            This will open gedit with the file. Paste the following line:



                                            host  all   all  127.0.0.1/32  trust


                                            just below -



                                            # Database administrative login by Unix domain socket


                                            Save and close it.
                                            Close the terminal and open it again and run this command:



                                            psql -U postgres


                                            You will now enter the psql console.
                                            Now change the password by entering this:



                                            ALTER USER [your prefered user name] with password '[desired password]';


                                            If it says user does not exist then instead of ALTER use CREATE.



                                            Lastly, remove that certain line you pasted in pg_hba and save it.







                                            share|improve this answer












                                            share|improve this answer



                                            share|improve this answer










                                            answered Nov 14 '17 at 12:08









                                            InconnuInconnu

                                            3,74322134




                                            3,74322134























                                                1














                                                Adding the answer for Windows User for the latest postgres version (>10),



                                                Go to your postgres installation location, and search for pg_hba.conf,
                                                you will find it in ..postgresdatapg_hba.conf



                                                Open that file with notepad, find this line,



                                                # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                # IPv4 local connections:
                                                host all all 127.0.0.1/32 md5
                                                # IPv6 local connections:
                                                host all all ::1/128 md5
                                                #..


                                                Change the method from md5 to trust,



                                                # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                # IPv4 local connections:
                                                host all all 127.0.0.1/32 trust
                                                # IPv6 local connections:
                                                host all all ::1/128 trust
                                                # ...


                                                Now go to your SQL Shell(PSQL) and leave everything blank,



                                                Server [localhost]:
                                                Database [postgres]:
                                                Port [8000]:
                                                Username [postgres]:


                                                It will not ask for password this time, and you will be logged in,



                                                Now run this line,
                                                ALTER USER yourusername WITH SUPERUSER



                                                Now you can leave the shell with q



                                                Again go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it.



                                                Now login with your new user and password and you can check du for its attributes.






                                                share|improve this answer




























                                                  1














                                                  Adding the answer for Windows User for the latest postgres version (>10),



                                                  Go to your postgres installation location, and search for pg_hba.conf,
                                                  you will find it in ..postgresdatapg_hba.conf



                                                  Open that file with notepad, find this line,



                                                  # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                  # IPv4 local connections:
                                                  host all all 127.0.0.1/32 md5
                                                  # IPv6 local connections:
                                                  host all all ::1/128 md5
                                                  #..


                                                  Change the method from md5 to trust,



                                                  # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                  # IPv4 local connections:
                                                  host all all 127.0.0.1/32 trust
                                                  # IPv6 local connections:
                                                  host all all ::1/128 trust
                                                  # ...


                                                  Now go to your SQL Shell(PSQL) and leave everything blank,



                                                  Server [localhost]:
                                                  Database [postgres]:
                                                  Port [8000]:
                                                  Username [postgres]:


                                                  It will not ask for password this time, and you will be logged in,



                                                  Now run this line,
                                                  ALTER USER yourusername WITH SUPERUSER



                                                  Now you can leave the shell with q



                                                  Again go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it.



                                                  Now login with your new user and password and you can check du for its attributes.






                                                  share|improve this answer


























                                                    1












                                                    1








                                                    1







                                                    Adding the answer for Windows User for the latest postgres version (>10),



                                                    Go to your postgres installation location, and search for pg_hba.conf,
                                                    you will find it in ..postgresdatapg_hba.conf



                                                    Open that file with notepad, find this line,



                                                    # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                    # IPv4 local connections:
                                                    host all all 127.0.0.1/32 md5
                                                    # IPv6 local connections:
                                                    host all all ::1/128 md5
                                                    #..


                                                    Change the method from md5 to trust,



                                                    # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                    # IPv4 local connections:
                                                    host all all 127.0.0.1/32 trust
                                                    # IPv6 local connections:
                                                    host all all ::1/128 trust
                                                    # ...


                                                    Now go to your SQL Shell(PSQL) and leave everything blank,



                                                    Server [localhost]:
                                                    Database [postgres]:
                                                    Port [8000]:
                                                    Username [postgres]:


                                                    It will not ask for password this time, and you will be logged in,



                                                    Now run this line,
                                                    ALTER USER yourusername WITH SUPERUSER



                                                    Now you can leave the shell with q



                                                    Again go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it.



                                                    Now login with your new user and password and you can check du for its attributes.






                                                    share|improve this answer













                                                    Adding the answer for Windows User for the latest postgres version (>10),



                                                    Go to your postgres installation location, and search for pg_hba.conf,
                                                    you will find it in ..postgresdatapg_hba.conf



                                                    Open that file with notepad, find this line,



                                                    # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                    # IPv4 local connections:
                                                    host all all 127.0.0.1/32 md5
                                                    # IPv6 local connections:
                                                    host all all ::1/128 md5
                                                    #..


                                                    Change the method from md5 to trust,



                                                    # TYPE  DATABASE        USER            ADDRESS                 METHOD

                                                    # IPv4 local connections:
                                                    host all all 127.0.0.1/32 trust
                                                    # IPv6 local connections:
                                                    host all all ::1/128 trust
                                                    # ...


                                                    Now go to your SQL Shell(PSQL) and leave everything blank,



                                                    Server [localhost]:
                                                    Database [postgres]:
                                                    Port [8000]:
                                                    Username [postgres]:


                                                    It will not ask for password this time, and you will be logged in,



                                                    Now run this line,
                                                    ALTER USER yourusername WITH SUPERUSER



                                                    Now you can leave the shell with q



                                                    Again go to the file pg_hba.conf and change METHOD from trust to md5 again, and save it.



                                                    Now login with your new user and password and you can check du for its attributes.







                                                    share|improve this answer












                                                    share|improve this answer



                                                    share|improve this answer










                                                    answered Feb 3 at 13:40









                                                    Bidhan MajhiBidhan Majhi

                                                    7011416




                                                    7011416























                                                        0














                                                        The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).



                                                        This file should contain lines of the following format:



                                                        hostname:port:database:username:password



                                                        (You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or , escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.



                                                        On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.






                                                        share|improve this answer




























                                                          0














                                                          The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).



                                                          This file should contain lines of the following format:



                                                          hostname:port:database:username:password



                                                          (You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or , escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.



                                                          On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.






                                                          share|improve this answer


























                                                            0












                                                            0








                                                            0







                                                            The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).



                                                            This file should contain lines of the following format:



                                                            hostname:port:database:username:password



                                                            (You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or , escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.



                                                            On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.






                                                            share|improve this answer













                                                            The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%postgresqlpgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).



                                                            This file should contain lines of the following format:



                                                            hostname:port:database:username:password



                                                            (You can add a reminder comment to the file by copying the line above and preceding it with #.) Each of the first four fields can be a literal value, or *, which matches anything. The password field from the first line that matches the current connection parameters will be used. (Therefore, put more-specific entries first when you are using wildcards.) If an entry needs to contain : or , escape this character with . A host name of localhost matches both TCP (host name localhost) and Unix domain socket (pghost empty or the default socket directory) connections coming from the local machine. In a standby server, a database name of replication matches streaming replication connections made to the master server. The database field is of limited usefulness because users have the same password for all databases in the same cluster.



                                                            On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.







                                                            share|improve this answer












                                                            share|improve this answer



                                                            share|improve this answer










                                                            answered Sep 20 '17 at 8:58









                                                            rahulnikharerahulnikhare

                                                            34648




                                                            34648























                                                                -1














                                                                This is what worked for me on windows:



                                                                Edit the pg_hba.conf file locates at C:Program FilesPostgreSQL9.3data.



                                                                # IPv4 local connections:
                                                                host all all 127.0.0.1/32 trust



                                                                Change the method from trust to md5 and restart the postgres service on windows.



                                                                After that, you can login using postgres user without password by using pgadmin. You can change password using File->Change password.



                                                                If postgres user does not have superuser privileges , then you cannot change the password. In this case , login with another user(pgsql)with superuser access and provide privileges to other users by right clicking on users and selecting properties->Role privileges.






                                                                share|improve this answer




























                                                                  -1














                                                                  This is what worked for me on windows:



                                                                  Edit the pg_hba.conf file locates at C:Program FilesPostgreSQL9.3data.



                                                                  # IPv4 local connections:
                                                                  host all all 127.0.0.1/32 trust



                                                                  Change the method from trust to md5 and restart the postgres service on windows.



                                                                  After that, you can login using postgres user without password by using pgadmin. You can change password using File->Change password.



                                                                  If postgres user does not have superuser privileges , then you cannot change the password. In this case , login with another user(pgsql)with superuser access and provide privileges to other users by right clicking on users and selecting properties->Role privileges.






                                                                  share|improve this answer


























                                                                    -1












                                                                    -1








                                                                    -1







                                                                    This is what worked for me on windows:



                                                                    Edit the pg_hba.conf file locates at C:Program FilesPostgreSQL9.3data.



                                                                    # IPv4 local connections:
                                                                    host all all 127.0.0.1/32 trust



                                                                    Change the method from trust to md5 and restart the postgres service on windows.



                                                                    After that, you can login using postgres user without password by using pgadmin. You can change password using File->Change password.



                                                                    If postgres user does not have superuser privileges , then you cannot change the password. In this case , login with another user(pgsql)with superuser access and provide privileges to other users by right clicking on users and selecting properties->Role privileges.






                                                                    share|improve this answer













                                                                    This is what worked for me on windows:



                                                                    Edit the pg_hba.conf file locates at C:Program FilesPostgreSQL9.3data.



                                                                    # IPv4 local connections:
                                                                    host all all 127.0.0.1/32 trust



                                                                    Change the method from trust to md5 and restart the postgres service on windows.



                                                                    After that, you can login using postgres user without password by using pgadmin. You can change password using File->Change password.



                                                                    If postgres user does not have superuser privileges , then you cannot change the password. In this case , login with another user(pgsql)with superuser access and provide privileges to other users by right clicking on users and selecting properties->Role privileges.







                                                                    share|improve this answer












                                                                    share|improve this answer



                                                                    share|improve this answer










                                                                    answered Nov 21 '18 at 8:23









                                                                    ChetanChetan

                                                                    1




                                                                    1






























                                                                        draft saved

                                                                        draft discarded




















































                                                                        Thanks for contributing an answer to Stack Overflow!


                                                                        • Please be sure to answer the question. Provide details and share your research!

                                                                        But avoid



                                                                        • Asking for help, clarification, or responding to other answers.

                                                                        • Making statements based on opinion; back them up with references or personal experience.


                                                                        To learn more, see our tips on writing great answers.




                                                                        draft saved


                                                                        draft discarded














                                                                        StackExchange.ready(
                                                                        function () {
                                                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f10845998%2fi-forgot-the-password-i-entered-during-postgres-installation%23new-answer', 'question_page');
                                                                        }
                                                                        );

                                                                        Post as a guest















                                                                        Required, but never shown





















































                                                                        Required, but never shown














                                                                        Required, but never shown












                                                                        Required, but never shown







                                                                        Required, but never shown

































                                                                        Required, but never shown














                                                                        Required, but never shown












                                                                        Required, but never shown







                                                                        Required, but never shown







                                                                        Popular posts from this blog

                                                                        Guess what letter conforming each word

                                                                        Port of Spain

                                                                        Run scheduled task as local user group (not BUILTIN)