Ruby-on-rails Rails：致命 - 用户的对等身份验证失败（PG::Error）

Rails: FATAL - Peer authentication failed for user (PG::Error)

提问by simo

I am running my development on Ubuntu 11.10, and RubyMine

我在 Ubuntu 11.10 和 RubyMine 上运行我的开发

Here is my development settings for the database.yml: which RubyMine created for me

这是我对 database.yml 的开发设置：RubyMine 为我创建的

development:
  adapter: postgresql
  encoding: unicode
  database: mydb_development
  pool: 5
  username: myuser
  password:

when I try to run the app, I get this error below, it seems that I didn't create a 'project' user yet, but, how can I create a user and grant it a database in postgres ? if this is the problem, then, what is the recommended tool to use in Ubuntu for this task ? if this is not the problem, then, please advice.

当我尝试运行该应用程序时，出现以下错误，似乎我还没有创建“项目”用户，但是，如何创建用户并在 postgres 中为其授予数据库？如果这是问题所在，那么在 Ubuntu 中推荐使用什么工具来完成这项任务？如果这不是问题，那么，请指教。

Exiting
/home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1194:in `initialize': FATAL:  Peer authentication failed for user "project" (PG::Error)
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1194:in `new'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:1194:in `connect'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:329:in `initialize'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `new'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:28:in `postgresql_connection'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:303:in `new_connection'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:313:in `checkout_new_connection'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:237:in `block (2 levels) in checkout'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:232:in `loop'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:232:in `block in checkout'
    from /home/sam/.rvm/rubies/ruby-1.9.3-p0/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:229:in `checkout'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:95:in `connection'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:398:in `retrieve_connection'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_specification.rb:168:in `retrieve_connection'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/connection_adapters/abstract/connection_specification.rb:142:in `connection'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/model_schema.rb:308:in `clear_cache!'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activerecord-3.2.3/lib/active_record/railtie.rb:91:in `block (2 levels) in <class:Railtie>'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/callbacks.rb:418:in `_run__757346023__prepare__404863399__callbacks'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/callbacks.rb:405:in `__run_callback'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/callbacks.rb:385:in `_run_prepare_callbacks'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/callbacks.rb:81:in `run_callbacks'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/actionpack-3.2.3/lib/action_dispatch/middleware/reloader.rb:74:in `prepare!'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/actionpack-3.2.3/lib/action_dispatch/middleware/reloader.rb:48:in `prepare!'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/application/finisher.rb:47:in `block in <module:Finisher>'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/initializable.rb:30:in `instance_exec'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/initializable.rb:30:in `run'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/initializable.rb:55:in `block in run_initializers'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/initializable.rb:54:in `each'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/initializable.rb:54:in `run_initializers'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/application.rb:136:in `initialize!'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/railtie/configurable.rb:30:in `method_missing'
    from /home/sam/RubymineProjects/project/config/environment.rb:5:in `<top (required)>'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/dependencies.rb:251:in `require'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/dependencies.rb:251:in `block in require'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/dependencies.rb:236:in `load_dependency'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/activesupport-3.2.3/lib/active_support/dependencies.rb:251:in `require'
    from /home/sam/RubymineProjects/project/config.ru:4:in `block in <main>'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/rack-1.4.1/lib/rack/builder.rb:51:in `instance_eval'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/rack-1.4.1/lib/rack/builder.rb:51:in `initialize'
    from /home/sam/RubymineProjects/project/config.ru:1:in `new'
    from /home/sam/RubymineProjects/project/config.ru:1:in `<main>'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/rack-1.4.1/lib/rack/builder.rb:40:in `eval'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/rack-1.4.1/lib/rack/builder.rb:40:in `parse_file'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/rack-1.4.1/lib/rack/server.rb:200:in `app'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/commands/server.rb:46:in `app'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/rack-1.4.1/lib/rack/server.rb:301:in `wrapped_app'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/rack-1.4.1/lib/rack/server.rb:252:in `start'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/commands/server.rb:70:in `start'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/commands.rb:55:in `block in <top (required)>'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/commands.rb:50:in `tap'
    from /home/sam/.rvm/gems/ruby-1.9.3-p0@project/gems/railties-3.2.3/lib/rails/commands.rb:50:in `<top (required)>'
    from /home/sam/RubymineProjects/project/script/rails:6:in `require'
    from /home/sam/RubymineProjects/project/script/rails:6:in `<top (required)>'
    from -e:1:in `load'
    from -e:1:in `<main>'

Process finished with exit code 1

回答by Bent Cardan

If you installed postresql on your server then just host: localhost to database.yml, I usually throw it in around where it says pool: 5. Otherwise if it's not localhost definitely tell that app where to find its database.

如果你在你的服务器上安装了 postresql，那么只需将 host: localhost 放到 database.yml 中，我通常会把它放在它说 pool: 5 的地方。否则，如果它不是 localhost，那么肯定会告诉该应用程序在哪里可以找到它的数据库。

development:
  adapter: postgresql
  encoding: unicode
  database: kickrstack_development
  host: localhost
  pool: 5
  username: kickrstack
  password: secret

Make sure your user credentials are set correctly by creating a database and assigning ownership to your app's user to establish the connection. To create a new user in postgresql 9 run:

通过创建数据库并将所有权分配给应用程序的用户以建立连接，确保您的用户凭据设置正确。要在 postgresql 9 中创建一个新用户，请运行：

sudo -u postgres psql

set the postgresql user password if you haven't, it's just backslash password.

如果没有，请设置 postgresql 用户密码，它只是反斜杠密码。

postgres=# \password

Create a new user and password and the user's new database:

创建新用户和密码以及用户的新数据库：

postgres=# create user "guy_on_stackoverflow" with password 'keepitonthedl';
postgres=# create database "dcaclab_development" owner "guy_on_stackoverflow"; 

Now update your database.yml file after you've confirmed creating the database, user, password and set these privileges. Don't forget host: localhost.

在您确认创建数据库、用户、密码并设置这些权限后，现在更新您的 database.yml 文件。不要忘记主机：本地主机。

回答by Starkers

This is the most foolproof way to get your rails app working with postgres in the development environmentin Ubuntu 13.10.

这是让您的 rails 应用程序在 Ubuntu 13.10的开发环境中与 postgres 一起工作的最简单的方法。

1) Create rails app with postgres YAML and 'pg' gem in the Gemfile:

1) 在 Gemfile 中使用 postgres YAML 和 'pg' gem 创建 rails 应用程序：

$ rails new my_application -d postgresql

2) Give it some CRUD functionality. If you're just seeing if postgres works, create a scaffold:

2) 给它一些 CRUD 功能。如果您只是想看看 postgres 是否有效，请创建一个脚手架：

$ rails g scaffold cats name:string age:integer colour:string

3) As of rails 4.0.1the -d postgresqloption generates a YAML that doesn't include a host parameter. I found I needed this.Edit the development section and create the following parameters:

3）由于rails 4.0.1所述-d postgresql选项生成一个YAML不包括主机参数。我发现我需要这个。编辑 development 部分并创建以下参数：

encoding: UTF-8
host: localhost
database: my_application_development
username: thisismynewusername
password: thisismynewpassword 

Note the databaseparameter is for a database that doesn't exit yet, and the usernameand passwordare credentials for a role that doesn't exist either. We'll create those later on!

请注意，该database参数用于尚未退出的数据库，并且username和password是也不存在的角色的凭据。我们稍后会创建它们！

This is how config/database.ymlshould look (no shame in copypasting :D ):

这是config/database.yml应该的样子（复制粘贴并不可耻：D）：

development:
  adapter: postgresql
  pool: 5
  # these are our new parameters
  encoding: UTF-8
  database: my_application_development
  host: localhost
  username: thisismynewusername
  password: thisismynewpassword

test:
  # this won't work 
  adapter: postgresql
  encoding: unicode
  database: my_application_test
  pool: 5
  username: my_application
  password:

production:
  # this won't work 
  adapter: postgresql
  encoding: unicode
  database: my_application_production
  pool: 5
  username: my_application
  password:

4) Start the postgres shell with this command:

4) 使用以下命令启动 postgres shell：

$ psql

4a) You may get this error if your current user (as in your computer user) doesn't have a corresponding administration postgres role.

4a) 如果您的当前用户（如您的计算机用户）没有相应的管理 postgres 角色，您可能会收到此错误。

psql: FATAL:  role "your_username" does not exist

Now I've only installed postgres once, so I may be wrong here, but I think postgres automatically creates an administration role with the same credentials as the user you installed postgres as.

现在我只安装了 postgres 一次，所以我在这里可能是错的，但我认为 postgres 会自动创建一个管理角色，其凭据与您安装 postgres 的用户相同。

4b) So this means you need to change to the user that installed postgres to use the psql command and start the shell:

4b) 所以这意味着您需要更改为安装 postgres 的用户才能使用 psql 命令并启动 shell：

$ sudo su postgres

And then run

然后运行

$ psql

5) You'll know you're in the postgres shell because your terminal will look like this:

5) 你会知道你在 postgres shell 中，因为你的终端看起来像这样：

$ psql
psql (9.1.10)
Type "help" for help.

postgres=# 

6) Using the postgresql syntax, let's create the user we specified in config/database.yml's developmentsection:

6）使用PostgreSQL的语法，让我们创建我们指定的用户config/database.yml的发展部分：

postgres=# CREATE ROLE thisismynewusername WITH LOGIN PASSWORD 'thisismynewpassword';

Now, there's some subtleties here so let's go over them.

现在，这里有一些微妙之处，让我们来看看它们。

• The role's username, thisismynewusername, does nothave quotes of any kind around it
• Specify the keyword LOGINafter the WITH. If you don't, the role will still be created, but it won't be able to log in to the database!
• The role's password, thisismynewpassword, needs to be in single quotes. Not double quotes.
• Add a semi colon on the end ;)

• 该角色的用户名，thisismynewusername，没有周围有任何形式的报价
• 在WITH之后指定关键字LOGIN。如果不这样做，角色仍将被创建，但将无法登录到数据库！
• 角色的密码thisismynewpassword需要用单引号括起来。不是双引号。
• 在末尾添加一个分号 ;)

You should see this in your terminal:

您应该在终端中看到这一点：

postgres=#
CREATE ROLE
postgres=#

That means, "ROLE CREATED", but postgres' alerts seem to adopt the same imperative conventions of git hub.

这意味着“角色已创建”，但 postgres 的警报似乎采用了与 git hub 相同的命令式约定。

7) Now, still in the postgres shell, we need to create the database with the name we set in the YAML. Make the user we created in step 6 its owner:

7) 现在，仍然在 postgres shell 中，我们需要使用我们在 YAML 中设置的名称创建数据库。使我们在第 6 步中创建的用户成为其所有者：

postgres=# CREATE DATABASE my_application_development OWNER thisismynewusername;

You'll know if you were successful because you'll get the output:

你会知道你是否成功，因为你会得到输出：

CREATE DATABASE

8) Quit the postgres shell:

8) 退出 postgres shell：

\q

9) Now the moment of truth:

9）现在是关键时刻：

$ RAILS_ENV=development rake db:migrate

If you get this:

如果你得到这个：

==  CreateCats: migrating =================================================
-- create_table(:cats)
   -> 0.0028s
==  CreateCats: migrated (0.0028s) ========================================

Congratulations, postgres is working perfectly with your app.

恭喜，postgres 与您的应用程序完美配合。

9a) On my local machine, I kept getting a permission error. I can't remember it exactly, but it was an error along the lines of

9a) 在我的本地机器上，我不断收到权限错误。我记不清了，但这是一个错误

Can't access the files. Change permissions to 666.

Though I'd advise thinking very carefully about recursively setting write privaledges on a production machine, locally, I gave my whole app read write privileges like this:

虽然我建议非常仔细地考虑在生产机器上递归设置写入权限，但我在本地为我的整个应用程序提供了这样的读写权限：

9b) Climb up one directory level:

9b) 爬上一层目录：

$ cd ..

9c) Set the permissions of the my_application directory and all its contents to 666:

9c) 将 my_application 目录及其所有内容的权限设置为 666：

$ chmod -R 0666 my_application

9d) And run the migration again:

9d) 再次运行迁移：

$ RAILS_ENV=development rake db:migrate

==  CreateCats: migrating =================================================
-- create_table(:cats)
   -> 0.0028s
==  CreateCats: migrated (0.0028s) ========================================

Some tips and tricks if you muck up

如果你搞砸了，一些提示和技巧

Try these before restarting all of these steps:

在重新启动所有这些步骤之前尝试这些：

The mynewusername user doesn't have privileges to CRUD to the my_app_developmentdatabase? Drop the database and create it again with mynewusername as the owner:

mynewusername 用户没有 CRUD 到my_app_development数据库的权限？删除数据库并使用 mynewusername 作为所有者再次创建它：

1) Start the postgres shell:

1）启动postgres外壳：

$ psql

2) Drop the my_app_developmentdatabase. Be careful! Drop means utterly delete!

2) 删除my_app_development数据库。当心！Drop意味着彻底删除！

postgres=# DROP DATABASE my_app_development;

3) Recreate another my_app_developmentand make mynewusername the owner:

3) 重新创建另一个my_app_development并使 mynewusername 成为所有者：

postgres=# CREATE DATABASE my_application_development OWNER mynewusername;

4) Quit the shell:

4）退出外壳：

postgres=# \q

The mynewusernameuser can't log into the database? Think you wrote the wrong password in the YAML and can't quite remember the password you entered using the postgres shell? Simply alter the role with the YAML password:

该mynewusername用户不能登录到数据库？认为您在 YAML 中写了错误的密码并且不太记得您使用 postgres shell 输入的密码？只需使用 YAML 密码更改角色：

1) Open up your YAML, and copy the password to your clipboard:

1) 打开 YAML，将密码复制到剪贴板：

 development:
      adapter: postgresql
      pool: 5
      # these are our new parameters
      encoding: UTF-8
      database: my_application_development
      host: localhost
      username: thisismynewusername
      password: musthavebeenverydrunkwheniwrotethis

2) Start the postgres shell:

2）启动postgres shell：

$ psql    

3) Update mynewusername's password. Paste in the password, and remember to put single quotes around it:

3) 更新mynewusername密码。粘贴密码，记得用单引号括起来：

postgres=# ALTER ROLE mynewusername PASSWORD `musthavebeenverydrunkwheniwrotethis`;

4) Quit the shell:

4）退出外壳：

postgres=# \q

Trying to connect to localhost via a database viewer such as Dbeaver, and don't know what your postgres user's password is? Change it like this:

尝试通过数据库查看器（例如 Dbeaver）连接到 localhost，但不知道您的 postgres 用户密码是什么？像这样改变它：

1) Run passwdas a superuser:

1）passwd以超级用户身份运行：

$ sudo passwd postgres

2) Enter your accounts password for sudo(nothing to do with postgres):

2）输入您的帐户密码sudo（与postgres无关）：

[sudo] password for starkers: myaccountpassword

3) Create the postgres account's new passwod:

3）创建postgres账户的新密码：

Enter new UNIX password: databasesarefun
Retype new UNIX password: databasesarefun
passwd: password updated successfully

Getting this error message?:

收到此错误消息？：

Run `$ bin/rake db:create db:migrate` to create your database
$ rake db:create db:migrate
PG::InsufficientPrivilege: ERROR:  permission denied to create database

4) You need to give your user the ability to create databases. From the psql shell:

4) 您需要让您的用户能够创建数据库。从 psql 外壳：

ALTER ROLE thisismynewusername WITH CREATEDB

回答by Sumit Munot

For permanent solution:

对于永久解决方案：

The problem is with your pg_hba. This line:

问题出在你的 pg_hba 上。这一行：

local   all             postgres                                peer

Should be

应该

local   all             postgres                                md5

Then restart your postgresql server after changing this file.

然后在更改此文件后重新启动您的 postgresql 服务器。

If you're on Linux, command would be

如果你在 Linux 上，命令将是

sudo service postgresql restart

回答by Tarun Garg

I was facing same problem on Ubuntu machine so I removed this error by following some steps. Switch to postgres user

我在 Ubuntu 机器上遇到了同样的问题，所以我按照一些步骤删除了这个错误。切换到 postgres 用户

$ sudo su - postgres

it will ask for password and by default password is postgres

它会要求输入密码，默认密码是 postgres

After switch the user to postgres, open psql console

切换用户到postgres后，打开psql控制台

$ psql

so check the version of postgres if multiple versions are available

因此，如果有多个版本可用，请检查 postgres 的版本

psql=# select VERSION();

PostgreSQL 9.1.13 on x86_64-unk....         # so version is 9.1

Now Open postgres user

现在开放 postgres user

vim /etc/postgresql/9.1/main/pg_hba.conf

9.1is version return form upper command

9.1是版本返回表单上层命令

and replace

并替换

local   all             postgres                                peer

to

到

local   all             postgres                                md5

Restart the service

重启服务

sudo service postgresql restart

I write steps on my blog also

我也在我的博客上写了步骤

http://tarungarg402.blogspot.in/2014/10/set-up-postgresql-on-ubuntu.html

http://tarungarg402.blogspot.in/2014/10/set-up-postgresql-on-ubuntu.html

回答by Rastee

You can go to your /var/lib/pgsql/data/pg_hba.conf file and add trust in place of Ident It worked for me.

您可以转到您的 /var/lib/pgsql/data/pg_hba.conf 文件并添加信任来代替 Ident 它对我有用。

local   all all trust
host    all 127.0.0.1/32    trust

For further details refer to this issue Ident authentication failed for user

有关更多详细信息，请参阅此问题 Ident authentication failed for user

回答by ispirett

Adding host: localhostwas the magic for me

添加host: localhost对我来说是神奇的

development:
  adapter: postgresql
  database: database_name_here
  host: localhost
  username: user_name_here

回答by Promise Preston

I also faced this same issue while working in my development environment, the problem was that I left host: localhostcommented out in the config/database.ymlfile.

在我的开发环境中工作时，我也遇到了同样的问题，问题是我host: localhost在config/database.yml文件中注释掉了。

So my application could not connect to the PostgreSQL database, simply uncommenting it solved the issue.

所以我的应用程序无法连接到 PostgreSQL 数据库，只需取消注释即可解决问题。

development:
  <<: *default
  database: database_name

  username: database_username 

  password: database_password

  host: localhost

That's all.

就这样。

I hope this helps

我希望这有帮助

回答by David Winiecki

If you get that error message (Peer authentication failed for user (PG::Error)) when running unit tests, make sure the test database exists.

如果Peer authentication failed for user (PG::Error)在运行单元测试时收到该错误消息 ( )，请确保测试数据库存在。