我忘记或者在安装过程中输入了PostgreSQL默认用户的密码。我似乎不能运行它,我得到以下错误:

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

是否有方法重置密码或如何创建具有超级用户权限的新用户?

我是PostgreSQL的新手,只是第一次安装它。我正在尝试使用Ruby on Rails,我正在运行Mac OS X v10.7 (Lion)。


当前回答

按照步骤1找到最佳答案。

如果你使用Windows操作系统,这是我的补充。只遵循第1步,然后在web上打开pgAdmin或postgres,并单击顶部导航上的file。单击reset layout,最后重新加载应用程序。不管你输入什么密码都可以。我用的是1234。

其他回答

我这么做是为了解决同样的问题:

在终端上用gedit编辑器打开pg_hba.conf文件:

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

它会要求输入密码。输入admin登录密码。 这将打开gedit文件。粘贴如下一行:

host  all   all  127.0.0.1/32  trust
just below -

# Database administrative login by Unix domain socket

保存并关闭它。

关闭终端,重新打开并运行如下命令:

psql -U postgres

现在您将进入psql控制台。

现在输入这个来修改密码:

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

如果它说用户不存在,那么使用CREATE而不是ALTER。

最后,删除粘贴到pg_hba中的特定行并保存它。

如果你在Windows上,你可以直接运行

net user postgres postgres

并以postgres/postgres用户/密码登录PostgreSQL。

pg_hba.conf (C:\Program Files\PostgreSQL\9.3\data)文件在给出这些答案后发生了变化。在Windows中,对我有用的是打开文件并将METHOD从md5更改为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

然后,我使用pgAdmin III在没有密码的情况下登录,通过“文件→更改密码”菜单修改了用户postgres的密码。

对于Windows(这对我很有帮助):

这就是我要参考的文档:如何重置PostgreSQL密码?

Open your cmd and go to C:\Program Files\PostgreSQL\12\data. This is usually the right path. You might have it stored somewhere else. Note that, if you have a different PostgreSQL version, there will be a different number. That doesn't matter. Find a pg_hba.conf file and copy it to somewhere else (that way you will have an unmodified version of this file, so you will be able to look at it after we make some changes) Open the pg_hba.conf file (not the backup, but the original) Find the multiple lines that start with host near the bottom of the file: host all all 127.0.0.1/32 md5 host all all ::1/128 md5 host replication all 127.0.0.1/32 md5 host replication all ::1/128 md5 Replace md5 with trust: host all all 127.0.0.1/32 trust host all all ::1/128 trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust Close this file Go to your search bar on windows and open Services app. Find postgres and restart it. Picture of services app Write cd.. in cmd and then cd bin. Your path should be C:\Program Files\PostgreSQL\12\bin Enter: psql -U postgres -h localhost Enter: ALTER USER postgres with password '<your new password>';Make sure that you include ; at the end “ALTER ROLE” should be displayed as an indication that the previous line was executed successfully Open original pg_hba.conf file and change back from trust to md5 Restart the server with Services app as before

当从命令行连接到PostgreSQL时,不要忘记添加-h localhost作为命令行参数。如果不是,PostgreSQL将尝试使用PEER认证模式进行连接。

下面显示了密码重置、使用PEER身份验证登录失败和使用TCP连接登录成功。

# 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

失败:

# psql -U postgres -W

Password for user postgres:
psql: FATAL:  Peer authentication failed for user "postgres"

使用-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=#