postgresql 我忘记了在安装 postgres 时输入的密码

I forgot the password I entered during postgres installation

提问by hilarl

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:

我忘记或错误输入了 Postgres 默认用户的密码（在安装过程中）。我似乎无法运行它，并收到以下错误：

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.

我是 Postgres 的新手，只是第一次安装它。我正在尝试将它与 Rails 一起使用，并且正在运行 Mac OS X Lion。

回答by Arsen7

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):

1. 找到文件pg_hba.conf- 它可能位于，例如在/etc/postgresql-9.1/pg_hba.conf.

   cd /etc/postgresql-9.1/

2. 把它备份

   cp pg_hba.conf pg_hba.conf-backup

3. 放置以下行（作为第一行未注释行，或作为唯一行）：

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.

对于下面的所有事件（本地和主机），如果您没有复制部分，则必须按照以下方式进行更改，不应存在 MD5 或 Peer 认证。

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 postgressince you are resetting postgresuser)

   ALTER USER my_user_name with password 'my_secure_password';

4. Restore the old pg_hba.confas it is very dangerousto 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

4. 重启你的 PostgreSQL 服务器（例如，在 Linux 上：）

   sudo /etc/init.d/postgresql restart

   如果服务（守护进程）没有开始在日志文件中报告：

   此版本不支持本地连接

   你应该改变

   local all all trust

   到

   host all all 127.0.0.1/32 trust

5. 您现在可以作为任何用户进行连接。以超级用户身份连接postgres（请注意，安装中的超级用户名可能不同。pgsql例如，在某些系统中，它称为。）

   psql -U postgres

   或者

   psql -h 127.0.0.1 -U postgres

   （请注意，使用第一个命令，您将不会始终与本地主机连接）

6. 重置密码（“替换为my_user_name Postgres的，因为你是重置Postgres的用户）

   ALTER USER my_user_name with password 'my_secure_password';

7. 恢复旧的，pg_hba.conf因为留在周围非常危险

   cp pg_hba.conf-backup pg_hba.conf

8. 重新启动服务器，以便安全运行 pg_hba.conf

   sudo /etc/init.d/postgresql restart

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

关于该 pg_hba 文件的进一步阅读：http: //www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

回答by SaeX

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

从命令行连接到 postgres 时，不要忘记添加-h localhostas 命令行参数。如果没有，postgres 将尝试使用 PEER 身份验证模式进行连接。

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

下面显示了密码重置、使用 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

Failing:

失败：

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

Working with -h localhost:

与-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=#

回答by SaiyanGirl

The pg_hba.conf(C:\Program Files\PostgreSQL\9.3\data) file has changed since these answers were given. What worked for me, in Windows, is to open the file and change the METHODfrom md5to trust:

自从给出这些答案以来，pg_hba.conf( C:\Program Files\PostgreSQL\9.3\data) 文件已更改。在 Windows 中，对我有用的是打开文件并将METHODfrom更改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

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

然后，使用 pgAdmin III，我不使用密码登录并postgres'通过转到更改用户密码File -> Change Password

回答by Daniel

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

请注意，在 Linux 上您可以简单地运行sudo su - postgres成为 postgres 用户，然后使用 psql 更改所需的内容。

回答by Ray Hunter

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

   local   all             postgres                                md5
   

2. Edit the line and change md5at the end to trustand save the file

3. Reload the postgresqlservice

   $ sudo service postgresql reload
   

4. This will load the configuration files. Now you can modify the postgresuser by logging into the psqlshell

   $ psql -U postgres
   

5. Update the postgresuser's password

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

6. Edit the file /etc/postgresql/<version>/main/pg_hba.confand change trustback to md5and save the file

7. Reload the postgresqlservice

   $ sudo service postgresql reload
   

8. Verify that the password change is working

   $ psql -U postgres -W
   

1. 编辑文件/etc/postgresql/<version>/main/pg_hba.conf并找到以下行：

   local   all             postgres                                md5
   

2. 编辑该行并md5在末尾更改为trust并保存文件

3. 重新加载postgresql服务

   $ sudo service postgresql reload
   

4. 这将加载配置文件。现在您可以postgres通过登录psqlshell来修改用户

   $ psql -U postgres
   

5. 更新postgres用户密码

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

6. 编辑文件/etc/postgresql/<version>/main/pg_hba.conf并改trust回md5并保存文件

7. 重新加载postgresql服务

   $ sudo service postgresql reload
   

8. 验证密码更改是否有效

   $ psql -U postgres -W
   

回答by Nathan Wailes

I was just having this problem on Windows 10 and the issue in my case was that I was just running psqland it was defaulting to trying to log in with my Windows username ("Nathan"), but there was no PostgreSQL user with that name, and it wasn't telling me that.

我只是在 Windows 10 上遇到了这个问题，我的问题是我只是在运行psql，它默认尝试使用我的 Windows 用户名（“Nathan”）登录，但没有具有该名称的 PostgreSQL 用户，它并没有告诉我。

So the solution was to run psql -U postgresrather than just psql, and then the password I entered at installation worked.

所以解决方案是运行psql -U postgres而不仅仅是psql，然后我在安装时输入的密码起作用。

回答by Bidhan Majhi

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

为最新的 postgres 版本 (>10) 添加 Windows 用户的答案，

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

去你的postgres安装位置，搜索pg_hba.conf，你会在..\postgres\data\pg_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,

将方法从 md5 更改为信任，

# 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,

现在转到您的 SQL Shell(PSQL) 并将所有内容留空，

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

现在运行这条线， ALTER USER yourusername WITH SUPERUSER

Now you can leave the shell with \q

现在你可以用 \q 离开 shell

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

再次转到文件 pg_hba.conf 并再次将 METHOD 从 trust 更改为 md5，并保存它。

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

现在使用您的新用户和密码登录，您可以检查 \du 的属性。

回答by Inconnu

What I did to resolve the same problem was:

我为解决同样的问题所做的是：

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

从终端使用 gedit 编辑器打开 pg_hba.conf 文件：

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:

它会要求输入密码。输入您的管理员登录密码。这将使用文件打开 gedit。粘贴以下行：

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:

您现在将进入 psql 控制台。现在输入以下内容更改密码：

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

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

如果它说 user 不存在那么代替ALTERuse CREATE。

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

最后，删除您在 pg_hba 中粘贴的特定行并保存。

回答by cpunky

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.

对于 Windows 安装，将创建一个 Windows 用户。而“psql”使用这个用户连接到端口。如果您更改 PostgreSQL 用户的密码，它不会更改 Windows 密码。仅当您有权访问命令行时，下面的命令行 juste 才有效。

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

相反，您可以使用 Windows GUI 应用程序“c:\Windows\system32\lusrmgr.exe”。此应用程序管理由 Windows 创建的用户。因此，您现在可以修改密码。

回答by rahulnikhare

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%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

用户主目录中的文件 .pgpass 或 PGPASSFILE 引用的文件可以包含密码，如果连接需要密码（并且没有另外指定密码）。在 Microsoft Windows 上，该文件名为 %APPDATA%\postgresql\pgpass.conf（其中 %APPDATA% 指的是用户配置文件中的 Application Data 子目录）。

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.

（您可以通过复制上面的行并在其前面加上 # 来向文件添加提醒注释。）前四个字段中的每一个都可以是文字值或 *，它可以匹配任何内容。将使用与当前连接参数匹配的第一行的密码字段。（因此，当您使用通配符时，请首先放置更具体的条目。）如果条目需要包含 : 或 \，请使用 . 主机名 localhost 匹配来自本地机器的 TCP（主机名 localhost）和 Unix 域套接字（pghost 为空或默认套接字目录）连接。在备用服务器中，复制的数据库名称与与主服务器建立的流式复制连接相匹配。数据库字段的用处有限，因为用户对同一集群中的所有数据库具有相同的密码。

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.

在 Unix 系统上，.pgpass 上的权限必须禁止对世界或组的任何访问；通过命令 chmod 0600 ~/.pgpass 实现这一点。如果权限不那么严格，则该文件将被忽略。在 Microsoft Windows 上，假定文件存储在安全目录中，因此不进行特殊权限检查。