Ruby-on-rails 获取错误：尝试让 pgsql 与 rails 一起工作时，用户“postgres”的对等身份验证失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18664074/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails
提问by orderof1
I'm getting the error:
我收到错误：
FATAL: Peer authentication failed for user "postgres"
when I try to make postgres work with Rails.
当我尝试使 postgres 与 Rails 一起工作时。
Here's my pg_hba.conf, my database.yml, and a dump of the full trace.
这是我的pg_hba.conf、我的database.yml和完整跟踪的转储。
I changed authentication to md5 in pg_hba and tried different things, but none seem to work.
我在 pg_hba 中将身份验证更改为 md5 并尝试了不同的方法，但似乎都不起作用。
I also tried creating a new user and database as per Rails 3.2, FATAL: Peer authentication failed for user (PG::Error)
我还尝试根据Rails 3.2创建新用户和数据库，致命：用户对等身份验证失败（PG::Error）
But they don't show up on pgadmin or even when I run sudo -u postgres psql -l.
但是它们不会出现在 pgadmin 上，甚至在我运行sudo -u postgres psql -l.
Any idea where I'm going wrong?
知道我哪里出错了吗？
回答by Marcelo De Polli
The problem is still your pg_hba.conffile (/etc/postgresql/9.1/main/pg_hba.conf*).
问题仍然是您的pg_hba.conf文件 ( /etc/postgresql/9.1/main/pg_hba.conf*)。
This line:
这一行：
local all postgres peer
Should be:
应该：
local all postgres md5
* If you can't find this file, running locate pg_hba.confshould show you where the file is.
* 如果你找不到这个文件，运行locate pg_hba.conf应该会告诉你文件在哪里。
After altering this file, don't forget to restart your PostgreSQL server. If you're on Linux, that would be sudo service postgresql restart.
更改此文件后，不要忘记重新启动 PostgreSQL 服务器。如果你在 Linux 上，那就是sudo service postgresql restart.
These are brief descriptions of both options according to the official PostgreSQL docs on authentication methods.
这些是根据官方 PostgreSQL docs on authentication methods对这两个选项的简要说明。
Peer authentication
对等认证
The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.
对等身份验证方法的工作原理是从内核获取客户端的操作系统用户名并将其用作允许的数据库用户名（具有可选的用户名映射）。此方法仅在本地连接上受支持。
Password authentication
密码认证
The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.
If you are at all concerned about password "sniffing" attacks then md5 is preferred. Plain password should always be avoided if possible. However, md5 cannot be used with the db_user_namespace feature. If the connection is protected by SSL encryption then password can be used safely (though SSL certificate authentication might be a better choice if one is depending on using SSL).
基于密码的身份验证方法是 md5 和密码。除了通过连接发送密码的方式（分别是 MD5 散列和明文）之外，这些方法的操作类似。
如果您完全担心密码“嗅探”攻击，则首选 md5。如果可能，应始终避免使用普通密码。但是，md5 不能与 db_user_namespace 功能一起使用。如果连接受 SSL 加密保护，则可以安全地使用密码（但如果依赖于使用 SSL，则 SSL 证书身份验证可能是更好的选择）。
Sample location for pg_hba.conf:/etc/postgresql/9.1/main/pg_hba.conf
样品位置pg_hba.conf：/etc/postgresql/9.1/main/pg_hba.conf
回答by Arivarasan L
After installing Postgresql I did the below steps.
安装 Postgresql 后，我执行了以下步骤。
open the file
pg_hba.conffor Ubuntu it will be in/etc/postgresql/9.x/mainand change this line:local all postgres peer
to
local all postgres trust
Restart the server
$ sudo service postgresql restartLogin into psql and set your password
$ psql -U postgres db> ALTER USER postgres with password 'your-pass';Finally change the
pg_hba.conffromlocal all postgres trust
to
local all postgres md5
打开
pg_hba.conf它所在的 Ubuntu文件/etc/postgresql/9.x/main并更改此行：local all postgres peer
到
local all postgres trust
重启服务器
$ sudo service postgresql restart登录 psql 并设置密码
$ psql -U postgres db> ALTER USER postgres with password 'your-pass';最后改变
pg_hba.conf从local all postgres trust
到
local all postgres md5
After restarting the postgresql server, you can access it with your own password
重启postgresql服务器后，就可以用自己的密码访问了
Authentication methods details:
身份验证方法详细信息：
trust - anyone who can connect to the server is authorized to access the database
peer - use client's operating system user name as database user name to access it.
md5 - password-base authentication
信任 - 任何可以连接到服务器的人都有权访问数据库
peer - 使用客户端的操作系统用户名作为数据库用户名来访问它。
md5 - 基于密码的身份验证
for further reference check here
进一步参考检查here
回答by StylusEater
If you connect over localhost (127.0.0.1) you shouldn't experience that particular issue. I wouldn't muck much with the pg_hba.conf but instead I would adjust your connection string:
如果您通过 localhost (127.0.0.1) 进行连接，则不应遇到该特定问题。我不会对 pg_hba.conf 造成太大影响，但我会调整您的连接字符串：
psql -U someuser -h 127.0.0.1 database
where someuser is your user you're connecting as and database is the database your user has permission to connect to.
其中 someuser 是您要连接的用户，而 database 是您的用户有权连接的数据库。
Here is what I do on Debian to setup postgres:
这是我在 Debian 上设置 postgres 的操作：
http://www.postgresql.org/download/linux/debian/ (Wheezy 7.x)
as root …
root@www0:~# echo "deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main" >> /etc/apt/sources.list
root@www0:~# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
root@www0:~# apt-get update
root@www0:~# apt-get install postgresql-9.4
root@www0:~# su - postgres
postgres@www0:~$ createuser --interactive -P someuser
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
postgres@www0:~$ createdb -O someuser database
postgres@www0:~$ psql -U someuser -h 127.0.0.1 database
Enjoy!
享受！
回答by iamuser2
This has worked for me !!
这对我有用！！
sudo -u postgres psql
回答by d.danailov
If you have an issue, you need to locate your pg_hba.conf. The command is:
如果您有问题，则需要找到您的pg_hba.conf. 命令是：
find / -name 'pg_hba.conf' 2>/dev/null
find / -name 'pg_hba.conf' 2>/dev/null
and after that change the configuration file:
然后更改配置文件：
Postgresql 9.3
PostgreSQL 9.3
Postgresql 9.4
PostgreSQL 9.4
The next step is: Restarting your db instance:
下一步是： 重新启动您的数据库实例：
service postgresql-9.3 restart
service postgresql-9.3 restart
If you have any problems, you need to set password again:
如果您有任何问题，您需要重新设置密码：
ALTER USER db_user with password 'db_password';
ALTER USER db_user with password 'db_password';
回答by Taimoor Changaiz
- Go to this /etc/postgresql/9.x/main/and open pg_hba.conffile
- 转到此/etc/postgresql/9.x/main/并打开pg_hba.conf文件
In my case:
就我而言：
$> sudo nano /etc/postgresql/9.3/main/pg_hba.conf
- Replace peerwith md5
- 用md5替换peer
So this will be changed to:
所以这将更改为：
Database administrative login by Unix domain socket local all postgres peer
通过 Unix 域套接字本地所有 postgres peer 进行数据库管理登录
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
This:
这个：
Database administrative login by Unix domain socket local all postgres md5
通过 Unix 域套接字本地所有 postgres md5 的数据库管理登录
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
Then restart pg server:
$> sudo service postgresql restart
然后重启 pg 服务器：
$> sudo 服务 postgresql 重启
Below is list of METHODS used to connect with postgres:
以下是用于连接 postgres 的方法列表：
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert". Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.
Note:If you have not create you postgres user yet. Create that and now you can access postgres server using that user credentials.
注意：如果您还没有创建您的 postgres 用户。创建它，现在您可以使用该用户凭据访问 postgres 服务器。
TIP:If it does not work after postgres restart then close terminal and open again.
提示：如果 postgres 重启后它不起作用，请关闭终端并再次打开。
回答by Gihan Gamage
sudo psql --host=localhost --dbname=database-name --username=postgres
This solved my issue
这解决了我的问题
回答by nirvanastack
I had the same problem.
我有同样的问题。
The solution from depa is absolutely correct.
depa 的解决方案是绝对正确的。
Just make sure that u have a user configured to use PostgreSQL.
只要确保你有一个用户配置为使用 PostgreSQL。
Check the file:
检查文件：
$ ls /etc/postgresql/9.1/main/pg_hba.conf -l
The permission of this file should be given to the user you have registered your psql with.
该文件的权限应该授予您注册 psql 的用户。
Further. If you are good till now..
更远。如果你到现在还好..
Update as per @depa's instructions.
按照@depa 的说明进行更新。
i.e.
IE
$ sudo nano /etc/postgresql/9.1/main/pg_hba.conf
and then make changes.
然后进行更改。
回答by ?smund
If you want to keep the default config but want md5 authentication with socket connection for one specific user/db connection, add a "local" line BEFORE the "local all/all" line:
如果您想保留默认配置，但希望对一个特定用户/数据库连接使用套接字连接进行 md5 身份验证，请在“local all/all”行之前添加“local”行：
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local username dbname md5 # <-- this line
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
回答by Luca Marletta
I was moving data directory on a cloned server and having troubles to login as postgres. Resetting postgres password like this worked for me.
我正在克隆的服务器上移动数据目录，并且无法以 postgres 身份登录。像这样重置 postgres 密码对我有用。
root# su postgres
postgres$ psql -U postgres
psql (9.3.6)
Type "help" for help.
postgres=#\password
Enter new password:
Enter it again:
postgres=#
